Adding various tests for combinations of

UNION, ROLLUP, GROUP_CONCAT, for better coverage.

1 files changed, 182 insertions, 0 deletions

diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index 0450e3c6fb0..b24ab1cb34c 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -1043,3 +1043,185 @@ INSERT INTO t1 SELECT 1 LIMIT 1 UNION SELECT 2;
 CREATE TABLE t1 AS SELECT 1 ORDER BY 1 UNION SELECT 2;
 --error ER_PARSE_ERROR
 CREATE TABLE t1 AS SELECT 1 LIMIT 1 UNION SELECT 2;
+
+
+--echo #
+--echo # MDEV-8909 union parser cleanup
+--echo #
+
+--echo # UNION with a non-parenthesized term
+--echo # The following two queries return a wrong result
+--echo # This will change when MDEV-10120 is fixed
+--echo # 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);
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a);
+DROP TABLE t1;
+
+--echo # 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);
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
+DROP TABLE t1;
+
+--echo # UNION with a parethesized term with ROLLUP
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10),(20),(30);
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP);
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) LIMIT 1;
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP) ORDER BY a LIMIT 1;
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP LIMIT 2);
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+--error ER_WRONG_USAGE
+SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a));
+DROP TABLE t1;
+
+--echo # UNION with a non-parethesized term with ROLLUP
+
+--echo # This will change after: MDEV-10120 Wrong result of UNION .. ORDER BY GROUP_CONCAT()
+--echo # 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);
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a);
+SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1;
+DROP TABLE t1;
+
+--echo # 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;
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 LIMIT 1;
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a;
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a LIMIT 1;
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY a DESC LIMIT 1;
+
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a);
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a ORDER BY a);
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 ORDER BY GROUP_CONCAT(a) LIMIT 1;
+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;
+
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a);
+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);
+SELECT a, GROUP_CONCAT(a) FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP) t1 GROUP BY a ORDER BY GROUP_CONCAT(a) DESC;
+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;
+
+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;
+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;
+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;
+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;
+
+--error ER_WRONG_USAGE
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) t1;
+--error ER_WRONG_USAGE
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) t1;
+--error ER_WRONG_USAGE
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) t1;
+--error ER_WRONG_USAGE
+SELECT * FROM (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a)) t1;
+
+DROP TABLE t1;
+
+--echo # 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 (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL);
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL) FROM t1;
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NOT NULL) FROM t1;
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP);
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP);
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1);
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+--error ER_WRONG_USAGE
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a;
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1;
+
+DROP TABLE t1;
+
+--echo # 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 (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1);
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1) FROM t1;
+
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP);
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP);
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT GROUP_CONCAT(a) FROM t1 GROUP BY a WITH ROLLUP) FROM t1;
+
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a);
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1);
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a));
+--error ER_WRONG_USAGE
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a;
+
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a)) FROM t1;
+--error ER_WRONG_USAGE
+SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP ORDER BY a LIMIT 1) AS a FROM t1;
+
+DROP TABLE t1;