SET @save_sql_mode=@@sql_mode;
set SQL_MODE='';
create table ADDDATE(a int);
drop table ADDDATE;
create table ADDDATE (a int);
drop table ADDDATE;
create table BIT_AND(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_AND(a int)' at line 1
create table BIT_AND (a int);
drop table BIT_AND;
create table BIT_OR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_OR(a int)' at line 1
create table BIT_OR (a int);
drop table BIT_OR;
create table BIT_XOR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_XOR(a int)' at line 1
create table BIT_XOR (a int);
drop table BIT_XOR;
create table CAST(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST(a int)' at line 1
create table CAST (a int);
drop table CAST;
create table COUNT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(a int)' at line 1
create table COUNT (a int);
drop table COUNT;
create table CURDATE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURDATE(a int)' at line 1
create table CURDATE (a int);
drop table CURDATE;
create table CURTIME(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURTIME(a int)' at line 1
create table CURTIME (a int);
drop table CURTIME;
create table DATE_ADD(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_ADD(a int)' at line 1
create table DATE_ADD (a int);
drop table DATE_ADD;
create table DATE_SUB(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_SUB(a int)' at line 1
create table DATE_SUB (a int);
drop table DATE_SUB;
create table EXTRACT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTRACT(a int)' at line 1
create table EXTRACT (a int);
drop table EXTRACT;
create table GROUP_CONCAT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(a int)' at line 1
create table GROUP_CONCAT (a int);
drop table GROUP_CONCAT;
create table GROUP_UNIQUE_USERS(a int);
drop table GROUP_UNIQUE_USERS;
create table GROUP_UNIQUE_USERS (a int);
drop table GROUP_UNIQUE_USERS;
create table MAX(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX(a int)' at line 1
create table MAX (a int);
drop table MAX;
create table MID(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MID(a int)' at line 1
create table MID (a int);
drop table MID;
create table MIN(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MIN(a int)' at line 1
create table MIN (a int);
drop table MIN;
create table NOW(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW(a int)' at line 1
create table NOW (a int);
drop table NOW;
create table POSITION(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'POSITION(a int)' at line 1
create table POSITION (a int);
drop table POSITION;
create table SESSION_USER(a int);
drop table SESSION_USER;
create table SESSION_USER (a int);
drop table SESSION_USER;
create table STD(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STD(a int)' at line 1
create table STD (a int);
drop table STD;
create table STDDEV(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV(a int)' at line 1
create table STDDEV (a int);
drop table STDDEV;
create table STDDEV_POP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_POP(a int)' at line 1
create table STDDEV_POP (a int);
drop table STDDEV_POP;
create table STDDEV_SAMP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_SAMP(a int)' at line 1
create table STDDEV_SAMP (a int);
drop table STDDEV_SAMP;
create table SUBDATE(a int);
drop table SUBDATE;
create table SUBDATE (a int);
drop table SUBDATE;
create table SUBSTR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR(a int)' at line 1
create table SUBSTR (a int);
drop table SUBSTR;
create table SUBSTRING(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTRING(a int)' at line 1
create table SUBSTRING (a int);
drop table SUBSTRING;
create table SUM(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(a int)' at line 1
create table SUM (a int);
drop table SUM;
create table SYSDATE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSDATE(a int)' at line 1
create table SYSDATE (a int);
drop table SYSDATE;
create table SYSTEM_USER(a int);
drop table SYSTEM_USER;
create table SYSTEM_USER (a int);
drop table SYSTEM_USER;
create table TRIM(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIM(a int)' at line 1
create table TRIM (a int);
drop table TRIM;
create table UNIQUE_USERS(a int);
drop table UNIQUE_USERS;
create table UNIQUE_USERS (a int);
drop table UNIQUE_USERS;
create table VARIANCE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARIANCE(a int)' at line 1
create table VARIANCE (a int);
drop table VARIANCE;
create table VAR_POP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_POP(a int)' at line 1
create table VAR_POP (a int);
drop table VAR_POP;
create table VAR_SAMP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_SAMP(a int)' at line 1
create table VAR_SAMP (a int);
drop table VAR_SAMP;
set SQL_MODE='IGNORE_SPACE';
create table ADDDATE(a int);
drop table ADDDATE;
create table ADDDATE (a int);
drop table ADDDATE;
create table BIT_AND(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_AND(a int)' at line 1
create table BIT_AND (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_AND (a int)' at line 1
create table BIT_OR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_OR(a int)' at line 1
create table BIT_OR (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_OR (a int)' at line 1
create table BIT_XOR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_XOR(a int)' at line 1
create table BIT_XOR (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT_XOR (a int)' at line 1
create table CAST(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST(a int)' at line 1
create table CAST (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST (a int)' at line 1
create table COUNT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(a int)' at line 1
create table COUNT (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT (a int)' at line 1
create table CURDATE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURDATE(a int)' at line 1
create table CURDATE (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURDATE (a int)' at line 1
create table CURTIME(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURTIME(a int)' at line 1
create table CURTIME (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURTIME (a int)' at line 1
create table DATE_ADD(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_ADD(a int)' at line 1
create table DATE_ADD (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_ADD (a int)' at line 1
create table DATE_SUB(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_SUB(a int)' at line 1
create table DATE_SUB (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_SUB (a int)' at line 1
create table EXTRACT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTRACT(a int)' at line 1
create table EXTRACT (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTRACT (a int)' at line 1
create table GROUP_CONCAT(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(a int)' at line 1
create table GROUP_CONCAT (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT (a int)' at line 1
create table GROUP_UNIQUE_USERS(a int);
drop table GROUP_UNIQUE_USERS;
create table GROUP_UNIQUE_USERS (a int);
drop table GROUP_UNIQUE_USERS;
create table MAX(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX(a int)' at line 1
create table MAX (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX (a int)' at line 1
create table MID(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MID(a int)' at line 1
create table MID (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MID (a int)' at line 1
create table MIN(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MIN(a int)' at line 1
create table MIN (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MIN (a int)' at line 1
create table NOW(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW(a int)' at line 1
create table NOW (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW (a int)' at line 1
create table POSITION(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'POSITION(a int)' at line 1
create table POSITION (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'POSITION (a int)' at line 1
create table SESSION_USER(a int);
drop table SESSION_USER;
create table SESSION_USER (a int);
drop table SESSION_USER;
create table STD(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STD(a int)' at line 1
create table STD (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STD (a int)' at line 1
create table STDDEV(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV(a int)' at line 1
create table STDDEV (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV (a int)' at line 1
create table STDDEV_POP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_POP(a int)' at line 1
create table STDDEV_POP (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_POP (a int)' at line 1
create table STDDEV_SAMP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_SAMP(a int)' at line 1
create table STDDEV_SAMP (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STDDEV_SAMP (a int)' at line 1
create table SUBDATE(a int);
drop table SUBDATE;
create table SUBDATE (a int);
drop table SUBDATE;
create table SUBSTR(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR(a int)' at line 1
create table SUBSTR (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR (a int)' at line 1
create table SUBSTRING(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTRING(a int)' at line 1
create table SUBSTRING (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTRING (a int)' at line 1
create table SUM(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(a int)' at line 1
create table SUM (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM (a int)' at line 1
create table SYSDATE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSDATE(a int)' at line 1
create table SYSDATE (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSDATE (a int)' at line 1
create table SYSTEM_USER(a int);
drop table SYSTEM_USER;
create table SYSTEM_USER (a int);
drop table SYSTEM_USER;
create table TRIM(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIM(a int)' at line 1
create table TRIM (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIM (a int)' at line 1
create table UNIQUE_USERS(a int);
drop table UNIQUE_USERS;
create table UNIQUE_USERS (a int);
drop table UNIQUE_USERS;
create table VARIANCE(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARIANCE(a int)' at line 1
create table VARIANCE (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARIANCE (a int)' at line 1
create table VAR_POP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_POP(a int)' at line 1
create table VAR_POP (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_POP (a int)' at line 1
create table VAR_SAMP(a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_SAMP(a int)' at line 1
create table VAR_SAMP (a int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VAR_SAMP (a int)' at line 1
#
# Test "UNIQUE KEY" and "UNIQUE" "KEY" grammar ambiguity
#
CREATE TABLE t1 (i INT KEY);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t2 (i INT UNIQUE);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `i` int DEFAULT NULL,
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE t3 (i INT UNIQUE KEY);
# Should output "UNIQUE KEY `i` (`i`)" only:
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `i` int DEFAULT NULL,
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1, t2, t3;
#
DROP TABLE IF EXISTS table_25930_a;
DROP TABLE IF EXISTS table_25930_b;
SET SQL_MODE = 'ANSI_QUOTES';
CREATE TABLE table_25930_a ( "blah" INT );
CREATE TABLE table_25930_b SELECT "blah" - 1 FROM table_25930_a;
desc table_25930_b;
Field	Type	Null	Key	Default	Extra
"blah" - 1	bigint	YES		NULL	
DROP TABLE table_25930_a;
DROP TABLE table_25930_b;
SET @@sql_mode=@save_sql_mode;
DROP PROCEDURE IF EXISTS p26030;
select "non terminated"$$
non terminated
non terminated
select "terminated";$$
terminated
terminated
select "non terminated, space"      $$
non terminated, space
non terminated, space
select "terminated, space";      $$
terminated, space
terminated, space
select "non terminated, comment" /* comment */$$
non terminated, comment
non terminated, comment
select "terminated, comment"; /* comment */$$
terminated, comment
terminated, comment
select "stmt 1";select "stmt 2 non terminated"$$
stmt 1
stmt 1
stmt 2 non terminated
stmt 2 non terminated
select "stmt 1";select "stmt 2 terminated";$$
stmt 1
stmt 1
stmt 2 terminated
stmt 2 terminated
select "stmt 1";select "stmt 2 non terminated, space"      $$
stmt 1
stmt 1
stmt 2 non terminated, space
stmt 2 non terminated, space
select "stmt 1";select "stmt 2 terminated, space";      $$
stmt 1
stmt 1
stmt 2 terminated, space
stmt 2 terminated, space
select "stmt 1";select "stmt 2 non terminated, comment" /* comment */$$
stmt 1
stmt 1
stmt 2 non terminated, comment
stmt 2 non terminated, comment
select "stmt 1";select "stmt 2 terminated, comment"; /* comment */$$
stmt 1
stmt 1
stmt 2 terminated, comment
stmt 2 terminated, comment
select "stmt 1";             select "space, stmt 2"$$
stmt 1
stmt 1
space, stmt 2
space, stmt 2
select "stmt 1";/* comment */select "comment, stmt 2"$$
stmt 1
stmt 1
comment, stmt 2
comment, stmt 2
DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() BEGIN SELECT 1; END; CALL p26030()
$$
1
1
DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() SELECT 1; CALL p26030()
$$
1
1
DROP PROCEDURE p26030;
select pi(3.14);
ERROR 42000: Incorrect parameter count in the call to native function 'pi'
select tan();
ERROR 42000: Incorrect parameter count in the call to native function 'tan'
select tan(1, 2);
ERROR 42000: Incorrect parameter count in the call to native function 'tan'
select makedate(1);
ERROR 42000: Incorrect parameter count in the call to native function 'makedate'
select makedate(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'makedate'
select maketime();
ERROR 42000: Incorrect parameter count in the call to native function 'maketime'
select maketime(1);
ERROR 42000: Incorrect parameter count in the call to native function 'maketime'
select maketime(1, 2);
ERROR 42000: Incorrect parameter count in the call to native function 'maketime'
select maketime(1, 2, 3, 4);
ERROR 42000: Incorrect parameter count in the call to native function 'maketime'
select atan();
ERROR 42000: Incorrect parameter count in the call to native function 'atan'
select atan2(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'atan2'
select concat();
ERROR 42000: Incorrect parameter count in the call to native function 'concat'
select concat("foo");
concat("foo")
foo
select concat_ws();
ERROR 42000: Incorrect parameter count in the call to native function 'concat_ws'
select concat_ws("foo");
ERROR 42000: Incorrect parameter count in the call to native function 'concat_ws'
select elt();
ERROR 42000: Incorrect parameter count in the call to native function 'elt'
select elt(1);
ERROR 42000: Incorrect parameter count in the call to native function 'elt'
select export_set();
ERROR 42000: Incorrect parameter count in the call to native function 'export_set'
select export_set("p1");
ERROR 42000: Incorrect parameter count in the call to native function 'export_set'
select export_set("p1", "p2");
ERROR 42000: Incorrect parameter count in the call to native function 'export_set'
select export_set("p1", "p2", "p3", "p4", "p5", "p6");
ERROR 42000: Incorrect parameter count in the call to native function 'export_set'
select field();
ERROR 42000: Incorrect parameter count in the call to native function 'field'
select field("p1");
ERROR 42000: Incorrect parameter count in the call to native function 'field'
select from_unixtime();
ERROR 42000: Incorrect parameter count in the call to native function 'from_unixtime'
select from_unixtime(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'from_unixtime'
select unix_timestamp(1, 2);
ERROR 42000: Incorrect parameter count in the call to native function 'unix_timestamp'
select greatest();
ERROR 42000: Incorrect parameter count in the call to native function 'greatest'
select greatest(12);
ERROR 42000: Incorrect parameter count in the call to native function 'greatest'
select last_insert_id(1, 2);
ERROR 42000: Incorrect parameter count in the call to native function 'last_insert_id'
select least();
ERROR 42000: Incorrect parameter count in the call to native function 'least'
select least(12);
ERROR 42000: Incorrect parameter count in the call to native function 'least'
select locate();
ERROR 42000: Incorrect parameter count in the call to native function 'locate'
select locate(1);
ERROR 42000: Incorrect parameter count in the call to native function 'locate'
select locate(1, 2, 3, 4);
ERROR 42000: Incorrect parameter count in the call to native function 'locate'
select log();
ERROR 42000: Incorrect parameter count in the call to native function 'log'
select log(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'log'
select make_set();
ERROR 42000: Incorrect parameter count in the call to native function 'make_set'
select make_set(1);
ERROR 42000: Incorrect parameter count in the call to native function 'make_set'
select source_pos_wait();
ERROR 42000: Incorrect parameter count in the call to native function 'source_pos_wait'
select source_pos_wait(1);
ERROR 42000: Incorrect parameter count in the call to native function 'source_pos_wait'
select source_pos_wait('binlog.999999', 4, -1);
select rand(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'rand'
select round(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'round'
select yearweek();
ERROR 42000: Incorrect parameter count in the call to native function 'yearweek'
select yearweek(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'yearweek'
select abs(3);
abs(3)
3
select abs(3 AS three);
ERROR 42000: Incorrect parameters in the call to native function 'abs'
select abs(3 three);
ERROR 42000: Incorrect parameters in the call to native function 'abs'
select abs(3 AS "three");
ERROR 42000: Incorrect parameters in the call to native function 'abs'
select abs(3 "three");
ERROR 42000: Incorrect parameters in the call to native function 'abs'
set @bar="bar";
set @foobar="foobar";
select instr("foobar", "bar");
instr("foobar", "bar")
4
select instr("foobar" AS p1, "bar");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar" p1, "bar");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar" AS "p1", "bar");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar" "p1", "bar");
instr("foobar" "p1", "bar")
4
select instr(@foobar "p1", "bar");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar", "bar" AS p2);
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar", "bar" p2);
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar", "bar" AS "p2");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar", "bar" "p2");
instr("foobar", "bar" "p2")
0
select instr("foobar", @bar "p2");
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select instr("foobar" AS p1, "bar" AS p2);
ERROR 42000: Incorrect parameters in the call to native function 'instr'
select conv(255, 10, 16);
conv(255, 10, 16)
FF
select conv(255 AS p1, 10, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255 p1, 10, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255 AS "p1", 10, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255 "p1", 10, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10 AS p2, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10 p2, 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10 AS "p2", 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10 "p2", 16);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10, 16 AS p3);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10, 16 p3);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10, 16 AS "p3");
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255, 10, 16 "p3");
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select conv(255 AS p1, 10 AS p2, 16 AS p3);
ERROR 42000: Incorrect parameters in the call to native function 'conv'
select atan(10);
atan(10)
1.4711276743037347
select atan(10 AS p1);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 p1);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 AS "p1");
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 "p1");
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10, 20);
atan(10, 20)
0.4636476090008061
select atan(10 AS p1, 20);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 p1, 20);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 AS "p1", 20);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 "p1", 20);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10, 20 AS p2);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10, 20 p2);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10, 20 AS "p2");
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10, 20 "p2");
ERROR 42000: Incorrect parameters in the call to native function 'atan'
select atan(10 AS p1, 20 AS p2);
ERROR 42000: Incorrect parameters in the call to native function 'atan'
DROP TABLE IF EXISTS t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
22:10:00
SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE;
STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE
22:01:00
SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
"1997-12-31 23:59:59" + INTERVAL 1 SECOND
1998-01-01 00:00:00
SELECT 1 + INTERVAL(1,0,1,2) + 1;
1 + INTERVAL(1,0,1,2) + 1
4
SELECT INTERVAL(1^1,0,1,2) + 1;
INTERVAL(1^1,0,1,2) + 1
2
SELECT INTERVAL(1,0+1,2,3) * 5.5;
INTERVAL(1,0+1,2,3) * 5.5
5.5
SELECT INTERVAL(3,3,1+3,4+4) / 0.5;
INTERVAL(3,3,1+3,4+4) / 0.5
2.0000
SELECT (INTERVAL(1,0,1,2) + 5) * 7 + INTERVAL(1,0,1,2) / 2;
(INTERVAL(1,0,1,2) + 5) * 7 + INTERVAL(1,0,1,2) / 2
50.0000
SELECT INTERVAL(1,0,1,2) + 1, 5 * INTERVAL(1,0,1,2);
INTERVAL(1,0,1,2) + 1	5 * INTERVAL(1,0,1,2)
3	10
SELECT INTERVAL(0,(1*5)/2) + INTERVAL(5,4,3);
INTERVAL(0,(1*5)/2) + INTERVAL(5,4,3)
2
SELECT 1^1 + INTERVAL 1+1 SECOND & 1 + INTERVAL 1+1 SECOND;
1^1 + INTERVAL 1+1 SECOND & 1 + INTERVAL 1+1 SECOND
NULL
SELECT 1%2 - INTERVAL 1^1 SECOND | 1%2 - INTERVAL 1^1 SECOND;
1%2 - INTERVAL 1^1 SECOND | 1%2 - INTERVAL 1^1 SECOND
NULL
CREATE TABLE t1 (a INT, b DATETIME);
INSERT INTO t1 VALUES (INTERVAL(3,2,1) + 1, "1997-12-31 23:59:59" + INTERVAL 1 SECOND);
SELECT * FROM t1 WHERE a = INTERVAL(3,2,1) + 1;
a	b
3	1998-01-01 00:00:00
DROP TABLE t1;
SET sql_mode = default;
DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1 (a1 INT, a2 INT, a3 INT, a4 DATETIME);
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
SELECT t1.* FROM t1 AS t0, { OJ t2 INNER JOIN t1 ON (t1.a1=t2.a1) } WHERE t0.a3=2;
a1	a2	a3	a4
SELECT t1.*,t2.* FROM { OJ ((t1 INNER JOIN t2 ON (t1.a1=t2.a2)) LEFT OUTER JOIN t3 ON t3.a3=t2.a1)};
a1	a2	a3	a4	a1	a2	a3	a4
SELECT t1.*,t2.* FROM { OJ ((t1 LEFT OUTER JOIN t2 ON t1.a3=t2.a2) INNER JOIN t3 ON (t3.a1=t2.a2))};
a1	a2	a3	a4	a1	a2	a3	a4
SELECT t1.*,t2.* FROM { OJ (t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a2) CROSS JOIN t3 ON (t3.a2=t2.a3)};
a1	a2	a3	a4	a1	a2	a3	a4
SELECT * FROM {oj t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a3} WHERE t1.a2 > 10;
a1	a2	a3	a4	a1	a2	a3	a4
SELECT {fn CONCAT(a1,a2)} FROM t1;
{fn CONCAT(a1,a2)}
UPDATE t3 SET a4={d '1789-07-14'} WHERE a1=0;
SELECT a1, a4 FROM t2 WHERE a4 LIKE {fn UCASE('1789-07-14')};
a1	a4
DROP TABLE t1, t2, t3;
#
# End of 5.1 tests
#
CREATE TABLE t (id INT PRIMARY KEY);
ALTER TABLE t RENAME TO `t1';;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`t1'' at line 1
DROP TABLE t;
#
# Bug#13819100 BROKEN SYNTAX ACCEPTED FOR START SLAVE, STOP SLAVE
#
STOP SLAVE ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,' at line 1
STOP SLAVE ,,,,,,,,,,,,, sql_thread, ,,,,,,,,,,,,,,,,,,, ;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,,,,,,, sql_thread, ,,,,,,,,,,,,,,,,,,,' at line 1
STOP SLAVE ,,,,,,,,,,,,, io_thread, ,,,,,,,,,,,,,,,,,,, ;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,,,,,,, io_thread, ,,,,,,,,,,,,,,,,,,,' at line 1
#
# Bug#13819132 BROKEN SYNTAX ACCEPTED FOR START TRANSACTION
#
START TRANSACTION ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,' at line 1
#
# Test of collective fix for three parser bugs:
#
# Bug #17727401, Bug #17426017, Bug #17473479:
#   The server accepts wrong syntax and then fails in different ways
#
CREATE TABLE t1 (i INT);
# bug #17426017
SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00)));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY VALUES (c00)))' at line 1
# bug#17473479
CREATE TABLE a(a int);
CREATE TABLE b(a int);
DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY a)' at line 1
DROP TABLE a, b;
# bug #17727401
SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY '') FROM t1' at line 1
# regression & coverage tests
# uniform syntax for FROM DUAL clause:
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE;
1
1
SELECT 1 FROM
(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE) a;
1
1
SELECT 1 FROM t1
WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE);
1
SELECT 1 FROM t1
UNION
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE;
1
1
(SELECT 1 FROM t1)
UNION 
(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE);
1
1
# "FOR UPDATE" tests
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
1
SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1' at line 1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;
1
# "INTO" clause tests
SELECT 1 FROM t1 INTO @var17727401;
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
SELECT 1 FROM DUAL INTO @var17727401;
SELECT 1 INTO @var17727401;
SELECT 1 INTO @var17727401 FROM t1;
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
SELECT 1 INTO @var17727401 FROM DUAL;
SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;
ERROR HY000: Multiple INTO clauses in one query block.
SELECT 1 INTO @var17727401_1 FROM DUAL
INTO @var17727401_2;
ERROR HY000: Multiple INTO clauses in one query block.
SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401;
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1' at line 1
SELECT 1 INTO @var17727401_1
FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
INTO @var17727401_2;
ERROR HY000: Multiple INTO clauses in one query block.
SELECT (SELECT 1 FROM t1 INTO @var17727401);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var17727401)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var17727401) a' at line 1
SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var17727401)' at line 1
SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 INTO t1' at line 1
(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1 FROM t1 INTO t1)' at line 1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
# ORDER and LIMIT clause combinations
(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
1
(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
1
((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1;
1
((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1;
1
(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
1
(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;
1
((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
1
SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1);
(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1)
NULL
SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1;
1
SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1);
(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1)
NULL
SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1) a;
1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
1
SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1);
(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1)
NULL
SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1) a;
1
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1' at line 1
SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FROM t1 LIMIT 1) a' at line 1
DROP TABLE t1;
#
# Bug #18106014: RECENT REGRESSION: MORE CASES OF ASSERTION FAILED:
#                !JOIN->PLAN_IS_CONST()
#
SELECT COUNT(1) FROM DUAL GROUP BY '1' ORDER BY 1 ;
COUNT(1)
1
SELECT COUNT(1)           GROUP BY '1' ORDER BY 1 ;
COUNT(1)
1
DO(SELECT 1 c           GROUP BY 1 HAVING 1 ORDER BY COUNT(1));
DO(SELECT 1 c FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT(1));
SELECT (SELECT 1 c                   GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is not expected';
null is not expected
1
SELECT (SELECT 1 c FROM DUAL         GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is not expected';
null is not expected
1
SELECT (SELECT 1 c                   GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c FROM DUAL         GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c           WHERE 1 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is not expected';
null is not expected
1
SELECT (SELECT 1 c FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is not expected';
null is not expected
1
SELECT (SELECT 1 c           WHERE 1 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c FROM DUAL WHERE 1 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c           WHERE 0 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c FROM DUAL WHERE 0 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c           WHERE 0 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT (SELECT 1 c FROM DUAL WHERE 0 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
'null is expected';
null is expected
NULL
SELECT 1 c FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT(1);
c
1
SELECT 1 c FROM DUAL GROUP BY 1 HAVING 0 ORDER BY COUNT(1);
c
SELECT 1 c           GROUP BY 1 HAVING 1 ORDER BY COUNT(1);
c
1
#
# Bug #18106058: RECENT REGRESSION: CRASH IN JOIN::MAKE_TMP_TABLES_INFO
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);
SELECT ((SELECT 1 AS f           HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f));
((SELECT 1 AS f           HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f))
1
SELECT ((SELECT 1 AS f FROM DUAL HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f));
((SELECT 1 AS f FROM DUAL HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f))
1
SELECT 1 AS f          FROM DUAL HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f;
f
1
SELECT 1 AS f                   HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
ORDER BY f;
f
1
DROP TABLE t1;
#
# Bug#17075846 : unquoted file names for variable values are
#                accepted but parsed incorrectly
#
SET default_storage_engine=a.myisam;
ERROR 42000: Incorrect argument type to variable 'default_storage_engine'
SET default_storage_engine = .a.MyISAM;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.a.MyISAM' at line 1
SET default_storage_engine = a.b.MyISAM;
ERROR 42000: Incorrect argument type to variable 'default_storage_engine'
SET default_storage_engine = `a`.MyISAM;
ERROR 42000: Incorrect argument type to variable 'default_storage_engine'
SET default_storage_engine = `a`.`MyISAM`;
ERROR 42000: Incorrect argument type to variable 'default_storage_engine'
set default_storage_engine = "a.MYISAM";
ERROR 42000: Unknown storage engine 'a.MYISAM'
set default_storage_engine = 'a.MYISAM';
ERROR 42000: Unknown storage engine 'a.MYISAM'
set default_storage_engine = `a.MYISAM`;
ERROR 42000: Unknown storage engine 'a.MYISAM'
CREATE TABLE t1 (s VARCHAR(100));
CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW
SET default_storage_engine = NEW.INNODB;
ERROR 42S22: Unknown column 'INNODB' in 'NEW'
DROP TABLE t1;
#
# Some additional coverage tests for WL#7199 and friends
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (10), (20);
SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 WHERE i = 10
ORDER BY i;
i
1
10
SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 WHERE i = 10
LIMIT 100;
i
1
10
SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 GROUP BY i HAVING i = 10
ORDER BY i;
i
1
10
SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 GROUP BY i HAVING i = 10
LIMIT 100;
i
1
10
(SELECT i FROM t1 WHERE i = 1) ORDER BY i;
i
1
(SELECT i FROM t1 WHERE i = 1) LIMIT 100;
i
1
(SELECT i FROM t1 GROUP BY i HAVING i = 1) ORDER BY i;
i
1
(SELECT i FROM t1 GROUP BY i HAVING i = 1) LIMIT 100;
i
1
DROP TABLE t1, t2;
#
# Bug#18486460 ASSERTION FAILED: N < M_SIZE AFTER FIX_INNER_REFS
#
CREATE TABLE t1(b INT);
CREATE TABLE t2(a INT, b INT, c INT, d INT);
EXPLAIN SELECT
(
SELECT
ROW(t1.b, a) = ROW( ROW(1, t2.c) = ROW(1, d), c) = a
FROM t1
)
FROM t2 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using temporary
2	DEPENDENT SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1276	Field or reference 'test.t2.c' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t2.d' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t2.c' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select (/* select#2 */ select (((`test`.`t1`.`b` = ((1 = 1) and (`test`.`t2`.`c` = `test`.`t2`.`d`))) and (`test`.`t2`.`a` = `test`.`t2`.`c`)) = `test`.`t2`.`a`) from `test`.`t1`) AS `(
SELECT
ROW(t1.b, a) = ROW( ROW(1, t2.c) = ROW(1, d), c) = a
FROM t1
)` from `test`.`t2` group by `test`.`t2`.`a`
DROP TABLE t1, t2;
#
# Bug#18498344: SELECT WITH ALIAS NOT WORKING IN 5.7
#
CREATE TABLE t1 (
a INT
);
INSERT INTO t1 VALUES ( 2 );
# Should succeed
SELECT *
FROM ( SELECT a FROM t1 UNION SELECT 1 ORDER BY a ) AS a1
WHERE a1.a = 1 OR a1.a = 2;
a
1
2
DROP TABLE t1;
#
# Bug #18484088: PROBLEMS IN CREATE_FUNC_CAST ON QUERY ERRORS...
#
DO(CONVERT(CONVERT('',DECIMAL(66,0)), DECIMAL(66,0))), CAST(CONVERT(1,DECIMAL(65,31)) AS DATE);
ERROR 42000: Too-big precision 66 specified for ''''. Maximum is 65.
SELECT CONVERT(CONVERT('',DECIMAL(65,0)), DECIMAL(66,0));
ERROR 42000: Too-big precision 66 specified for 'CONVERT('',DECIMAL(65,0))'. Maximum is 65.
#
# Bug #18759387: PROBLEM IN ITEM_FUNC_XOR::NEG_TRANSFORMER
#
SELECT 1<
!(1 XOR TO_BASE64()));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
SELECT 1<
!(1 XOR TO_BASE64());
ERROR 42000: Incorrect parameter count in the call to native function 'TO_BASE64'
SELECT  !('' XOR LENGTH());
ERROR 42000: Incorrect parameter count in the call to native function 'LENGTH'
SELECT  !((UNHEX() IS NULL));
ERROR 42000: Incorrect parameter count in the call to native function 'UNHEX'
#
# Bug #20086997: PARSER CONFUSES WITH 7BIT-CHARACTER STRING DETECTION
#
CREATE DATABASE mysqltest1 CHARACTER SET LATIN2;
USE mysqltest1;
CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET LATIN2);
SET CHARACTER SET cp1250_latin2;
INSERT INTO t1 VALUES ('');
INSERT INTO t1 VALUES ('' '');
SELECT HEX(a) FROM t1;
HEX(a)
A3A1AAAF
A3A1AAAF
DROP DATABASE mysqltest1;
USE test;
#
# WL #7201, WL #7202 and WL#8062 coverage tests
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 () SELECT * FROM t1;
INSERT INTO t1 SELECT HIGH_PRIORITY * FROM t1;
INSERT INTO t1 SELECT DISTINCT ALL * FROM t1;
ERROR HY000: Incorrect usage of ALL and DISTINCT
REPLACE INTO `` SELECT * FROM ``;
ERROR 42000: Incorrect table name ''
DELETE QUICK FROM t1 WHERE i = 0;
DROP TABLE t1;
#
# Bug #21035515: PARSE_GCOL_EXPR SHOULD BE A KIND OF RESERVED WORD,
#                NOT A KEYWORD
SET @parse_gcol_expr = 1;
SELECT 1 AS parse_gcol_expr;
parse_gcol_expr
1
CREATE TABLE parse_gcol_expr (i INT);
DROP TABLE parse_gcol_expr;
# parse_gcol_expr can't be a label:
CREATE PROCEDURE p1()
BEGIN
parse_gcol_expr: LOOP
SELECT 1;
END LOOP parse_gcol_expr;
END|
DROP PROCEDURE p1;
# PARSE_GCOL_EXPR is not a valid statement:
PARSE_GCOL_EXPR (1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARSE_GCOL_EXPR (1)' at line 1
#
# Bug #17400320	ALGORITHM= IS NOT SUPPORTED FOR ALTER TABLE WITH <PARTITION_OPTIONS>
#
CREATE TABLE t1 (x INT PRIMARY KEY);
ALTER TABLE t1;
ALTER TABLE t1 ALGORITHM=DEFAULT;
ALTER TABLE t1 ALGORITHM=COPY;
ALTER TABLE t1 ALGORITHM=INPLACE;
ALTER TABLE t1 LOCK=DEFAULT;
ALTER TABLE t1 LOCK=NONE;
ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
ALTER TABLE t1 LOCK=SHARED;
ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
ALTER TABLE t1 LOCK=EXCLUSIVE;
ALTER TABLE t1 LOCK=SHARED, ALGORITHM=COPY,
LOCK=NONE, ALGORITHM=DEFAULT,
LOCK=EXCLUSIVE, ALGORITHM=INPLACE;
ALTER TABLE t1 WITH VALIDATION;
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
ALTER TABLE t1 WITHOUT VALIDATION;
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
ALTER TABLE t1 LOCK=SHARED, WITH VALIDATION, ALGORITHM=COPY,
LOCK=EXCLUSIVE, WITHOUT VALIDATION, ALGORITHM=INPLACE;
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
DROP TABLE t1;
#
# WL#8083: Introduce <query expression> parser rule
# Bug#14743786: PARSE ERROR WITH UNION PARENTHESES ON TOP LEVEL
#
#
# Part1: Regression Testing.
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );
CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 ), ( 2 );
CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );
SELECT 1 UNION SELECT 2;
1
1
2
(SELECT 1) UNION SELECT 2;
1
1
2
SELECT 1 UNION (SELECT 2);
1
1
2
(SELECT 1) UNION (SELECT 2);
1
1
2
SELECT 2 FROM t1 UNION ((SELECT 3 FROM t1));
2
2
3
(SELECT 2 FROM t1)  UNION SELECT 3 FROM t1;
2
2
3
((SELECT 2 FROM t1)) UNION SELECT 3 FROM t1;
2
2
3
(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
1
1
(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
1
1
SELECT a FROM t1 LIMIT 1 UNION ALL SELECT a FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT a FROM t1' at line 1
(SELECT 1) UNION (SELECT 2 UNION SELECT 3);
1
1
2
3
(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT a FROM t1 ORDER BY a) LIMIT 2;
a
1
1
(SELECT a FROM t1 LIMIT 1) UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2;
a
1
1
SELECT 1 UNION SELECT 2 FROM t1 ORDER BY a LIMIT 1;
ERROR 42S22: Unknown column 'a' in 'order clause'
SELECT 1 UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1);
1
1
2
(SELECT 1 FROM t1 LIMIT 2) UNION  SELECT 2 FROM t1 ORDER BY a LIMIT 1;
ERROR 42S22: Unknown column 'a' in 'order clause'
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1);
1
1
2
(SELECT a FROM t1 LIMIT 2) LIMIT 1;
a
1
((SELECT a FROM t1 LIMIT 2)) LIMIT 1;
a
1
(SELECT a FROM t1 LIMIT 2) ORDER BY 1;
a
1
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1) LIMIT 1;
1
1
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1)
ORDER BY a;
ERROR 42S22: Unknown column 'a' in 'order clause'
(SELECT 1 FROM t1 LIMIT 1) UNION ((SELECT 2 FROM t1 ORDER BY a LIMIT 2))
ORDER BY a;
ERROR 42S22: Unknown column 'a' in 'order clause'
(SELECT a FROM t2 LIMIT 1) UNION (SELECT a FROM t3 LIMIT 2) LIMIT 1;
a
2
(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
1
1
(SELECT a FROM t1 LIMIT 1) ORDER BY t1.a;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
((SELECT a FROM t1 LIMIT 1)) ORDER BY t1.a;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT 2) ORDER BY t1.b;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
(SELECT a FROM t1 LIMIT 1) UNION ALL ((SELECT 2)) ORDER BY t1.b;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT a FROM t1 ORDER BY a LIMIT 2)
ORDER BY t1.b;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
1
1
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
1
1
( (SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) UNION SELECT 1;
1
1
( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
1
1
( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SELECT a FROM t1 ORDER BY a UNION SELECT a FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT a FROM t1' at line 1
(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t1;
a
1
(SELECT * FROM t1 ORDER BY a) UNION SELECT * FROM t1;
a
1
SELECT a FROM t1
UNION
SELECT a FROM t1 ORDER BY a
UNION
SELECT a FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
SELECT a FROM t1' at line 4
(SELECT SQL_CALC_FOUND_ROWS a FROM t1 LIMIT 2)
UNION
(SELECT a FROM t2 ORDER BY a) LIMIT 2;
a
1
2
Warnings:
Warning	1287	SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
DROP TABLE t1, t2, t3;
CREATE PROCEDURE p1() BEGIN IF whatever THEN SELECT 1; END IF; END|
CALL p1();
ERROR 42S22: Unknown column 'whatever' in 'field list'
DROP PROCEDURE p1;
#
# Part 2: Test of changed (fixed) behavior.
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );
CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 ), ( 2 );
CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );
(SELECT 1 FROM t1   UNION   SELECT 2 FROM t1);
1
1
2
((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1));
1
1
2
(SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1));
1
1
2
((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)));
1
1
2
((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1))));
1
1
2
((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1);
1
1
2
(((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1);
1
1
2
((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1));
1
1
2
((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1));
1
1
2
(((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)));
1
1
2
((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1)));
1
1
2
(((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1))));
1
1
2
((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));
1
1
2
(SELECT 1 UNION SELECT 2) ORDER BY 1;
1
1
2
((SELECT 1 UNION SELECT 2)) ORDER BY 1;
1
1
2
((SELECT 1) ORDER BY 1);
1
1
((SELECT 1) LIMIT 1);
1
1
(SELECT 1 UNION SELECT 2) LIMIT 1;
1
1
((SELECT 1 UNION SELECT 2)) LIMIT 1;
1
1
(SELECT a FROM t1) LIMIT 1 UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2' at line 1
DROP TABLE t1, t2, t3;
#
# Part 3: The <table factor> syntax.
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );
CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 );
CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 );
CREATE TABLE t4 ( a INT );
INSERT INTO t4 VALUES ( 3 );
SELECT * FROM    (SELECT 1 FROM t1   UNION   SELECT 2 FROM t1) dt;
1
1
2
SELECT * FROM   ((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1)) dt;
1
1
2
SELECT * FROM    (SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)) dt;
1
1
2
SELECT * FROM   ((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1))) dt;
1
1
2
SELECT * FROM   ((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1)))) dt;
1
1
2
SELECT * FROM   ((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1) dt;
1
1
2
SELECT * FROM  (((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1) dt;
1
1
2
SELECT * FROM ((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1)) dt;
1
1
2
SELECT * FROM   ((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)) dt;
1
1
2
SELECT * FROM  (((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1))) dt;
1
1
2
SELECT * FROM ((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1))) dt;
1
1
2
SELECT * FROM  (((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1)))) dt;
1
1
2
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1)))) dt;
1
1
2
SELECT * FROM    (SELECT 1 FROM t1   UNION   SELECT 2 FROM t1);
ERROR 42000: Every derived table must have its own alias
SELECT * FROM   ((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM    (SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM   ((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM   ((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1))));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM   ((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1);
ERROR 42000: Every derived table must have its own alias
SELECT * FROM  (((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1);
ERROR 42000: Every derived table must have its own alias
SELECT * FROM ((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM   ((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM  (((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM ((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1)));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM  (((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1))));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));
ERROR 42000: Every derived table must have its own alias
SELECT * FROM  ( t1 JOIN t2 ON TRUE );
a	a
1	2
SELECT * FROM (( t1 JOIN t2 ON TRUE ));
a	a
1	2
SELECT * FROM ( t1 JOIN t2 ON TRUE  JOIN t3 ON TRUE );
a	a	a
1	2	3
SELECT * FROM ((t1 JOIN t2 ON TRUE) JOIN t3 ON TRUE );
a	a	a
1	2	3
SELECT * FROM (t1 INNER JOIN t2 ON (t1.a = t2.a));
a	a
SELECT 1 FROM (SELECT 1 FROM t1 INTO @v) a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v) a' at line 1
SELECT 1 FROM (t1);
1
1
SELECT 1 FROM ((t1));
1
1
SELECT 1 UNION SELECT 2 FROM (t2);
1
1
2
SELECT 1 FROM  (SELECT 2  ORDER BY 1) AS res;
1
1
#
# This syntax is no longer allowed
#
SELECT 1 FROM ((SELECT 2) ORDER BY 1) AS res;
1
1
SELECT 1 FROM ((SELECT 2) a ORDER BY 1) AS res;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1) AS res' at line 1
SELECT 1 FROM ((SELECT 2) LIMIT 1) AS res;
1
1
SELECT 1 FROM ((SELECT 2) a LIMIT 1) AS res;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1) AS res' at line 1
SELECT * FROM ( t1 AS alias1 );
a
1
SELECT * FROM   t1 AS alias1, t2 AS alias2;
a	a
1	2
SELECT * FROM ( t1 AS alias1, t2 AS alias2 );
a	a
1	2
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 JOIN t3 ON TRUE );
ERROR 42000: Not unique table/alias: 't1'
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 t11 JOIN t3 ON TRUE ) t1a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a' at line 1
SELECT * FROM ( t1 JOIN t2 ON TRUE, SELECT 1 FROM DUAL );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL )' at line 1
SELECT * FROM ( t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a );
a	a	1
1	2	1
SELECT * FROM t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a;
a	a	1
1	2	1
SELECT * FROM ( SELECT 1 FROM DUAL );
ERROR 42000: Every derived table must have its own alias
SELECT * FROM ( SELECT 1 FROM DUAL ) t1a;
1
1
SELECT * FROM  ( t1, t2 );
a	a
1	2
SELECT * FROM (( t1, t2 ));
a	a
1	2
SELECT * FROM  ( (t1),   t2  );
a	a
1	2
SELECT * FROM  (((t1)),  t2  );
a	a
1	2
SELECT * FROM  ( (t1),  (t2) );
a	a
1	2
SELECT * FROM  (  t1,   (t2) );
a	a
1	2
((SELECT 1 UNION SELECT 1) UNION SELECT 1);
1
1
SELECT * FROM ((SELECT 1 UNION SELECT 1) UNION SELECT 1) a;
1
1
SELECT * FROM (t1, t2) JOIN (t3, t4) ON TRUE;
a	a	a	a
1	2	3	3
SELECT * FROM ((t1, t2) JOIN t3 ON TRUE);
a	a	a
1	2	3
SELECT * FROM t1 JOIN ( t2, t3 ) USING ( a );
ERROR 23000: Column 'a' in from clause is ambiguous
DROP TABLE t1, t2, t3, t4;
# Check the format in the note.
CREATE TABLE t1 (a INT);
EXPLAIN SELECT 1 FROM (SELECT 1 FROM t1) t;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t1`
DROP TABLE t1;
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE t3 ( c INT );
CREATE TABLE t4 ( d INT );
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (2);
INSERT INTO t4 VALUES (2);
SELECT * FROM t1 LEFT JOIN ( t2, t3, t4 ) ON a = c;
a	b	c	d
1	NULL	NULL	NULL
SELECT * FROM t1 NATURAL JOIN ((t1 NATURAL JOIN t1), (t1 NATURAL JOIN t1));
ERROR 42000: Not unique table/alias: 't1'
DROP TABLE t1, t2, t3, t4;
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE t3 ( c INT );
CREATE TABLE t4 ( d INT );
CREATE TABLE t5 ( d INT );
SELECT * FROM t5 NATURAL JOIN ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4));
d	a	b	c
SELECT * FROM ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4)) NATURAL JOIN t5;
d	a	b	c
SELECT * FROM t1 JOIN ( t2, t3 ) ON TRUE;
a	b	c
SELECT * FROM ( t1, t2 , t3 );
a	b	c
SELECT * FROM ( ( t1, t2 ), t3 );
a	b	c
SELECT * FROM ( ((t1, t2)), t3 );
a	b	c
SELECT * FROM ( t1, ( t2, t3 ) );
a	b	c
SELECT * FROM ( t1, ((t2, t3)) );
a	b	c
SELECT * FROM ((( t1, t2 ), t3));
a	b	c
SELECT * FROM ((((t1, t2)), t3));
a	b	c
SELECT * FROM ((t1, ( t2, t3 )));
a	b	c
SELECT * FROM ((t1, ((t2, t3))));
a	b	c
CREATE VIEW v1 AS SELECT 1 INTO @v;
ERROR HY000: View's SELECT contains a 'INTO' clause
CREATE VIEW v1 AS SELECT 1 FROM ( SELECT 1 FROM t1 ) my_table;
DROP TABLE t1, t2, t3, t4, t5;
DROP VIEW v1;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1);
SELECT 1 INTO @v;
(SELECT 1 INTO @v);
((SELECT 1 INTO @v));
SELECT 1 FROM t1 INTO @v;
(SELECT 1 FROM t1 INTO @v);
((SELECT 1 FROM t1 INTO @v));
SELECT 1 FROM t1 INTO @v UNION SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
(SELECT 1 FROM t1 INTO @v) UNION SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
SELECT 1 FROM t1 INTO @v UNION (SELECT 1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1)' at line 1
((SELECT 1 FROM t1 INTO @v) UNION (SELECT 1));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1))' at line 1
SELECT 1 INTO @v UNION SELECT 1;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
(SELECT 1 INTO @v) UNION SELECT 1;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
SELECT 1 INTO @v UNION (SELECT 1);
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
((SELECT 1 INTO @v) UNION (SELECT 1));
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
SELECT 1 UNION SELECT 2 INTO @v;
ERROR 42000: Result consisted of more than one row
(SELECT 1) UNION SELECT 2 INTO @v;
ERROR 42000: Result consisted of more than one row
(SELECT 1) UNION (SELECT 2 INTO @v);
ERROR 42000: Result consisted of more than one row
((SELECT 1) UNION (SELECT 2 INTO @v));
ERROR 42000: Result consisted of more than one row
SELECT 1 UNION SELECT 2 INTO @v FROM t1;
ERROR 42000: Result consisted of more than one row
(SELECT 1) UNION SELECT 2 INTO @v FROM t1;
ERROR 42000: Result consisted of more than one row
(SELECT 1) UNION (SELECT 2 INTO @v FROM t1);
ERROR 42000: Result consisted of more than one row
((SELECT 1) UNION (SELECT 2 INTO @v FROM t1));
ERROR 42000: Result consisted of more than one row
SELECT 1 UNION SELECT 1 INTO @v FROM t1;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1) UNION SELECT 1 INTO @v FROM t1;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1) UNION (SELECT 1 INTO @v FROM t1);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
((SELECT 1) UNION (SELECT 1 INTO @v FROM t1));
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 2 INTO OUTFILE 'parser.test.file1';
SELECT 1 UNION (SELECT 2 INTO OUTFILE 'parser.test.file2');
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1) UNION SELECT 2 INTO OUTFILE 'parser.test.file3';
(SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file4');
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
((SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file5'));
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT * FROM (SELECT a INTO @v FROM t1) t1a;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
SELECT * FROM (SELECT a INTO @v) t1a;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
DROP TABLE t1;
#
# Part 4: The <joined table> syntax.
#
CREATE TABLE t1( a INT );
CREATE TABLE t2( b INT );
CREATE TABLE t3( c INT );
CREATE TABLE t4( d INT );
CREATE TABLE t5( e INT );
# We use EXPLAIN so we can get the parser's interpretation of the
# nesting. We don't really care about the execution plan.
SET optimizer_switch = 'block_nested_loop=off';
SELECT * FROM t1 JOIN t2;
a	b
SELECT * FROM t1 JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 INNER JOIN t2;
a	b
SELECT * FROM t1 INNER JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 INNER JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 CROSS JOIN t2;
a	b
SELECT * FROM t1 CROSS JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 CROSS JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 STRAIGHT_JOIN t2;
a	b
SELECT * FROM t1 STRAIGHT_JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 STRAIGHT_JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 t11 NATURAL JOIN t1 t12;
a
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 ON t11.a = t12.a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON t11.a = t12.a' at line 1
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 USING ( a );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING ( a )' at line 1
SELECT * FROM t1 t11 NATURAL INNER JOIN t1 t12;
a
SELECT * FROM t1 LEFT JOIN t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SELECT * FROM t1 LEFT JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 LEFT JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 NATURAL LEFT JOIN t2;
a	b
SELECT * FROM t1 LEFT OUTER JOIN t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SELECT * FROM t1 LEFT OUTER JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 LEFT OUTER JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;
a	b
SELECT * FROM t1 RIGHT JOIN t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SELECT * FROM t1 RIGHT JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 RIGHT JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 NATURAL RIGHT JOIN t2;
b	a
SELECT * FROM t1 RIGHT OUTER JOIN t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON a = b;
a	b
SELECT * FROM t1 t11 RIGHT OUTER JOIN t1 t12 USING ( a );
a
SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
b	a
# Right-deep join nesting.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
# Right-deep join nesting from t2 and on.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t2.b = t3.c;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c` = `test`.`t2`.`b`) and (`test`.`t4`.`d` = `test`.`t2`.`b`))
# Right-deep join nesting from t2 and on.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`d` = `test`.`t3`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`a`))
# Left-deep join nesting.
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t2.b = t3.c;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
# A cross join joined with an inner join (i.e. with a join condition).
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING ( a );
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t11	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t12	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` join `test`.`t1` `t12` where (`test`.`t12`.`a` = `test`.`t11`.`a`)
# A left-deep cross join tree joined with an inner join.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t5	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d`,`test`.`t5`.`e` AS `e` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` join `test`.`t5` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
# The different kinds of <table_ref> when used with context-dependent
# join. Tests the top-down build of the parse tree.
EXPLAIN SELECT * FROM t1 JOIN (t2) JOIN t3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN SELECT * FROM t1 JOIN (SELECT 1 AS b) a JOIN t3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	PRIMARY	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,'1' AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t3`
EXPLAIN SELECT * FROM t1 JOIN (t2 JOIN t3) JOIN t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4`
EXPLAIN SELECT * FROM t1 JOIN (t2, t3) JOIN t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4`
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t4.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c` = `test`.`t2`.`b`) and (`test`.`t4`.`d` = `test`.`t1`.`a`))
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t11	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t12	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t13	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` join `test`.`t1` `t12` join `test`.`t1` `t13` where ((`test`.`t12`.`a` = `test`.`t11`.`a`) and (`test`.`t13`.`a` = `test`.`t11`.`a`))
EXPLAIN SELECT * FROM t2 JOIN t1 t11 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t11	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t12	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t13	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` join `test`.`t1` `t11` join `test`.`t1` `t12` join `test`.`t1` `t13` where ((`test`.`t12`.`a` = `test`.`t11`.`a`) and (`test`.`t13`.`a` = `test`.`t11`.`a`))
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`d` = `test`.`t3`.`c`)
SET optimizer_switch = DEFAULT;
DROP TABLE t1, t2, t3, t4, t5;
# Testing correct nesting of natural joins.
CREATE TABLE t1( a INT, b int );
CREATE TABLE t2( a INT, c int );
CREATE TABLE t3( a INT, d int );
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES         (2, 2), (3, 3), (4, 4);
INSERT INTO t3 VALUES                 (3, 3), (4, 4), (5, 5);
# The two queries below should produce identical result sets.
SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL RIGHT JOIN t3;
a	d	b	c
3	3	3	3
4	4	NULL	NULL
5	5	NULL	NULL
SELECT * FROM (t1 NATURAL LEFT JOIN t2) NATURAL RIGHT JOIN t3;
a	d	b	c
3	3	3	3
4	4	NULL	NULL
5	5	NULL	NULL
# This result should differ from the result sets above.
SELECT * FROM t1 NATURAL LEFT JOIN (t2 NATURAL RIGHT JOIN t3);
a	b	d	c
1	1	NULL	NULL
2	2	NULL	NULL
3	3	3	3
DROP TABLE t1, t2, t3;
#
# Bug#22995438: BUILD INDEX DEFINITION SYNTAX BOTTOM-UP
#
# This is regression testing for the refactoring.
CREATE TABLE t1 (
a INT,
b INT,
c INT,
d INT,
e INT,
f INT,
g INT,
h INT,
i INT,
j INT,
k INT,
l INT,
m INT,
n INT,
o INT
);
CREATE        INDEX a_index             ON t1( a );
CREATE UNIQUE INDEX b_index             ON t1( b );
CREATE        INDEX c_index USING btree ON t1( c );
CREATE        INDEX c_index USING btree USING btree ON t1( c );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING btree ON t1( c )' at line 1
CREATE        INDEX d_index USING rtree ON t1( d );
ERROR 42000: A SPATIAL index may only contain a geometrical type column
CREATE        INDEX e_index TYPE  btree ON t1( e );
CREATE        INDEX type TYPE  btree ON t1( f );
CREATE        INDEX TYPE  btree ON t1( g );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'btree ON t1( g )' at line 1
CREATE        INDEX h_index TYPE  rtree ON t1( h );
ERROR 42000: A SPATIAL index may only contain a geometrical type column
CREATE        INDEX i_index             ON t1( i ) KEY_BLOCK_SIZE = 1;
CREATE        INDEX j_index             ON t1( j ) KEY_BLOCK_SIZE = 1 KEY_BLOCK_SIZE = 1;
CREATE        INDEX k_index             ON t1( k ) COMMENT 'A comment';
CREATE        INDEX k_index2            ON t1( k ) COMMENT 'A comment' COMMENT 'Another comment';
Warnings:
Warning	1831	Duplicate index 'k_index2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
CREATE        INDEX l_index             ON t1( l ) USING btree;
CREATE        INDEX m_index             ON t1( m ) TYPE btree;
CREATE        INDEX n_index USING btree ON t1( n ) USING btree;
CREATE        INDEX x_index USING btree ON t1( o ) USING rtree;
ERROR 42000: A SPATIAL index may only contain a geometrical type column
CREATE        INDEX o_index USING rtree ON t1( o ) USING btree;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SHOW INDEXES FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	b_index	1	b	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	a_index	1	a	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	c_index	1	c	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	e_index	1	e	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	type	1	f	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	i_index	1	i	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	j_index	1	j	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	k_index	1	k	A	0	NULL	NULL	YES	BTREE		A comment	YES	NULL
t1	1	k_index2	1	k	A	0	NULL	NULL	YES	BTREE		Another comment	YES	NULL
t1	1	l_index	1	l	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	m_index	1	m	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	n_index	1	n	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	o_index	1	o	A	0	NULL	NULL	YES	BTREE			YES	NULL
DROP TABLE t1;
#
# WL#8907: Parser refactoring: merge all SELECT rules into one.
#
# Warning on hints in CREATE or ALTER VIEW:
CREATE VIEW v1 AS SELECT /*+ QB_NAME(a) */ 1;
ALTER VIEW v1 AS SELECT /*+ QB_NAME(a) */ 1;
SELECT * FROM v1;
1
1
DROP VIEW v1;
# The "Hints aren't supported in CREATE or ALTER VIEW" warning is masked:
CREATE VIEW v1 AS SELECT /*+ BAD_HINT */ 1;
Warnings:
Warning	1064	Optimizer hint syntax error near 'BAD_HINT */ 1' at line 1
ALTER VIEW v1 AS SELECT /*+ BAD_HINT */ 1;
Warnings:
Warning	1064	Optimizer hint syntax error near 'BAD_HINT */ 1' at line 1
SELECT * FROM v1;
1
1
DROP VIEW v1;
#
#
# Bug#23321895: CRASH IN IS_VIEW_OR_DERIVED OR
# TABLE_LIST::IS_LEAF_FOR_NAME_RESOLUTION
#
CREATE TABLE t1( a INT );
CREATE TABLE t2( a INT );
CREATE TABLE t3( a INT );
CREATE TABLE t4( a INT );
SELECT 1
FROM ( SELECT 1 FROM t1 JOIN t2 ON @@q ) AS d
JOIN t3 LEFT JOIN t4 ON 1;
ERROR HY000: Unknown system variable 'q'
DROP TABLE t1, t2, t3, t4;
#
# Bug#39559: dump of stored procedures / functions with C-style
#     comment can't be read back
#
+----------+--------+
| expected | result |
+----------+--------+
|        2 |      2 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        1 |      1 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        3 |      3 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        2 |      2 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        7 |      7 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        8 |      8 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        7 |      7 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        4 |      4 |
+----------+--------+
+----------+--------+
| expected | result |
+----------+--------+
|        4 |      4 |
+----------+--------+
# Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
#
COMMIT AND CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
COMMIT AND NO CHAIN RELEASE;
COMMIT RELEASE;
COMMIT CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
COMMIT NO CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
COMMIT AND NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
COMMIT AND RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
COMMIT NO RELEASE;
COMMIT CHAIN NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
COMMIT NO CHAIN NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
COMMIT AND RELEASE CHAIN;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
COMMIT AND NO CHAIN NO RELEASE;
ROLLBACK AND CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
ROLLBACK AND NO CHAIN RELEASE;
ROLLBACK RELEASE;
ROLLBACK CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
ROLLBACK NO CHAIN RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
ROLLBACK AND NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
ROLLBACK AND RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
ROLLBACK NO RELEASE;
ROLLBACK CHAIN NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
ROLLBACK NO CHAIN NO RELEASE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
ROLLBACK AND RELEASE CHAIN;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
ROLLBACK AND NO CHAIN NO RELEASE;
#
# Bug#26132947: SERVER CAN EXIT ON ALTER TABLE ADD PARTITION SYNTAX
#
CREATE TABLE t1 (a INT PRIMARY KEY) PARTITION BY HASH (a) PARTITIONS 1;
ALTER TABLE t1 ADD PARTITION;
ERROR HY000: At least one partition must be added
DROP TABLE t1;
#
#
# Bug#25717617: Wrong syntax error line numbers when sql_mode has the
#               IGNORE_SPACE flag
#
SET @save_sql_mode=@@sql_mode;
SET sql_mode='IGNORE_SPACE';
# Expected error line number is 2:
CREATE
TABLE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
# Expected error line number is 5:
CREATE
#
#
#
TABLE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
#
# Regression test added in WL#8657
#
CREATE TEMPORARY TABLE t1(a INT);
SHOW COLUMNS FROM t1 WHERE FIELD='a';
Field	Type	Null	Key	Default	Extra
a	int	YES		NULL	NULL
DROP TABLE t1;
ALTER TABLE t1 RENAME TO ``.t1;
ERROR 42000: Incorrect database name ''
CREATE TABLE t1 (a INT) PARTITION BY KEY ALGORITHM = 10 () PARTITIONS 3;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 () PARTITIONS 3' at line 1
ALTER EVENT ev1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
ALTER INSTANCE ROTATE MyISAM MASTER KEY;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MyISAM MASTER KEY' at line 1
REVOKE SELECT(c1) ON FUNCTION *.* FROM r1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
GRANT SELECT(c1) ON FUNCTION *.* TO r1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
CREATE INDEX idx1 ON `` (c1);
ERROR 42000: Incorrect table name ''
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE `` WITH VALIDATION;
ERROR 42000: Incorrect table name ''
CHECK TABLE ``;
ERROR 42000: Incorrect table name ''
DROP INDEX idx1 ON ``;
ERROR 42000: Incorrect table name ''
CACHE INDEX `` IN c;
ERROR 42000: Incorrect table name ''
CACHE INDEX `` PARTITION (ALL) IN c;
ERROR 42000: Incorrect table name ''
LOAD INDEX INTO CACHE `` PARTITION (ALL);
ERROR 42000: Incorrect table name ''
LOAD INDEX INTO CACHE ``;
ERROR 42000: Incorrect table name ''
SET @@sql_mode=@save_sql_mode;
#
# Bug#24756971: REMOVE OBSOLETE LEXER HACK AROUND SELECT LIST
#
SELECT 1,,2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',2' at line 1
SELECT ,,1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,1' at line 1
SELECT ,,,;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,' at line 1
#
# Removal of undocumented syntax, see bug#27389878.
#
CREATE DATABASE db CHARSET DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
CREATE DATABASE db COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
CREATE TABLE t (i INT) CHARSET DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
CREATE TABLE t (i INT) COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
ALTER DATABASE db CHARSET DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
ALTER DATABASE db COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
ALTER TABLE t COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
SET NAMES utf8 COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
SET NAMES DEFAULT COLLATE DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE DEFAULT' at line 1
CREATE PROCEDURE p1()
BEGIN
DECLARE c CHAR(1) CHARSET DEFAULT;
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT;
END' at line 3
CREATE PROCEDURE p1()
BEGIN
DECLARE c CHAR(1) COLLATE DEFAULT;
END//
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT;
END' at line 3
CREATE PROCEDURE p1(c CHAR(1) CHARSET DEFAULT) BEGIN END;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT) BEGIN END' at line 1
CREATE PROCEDURE p1(c CHAR(1) COLLATE DEFAULT) BEGIN END;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT) BEGIN END' at line 1
CREATE FUNCTION f1(c CHAR(1) CHARSET DEFAULT) RETURNS INT RETURN 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT) RETURNS INT RETURN 1' at line 1
CREATE FUNCTION f1(c CHAR(1) COLLATE DEFAULT) RETURNS INT RETURN 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT) RETURNS INT RETURN 1' at line 1
CREATE FUNCTION f1() RETURNS CHAR(1) CHARSET DEFAULT RETURN '';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT RETURN ''' at line 1
CREATE FUNCTION f1() RETURNS CHAR(1) COLLATE DEFAULT RETURN '';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT RETURN ''' at line 1
#
# Bug#27814204: THE "ADMIN" WORD SHOULD BE A NON-RESERVED WORD IN THE
#               SQL GRAMMAR
#
CREATE TEMPORARY TABLE admin (admin INT);
DROP TABLE admin;
#
# Bug#27760787: ERROR IN SQL SYNTAX WHEN USING "DEFAULT" KEYWORD IN
#               ALTER TABLE COMMAND
SELECT @@default_collation_for_utf8mb4;
@@default_collation_for_utf8mb4
utf8mb4_0900_ai_ci
#########################################################################
#
# 1. @@default_collation_for_utf8mb4 does not matter:
#
#########################################################################
CREATE DATABASE db1 CHARSET cp1251 COLLATE cp1251_general_ci;
USE db1;
CREATE TABLE t1 (i INT) CHARSET utf8mb4;
#
# Implicit COLLATE:
#
ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
#
# Explicit COLLATE:
#
ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT COLLATE cp1251_bin;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_bin
DROP DATABASE db1;
#########################################################################
#
# 2. @@default_collation_for_utf8mb4 == utf8mb4_general_ci
#    @@collation_database            == utf8mb4_0900_ai_ci
#
#########################################################################
SET @@default_collation_for_utf8mb4 = utf8mb4_general_ci;
Warnings:
Warning	1681	Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
CREATE DATABASE db2 COLLATE utf8mb4_0900_ai_ci;
USE db2;
CREATE TABLE t2 (i INT) CHARSET latin1;
#
# Implicit COLLATE result in utf8mb4_0900_ai_ci (@@collation_database):
#
ALTER TABLE t2 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Cleanup:
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;
#
# Explicit COLLATE should result in utf8mb4_bin:
#
ALTER TABLE t2 CONVERT TO CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
DROP DATABASE db2;
#########################################################################
#
# 3. @@default_collation_for_utf8mb4 == utf8mb4_0900_ai_ci
#    @@collation_database            == utf8mb4_general_ci
#
#########################################################################
SET @@default_collation_for_utf8mb4 = DEFAULT;
Warnings:
Warning	1681	Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
SELECT @@default_collation_for_utf8mb4;
@@default_collation_for_utf8mb4
utf8mb4_0900_ai_ci
CREATE DATABASE db3 COLLATE utf8mb4_general_ci;
USE db3;
CREATE TABLE t3 (i INT) CHARSET latin1;
#
# Implicit COLLATE should result in utf8mb4_general_ci (@@collation_database):
#
ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
#
# Explicit COLLATE should result in utf8mb4_bin:
#
ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
#########################################################################
#
# 4.  Incompatible character set in @@character_set_database and COLLATE should fail:
#
#########################################################################
ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT COLLATE cp1251_general_cs;
ERROR 42000: COLLATION 'cp1251_general_cs' is not valid for CHARACTER SET 'utf8mb4'
DROP DATABASE db3;
# Cleanup
USE test;

Bug #27714748: @@PARSER_MAX_MEM_SIZE DOES NOT WORK FOR ROUTINES

SET parser_max_mem_size = 10000000;
ERROR HY000: Memory capacity of 10000000 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.
SET parser_max_mem_size = default;
#
# Bug #28968848: SIMPLIFY MYSQL_YYABORT
#
CREATE PROCEDURE p1 () wrong syntax;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'syntax' at line 1
#
# Bug #25220656: THE "PERSIST" EXTENSION IS A RESERVED KEYWORD
#
SELECT 1 AS PERSIST, 2 AS PERSIST_ONLY;
PERSIST	PERSIST_ONLY
1	2
CREATE ROLE EVENT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EVENT' at line 1
CREATE ROLE FILE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FILE' at line 1
CREATE ROLE NONE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NONE' at line 1
CREATE ROLE PROCESS;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCESS' at line 1
CREATE ROLE PROXY;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROXY' at line 1
CREATE ROLE RELOAD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELOAD' at line 1
CREATE ROLE REPLICATION;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLICATION' at line 1
CREATE ROLE RESOURCE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RESOURCE' at line 1
CREATE ROLE SUPER;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUPER' at line 1
SET GLOBAL = DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= DEFAULT' at line 1
SET LOCAL = DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= DEFAULT' at line 1
SET PERSIST = DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= DEFAULT' at line 1
SET PERSIST_ONLY = DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= DEFAULT' at line 1
SET SESSION = DEFAULT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= DEFAULT' at line 1
#
# Bug#29033659: SOME NON-RESERVED WORDS CAN'T BE USED AS SP LABELS
#
CREATE FUNCTION f1() RETURNS INT
BEGIN
ACCOUNT: LOOP RETURN 1; END LOOP;
ALWAYS: LOOP RETURN 1; END LOOP;
BACKUP: LOOP RETURN 1; END LOOP;
CLOSE: LOOP RETURN 1; END LOOP;
FORMAT: LOOP RETURN 1; END LOOP;
GROUP_REPLICATION: LOOP RETURN 1; END LOOP;
HOST: LOOP RETURN 1; END LOOP;
INVISIBLE: LOOP RETURN 1; END LOOP;
OPEN: LOOP RETURN 1; END LOOP;
OPTIONS: LOOP RETURN 1; END LOOP;
OWNER: LOOP RETURN 1; END LOOP;
PARSER: LOOP RETURN 1; END LOOP;
PORT: LOOP RETURN 1; END LOOP;
REMOVE: LOOP RETURN 1; END LOOP;
RESTORE: LOOP RETURN 1; END LOOP;
ROLE: LOOP RETURN 1; END LOOP;
SECONDARY: LOOP RETURN 1; END LOOP;
SECONDARY_ENGINE: LOOP RETURN 1; END LOOP;
SECONDARY_LOAD: LOOP RETURN 1; END LOOP;
SECONDARY_UNLOAD: LOOP RETURN 1; END LOOP;
SECURITY: LOOP RETURN 1; END LOOP;
SERVER: LOOP RETURN 1; END LOOP;
SOCKET: LOOP RETURN 1; END LOOP;
SONAME: LOOP RETURN 1; END LOOP;
UPGRADE: LOOP RETURN 1; END LOOP;
VISIBLE: LOOP RETURN 1; END LOOP;
WRAPPER: LOOP RETURN 1; END LOOP;
END|
DROP FUNCTION f1;
#
# Bug#29205289: PARSER ACCEPTS UNDOCUMENTED SYNTAX: `... = TABLE_ALIAS`
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1=a READ;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a READ' at line 1
HANDLER t1 OPEN=a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a' at line 1
SELECT * FROM t1=a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a' at line 1
SELECT * FROM (SELECT 1)=a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a' at line 1
SELECT * FROM t1 JOIN t1=a;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a' at line 1
UPDATE t1=a SET i=0;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=a SET i=0' at line 1
DROP TABLE t1;
#
# Bug #22320942: ODBC OUTER JOIN ESCAPE SEQUENCE SYNTAX IS BROKEN
#
CREATE TEMPORARY TABLE t1 (i INT);
CREATE TEMPORARY TABLE t2 (i INT);
SELECT * FROM { OJ t1 LEFT JOIN t2 ON TRUE };
i	i
SELECT * FROM { `OJ` t1 LEFT JOIN t2 ON TRUE };
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`OJ` t1 LEFT JOIN t2 ON TRUE }' at line 1
SELECT * FROM { random_identifier t1 LEFT JOIN t2 ON TRUE };
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'random_identifier t1 LEFT JOIN t2 ON TRUE }' at line 1
DROP TABLE t1, t2;
#
# Bug #28997518: "COLLATE X GENERATED ALWAYS ... COLLATE Y": X HAS
#                 A PRIORITY OVER Y
#
CREATE TABLE t1 (
x VARCHAR(10)
COLLATE ascii_bin
COLLATE ascii_bin
);
ERROR 42000: Multiple COLLATE clauses near 'ascii_bin
)' at line 4
CREATE TABLE t2 (
x VARCHAR(10)
COLLATE ascii_bin
COLLATE ascii_general_ci
);
ERROR 42000: Multiple COLLATE clauses near 'ascii_general_ci
)' at line 4
CREATE TABLE t3 (
x VARCHAR(10)
COLLATE ascii_bin
NOT NULL
COLLATE ascii_bin
);
ERROR 42000: Multiple COLLATE clauses near 'ascii_bin
)' at line 5
CREATE TABLE t3 (
x VARCHAR(10)
COLLATE ascii_bin
GENERATED ALWAYS AS(NULL)
COLLATE ascii_bin
);
ERROR 42000: Multiple COLLATE clauses near 'COLLATE ascii_bin
GENERATED ALWAYS AS(NULL)
COLLATE ascii_bin
)' at line 3
CREATE TABLE t4 (
x VARCHAR(10)
GENERATED ALWAYS AS(NULL)
COLLATE ascii_bin
COLLATE ascii_bin
);
ERROR 42000: Multiple COLLATE clauses near 'ascii_bin
)' at line 5
#
# Bug#28961997: REMOVE PARENTHESES INFORMATION FROM AST
#
CREATE VIEW v1 AS (SELECT 1 ORDER BY 1) UNION (SELECT 3 ORDER BY 1) ORDER BY 1;
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` union select 3 AS `3` order by `1`	utf8mb4	utf8mb4_0900_ai_ci
DROP VIEW v1;
#
# Bug#29871803: REQUIRED PARENTHESES NO LONGER PRINTED FOR CERTAIN UNION
# STATEMENTS
#
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
CREATE TABLE t3(a INTEGER, b INTEGER, c INTEGER);
INSERT INTO t3 VALUES(1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
3	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	(/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` limit 1) union /* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`
EXPLAIN SELECT a FROM t1 UNION (SELECT a FROM t2 LIMIT 1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
3	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` union (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` limit 1)
CREATE VIEW v1 as (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
SHOW CREATE VIEW v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` order by `t1`.`a` limit 1) union select `t2`.`a` AS `a` from `t2`	utf8mb4	utf8mb4_0900_ai_ci
CREATE VIEW v2 as SELECT a FROM t1 UNION (SELECT a FROM t2 ORDER BY a LIMIT 1);
SHOW CREATE VIEW v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` union (select `t2`.`a` AS `a` from `t2` order by `t2`.`a` limit 1)	utf8mb4	utf8mb4_0900_ai_ci
EXPLAIN (SELECT * FROM t3 ORDER BY a LIMIT 3) ORDER BY b DESC LIMIT 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t3	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using filesort
2	RESULT	<ordered/limited1>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
Warnings:
Note	1003	(/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` order by `test`.`t3`.`a` limit 3) order by `b` desc limit 2
EXPLAIN ((SELECT * FROM t3 ORDER BY a LIMIT 3)
ORDER BY b DESC LIMIT 2)
ORDER BY c LIMIT 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t3	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using filesort
2	RESULT	<ordered/limited1>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
3	RESULT	<ordered/limited2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary; Using filesort
Warnings:
Note	1003	((/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` order by `test`.`t3`.`a` limit 3) order by `b` desc limit 2) order by `c` limit 1
DROP VIEW v1;
DROP VIEW v2;
DROP TABLE t1, t2, t3;
#
# Bug#30131161: SYNTAX ERROR WITH || WHEN USING PIPES_AS_CONCAT AFTER
#               UPGRADING TO 8.0.17
#
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));
SELECT 'ab' LIKE 'a%', 'ab' LIKE 'a' || '%';
'ab' LIKE 'a%'	'ab' LIKE 'a' || '%'
1	1
SELECT 'ab' NOT LIKE 'a%', 'ab' NOT LIKE 'a' || '%';
'ab' NOT LIKE 'a%'	'ab' NOT LIKE 'a' || '%'
0	0
SELECT 'ab' LIKE 'ac', 'ab' LIKE 'a' || 'c';
'ab' LIKE 'ac'	'ab' LIKE 'a' || 'c'
0	0
SELECT 'ab' NOT LIKE 'ac', 'ab' NOT LIKE 'a' || 'c';
'ab' NOT LIKE 'ac'	'ab' NOT LIKE 'a' || 'c'
1	1
SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!' || '%' ESCAPE '!';
'a%' LIKE 'a!%' ESCAPE '!'	'a%' LIKE 'a!' || '%' ESCAPE '!'
1	1
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!' || '%' ESCAPE '!';
'a%' NOT LIKE 'a!%' ESCAPE '!'	'a%' NOT LIKE 'a!' || '%' ESCAPE '!'
0	0
SELECT 'a%' LIKE 'a!%' ESCAPE '$', 'a%' LIKE 'a!' || '%' ESCAPE '$';
'a%' LIKE 'a!%' ESCAPE '$'	'a%' LIKE 'a!' || '%' ESCAPE '$'
0	0
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '$', 'a%' NOT LIKE 'a!' || '%' ESCAPE '$';
'a%' NOT LIKE 'a!%' ESCAPE '$'	'a%' NOT LIKE 'a!' || '%' ESCAPE '$'
1	1
SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!%' ESCAPE '' || '!';
'a%' LIKE 'a!%' ESCAPE '!'	'a%' LIKE 'a!%' ESCAPE '' || '!'
1	1
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!%' ESCAPE '' || '!';
'a%' NOT LIKE 'a!%' ESCAPE '!'	'a%' NOT LIKE 'a!%' ESCAPE '' || '!'
0	0
SELECT 'a%' LIKE 'a!%' ESCAPE '' || '$', 'a%' LIKE 'a!%' ESCAPE '' || '$';
'a%' LIKE 'a!%' ESCAPE '' || '$'	'a%' LIKE 'a!%' ESCAPE '' || '$'
0	0
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '' || '$', 'a%' NOT LIKE 'a!%' ESCAPE '' || '$';
'a%' NOT LIKE 'a!%' ESCAPE '' || '$'	'a%' NOT LIKE 'a!%' ESCAPE '' || '$'
1	1
SELECT 1 ^ 100, 1 ^ '10' || '0';
1 ^ 100	1 ^ '10' || '0'
101	101
SELECT -1 || '0';
-1 || '0'
-10
SET sql_mode=DEFAULT;
#
# WL#13559: Deprecate syntax: confusing combinations of UNION and INTO
#
#
# No warning expected:
#
SELECT 1 UNION SELECT 1 INTO @var;
(SELECT 1 UNION SELECT 1 INTO @var);
SELECT 1 UNION SELECT 1 FROM DUAL INTO @var;
(SELECT 1 UNION SELECT 1 FROM DUAL INTO @var);
SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var;
(SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var);
# Check that this also works with flatten_equal_set_ops
# (minimum three operands to check this)
SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var);
SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var);
SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var);
#
# Deprecation warning expected:
#
SELECT 1 UNION SELECT 1 INTO @var FROM DUAL;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
# Check that warning also works with flatten_equal_set_ops
# (minimum three operands to check this)
SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FROM DUAL;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
(SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE);
Warnings:
Warning	3962	The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead.
# Check that PT_set_operation::has_into_clause works correctly
# for more than two operands after flatten_equal_set_ops
SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL) UNION SELECT 1;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
SELECT 1 UNION SELECT 1 UNION SELECT * FROM (SELECT 2 UNION SELECT 1 INTO @var FROM DUAL) t;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
SELECT 1 UNION SELECT 1 UNION SELECT * FROM (SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FROM DUAL) t;
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.
#
# Syntax error expected:
#
SELECT 1 UNION (SELECT 1 FROM DUAL INTO @var);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @var)' at line 1
#
# Bug#30871301: CTE CAN FAIL WITH A PARSE ERROR ON PSEUDO-COMMENTS
#
WITH cte AS (SELECT 0 /*! ) */ SELECT * FROM cte a, cte b;
0	0
0	0
WITH cte AS /*! ( */ SELECT 0) SELECT * FROM cte a, cte b;
0	0
0	0
#
# Bug#30528450: SPECIAL SYMBOL NAMED COLUMN NOT HONORED IN A SELECT
#
CREATE TABLE t1 (c1 INT, `*` INT, c3 INT);
INSERT INTO t1 VALUES (1, 2, 3);
SELECT `*` FROM t1;
*
2
SELECT t1.`*`, t1.* FROM t1;
*	c1	*	c3
2	1	2	3
SELECT test.t1.`*`, test.t1.* FROM t1;
*	c1	*	c3
2	1	2	3
DROP TABLE t1;
#
# Bug#30592703: UNDOCUMENTED SYNTAX
#               '('<QUERY BLOCK>')' {LIMIT|ORDER [LIMIT]} INTO ...
#
(SELECT 1) LIMIT 1 INTO @var;
SELECT @var;
@var
1
(SELECT 2 AS c) ORDER BY c INTO @var;
SELECT @var;
@var
2
(SELECT 3 AS c) ORDER BY c LIMIT 1 INTO @var;
SELECT @var;
@var
3
(SELECT 4) INTO @var;
SELECT @var;
@var
4
#
# <time zone> syntax.
#
SET time_zone = '+01:00';
SELECT cast( DATE'2019-10-10' AT LOCAL AS DATETIME );
ERROR 42000: This version of MySQL doesn't yet support 'AT LOCAL'
SELECT cast( DATE'2019-10-10' AT TIME ZONE 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( TIME'10:10' AT LOCAL AS DATETIME );
ERROR 42000: This version of MySQL doesn't yet support 'AT LOCAL'
SELECT cast( TIME'10:10' AT TIME ZONE '+01:00' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( TIME'10:10' AT TIME ZONE INTERVAL 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( TIME'10:10' AT TIME ZONE '' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( TIME'10:10' AT TIME ZONE INTERVAL '' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( TIMESTAMP'2019-10-10 10:11:12' AT LOCAL AS DATETIME );
ERROR 42000: This version of MySQL doesn't yet support 'AT LOCAL'
SELECT cast( NULL AT TIME ZONE 'UTC' AS DATETIME );
cast( NULL AT TIME ZONE 'UTC' AS DATETIME )
NULL
# Casting to prohibited types
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS BINARY );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BINARY )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS CHAR );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAR )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DATE );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DECIMAL );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECIMAL )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DOUBLE );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS FLOAT );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FLOAT )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS SIGNED INTEGER );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SIGNED INTEGER )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS TIME );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIME )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS UNSIGNED INTEGER );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED INTEGER )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS JSON );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON )' at line 1
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS REAL );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REAL )' at line 1
#
# Bug#32230229: MISMATCH EXPLAIN RESULT TO ACTUAL RUN WITH
#               USER-DEFINED VARIABLE
#
EXPLAIN SELECT 1 INTO @x FROM DUAL INTO @y;
ERROR HY000: Multiple INTO clauses in one query block.
EXPLAIN SELECT 1 INTO DUMPFILE 'file1' FROM DUAL INTO DUMPFILE 'file2';
ERROR HY000: Multiple INTO clauses in one query block.
EXPLAIN SELECT 1 INTO OUTFILE 'file1' FROM DUAL INTO OUTFILE 'file2';
ERROR HY000: Multiple INTO clauses in one query block.
#
# Deprecate usage of "full" as unquoted identfier
#
CREATE TABLE full(i INT);
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
DROP TABLE full;
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
CREATE TABLE `full`(i INT);
SELECT * from `full`;
i
SELECT * from `full` AS full;
i
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
SELECT * from `full` AS `full`;
i
SELECT * from full;
i
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
SELECT * from full as full;
i
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
SELECT * from `full` full;
i
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
SELECT * from full outer;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer' at line 1
SET @save_sql_mode=@@sql_mode;
SET SQL_MODE = 'ANSI_QUOTES';
SELECT * from `full`;
i
SELECT * from "full";
i
SELECT * from full;
i
Warnings:
Warning	4119	Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
SET @@sql_mode=@save_sql_mode;
DROP TABLE `full`;