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(11) 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(11) 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(11) 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(12) 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 master_pos_wait(); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select master_pos_wait(1); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select master_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 # # Should use myisam create logfile group ndb_lg1 add undofile 'ndb_undo1.dat' initial_size=32M engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' create logfile group ndb_lg1 add undofile 'ndb_undo1.dat' engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' create logfile group ndb_lg1 add undofile 'ndb_undo1.dat' engine=innodb; ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB). create tablespace ndb_ts1 add datafile 'ndb_ts1.dat' use logfile group ndb_lg1 engine=myisam initial_size=32M; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE' create tablespace ndb_ts1 add datafile 'ndb_ts1.dat' use logfile group ndb_lg1 engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE' create tablespace ndb_ts1 add datafile 'ndb_ts1.dat' use logfile group ndb_lg1 engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE' alter logfile group ndb_lg1 add undofile 'ndb_undo1.dat' wait engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' alter logfile group ndb_lg1 add undofile 'ndb_undo1.dat' engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' alter logfile group ndb_lg1 add undofile 'ndb_undo1.dat' engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' alter tablespace ndb_ts1 add datafile 'ndb_ts1.dat' initial_size=32M engine=myisam; ERROR HY000: Tablespace ndb_ts1 doesn't exist. alter tablespace ndb_ts1 add datafile 'ndb_ts1.dat' engine=myisam; ERROR HY000: Tablespace ndb_ts1 doesn't exist. alter tablespace ndb_ts1 add datafile 'ndb_ts1.dat' engine=myisam; ERROR HY000: Tablespace ndb_ts1 doesn't exist. drop logfile group ndb_lg1 engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' drop logfile group ndb_lg1 engine=myisam; ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP' drop tablespace ndb_ts1 engine=myisam; ERROR HY000: Tablespace ndb_ts1 doesn't exist. drop tablespace ndb_ts1 engine=myisam; ERROR HY000: Tablespace ndb_ts1 doesn't exist. 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 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_2' at line 1 SELECT 1 INTO @var17727401_1 FROM DUAL INTO @var17727401_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 'INTO @var17727401_2' at line 2 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 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_2' at line 3 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 # 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 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); 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 2 UNION SELECT 3)' at line 1 (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 ); 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 UNION SELECT 1 )' at line 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; 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 UNION SELECT 1 ) UNION SELECT 1' 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 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 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: Incorrect usage of UNION and INTO (SELECT 1 INTO @v) UNION SELECT 1; ERROR HY000: Incorrect usage of UNION and INTO SELECT 1 INTO @v UNION (SELECT 1); ERROR HY000: Incorrect usage of UNION and INTO ((SELECT 1 INTO @v) UNION (SELECT 1)); ERROR HY000: Incorrect usage of UNION and INTO 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; (SELECT 1) UNION SELECT 1 INTO @v FROM t1; (SELECT 1) UNION (SELECT 1 INTO @v FROM t1); ((SELECT 1) UNION (SELECT 1 INTO @v FROM t1)); SELECT 1 UNION SELECT 2 INTO OUTFILE 'parser.test.file1'; SELECT 1 UNION (SELECT 2 INTO OUTFILE 'parser.test.file2'); (SELECT 1) UNION SELECT 2 INTO OUTFILE 'parser.test.file3'; (SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file4'); ((SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file5')); SELECT * FROM (SELECT a INTO @v FROM t1) 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 '(SELECT a INTO @v FROM t1) t1a' at line 1 SELECT * FROM (SELECT a INTO @v) 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 '(SELECT a INTO @v) t1a' at line 1 DROP TABLE t1; # # Part 4: The 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 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 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 3 3 3 3 1 1 NULL NULL 2 2 NULL NULL 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; Warnings: Warning 3515 Hints aren't supported in CREATE or ALTER VIEW ALTER VIEW v1 AS SELECT /*+ QB_NAME(a) */ 1; Warnings: Warning 3515 Hints aren't supported in CREATE or ALTER VIEW 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(11) 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(11) 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(11) 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(11) 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(11) 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(11) 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(11) 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