diff options
Diffstat (limited to 'mysql-test/main/parser.test')
| -rw-r--r-- | mysql-test/main/parser.test | 1381 |
1 files changed, 1381 insertions, 0 deletions
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test new file mode 100644 index 00000000000..1f176c6afc5 --- /dev/null +++ b/mysql-test/main/parser.test @@ -0,0 +1,1381 @@ +# +# This file contains tests covering the parser +# + +#============================================================================= +# LEXICAL PARSER (lex) +#============================================================================= + +# +# Maintainer: these tests are for the lexical parser, so every character, +# even whitespace or comments, is significant here. +# + +SET @save_sql_mode=@@sql_mode; + +# +# Documenting the current behavior, to detect incompatible changes. +# In each cases: +# - no error is the correct result +# - an error is the expected result with the current implementation, +# and is a limitation. + +set SQL_MODE=''; + +create table ADDDATE(a int); +drop table ADDDATE; +create table ADDDATE (a int); +drop table ADDDATE; + +--error ER_PARSE_ERROR +create table BIT_AND(a int); +create table BIT_AND (a int); +drop table BIT_AND; + +--error ER_PARSE_ERROR +create table BIT_OR(a int); +create table BIT_OR (a int); +drop table BIT_OR; + +--error ER_PARSE_ERROR +create table BIT_XOR(a int); +create table BIT_XOR (a int); +drop table BIT_XOR; + +--error ER_PARSE_ERROR +create table CAST(a int); +create table CAST (a int); +drop table CAST; + +--error ER_PARSE_ERROR +create table COUNT(a int); +create table COUNT (a int); +drop table COUNT; + +--error ER_PARSE_ERROR +create table CURDATE(a int); +create table CURDATE (a int); +drop table CURDATE; + +--error ER_PARSE_ERROR +create table CURTIME(a int); +create table CURTIME (a int); +drop table CURTIME; + +--error ER_PARSE_ERROR +create table DATE_ADD(a int); +create table DATE_ADD (a int); +drop table DATE_ADD; + +--error ER_PARSE_ERROR +create table DATE_SUB(a int); +create table DATE_SUB (a int); +drop table DATE_SUB; + +--error ER_PARSE_ERROR +create table EXTRACT(a int); +create table EXTRACT (a int); +drop table EXTRACT; + +--error ER_PARSE_ERROR +create table GROUP_CONCAT(a int); +create table GROUP_CONCAT (a int); +drop table GROUP_CONCAT; + +# Limitation removed in 5.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; + +--error ER_PARSE_ERROR +create table MAX(a int); +create table MAX (a int); +drop table MAX; + +--error ER_PARSE_ERROR +create table MID(a int); +create table MID (a int); +drop table MID; + +--error ER_PARSE_ERROR +create table MIN(a int); +create table MIN (a int); +drop table MIN; + +--error ER_PARSE_ERROR +create table NOW(a int); +create table NOW (a int); +drop table NOW; + +--error ER_PARSE_ERROR +create table POSITION(a int); +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; + +--error ER_PARSE_ERROR +create table STD(a int); +create table STD (a int); +drop table STD; + +--error ER_PARSE_ERROR +create table STDDEV(a int); +create table STDDEV (a int); +drop table STDDEV; + +--error ER_PARSE_ERROR +create table STDDEV_POP(a int); +create table STDDEV_POP (a int); +drop table STDDEV_POP; + +--error ER_PARSE_ERROR +create table STDDEV_SAMP(a int); +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; + +--error ER_PARSE_ERROR +create table SUBSTR(a int); +create table SUBSTR (a int); +drop table SUBSTR; + +--error ER_PARSE_ERROR +create table SUBSTRING(a int); +create table SUBSTRING (a int); +drop table SUBSTRING; + +--error ER_PARSE_ERROR +create table SUM(a int); +create table SUM (a int); +drop table SUM; + +--error ER_PARSE_ERROR +create table SYSDATE(a int); +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; + +--error ER_PARSE_ERROR +create table TRIM(a int); +create table TRIM (a int); +drop table TRIM; + +# Limitation removed in 5.1 +create table UNIQUE_USERS(a int); +drop table UNIQUE_USERS; +create table UNIQUE_USERS (a int); +drop table UNIQUE_USERS; + +--error ER_PARSE_ERROR +create table VARIANCE(a int); +create table VARIANCE (a int); +drop table VARIANCE; + +--error ER_PARSE_ERROR +create table VAR_POP(a int); +create table VAR_POP (a int); +drop table VAR_POP; + +--error ER_PARSE_ERROR +create table VAR_SAMP(a int); +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; + +--error ER_PARSE_ERROR +create table BIT_AND(a int); +--error ER_PARSE_ERROR +create table BIT_AND (a int); + +--error ER_PARSE_ERROR +create table BIT_OR(a int); +--error ER_PARSE_ERROR +create table BIT_OR (a int); + +--error ER_PARSE_ERROR +create table BIT_XOR(a int); +--error ER_PARSE_ERROR +create table BIT_XOR (a int); + +--error ER_PARSE_ERROR +create table CAST(a int); +--error ER_PARSE_ERROR +create table CAST (a int); + +--error ER_PARSE_ERROR +create table COUNT(a int); +--error ER_PARSE_ERROR +create table COUNT (a int); + +--error ER_PARSE_ERROR +create table CURDATE(a int); +--error ER_PARSE_ERROR +create table CURDATE (a int); + +--error ER_PARSE_ERROR +create table CURTIME(a int); +--error ER_PARSE_ERROR +create table CURTIME (a int); + +--error ER_PARSE_ERROR +create table DATE_ADD(a int); +--error ER_PARSE_ERROR +create table DATE_ADD (a int); + +--error ER_PARSE_ERROR +create table DATE_SUB(a int); +--error ER_PARSE_ERROR +create table DATE_SUB (a int); + +--error ER_PARSE_ERROR +create table EXTRACT(a int); +--error ER_PARSE_ERROR +create table EXTRACT (a int); + +--error ER_PARSE_ERROR +create table GROUP_CONCAT(a int); +--error ER_PARSE_ERROR +create table GROUP_CONCAT (a int); + +# Limitation removed in 5.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; + +--error ER_PARSE_ERROR +create table MAX(a int); +--error ER_PARSE_ERROR +create table MAX (a int); + +--error ER_PARSE_ERROR +create table MID(a int); +--error ER_PARSE_ERROR +create table MID (a int); + +--error ER_PARSE_ERROR +create table MIN(a int); +--error ER_PARSE_ERROR +create table MIN (a int); + +--error ER_PARSE_ERROR +create table NOW(a int); +--error ER_PARSE_ERROR +create table NOW (a int); + +--error ER_PARSE_ERROR +create table POSITION(a int); +--error ER_PARSE_ERROR +create table POSITION (a int); + +create table SESSION_USER(a int); +drop table SESSION_USER; +create table SESSION_USER (a int); +drop table SESSION_USER; + +--error ER_PARSE_ERROR +create table STD(a int); +--error ER_PARSE_ERROR +create table STD (a int); + +--error ER_PARSE_ERROR +create table STDDEV(a int); +--error ER_PARSE_ERROR +create table STDDEV (a int); + +--error ER_PARSE_ERROR +create table STDDEV_POP(a int); +--error ER_PARSE_ERROR +create table STDDEV_POP (a int); + +--error ER_PARSE_ERROR +create table STDDEV_SAMP(a int); +--error ER_PARSE_ERROR +create table STDDEV_SAMP (a int); + +create table SUBDATE(a int); +drop table SUBDATE; +create table SUBDATE (a int); +drop table SUBDATE; + +--error ER_PARSE_ERROR +create table SUBSTR(a int); +--error ER_PARSE_ERROR +create table SUBSTR (a int); + +--error ER_PARSE_ERROR +create table SUBSTRING(a int); +--error ER_PARSE_ERROR +create table SUBSTRING (a int); + +--error ER_PARSE_ERROR +create table SUM(a int); +--error ER_PARSE_ERROR +create table SUM (a int); + +--error ER_PARSE_ERROR +create table SYSDATE(a int); +--error ER_PARSE_ERROR +create table SYSDATE (a int); + +create table SYSTEM_USER(a int); +drop table SYSTEM_USER; +create table SYSTEM_USER (a int); +drop table SYSTEM_USER; + +--error ER_PARSE_ERROR +create table TRIM(a int); +--error ER_PARSE_ERROR +create table TRIM (a int); + +# Limitation removed in 5.1 +create table UNIQUE_USERS(a int); +drop table UNIQUE_USERS; +create table UNIQUE_USERS (a int); +drop table UNIQUE_USERS; + +--error ER_PARSE_ERROR +create table VARIANCE(a int); +--error ER_PARSE_ERROR +create table VARIANCE (a int); + +--error ER_PARSE_ERROR +create table VAR_POP(a int); +--error ER_PARSE_ERROR +create table VAR_POP (a int); + +--error ER_PARSE_ERROR +create table VAR_SAMP(a int); +--error ER_PARSE_ERROR +create table VAR_SAMP (a int); + +# +# Bug#25930 (CREATE TABLE x SELECT ... parses columns wrong when ran with +# ANSI_QUOTES mode) +# + +--disable_warnings +DROP TABLE IF EXISTS table_25930_a; +DROP TABLE IF EXISTS table_25930_b; +--enable_warnings + +SET SQL_MODE = 'ANSI_QUOTES'; +CREATE TABLE table_25930_a ( "blah" INT ); +CREATE TABLE table_25930_b SELECT "blah" - 1 FROM table_25930_a; + +# The lexer used to chop the first <">, +# not marking the start of the token "blah" correctly. +desc table_25930_b; + +DROP TABLE table_25930_a; +DROP TABLE table_25930_b; + + +SET @@sql_mode=@save_sql_mode; + +# +# Bug#26030 (Parsing fails for stored routine w/multi-statement execution +# enabled) +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p26030; +--enable_warnings + +delimiter $$; + +select "non terminated"$$ +select "terminated";$$ +select "non terminated, space" $$ +select "terminated, space"; $$ +select "non terminated, comment" /* comment */$$ +select "terminated, comment"; /* comment */$$ + +# Multi queries can not be used in --ps-protocol test mode +--disable_ps_protocol + +select "stmt 1";select "stmt 2 non terminated"$$ +select "stmt 1";select "stmt 2 terminated";$$ +select "stmt 1";select "stmt 2 non terminated, space" $$ +select "stmt 1";select "stmt 2 terminated, space"; $$ +select "stmt 1";select "stmt 2 non terminated, comment" /* comment */$$ +select "stmt 1";select "stmt 2 terminated, comment"; /* comment */$$ + +select "stmt 1"; select "space, stmt 2"$$ +select "stmt 1";/* comment */select "comment, stmt 2"$$ + +DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() BEGIN SELECT 1; END; CALL p26030() +$$ + +DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() SELECT 1; CALL p26030() +$$ + +--enable_ps_protocol + +delimiter ;$$ +DROP PROCEDURE p26030; + +#============================================================================= +# SYNTACTIC PARSER (bison) +#============================================================================= + +# +# +# Bug#21114 (Foreign key creation fails to table with name format) +# + +# Test coverage with edge conditions + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select pi(3.14); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select tan(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select tan(1, 2); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select makedate(1); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select makedate(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select maketime(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select maketime(1); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select maketime(1, 2); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select maketime(1, 2, 3, 4); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select atan(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select atan2(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select concat(); +select concat("foo"); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select concat_ws(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select concat_ws("foo"); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select encrypt(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select encrypt(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select des_encrypt("p1", "p2", "not expected"); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select des_decrypt("p1", "p2", "not expected"); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select elt(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select elt(1); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select export_set(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select export_set("p1"); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select export_set("p1", "p2"); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select export_set("p1", "p2", "p3", "p4", "p5", "p6"); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select field(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select field("p1"); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select from_unixtime(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select from_unixtime(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select unix_timestamp(1, 2); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select greatest(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select greatest(12); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select last_insert_id(1, 2); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select least(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select least(12); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select locate(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select locate(1); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select locate(1, 2, 3, 4); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select log(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select log(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select make_set(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select make_set(1); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select master_pos_wait(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select master_pos_wait(1); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select master_pos_wait(1, 2, 3, 4, 5); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select rand(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select round(1, 2, 3); + +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select yearweek(); +-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +select yearweek(1, 2, 3); + +# +# Bug#24736: UDF functions parsed as Stored Functions +# + +# Verify that the syntax for calling UDF : foo(expr AS param, ...) +# can not be used when calling native functions + +# Native function with 1 argument + +select abs(3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 AS three); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 three); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 AS "three"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select abs(3 "three"); + +# Native function with 2 arguments + +set @bar="bar"; +set @foobar="foobar"; + +select instr("foobar", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS p1, "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" p1, "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS "p1", "bar"); +## String concatenation, valid syntax +select instr("foobar" "p1", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr(@foobar "p1", "bar"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" AS p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", "bar" AS "p2"); +## String concatenation, valid syntax +select instr("foobar", "bar" "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar", @bar "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select instr("foobar" AS p1, "bar" AS p2); + +# Native function with 3 arguments + +select conv(255, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS p1, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 p1, 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS "p1", 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 "p1", 10, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 AS p2, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 p2, 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 AS "p2", 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10 "p2", 16); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 AS p3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 p3); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 AS "p3"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255, 10, 16 "p3"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select conv(255 AS p1, 10 AS p2, 16 AS p3); + +# Native function with a variable number of arguments + +# Bug in libm.so on Solaris: +# atan(10) from 32-bit version returns 1.4711276743037347 +# atan(10) from 64-bit version returns 1.4711276743037345 +--replace_result 1.4711276743037345 1.4711276743037347 +select atan(10); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 p1); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS "p1"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 "p1"); + +select atan(10, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 p1, 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS "p1", 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 "p1", 20); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 AS p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 p2); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 AS "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10, 20 "p2"); +-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT +select atan(10 AS p1, 20 AS p2); + +# +# Bug#22312 Syntax error in expression with INTERVAL() +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; +SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE; +SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; +SELECT 1 + INTERVAL(1,0,1,2) + 1; +SELECT INTERVAL(1^1,0,1,2) + 1; +SELECT INTERVAL(1,0+1,2,3) * 5.5; +SELECT INTERVAL(3,3,1+3,4+4) / 0.5; +SELECT (INTERVAL(1,0,1,2) + 5) * 7 + INTERVAL(1,0,1,2) / 2; +SELECT INTERVAL(1,0,1,2) + 1, 5 * INTERVAL(1,0,1,2); +SELECT INTERVAL(0,(1*5)/2) + INTERVAL(5,4,3); + +--disable_warnings +SELECT 1^1 + INTERVAL 1+1 SECOND & 1 + INTERVAL 1+1 SECOND; +SELECT 1%2 - INTERVAL 1^1 SECOND | 1%2 - INTERVAL 1^1 SECOND; +--enable_warnings + +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; +DROP TABLE t1; + +# +# Bug#28317 Left Outer Join with {oj outer-join} +# + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3; +--enable_warnings +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; +SELECT t1.*,t2.* FROM { OJ ((t1 INNER JOIN t2 ON (t1.a1=t2.a2)) LEFT OUTER JOIN t3 ON t3.a3=t2.a1)}; +SELECT t1.*,t2.* FROM { OJ ((t1 LEFT OUTER JOIN t2 ON t1.a3=t2.a2) INNER JOIN t3 ON (t3.a1=t2.a2))}; +SELECT t1.*,t2.* FROM { OJ (t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a2) CROSS JOIN t3 ON (t3.a2=t2.a3)}; +SELECT * FROM {oj t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a3} WHERE t1.a2 > 10; +SELECT {fn CONCAT(a1,a2)} FROM t1; +UPDATE t3 SET a4={d '1789-07-14'} WHERE a1=0; +SELECT a1, a4 FROM t2 WHERE a4 LIKE {fn UCASE('1789-07-14')}; +DROP TABLE t1, t2, t3; + +--echo # +--echo # End of 5.1 tests +--echo # + +--echo # +--echo # Bug#17075846 : unquoted file names for variable values are +--echo # accepted but parsed incorrectly +--echo # +--error ER_WRONG_TYPE_FOR_VAR +SET default_storage_engine=a.myisam; +--error ER_WRONG_TYPE_FOR_VAR +SET default_storage_engine = .a.MyISAM; +--error ER_WRONG_TYPE_FOR_VAR +SET default_storage_engine = a.b.MyISAM; +--error ER_WRONG_TYPE_FOR_VAR +SET default_storage_engine = `a`.MyISAM; +--error ER_WRONG_TYPE_FOR_VAR +SET default_storage_engine = `a`.`MyISAM`; +--error ER_UNKNOWN_STORAGE_ENGINE +set default_storage_engine = "a.MYISAM"; +--error ER_UNKNOWN_STORAGE_ENGINE +set default_storage_engine = 'a.MYISAM'; +--error ER_UNKNOWN_STORAGE_ENGINE +set default_storage_engine = `a.MYISAM`; +CREATE TABLE t1 (s VARCHAR(100)); +--ERROR ER_BAD_FIELD_ERROR +CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW +SET default_storage_engine = NEW.INNODB; +DROP TABLE t1; + +# +# MDEV-8328 Evaluation of two "!" operators depends on space in beetween +# +--error ER_PARSE_ERROR +select 0==0; +select 1=!0, 1 = ! 0; +select !!0, ! ! 0; +select 2>!0, 2 > ! 0; +select 0<=!0, 0 <= !0; +select 1<<!0, 1 << !0; +select 0<!0, 0 < ! 0; + +--echo # +--echo # MDEV-11171 Assertion `m_cpp_buf <= ptr && ptr <= m_cpp_buf + m_buf_length' failed in Lex_input_stream::body_utf8_append(const char*, const char*) +--echo # +CREATE TABLE t1 (id INT); +--error ER_PARSE_ERROR +CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW SET @a = 1\; +--error ER_PARSE_ERROR +PREPARE stmt FROM 'CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW SET @a = 1\\'; +DROP TABLE t1; + +# +# start of 10.1 tests +# + +--echo # +--echo # MDEV-7792 - SQL Parsing Error - UNION AND ORDER BY WITH JOIN +--echo # +CREATE TABLE t1(a INT); +SELECT * FROM t1 JOIN ((SELECT 1 AS b) UNION ALL (SELECT 2 AS b) ORDER BY b DESC) s1 WHERE a=1; +DROP TABLE t1; + + +--echo # +--echo # Test of collective fix for three parser bugs: +--echo # +--echo # Bug #17727401, Bug #17426017, Bug #17473479: +--echo # The server accepts wrong syntax and then fails in different ways +--echo # + +CREATE TABLE t1 (i INT); + +--echo # bug #17426017 +--error ER_PARSE_ERROR +SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00))); + +--echo # bug#17473479 +CREATE TABLE a(a int); +CREATE TABLE b(a int); +--error ER_PARSE_ERROR +DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a); +DROP TABLE a, b; + +--echo # bug #17727401 +--error ER_PARSE_ERROR +SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') FROM t1; + +--echo # regression & coverage tests + +--echo # uniform syntax for FROM DUAL clause: + +SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + FOR UPDATE; + +--error ER_ORDER_WITH_PROC +SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + PROCEDURE ANALYSE() FOR UPDATE; + +SELECT 1 FROM + (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + FOR UPDATE) a; + +--error ER_PARSE_ERROR +SELECT 1 FROM + (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + PROCEDURE ANALYSE() FOR UPDATE) a; + +SELECT 1 FROM t1 + WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + FOR UPDATE); + +--error ER_PARSE_ERROR +SELECT 1 FROM t1 + WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + PROCEDURE ANALYSE() FOR UPDATE); + +SELECT 1 FROM t1 +UNION +SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + FOR UPDATE; + +--error ER_PARSE_ERROR +SELECT 1 FROM t1 +UNION +SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + PROCEDURE ANALYSE() FOR UPDATE; + +--error ER_PARSE_ERROR +SELECT 1 FROM DUAL PROCEDURE ANALYSE() +UNION +SELECT 1 FROM t1; + +(SELECT 1 FROM t1) +UNION +(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + FOR UPDATE); + +--error ER_PARSE_ERROR +(SELECT 1 FROM t1) +UNION +(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 + PROCEDURE ANALYSE() FOR UPDATE); + +--echo # "FOR UPDATE" tests + +SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1; +SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1; +SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE; + + +--echo # "INTO" clause tests + +SELECT 1 FROM t1 INTO @var17727401; +SELECT 1 FROM DUAL INTO @var17727401; +SELECT 1 INTO @var17727401; + +SELECT 1 INTO @var17727401 FROM t1; +SELECT 1 INTO @var17727401 FROM DUAL; + +--error ER_PARSE_ERROR +SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2; + +--error ER_PARSE_ERROR +SELECT 1 INTO @var17727401_1 FROM DUAL + INTO @var17727401_2; + +SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1; +SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401; + +--error ER_PARSE_ERROR +SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1; + +--error ER_PARSE_ERROR +SELECT 1 INTO @var17727401_1 + FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 + INTO @var17727401_2; + +--error ER_PARSE_ERROR +SELECT (SELECT 1 FROM t1 INTO @var17727401); +--error ER_PARSE_ERROR +SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a; +--error ER_PARSE_ERROR +SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401); + +--error ER_PARSE_ERROR +SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1; +--error ER_WRONG_USAGE +(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1); + +SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401; + +--error ER_PARSE_ERROR +SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE(); + +--error ER_PARSE_ERROR +SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401; + +--echo # ORDER and LIMIT clause combinations + +# Limited support for (SELECT ...) ORDER/LIMIT: + +(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1; +(SELECT 1 FROM t1 LIMIT 1) LIMIT 1; + +--error ER_PARSE_ERROR +((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1; +--error ER_PARSE_ERROR +((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1; + +(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1; +(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1; + +--error ER_PARSE_ERROR +((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1); +--error ER_PARSE_ERROR +((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1); + +# ORDER/LIMIT and UNION: + +let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1; +eval $q; +eval SELECT ($q); +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1; +eval $q; +eval SELECT ($q); +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1; +eval $q; +eval SELECT ($q); +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1; +--error ER_PARSE_ERROR +eval $q; +--error ER_PARSE_ERROR +eval SELECT ($q); +--error ER_PARSE_ERROR +eval SELECT 1 FROM ($q) a; + +DROP TABLE t1; + +--echo # +--echo # MDEV-8380: Subquery parse error +--echo # +CREATE TABLE t1 ( a INT); +INSERT INTO t1 VALUES ( 2 ); +SELECT * +FROM ( (SELECT a FROM t1 ORDER BY a) UNION (SELECT 1 as b ORDER BY b ) ) AS a1 +WHERE a1.a = 1 OR a1.a = 2; +DROP TABLE t1; + +--echo # +--echo # MDEV-10080 Derived tables allow double LIMIT clause +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +--error ER_PARSE_ERROR +SELECT * FROM (SELECT * FROM t1 LIMIT 1 LIMIT 2) t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-10109 Disallow syntactically INSERT .. SELECT .. {ORDER BY ..| LIMIT ..} .. UNION .. +--echo # + +--error ER_PARSE_ERROR +INSERT INTO t1 SELECT 1 ORDER BY 1 UNION SELECT 2; +--error ER_PARSE_ERROR +INSERT INTO t1 SELECT 1 LIMIT 1 UNION SELECT 2; +--error ER_PARSE_ERROR +CREATE TABLE t1 AS SELECT 1 ORDER BY 1 UNION SELECT 2; +--error ER_PARSE_ERROR +CREATE TABLE t1 AS SELECT 1 LIMIT 1 UNION SELECT 2; + + +--echo # +--echo # MDEV-8909 union parser cleanup +--echo # + +--echo # UNION with a non-parenthesized term +--echo # The following two queries return a wrong result +--echo # This will change when MDEV-10120 is fixed +--echo # For now, we're testing the parser. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a); +SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a); +DROP TABLE t1; + +--echo # UNION with a parenthesed term +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 2); + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)); +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1; +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a; +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1; +DROP TABLE t1; + +--echo # UNION with a parenthesized term with ROLLUP + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP); +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) LIMIT 1; +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) ORDER BY a LIMIT 1; +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2); + + +--error ER_WRONG_USAGE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY a); +--error ER_WRONG_USAGE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)); +--error ER_WRONG_USAGE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a)); +DROP TABLE t1; + +--echo # UNION with a non-parethesized term with ROLLUP + +--echo # This will change after: MDEV-10120 Wrong result of UNION .. ORDER BY GROUP_CONCAT() +--echo # Currently we're testing the parser only + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a); +SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a); +SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1; +DROP TABLE t1; + +--echo # Derived table with ROLLUP +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); + +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1; +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 LIMIT 1; +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a; +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a LIMIT 1; +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a DESC LIMIT 1; + +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a); +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a ORDER BY a); +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a) LIMIT 1; +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1; + +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a); +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a); +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a) DESC; +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a) DESC; + +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a) LIMIT 1; +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1; +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a) DESC LIMIT 1; +SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a) DESC LIMIT 1; + +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) t1; +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) t1; +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) t1; +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a)) t1; + +DROP TABLE t1; + +--echo # Subquery, one row, ROLLUP + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL) FROM t1; +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL) FROM t1; +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; + +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP); +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP); +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1; +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1; + +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a); +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1); +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)); +--error ER_WRONG_USAGE +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a; +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1; + +DROP TABLE t1; + +--echo # Subquery, multiple rows, ROLLUP + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1); +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1; + +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP); +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP); +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1; +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1; + +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a); +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1); +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)); +--error ER_WRONG_USAGE +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a; + +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1; +--error ER_WRONG_USAGE +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1; + +DROP TABLE t1; + + +--echo +--echo MDEV-10101 Wrong error message of SELECT 1 UNION (SELECT 1 FROM t1 GROUP BY 1 WITH ROLLUP) +--echo + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP); +(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1; +SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1; + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) LIMIT 1; +(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1 LIMIT 1; +SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1 LIMIT 1; + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) ORDER BY a LIMIT 1; +(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1 ORDER BY a LIMIT 1; +SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1 ORDER BY a LIMIT 1; + +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2); +(SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2) UNION SELECT 1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-10779 Failing assertion lex->proc_list.elements == 0 or syntax error on PROCEDURE ANALYSE in UNION +--echo # + +CREATE TABLE t1 (i INT); +--error ER_WRONG_USAGE +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)) +UNION +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)); + +--error ER_WRONG_USAGE +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)) +UNION +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10); + +--error ER_WRONG_USAGE +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)) +UNION +(SELECT 1); + +--error ER_WRONG_USAGE +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)) +UNION +SELECT 1; + +--error ER_PARSE_ERROR +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10) +UNION +(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)); + +--error ER_PARSE_ERROR +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10) +UNION +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10); + +--error ER_PARSE_ERROR +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10) +UNION +(SELECT 1); + +--error ER_PARSE_ERROR +SELECT * FROM t1 PROCEDURE ANALYSE(10, 10) +UNION +SELECT 1; + +DROP TABLE t1; + +# +# MDEV-11582 InnoDB: Failing assertion: !((field)->vcol_info && !(field)->stored_in_db()) +# +--error ER_PARSE_ERROR +create table t1 (a serial null); +--error ER_PARSE_ERROR +create table t1 (a serial auto_increment); +--error ER_PARSE_ERROR +create table t1 (a serial serial default value); +--error ER_PARSE_ERROR +create table t1 (a serial collate binary); +--error ER_PARSE_ERROR +create table t1 (i int, vc serial as (i)); +--error ER_PARSE_ERROR +create function fs() returns serial return 1; + +create table t1 ( id serial ); +show create table t1; +drop table t1; + +--echo # +--echo # MDEV-10343 Providing compatibility for basic SQL data types +--echo # +CREATE TABLE clob (clob int); +DROP TABLE clob; + +CREATE TABLE raw (raw int); +DROP TABLE raw; + +CREATE TABLE varchar2 (varchar2 int); +DROP TABLE varchar2; + + +--echo # +--echo # MDEV-15620 Crash when using "SET @@NEW.a=expr" inside a trigger +--echo # + +CREATE TABLE t1 (a INT); +--error ER_UNKNOWN_STRUCTURED_VARIABLE +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @@NEW.a=0; +DROP TABLE t1; + +--echo # +--echo # MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string +--echo # + +CREATE TABLE trim_oracle (trim_oracle int); +DROP TABLE trim_oracle; + +--echo # +--echo # MDEV-15615 Unexpected syntax error instead of "Unknown system variable" inside an SP +--echo # + +DELIMITER $$; +--error ER_UNKNOWN_SYSTEM_VARIABLE +BEGIN NOT ATOMIC + DECLARE a INT; + SET GLOBAL a=10; +END; +$$ +DELIMITER ;$$ |