diff options
Diffstat (limited to 'mysql-test/t/parser.test')
| -rw-r--r-- | mysql-test/t/parser.test | 256 |
1 files changed, 256 insertions, 0 deletions
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 31c6ab92ba8..0a19b03a4eb 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -765,3 +765,259 @@ DROP TABLE t1; 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_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +(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_WRONG_USAGE +SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2; + +--error ER_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE(); + +--error ER_WRONG_USAGE +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_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1; +--error ER_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1; +--error ER_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +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_WRONG_USAGE +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_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1; +--error ER_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1; +--error ER_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +eval SELECT 1 FROM ($q) a; + +let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1; +--error ER_WRONG_USAGE +eval $q; +--error ER_WRONG_USAGE +eval SELECT ($q); +--error ER_WRONG_USAGE +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; |