diff options
Diffstat (limited to 'mysql-test/t/parser.test')
| -rw-r--r-- | mysql-test/t/parser.test | 220 |
1 files changed, 220 insertions, 0 deletions
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 0450e3c6fb0..90b0fb11648 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -759,6 +759,22 @@ 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; + +# +# start of 10.1 tests +# + --echo # --echo # MDEV-7792 - SQL Parsing Error - UNION AND ORDER BY WITH JOIN --echo # @@ -1043,3 +1059,207 @@ INSERT INTO t1 SELECT 1 LIMIT 1 UNION SELECT 2; 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); + +--error ER_INVALID_GROUP_FUNC_USE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)); +--error ER_INVALID_GROUP_FUNC_USE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1; +--error ER_INVALID_GROUP_FUNC_USE +SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a; +--error ER_INVALID_GROUP_FUNC_USE +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; |