MDEV-8380: Subquery parse errormdev-8380

backport mysql parser fixes
0034963fbf199696792491bcb79d5f0731c98804
5948561812bc691bd0c13cf518a3fe77d9daf920

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;