summaryrefslogtreecommitdiff
path: root/mysql-test/t
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t')
-rw-r--r--mysql-test/t/parser.test256
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--mysql-test/t/union.test37
3 files changed, 297 insertions, 4 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;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 80efe7a97ae..8fb9658260b 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -4274,8 +4274,7 @@ SELECT * FROM (
# This was not allowed previously. Possibly, it should be allowed on the future.
# For now, the intent is to keep the fix as non-intrusive as possible.
---error ER_PARSE_ERROR
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
--error ER_PARSE_ERROR
@@ -4310,7 +4309,7 @@ SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
# aliases after.
#
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
---error ER_PARSE_ERROR
+--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
--error ER_PARSE_ERROR
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
@@ -4402,8 +4401,9 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
--error ER_PARSE_ERROR
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
---error ER_PARSE_ERROR
+--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
--error ER_PARSE_ERROR
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index b8b040b0d0d..56fb2e3a13d 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -26,8 +26,12 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g
--error 1250
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b;
explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc;
+select count(*) from (
+(select a,b from t1 limit 2) union all (select a,b from t2 order by a)) q;
(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2;
select found_rows();
+select count(*) from (
+select a,b from t1 union all select a,b from t2) q;
select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2;
select found_rows();
@@ -206,18 +210,30 @@ create table t2 (a int);
insert into t2 values (3),(4),(5);
# Test global limits
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1) UNION all (SELECT * FROM t2)) q;
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
select found_rows();
# Test cases where found_rows() should return number of returned rows
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1)) q;
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
select found_rows();
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
+select found_rows();
# In these case found_rows() should work
--error ER_WRONG_USAGE
@@ -226,20 +242,41 @@ SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
select found_rows();
# The following examples will not be exact
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION SELECT * FROM t2) q;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all SELECT * FROM t2 LIMIT 2;
select found_rows();
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION all SELECT * FROM t2) q;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
select found_rows();
--error ER_WRONG_USAGE
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 100) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100) UNION SELECT * FROM t2;
--error ER_WRONG_USAGE
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2;
--error ER_WRONG_USAGE
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 2;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
select found_rows();
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION SELECT * FROM t2) q;
--error ER_WRONG_USAGE
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 limit 2,2) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2) UNION SELECT * FROM t2;
# Test some limits with ORDER BY
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
View Lorry Controller Status