summaryrefslogtreecommitdiff
path: root/mysql-test/r/parser.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/parser.result')
-rw-r--r--mysql-test/r/parser.result449
1 files changed, 416 insertions, 33 deletions
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 26f4b018726..7a0a8667350 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -715,7 +715,7 @@ FOR UPDATE) a;
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
+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 'PROCEDURE ANALYSE() FOR UPDATE) a' at line 3
SELECT 1 FROM t1
WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
FOR UPDATE);
@@ -723,7 +723,7 @@ FOR UPDATE);
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
+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 'PROCEDURE ANALYSE() FOR UPDATE)' at line 3
SELECT 1 FROM t1
UNION
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
@@ -734,11 +734,12 @@ 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
+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 'PROCEDURE ANALYSE() FOR UPDATE' at line 4
SELECT 1 FROM DUAL PROCEDURE ANALYSE()
UNION
SELECT 1 FROM t1;
-ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+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 FROM t1' at line 2
(SELECT 1 FROM t1)
UNION
(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
@@ -749,7 +750,7 @@ FOR UPDATE);
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
+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 'PROCEDURE ANALYSE() FOR UPDATE)' at line 4
# "FOR UPDATE" tests
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
1
@@ -768,10 +769,10 @@ 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
+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_2' at line 1
SELECT 1 INTO @var17727401_1 FROM DUAL
INTO @var17727401_2;
-ERROR HY000: Incorrect usage of INTO and INTO
+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_2' at line 2
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
@@ -783,7 +784,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
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
+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_2' at line 3
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;
@@ -791,16 +792,16 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
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
+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 FROM t1 INTO t1' 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 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
+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 'PROCEDURE ANALYSE()' at line 1
SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401;
-ERROR HY000: Incorrect usage of PROCEDURE and INTO
+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
# ORDER and LIMIT clause combinations
(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
1
@@ -846,53 +847,53 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
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
+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 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1)' at line 1
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
+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 FROM t1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1)' at line 1
SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1) a;
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1)' at line 1
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
+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 FROM t1) a' at line 1
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
+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) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1 ORDER BY 1' at line 1
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
+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 FROM t1 ORDER BY 1)' at line 1
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
+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 FROM t1 ORDER BY 1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1 LIMIT 1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1 LIMIT 1)' at line 1
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
+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 FROM t1 LIMIT 1) a' at line 1
SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1 ORDER BY 1' at line 1
SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1);
-ERROR HY000: Incorrect usage of UNION and LIMIT
+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 FROM t1 ORDER BY 1)' at line 1
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
+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 FROM t1 ORDER BY 1) a' at line 1
SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1 LIMIT 1' at line 1
SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1);
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 FROM t1 LIMIT 1)' at line 1
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
+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 FROM t1 LIMIT 1) a' at line 1
DROP TABLE t1;
#
# MDEV-8380: Subquery parse error
@@ -906,3 +907,385 @@ a
2
1
DROP TABLE t1;
+#
+# MDEV-10080 Derived tables allow double LIMIT clause
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM (SELECT * FROM t1 LIMIT 1 LIMIT 2) 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 'LIMIT 2) t1' at line 1
+DROP TABLE t1;
+#
+# MDEV-10109 Disallow syntactically INSERT .. SELECT .. {ORDER BY ..| LIMIT ..} .. UNION ..
+#
+INSERT INTO t1 SELECT 1 ORDER BY 1 UNION SELECT 2;
+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 2' at line 1
+INSERT INTO t1 SELECT 1 LIMIT 1 UNION SELECT 2;
+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 2' at line 1
+CREATE TABLE t1 AS SELECT 1 ORDER BY 1 UNION SELECT 2;
+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 2' at line 1
+CREATE TABLE t1 AS SELECT 1 LIMIT 1 UNION SELECT 2;
+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 2' at line 1
+#
+# MDEV-8909 union parser cleanup
+#
+# UNION with a non-parenthesized term
+# The following two queries return a wrong result
+# This will change when MDEV-10120 is fixed
+# 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);
+a
+1
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a);
+a
+1
+DROP TABLE t1;
+# 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);
+a
+1
+10
+20
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
+ERROR HY000: Invalid use of group function
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
+ERROR HY000: Invalid use of group function
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
+ERROR HY000: Invalid use of group function
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
+ERROR HY000: Invalid use of group function
+DROP TABLE t1;
+# 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);
+a
+1
+10
+20
+30
+NULL
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) LIMIT 1;
+a
+1
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) ORDER BY a LIMIT 1;
+a
+NULL
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2);
+a
+1
+10
+20
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a));
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+DROP TABLE t1;
+# UNION with a non-parethesized term with ROLLUP
+# This will change after: MDEV-10120 Wrong result of UNION .. ORDER BY GROUP_CONCAT()
+# 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);
+a
+1
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a);
+a
+1
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1;
+a
+1
+DROP TABLE t1;
+# 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;
+a
+10
+20
+30
+NULL
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 LIMIT 1;
+a
+10
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a;
+a
+NULL
+10
+20
+30
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a LIMIT 1;
+a
+NULL
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a DESC LIMIT 1;
+a
+30
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a);
+a GROUP_CONCAT(a)
+10 10,20,30
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a ORDER BY a);
+a GROUP_CONCAT(a)
+10 10,20,30
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a) LIMIT 1;
+a GROUP_CONCAT(a)
+10 10,20,30
+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;
+a GROUP_CONCAT(a)
+10 10,20,30
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a);
+a GROUP_CONCAT(a)
+NULL NULL
+10 10
+20 20
+30 30
+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);
+a GROUP_CONCAT(a)
+NULL NULL
+10 10
+20 20
+30 30
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a) DESC;
+a GROUP_CONCAT(a)
+30 30
+20 20
+10 10
+NULL NULL
+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;
+a GROUP_CONCAT(a)
+30 30
+20 20
+10 10
+NULL NULL
+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;
+a GROUP_CONCAT(a)
+NULL NULL
+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;
+a GROUP_CONCAT(a)
+NULL NULL
+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;
+a GROUP_CONCAT(a)
+30 30
+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;
+a GROUP_CONCAT(a)
+30 30
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a)) t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+DROP TABLE t1;
+# 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 * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL)
+NULL
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL);
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL)
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+(SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL) FROM t1;
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL)
+NULL
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL) FROM t1;
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL)
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP);
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP);
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+DROP TABLE t1;
+# 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 * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+(SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+10
+10
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+10
+10
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1)
+10
+10
+10
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP);
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP);
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1);
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1;
+ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
+DROP TABLE t1;
+
+MDEV-10101 Wrong error message of SELECT 1 UNION (SELECT 1 FROM t1 GROUP BY 1 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);
+a
+1
+10
+20
+30
+NULL
+(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1;
+a
+10
+20
+30
+NULL
+1
+SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1;
+a
+10
+20
+30
+NULL
+1
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) LIMIT 1;
+a
+1
+(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1 LIMIT 1;
+a
+10
+SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1 LIMIT 1;
+a
+10
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) ORDER BY a LIMIT 1;
+a
+NULL
+(SELECT a FROM t1 GROUP BY a WITH ROLLUP) UNION SELECT 1 ORDER BY a LIMIT 1;
+a
+NULL
+SELECT a FROM t1 GROUP BY a WITH ROLLUP UNION SELECT 1 ORDER BY a LIMIT 1;
+a
+NULL
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2);
+a
+1
+10
+20
+(SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2) UNION SELECT 1;
+a
+10
+20
+1
+DROP TABLE t1;
+#
+# MDEV-10779 Failing assertion lex->proc_list.elements == 0 or syntax error on PROCEDURE ANALYSE in UNION
+#
+CREATE TABLE t1 (i INT);
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
+UNION
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10));
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
+UNION
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10);
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
+UNION
+(SELECT 1);
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
+UNION
+SELECT 1;
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)
+UNION
+(SELECT * FROM t1 PROCEDURE ANALYSE(10, 10));
+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 * FROM t1 PROCEDURE ANALYSE(10, 10))' at line 2
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)
+UNION
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10);
+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 * FROM t1 PROCEDURE ANALYSE(10, 10)' at line 2
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)
+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 2
+SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)
+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 2
+DROP TABLE t1;
View Lorry Controller Status