|
|
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
|
|
|
#
|
|
|
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 ('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
|
|
|
INSERT INTO t1 VALUES ('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' '');
|
|
|
SELECT HEX(a) FROM t1;
|
|
|
HEX(a)
|
|
|
A3A1AAAF
|
|
|
A3A1AAAF
|
|
|
DROP DATABASE mysqltest1;
|
|
|
USE test;
|
|
|
#
|
|
|
# WL #7201, WL #7202 and WL#8062 coverage tests
|
|
|
#
|
|
|
CREATE TABLE t1 (i INT);
|
|
|
INSERT INTO t1 () SELECT * FROM t1;
|
|
|
INSERT INTO t1 SELECT HIGH_PRIORITY * FROM t1;
|
|
|
INSERT INTO t1 SELECT DISTINCT ALL * FROM t1;
|
|
|
ERROR HY000: Incorrect usage of ALL and DISTINCT
|
|
|
REPLACE INTO `` SELECT * FROM ``;
|
|
|
ERROR 42000: Incorrect table name ''
|
|
|
DELETE QUICK FROM t1 WHERE i = 0;
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug #21035515: PARSE_GCOL_EXPR SHOULD BE A KIND OF RESERVED WORD,
|
|
|
# NOT A KEYWORD
|
|
|
SET @parse_gcol_expr = 1;
|
|
|
SELECT 1 AS parse_gcol_expr;
|
|
|
parse_gcol_expr
|
|
|
1
|
|
|
CREATE TABLE parse_gcol_expr (i INT);
|
|
|
DROP TABLE parse_gcol_expr;
|
|
|
# parse_gcol_expr can't be a label:
|
|
|
CREATE PROCEDURE p1()
|
|
|
BEGIN
|
|
|
parse_gcol_expr: LOOP
|
|
|
SELECT 1;
|
|
|
END LOOP parse_gcol_expr;
|
|
|
END|
|
|
|
DROP PROCEDURE p1;
|
|
|
# PARSE_GCOL_EXPR is not a valid statement:
|
|
|
PARSE_GCOL_EXPR (1);
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARSE_GCOL_EXPR (1)' at line 1
|
|
|
#
|
|
|
# Bug #17400320 ALGORITHM= IS NOT SUPPORTED FOR ALTER TABLE WITH <PARTITION_OPTIONS>
|
|
|
#
|
|
|
CREATE TABLE t1 (x INT PRIMARY KEY);
|
|
|
ALTER TABLE t1;
|
|
|
ALTER TABLE t1 ALGORITHM=DEFAULT;
|
|
|
ALTER TABLE t1 ALGORITHM=COPY;
|
|
|
ALTER TABLE t1 ALGORITHM=INPLACE;
|
|
|
ALTER TABLE t1 LOCK=DEFAULT;
|
|
|
ALTER TABLE t1 LOCK=NONE;
|
|
|
ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
|
|
|
ALTER TABLE t1 LOCK=SHARED;
|
|
|
ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
|
|
|
ALTER TABLE t1 LOCK=EXCLUSIVE;
|
|
|
ALTER TABLE t1 LOCK=SHARED, ALGORITHM=COPY,
|
|
|
LOCK=NONE, ALGORITHM=DEFAULT,
|
|
|
LOCK=EXCLUSIVE, ALGORITHM=INPLACE;
|
|
|
ALTER TABLE t1 WITH VALIDATION;
|
|
|
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
|
|
|
ALTER TABLE t1 WITHOUT VALIDATION;
|
|
|
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
|
|
|
ALTER TABLE t1 LOCK=SHARED, WITH VALIDATION, ALGORITHM=COPY,
|
|
|
LOCK=EXCLUSIVE, WITHOUT VALIDATION, ALGORITHM=INPLACE;
|
|
|
ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# WL#8083: Introduce <query expression> parser rule
|
|
|
# Bug#14743786: PARSE ERROR WITH UNION PARENTHESES ON TOP LEVEL
|
|
|
#
|
|
|
#
|
|
|
# Part1: Regression Testing.
|
|
|
#
|
|
|
CREATE TABLE t1 ( a INT );
|
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
|
CREATE TABLE t2 ( a INT );
|
|
|
INSERT INTO t2 VALUES ( 2 ), ( 2 );
|
|
|
CREATE TABLE t3 ( a INT );
|
|
|
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );
|
|
|
SELECT 1 UNION SELECT 2;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(SELECT 1) UNION SELECT 2;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT 1 UNION (SELECT 2);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(SELECT 1) UNION (SELECT 2);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT 2 FROM t1 UNION ((SELECT 3 FROM t1));
|
|
|
2
|
|
|
2
|
|
|
3
|
|
|
(SELECT 2 FROM t1) UNION SELECT 3 FROM t1;
|
|
|
2
|
|
|
2
|
|
|
3
|
|
|
((SELECT 2 FROM t1)) UNION SELECT 3 FROM t1;
|
|
|
2
|
|
|
2
|
|
|
3
|
|
|
(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
|
|
|
1
|
|
|
1
|
|
|
(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
|
|
|
1
|
|
|
1
|
|
|
SELECT a FROM t1 LIMIT 1 UNION ALL SELECT a FROM t1;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT a FROM t1' at line 1
|
|
|
(SELECT 1) UNION (SELECT 2 UNION SELECT 3);
|
|
|
ERROR 42000: You 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
|
|
|
Warnings:
|
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
CREATE PROCEDURE p1() BEGIN IF whatever THEN SELECT 1; END IF; END|
|
|
|
CALL p1();
|
|
|
ERROR 42S22: Unknown column 'whatever' in 'field list'
|
|
|
DROP PROCEDURE p1;
|
|
|
#
|
|
|
# Part 2: Test of changed (fixed) behavior.
|
|
|
#
|
|
|
CREATE TABLE t1 ( a INT );
|
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
|
CREATE TABLE t2 ( a INT );
|
|
|
INSERT INTO t2 VALUES ( 2 ), ( 2 );
|
|
|
CREATE TABLE t3 ( a INT );
|
|
|
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );
|
|
|
(SELECT 1 FROM t1 UNION SELECT 2 FROM t1);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 FROM t1 UNION SELECT 2 FROM t1));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(SELECT 1 FROM t1 UNION (SELECT 2 FROM t1));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 FROM t1 UNION (SELECT 2 FROM t1)));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 FROM t1 UNION ((SELECT 2 FROM t1))));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 FROM t1) UNION SELECT 2 FROM t1);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1)));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((((SELECT 1 FROM t1)) UNION (SELECT 2 FROM t1)));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(((SELECT 1 FROM t1) UNION ((SELECT 2 FROM t1))));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
(SELECT 1 UNION SELECT 2) ORDER BY 1;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1 UNION SELECT 2)) ORDER BY 1;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
((SELECT 1) ORDER BY 1);
|
|
|
1
|
|
|
1
|
|
|
((SELECT 1) LIMIT 1);
|
|
|
1
|
|
|
1
|
|
|
(SELECT 1 UNION SELECT 2) LIMIT 1;
|
|
|
1
|
|
|
1
|
|
|
((SELECT 1 UNION SELECT 2)) LIMIT 1;
|
|
|
1
|
|
|
1
|
|
|
(SELECT a FROM t1) LIMIT 1 UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2' at line 1
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
#
|
|
|
# Part 3: The <table factor> syntax.
|
|
|
#
|
|
|
CREATE TABLE t1 ( a INT );
|
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
|
CREATE TABLE t2 ( a INT );
|
|
|
INSERT INTO t2 VALUES ( 2 );
|
|
|
CREATE TABLE t3 ( a INT );
|
|
|
INSERT INTO t3 VALUES ( 3 );
|
|
|
CREATE TABLE t4 ( a INT );
|
|
|
INSERT INTO t4 VALUES ( 3 );
|
|
|
SELECT * FROM (SELECT 1 FROM t1 UNION SELECT 2 FROM t1) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION SELECT 2 FROM t1)) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM (SELECT 1 FROM t1 UNION (SELECT 2 FROM t1)) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION (SELECT 2 FROM t1))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION ((SELECT 2 FROM t1)))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((SELECT 1 FROM t1) UNION SELECT 2 FROM t1) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM (((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1)) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1)) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM (((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION (SELECT 2 FROM t1))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM (((SELECT 1 FROM t1) UNION ((SELECT 2 FROM t1)))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1)))) dt;
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT * FROM (SELECT 1 FROM t1 UNION SELECT 2 FROM t1);
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION SELECT 2 FROM t1));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM (SELECT 1 FROM t1 UNION (SELECT 2 FROM t1));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION (SELECT 2 FROM t1)));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((SELECT 1 FROM t1 UNION ((SELECT 2 FROM t1))));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((SELECT 1 FROM t1) UNION SELECT 2 FROM t1);
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM (((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1);
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION SELECT 2 FROM t1));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM (((SELECT 1 FROM t1) UNION (SELECT 2 FROM t1)));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION (SELECT 2 FROM t1)));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM (((SELECT 1 FROM t1) UNION ((SELECT 2 FROM t1))));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM (( t1 JOIN t2 ON TRUE ));
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE JOIN t3 ON TRUE );
|
|
|
a a a
|
|
|
1 2 3
|
|
|
SELECT * FROM ((t1 JOIN t2 ON TRUE) JOIN t3 ON TRUE );
|
|
|
a a a
|
|
|
1 2 3
|
|
|
SELECT * FROM (t1 INNER JOIN t2 ON (t1.a = t2.a));
|
|
|
a a
|
|
|
SELECT 1 FROM (SELECT 1 FROM t1 INTO @v) a;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v) a' at line 1
|
|
|
SELECT 1 FROM (t1);
|
|
|
1
|
|
|
1
|
|
|
SELECT 1 FROM ((t1));
|
|
|
1
|
|
|
1
|
|
|
SELECT 1 UNION SELECT 2 FROM (t2);
|
|
|
1
|
|
|
1
|
|
|
2
|
|
|
SELECT 1 FROM (SELECT 2 ORDER BY 1) AS res;
|
|
|
1
|
|
|
1
|
|
|
#
|
|
|
# This syntax is no longer allowed
|
|
|
#
|
|
|
SELECT 1 FROM ((SELECT 2) ORDER BY 1) AS res;
|
|
|
1
|
|
|
1
|
|
|
SELECT 1 FROM ((SELECT 2) a ORDER BY 1) AS res;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY 1) AS res' at line 1
|
|
|
SELECT 1 FROM ((SELECT 2) LIMIT 1) AS res;
|
|
|
1
|
|
|
1
|
|
|
SELECT 1 FROM ((SELECT 2) a LIMIT 1) AS res;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1) AS res' at line 1
|
|
|
SELECT * FROM ( t1 AS alias1 );
|
|
|
a
|
|
|
1
|
|
|
SELECT * FROM t1 AS alias1, t2 AS alias2;
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( t1 AS alias1, t2 AS alias2 );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 JOIN t3 ON TRUE );
|
|
|
ERROR 42000: Not unique table/alias: 't1'
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 t11 JOIN t3 ON TRUE ) t1a;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1a' at line 1
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE, SELECT 1 FROM DUAL );
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL )' at line 1
|
|
|
SELECT * FROM ( t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a );
|
|
|
a a 1
|
|
|
1 2 1
|
|
|
SELECT * FROM t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a;
|
|
|
a a 1
|
|
|
1 2 1
|
|
|
SELECT * FROM ( SELECT 1 FROM DUAL );
|
|
|
ERROR 42000: Every derived table must have its own alias
|
|
|
SELECT * FROM ( SELECT 1 FROM DUAL ) t1a;
|
|
|
1
|
|
|
1
|
|
|
SELECT * FROM ( t1, t2 );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM (( t1, t2 ));
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( (t1), t2 );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM (((t1)), t2 );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( (t1), (t2) );
|
|
|
a a
|
|
|
1 2
|
|
|
SELECT * FROM ( t1, (t2) );
|
|
|
a a
|
|
|
1 2
|
|
|
((SELECT 1 UNION SELECT 1) UNION SELECT 1);
|
|
|
1
|
|
|
1
|
|
|
SELECT * FROM ((SELECT 1 UNION SELECT 1) UNION SELECT 1) a;
|
|
|
1
|
|
|
1
|
|
|
SELECT * FROM (t1, t2) JOIN (t3, t4) ON TRUE;
|
|
|
a a a a
|
|
|
1 2 3 3
|
|
|
SELECT * FROM ((t1, t2) JOIN t3 ON TRUE);
|
|
|
a a a
|
|
|
1 2 3
|
|
|
SELECT * FROM t1 JOIN ( t2, t3 ) USING ( a );
|
|
|
ERROR 23000: Column 'a' in from clause is ambiguous
|
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
# Check the format in the note.
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
EXPLAIN SELECT 1 FROM (SELECT 1 FROM t1) t;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1`
|
|
|
DROP TABLE t1;
|
|
|
CREATE TABLE t1 ( a INT );
|
|
|
CREATE TABLE t2 ( b INT );
|
|
|
CREATE TABLE t3 ( c INT );
|
|
|
CREATE TABLE t4 ( d INT );
|
|
|
INSERT INTO t1 VALUES (1);
|
|
|
INSERT INTO t2 VALUES (2);
|
|
|
INSERT INTO t3 VALUES (2);
|
|
|
INSERT INTO t4 VALUES (2);
|
|
|
SELECT * FROM t1 LEFT JOIN ( t2, t3, t4 ) ON a = c;
|
|
|
a b c d
|
|
|
1 NULL NULL NULL
|
|
|
SELECT * FROM t1 NATURAL JOIN ((t1 NATURAL JOIN t1), (t1 NATURAL JOIN t1));
|
|
|
ERROR 42000: Not unique table/alias: 't1'
|
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
CREATE TABLE t1 ( a INT );
|
|
|
CREATE TABLE t2 ( b INT );
|
|
|
CREATE TABLE t3 ( c INT );
|
|
|
CREATE TABLE t4 ( d INT );
|
|
|
CREATE TABLE t5 ( d INT );
|
|
|
SELECT * FROM t5 NATURAL JOIN ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4));
|
|
|
d a b c
|
|
|
SELECT * FROM ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4)) NATURAL JOIN t5;
|
|
|
d a b c
|
|
|
SELECT * FROM t1 JOIN ( t2, t3 ) ON TRUE;
|
|
|
a b c
|
|
|
SELECT * FROM ( t1, t2 , t3 );
|
|
|
a b c
|
|
|
SELECT * FROM ( ( t1, t2 ), t3 );
|
|
|
a b c
|
|
|
SELECT * FROM ( ((t1, t2)), t3 );
|
|
|
a b c
|
|
|
SELECT * FROM ( t1, ( t2, t3 ) );
|
|
|
a b c
|
|
|
SELECT * FROM ( t1, ((t2, t3)) );
|
|
|
a b c
|
|
|
SELECT * FROM ((( t1, t2 ), t3));
|
|
|
a b c
|
|
|
SELECT * FROM ((((t1, t2)), t3));
|
|
|
a b c
|
|
|
SELECT * FROM ((t1, ( t2, t3 )));
|
|
|
a b c
|
|
|
SELECT * FROM ((t1, ((t2, t3))));
|
|
|
a b c
|
|
|
CREATE VIEW v1 AS SELECT 1 INTO @v;
|
|
|
ERROR HY000: View's SELECT contains a 'INTO' clause
|
|
|
CREATE VIEW v1 AS SELECT 1 FROM ( SELECT 1 FROM t1 ) my_table;
|
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
|
DROP VIEW v1;
|
|
|
CREATE TABLE t1( a INT );
|
|
|
INSERT INTO t1 VALUES (1);
|
|
|
SELECT 1 INTO @v;
|
|
|
(SELECT 1 INTO @v);
|
|
|
((SELECT 1 INTO @v));
|
|
|
SELECT 1 FROM t1 INTO @v;
|
|
|
(SELECT 1 FROM t1 INTO @v);
|
|
|
((SELECT 1 FROM t1 INTO @v));
|
|
|
SELECT 1 FROM t1 INTO @v UNION SELECT 1;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
|
|
|
(SELECT 1 FROM t1 INTO @v) UNION SELECT 1;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1' at line 1
|
|
|
SELECT 1 FROM t1 INTO @v UNION (SELECT 1);
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1)' at line 1
|
|
|
((SELECT 1 FROM t1 INTO @v) UNION (SELECT 1));
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1))' at line 1
|
|
|
SELECT 1 INTO @v UNION SELECT 1;
|
|
|
ERROR HY000: 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 <joined table> syntax.
|
|
|
#
|
|
|
CREATE TABLE t1( a INT );
|
|
|
CREATE TABLE t2( b INT );
|
|
|
CREATE TABLE t3( c INT );
|
|
|
CREATE TABLE t4( d INT );
|
|
|
CREATE TABLE t5( e INT );
|
|
|
# We use EXPLAIN so we can get the parser's interpretation of the
|
|
|
# nesting. We don't really care about the execution plan.
|
|
|
SET optimizer_switch = 'block_nested_loop=off';
|
|
|
SELECT * FROM t1 JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 INNER JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 INNER JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 INNER JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 CROSS JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 CROSS JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 CROSS JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 STRAIGHT_JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 t11 NATURAL JOIN t1 t12;
|
|
|
a
|
|
|
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 ON t11.a = t12.a;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON t11.a = t12.a' at line 1
|
|
|
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 USING ( a );
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING ( a )' at line 1
|
|
|
SELECT * FROM t1 t11 NATURAL INNER JOIN t1 t12;
|
|
|
a
|
|
|
SELECT * FROM t1 LEFT JOIN t2;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 LEFT JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 NATURAL LEFT JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 LEFT OUTER JOIN t2;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
|
SELECT * FROM t1 LEFT OUTER JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 LEFT OUTER JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;
|
|
|
a b
|
|
|
SELECT * FROM t1 RIGHT JOIN t2;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
|
SELECT * FROM t1 RIGHT JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 RIGHT JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 NATURAL RIGHT JOIN t2;
|
|
|
b a
|
|
|
SELECT * FROM t1 RIGHT OUTER JOIN t2;
|
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
|
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON a = b;
|
|
|
a b
|
|
|
SELECT * FROM t1 t11 RIGHT OUTER JOIN t1 t12 USING ( a );
|
|
|
a
|
|
|
SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
|
|
|
b a
|
|
|
# Right-deep join nesting.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
|
|
|
# Right-deep join nesting from t2 and on.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t2.b = t3.c;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c` = `test`.`t2`.`b`) and (`test`.`t4`.`d` = `test`.`t2`.`b`))
|
|
|
# Right-deep join nesting from t2 and on.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`d` = `test`.`t3`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`a`))
|
|
|
# Left-deep join nesting.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t2.b = t3.c;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
|
|
|
# A cross join joined with an inner join (i.e. with a join condition).
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
|
|
|
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING ( a );
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t11 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t12 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` join `test`.`t1` `t12` where (`test`.`t12`.`a` = `test`.`t11`.`a`)
|
|
|
# A left-deep cross join tree joined with an inner join.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t5 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d`,`test`.`t5`.`e` AS `e` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` join `test`.`t5` where (`test`.`t2`.`b` = `test`.`t1`.`a`)
|
|
|
# The different kinds of <table_ref> when used with context-dependent
|
|
|
# join. Tests the top-down build of the parse tree.
|
|
|
EXPLAIN SELECT * FROM t1 JOIN (t2) JOIN t3;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3`
|
|
|
EXPLAIN SELECT * FROM t1 JOIN (SELECT 1 AS b) a JOIN t3;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 PRIMARY <derived2> NULL system NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,'1' AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t3`
|
|
|
EXPLAIN SELECT * FROM t1 JOIN (t2 JOIN t3) JOIN t4;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4`
|
|
|
EXPLAIN SELECT * FROM t1 JOIN (t2, t3) JOIN t4;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4`
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t4.d;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c` = `test`.`t2`.`b`) and (`test`.`t4`.`d` = `test`.`t1`.`a`))
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t2.b;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
|
|
|
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t11 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t12 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t13 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` join `test`.`t1` `t12` join `test`.`t1` `t13` where ((`test`.`t12`.`a` = `test`.`t11`.`a`) and (`test`.`t13`.`a` = `test`.`t11`.`a`))
|
|
|
EXPLAIN SELECT * FROM t2 JOIN t1 t11 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t11 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t12 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
1 SIMPLE t13 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` join `test`.`t1` `t11` join `test`.`t1` `t12` join `test`.`t1` `t13` where ((`test`.`t12`.`a` = `test`.`t11`.`a`) and (`test`.`t13`.`a` = `test`.`t11`.`a`))
|
|
|
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`d` AS `d` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where (`test`.`t4`.`d` = `test`.`t3`.`c`)
|
|
|
SET optimizer_switch = DEFAULT;
|
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
|
# Testing correct nesting of natural joins.
|
|
|
CREATE TABLE t1( a INT, b int );
|
|
|
CREATE TABLE t2( a INT, c int );
|
|
|
CREATE TABLE t3( a INT, d int );
|
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
|
|
|
INSERT INTO t2 VALUES (2, 2), (3, 3), (4, 4);
|
|
|
INSERT INTO t3 VALUES (3, 3), (4, 4), (5, 5);
|
|
|
# The two queries below should produce identical result sets.
|
|
|
SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL RIGHT JOIN t3;
|
|
|
a d b c
|
|
|
3 3 3 3
|
|
|
4 4 NULL NULL
|
|
|
5 5 NULL NULL
|
|
|
SELECT * FROM (t1 NATURAL LEFT JOIN t2) NATURAL RIGHT JOIN t3;
|
|
|
a d b c
|
|
|
3 3 3 3
|
|
|
4 4 NULL NULL
|
|
|
5 5 NULL NULL
|
|
|
# This result should differ from the result sets above.
|
|
|
SELECT * FROM t1 NATURAL LEFT JOIN (t2 NATURAL RIGHT JOIN t3);
|
|
|
a b d c
|
|
|
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
|
|
|
#
|
|
|
# Bug#28961997: REMOVE PARENTHESES INFORMATION FROM AST
|
|
|
#
|
|
|
CREATE VIEW v1 AS (SELECT 1 ORDER BY 1) UNION (SELECT 3 ORDER BY 1) ORDER BY 1;
|
|
|
SHOW CREATE VIEW v1;
|
|
|
View Create View character_set_client collation_connection
|
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` union select 3 AS `3` order by `1` utf8mb4 utf8mb4_0900_ai_ci
|
|
|
DROP VIEW v1;
|
|
|
#
|
|
|
# Bug#29871803: REQUIRED PARENTHESES NO LONGER PRINTED FOR CERTAIN UNION
|
|
|
# STATEMENTS
|
|
|
#
|
|
|
CREATE TABLE t1(a INTEGER);
|
|
|
CREATE TABLE t2(a INTEGER);
|
|
|
CREATE TABLE t3(a INTEGER, b INTEGER, c INTEGER);
|
|
|
INSERT INTO t3 VALUES(1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
|
|
|
EXPLAIN (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using filesort
|
|
|
2 UNION t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
|
Warnings:
|
|
|
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` limit 1) union /* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`
|
|
|
EXPLAIN SELECT a FROM t1 UNION (SELECT a FROM t2 LIMIT 1);
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
2 UNION t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` union (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` limit 1)
|
|
|
CREATE VIEW v1 as (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
|
|
|
SHOW CREATE VIEW v1;
|
|
|
View Create View character_set_client collation_connection
|
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` order by `t1`.`a` limit 1) union select `t2`.`a` AS `a` from `t2` utf8mb4 utf8mb4_0900_ai_ci
|
|
|
CREATE VIEW v2 as SELECT a FROM t1 UNION (SELECT a FROM t2 ORDER BY a LIMIT 1);
|
|
|
SHOW CREATE VIEW v2;
|
|
|
View Create View character_set_client collation_connection
|
|
|
v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` union (select `t2`.`a` AS `a` from `t2` order by `t2`.`a` limit 1) utf8mb4 utf8mb4_0900_ai_ci
|
|
|
EXPLAIN (SELECT * FROM t3 ORDER BY a LIMIT 3) ORDER BY b DESC LIMIT 2;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 100.00 Using filesort
|
|
|
Warnings:
|
|
|
Note 1003 (/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` order by `test`.`t3`.`a` limit 3) order by `test`.`t3`.`b` desc limit 2
|
|
|
EXPLAIN ((SELECT * FROM t3 ORDER BY a LIMIT 3)
|
|
|
ORDER BY b DESC LIMIT 2)
|
|
|
ORDER BY c LIMIT 1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 100.00 Using filesort
|
|
|
Warnings:
|
|
|
Note 1003 (/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` order by `test`.`t3`.`a` limit 3) order by `test`.`t3`.`c` limit 1
|
|
|
DROP VIEW v1;
|
|
|
DROP VIEW v2;
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
#
|
|
|
# Bug#30131161: SYNTAX ERROR WITH || WHEN USING PIPES_AS_CONCAT AFTER
|
|
|
# UPGRADING TO 8.0.17
|
|
|
#
|
|
|
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));
|
|
|
SELECT 'ab' LIKE 'a%', 'ab' LIKE 'a' || '%';
|
|
|
'ab' LIKE 'a%' 'ab' LIKE 'a' || '%'
|
|
|
1 1
|
|
|
SELECT 'ab' NOT LIKE 'a%', 'ab' NOT LIKE 'a' || '%';
|
|
|
'ab' NOT LIKE 'a%' 'ab' NOT LIKE 'a' || '%'
|
|
|
0 0
|
|
|
SELECT 'ab' LIKE 'ac', 'ab' LIKE 'a' || 'c';
|
|
|
'ab' LIKE 'ac' 'ab' LIKE 'a' || 'c'
|
|
|
0 0
|
|
|
SELECT 'ab' NOT LIKE 'ac', 'ab' NOT LIKE 'a' || 'c';
|
|
|
'ab' NOT LIKE 'ac' 'ab' NOT LIKE 'a' || 'c'
|
|
|
1 1
|
|
|
SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!' || '%' ESCAPE '!';
|
|
|
'a%' LIKE 'a!%' ESCAPE '!' 'a%' LIKE 'a!' || '%' ESCAPE '!'
|
|
|
1 1
|
|
|
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!' || '%' ESCAPE '!';
|
|
|
'a%' NOT LIKE 'a!%' ESCAPE '!' 'a%' NOT LIKE 'a!' || '%' ESCAPE '!'
|
|
|
0 0
|
|
|
SELECT 'a%' LIKE 'a!%' ESCAPE '$', 'a%' LIKE 'a!' || '%' ESCAPE '$';
|
|
|
'a%' LIKE 'a!%' ESCAPE '$' 'a%' LIKE 'a!' || '%' ESCAPE '$'
|
|
|
0 0
|
|
|
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '$', 'a%' NOT LIKE 'a!' || '%' ESCAPE '$';
|
|
|
'a%' NOT LIKE 'a!%' ESCAPE '$' 'a%' NOT LIKE 'a!' || '%' ESCAPE '$'
|
|
|
1 1
|
|
|
SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!%' ESCAPE '' || '!';
|
|
|
'a%' LIKE 'a!%' ESCAPE '!' 'a%' LIKE 'a!%' ESCAPE '' || '!'
|
|
|
1 1
|
|
|
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!%' ESCAPE '' || '!';
|
|
|
'a%' NOT LIKE 'a!%' ESCAPE '!' 'a%' NOT LIKE 'a!%' ESCAPE '' || '!'
|
|
|
0 0
|
|
|
SELECT 'a%' LIKE 'a!%' ESCAPE '' || '$', 'a%' LIKE 'a!%' ESCAPE '' || '$';
|
|
|
'a%' LIKE 'a!%' ESCAPE '' || '$' 'a%' LIKE 'a!%' ESCAPE '' || '$'
|
|
|
0 0
|
|
|
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '' || '$', 'a%' NOT LIKE 'a!%' ESCAPE '' || '$';
|
|
|
'a%' NOT LIKE 'a!%' ESCAPE '' || '$' 'a%' NOT LIKE 'a!%' ESCAPE '' || '$'
|
|
|
1 1
|
|
|
SELECT 1 ^ 100, 1 ^ '10' || '0';
|
|
|
1 ^ 100 1 ^ '10' || '0'
|
|
|
101 101
|
|
|
SELECT -1 || '0';
|
|
|
-1 || '0'
|
|
|
-10
|
|
|
SET sql_mode=DEFAULT;
|
|
|
|
