diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2020-07-09 14:01:06 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2020-07-10 00:01:24 +0530 |
commit | 737c3025e9ed55855ee66806ad14e9e7e7852fa7 (patch) | |
tree | 1798ffb7d1a2229f28e32089b76286139ad2d32d | |
parent | a759f9af51b2093502d3a06c0150e9aa7fc21068 (diff) | |
download | mariadb-git-737c3025e9ed55855ee66806ad14e9e7e7852fa7.tar.gz |
MDEV-10120: Wrong result of UNION .. ORDER BY GROUP_CONCAT()
Reject queries that have aggregate functions with UNION as these
are not allowed by standard.
-rw-r--r-- | mysql-test/r/parser.result | 15 | ||||
-rw-r--r-- | mysql-test/r/union.result | 29 | ||||
-rw-r--r-- | mysql-test/t/parser.test | 5 | ||||
-rw-r--r-- | mysql-test/t/union.test | 21 | ||||
-rw-r--r-- | sql/sql_select.cc | 17 |
5 files changed, 53 insertions, 34 deletions
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 09bbd7cf176..45fcca146fe 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -943,11 +943,9 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp 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 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a); -a -1 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION DROP TABLE t1; # UNION with a parenthesed term CREATE TABLE t1 (a INT); @@ -1010,14 +1008,11 @@ DROP TABLE t1; 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 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a); -a -1 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION 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 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION DROP TABLE t1; # Derived table with ROLLUP CREATE TABLE t1 (a INT); diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index c02e590490e..f9df02b7f81 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1757,8 +1757,7 @@ union select 4 order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) ; -foo -1 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION prepare stmt1 from 'select 1 as foo union select 2 @@ -1768,12 +1767,7 @@ union select 4 order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) '; -execute stmt1; -foo -1 -execute stmt1; -foo -1 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION select 1 as foo union select 2 @@ -1783,8 +1777,7 @@ union (select 4) order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) ; -foo -1 +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION prepare stmt1 from 'select 1 as foo union select 2 @@ -1794,13 +1787,7 @@ union (select 4) order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) '; -execute stmt1; -foo -1 -execute stmt1; -foo -1 -deallocate prepare stmt1; +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION End of 5.1 tests # # mdev-5091: Asseirtion failure for UNION with ORDER BY @@ -2299,3 +2286,11 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select 1 AS `1`,2 AS `2` union all select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 1 group by 1 having 0 DROP TABLE t1,t2; +# +# MDEV-10120: Wrong result of UNION .. ORDER BY GROUP_CONCAT() +# +CREATE TABLE t1 (a INT); +INSERT t1 VALUES (1),(2),(3); +(SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a) ORDER BY GROUP_CONCAT(a); +ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION +DROP TABLE t1; diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 5faaca00ea8..7e11a71c500 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -1090,7 +1090,9 @@ CREATE TABLE t1 AS SELECT 1 LIMIT 1 UNION SELECT 2; --echo # For now, we're testing the parser. CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20),(30); +--error ER_AGGREGATE_ORDER_FOR_UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a); +--error ER_AGGREGATE_ORDER_FOR_UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a); DROP TABLE t1; @@ -1131,8 +1133,11 @@ DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20),(30); +--error ER_AGGREGATE_ORDER_FOR_UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a); +--error ER_AGGREGATE_ORDER_FOR_UNION SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a); +--error ER_AGGREGATE_ORDER_FOR_UNION 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; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 707bda6d81b..9f747c9080f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1184,11 +1184,11 @@ select 4 order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) ; +--error ER_AGGREGATE_ORDER_FOR_UNION eval $my_stmt; +--error ER_AGGREGATE_ORDER_FOR_UNION eval prepare stmt1 from '$my_stmt'; -execute stmt1; -execute stmt1; let $my_stmt= select 1 as foo @@ -1201,13 +1201,11 @@ union order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) ; +--error ER_AGGREGATE_ORDER_FOR_UNION eval $my_stmt; +--error ER_AGGREGATE_ORDER_FOR_UNION eval prepare stmt1 from '$my_stmt'; -execute stmt1; -execute stmt1; - -deallocate prepare stmt1; --echo End of 5.1 tests @@ -1637,3 +1635,14 @@ eval $q; eval EXPLAIN EXTENDED $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-10120: Wrong result of UNION .. ORDER BY GROUP_CONCAT() +--echo # + +CREATE TABLE t1 (a INT); +INSERT t1 VALUES (1),(2),(3); + +--error ER_AGGREGATE_ORDER_FOR_UNION +(SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a) ORDER BY GROUP_CONCAT(a); +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4cca2d67eb8..c33e554aaca 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22709,10 +22709,13 @@ int setup_order(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, List<Item> &fields, List<Item> &all_fields, ORDER *order, bool from_window_spec) { + SELECT_LEX *select = thd->lex->current_select; enum_parsing_place context_analysis_place= thd->lex->current_select->context_analysis_place; thd->where="order clause"; - for (; order; order=order->next) + const bool for_union = select->master_unit()->is_union() && + select == select->master_unit()->fake_select_lex; + for (uint number = 1; order; order=order->next, number++) { if (find_order_in_list(thd, ref_pointer_array, tables, order, fields, all_fields, false, true, from_window_spec)) @@ -22723,6 +22726,18 @@ int setup_order(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, my_error(ER_WINDOW_FUNCTION_IN_WINDOW_SPEC, MYF(0)); return 1; } + + /* + UNION queries cannot be used with an aggregate function in + an ORDER BY clause + */ + + if (for_union && (*order->item)->with_sum_func) + { + my_error(ER_AGGREGATE_ORDER_FOR_UNION, MYF(0), number); + return 1; + } + if (from_window_spec && (*order->item)->with_sum_func && (*order->item)->type() != Item::SUM_FUNC_ITEM) (*order->item)->split_sum_func(thd, ref_pointer_array, |