summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/parser.result227
-rw-r--r--mysql-test/r/subselect.result12
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result12
-rw-r--r--mysql-test/r/subselect_no_mat.result12
-rw-r--r--mysql-test/r/subselect_no_opts.result12
-rw-r--r--mysql-test/r/subselect_no_scache.result12
-rw-r--r--mysql-test/r/subselect_no_semijoin.result12
-rw-r--r--mysql-test/r/union.result94
8 files changed, 368 insertions, 25 deletions
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 1d29f2c4a0e..01cc9d79aaf 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -650,3 +650,230 @@ 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;
a b
DROP TABLE t1;
+#
+# Test of collective fix for three parser bugs:
+#
+# Bug #17727401, Bug #17426017, Bug #17473479:
+# The server accepts wrong syntax and then fails in different ways
+#
+CREATE TABLE t1 (i INT);
+# bug #17426017
+SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00)));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY VALUES (c00)))' at line 1
+# bug#17473479
+CREATE TABLE a(a int);
+CREATE TABLE b(a int);
+DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY a)' at line 1
+DROP TABLE a, b;
+# bug #17727401
+SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') FROM t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY '') FROM t1' at line 1
+# regression & coverage tests
+# uniform syntax for FROM DUAL clause:
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE;
+1
+1
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE;
+ERROR HY000: Can't use ORDER clause with this procedure
+SELECT 1 FROM
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE) a;
+1
+1
+SELECT 1 FROM
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE) a;
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM t1
+WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE);
+1
+SELECT 1 FROM t1
+WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE);
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE;
+1
+1
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE;
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM DUAL PROCEDURE ANALYSE()
+UNION
+SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE);
+1
+1
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE);
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+# "FOR UPDATE" tests
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;
+1
+# "INTO" clause tests
+SELECT 1 FROM t1 INTO @var17727401;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+SELECT 1 FROM DUAL INTO @var17727401;
+SELECT 1 INTO @var17727401;
+SELECT 1 INTO @var17727401 FROM t1;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+SELECT 1 INTO @var17727401 FROM DUAL;
+SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT 1 INTO @var17727401_1 FROM DUAL
+INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1' at line 1
+SELECT 1 INTO @var17727401_1
+FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
+INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT (SELECT 1 FROM t1 INTO @var17727401);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @var17727401)' at line 1
+SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @var17727401) a' at line 1
+SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @var17727401)' at line 1
+SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
+ERROR HY000: Incorrect usage of UNION and INTO
+(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);
+ERROR HY000: Incorrect usage of UNION and INTO
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE();
+ERROR HY000: Incorrect usage of PROCEDURE and INTO
+SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401;
+ERROR HY000: Incorrect usage of PROCEDURE and INTO
+# ORDER and LIMIT clause combinations
+(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
+1
+(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
+1
+((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1) ORDER BY 1' at line 1
+((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 1) LIMIT 1' at line 1
+(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
+1
+(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;
+1
+((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 1) ORDER BY 1)' at line 1
+((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1) LIMIT 1)' at line 1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1' at line 1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1)' at line 1
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1) a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1) a' at line 1
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1' at line 1
+SELECT (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1)' at line 1
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+DROP TABLE t1;
+#
+# MDEV-8380: Subquery parse error
+#
+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;
+a
+2
+1
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3732f62d5e5..b9f9fb05c21 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5132,8 +5132,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5175,7 +5176,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5294,7 +5295,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 140b7902660..902c45ff213 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -5134,8 +5134,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5177,7 +5178,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5296,7 +5297,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 99ac9f4b300..89034b7995d 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5132,8 +5132,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5175,7 +5176,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5294,7 +5295,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 7ecd40c3863..6e90aac09ed 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -5128,8 +5128,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5171,7 +5172,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5290,7 +5291,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index c70db6f8cc0..1c18817ae8d 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5138,8 +5138,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5181,7 +5182,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5300,7 +5301,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 6a7d6b3a81c..de9c4e8a12b 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -5128,8 +5128,9 @@ SELECT a FROM t1 WHERE a = 2
a
1
2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
a
1
@@ -5171,7 +5172,7 @@ a 1
1 1
2 1
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1
SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5290,7 +5291,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
1
1
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index ef1749eda52..9b7a1c57aa1 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -89,6 +89,10 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
Warnings:
Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`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;
+count(*)
+6
(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2;
a b
1 a
@@ -96,6 +100,10 @@ a b
select found_rows();
found_rows()
6
+select count(*) from (
+select a,b from t1 union all select a,b from t2) q;
+count(*)
+8
select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2;
a b
1 a
@@ -308,12 +316,20 @@ create table t1 (a int);
insert into t1 values (1),(2),(3);
create table t2 (a int);
insert into t2 values (3),(4),(5);
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+6
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
a
1
select found_rows();
found_rows()
6
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
a
1
@@ -321,6 +337,10 @@ a
select found_rows();
found_rows()
4
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
a
1
@@ -330,6 +350,10 @@ a
select found_rows();
found_rows()
4
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1)) q;
+COUNT(*)
+4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
a
1
@@ -345,6 +369,16 @@ a
select found_rows();
found_rows()
4
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
+a
+1
+select found_rows();
+found_rows()
+4
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
ERROR HY000: Incorrect usage of UNION and LIMIT
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
@@ -354,13 +388,29 @@ a
select found_rows();
found_rows()
6
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION SELECT * FROM t2) q;
+COUNT(*)
+5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
a
1
2
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION all SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all SELECT * FROM t2 LIMIT 2;
+a
+1
+3
select found_rows();
found_rows()
-5
+4
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION all SELECT * FROM t2) q;
+COUNT(*)
+6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
a
1
@@ -373,10 +423,39 @@ found_rows()
5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 100) UNION SELECT * FROM t2) q;
+COUNT(*)
+5
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100) UNION SELECT * FROM t2;
+a
+1
+2
+3
+4
+5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2;
+a
+1
+3
+4
+5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 2;
+a
+1
+3
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
a
3
@@ -384,8 +463,21 @@ a
select found_rows();
found_rows()
5
+SELECT COUNT(*) FROM (
+SELECT * FROM t1 UNION SELECT * FROM t2) q;
+COUNT(*)
+5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT * FROM t1 limit 2,2) UNION SELECT * FROM t2) q;
+COUNT(*)
+3
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2) UNION SELECT * FROM t2;
+a
+3
+4
+5
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
a
5
View Lorry Controller Status