From caee832df14c3cb7fae34eab379ca33a49be6833 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 1 Jun 2016 14:29:20 +0400 Subject: MDEV-10124 Incorrect usage of CUBE/ROLLUP and ORDER BY with GROUP_CONCAT(a ORDER BY a) Allowing GROUP_CONCAT(... ORDER BY ..) in queries with ROLLUP. The story of the related code: 1. The original patch from Wax commit: 0b505fb437eedd1b31c99888247c2259539c095b date: Tue Mar 18 03:07:40 2003 opt_gorder_clause reused the regular order_clause, which already had some protection against ROLLUP queries: order_clause: ORDER_SYM BY { LEX *lex=Lex; if (lex->current_select->linkage != GLOBAL_OPTIONS_TYPE && lex->current_select->select_lex()->olap != UNSPECIFIED_OLAP_TYPE) { net_printf(lex->thd, ER_WRONG_USAGE, "CUBE/ROLLUP", "ORDER BY"); YYABORT; } } order_list; The assumption that ORDER BY in group_concat() had to have the same ROLLUP restriction (with order_clause) was wrong. Moreover, GROUP_CONCAT() in select_item_list was not affected by this restriction, because WITH ROLLUP goes after select_item_list and therefore sel->olap is always equal to UNSPECIFIED_OLAP_TYPE during select_item_list. GROUP BY was not affected for two reasons: - it goes before WITH ROLLUP and sel->olap is still UNSPECIFIED_OLAP_TYPE - Aggregate functions like AVG(), GROUP_CONCAT() in GROUP BY are not allowed So only GROUP_CONCAT() in HAVING and ORDER BY clauses were erroneously affected by this restriction. 2. Bug#27848 rollup in union part causes error with order of union commit: 3f6073ae63f9cb738cb6f0a6a8136e1d21ba0e1b Author: unknown 2007-12-15 01:42:46 The condition in the ROLLUP protection code became more complex. Note, opt_gconcat_order still reused the regular order_clause. 3. Bug#16347426 ASSERTION FAILED: (SELECT_INSERT && !TABLES->NEXT_NAME_RESOLUTION_TABLE) || !TAB commit: 2d83663380f5c0ea720e31f51898912b0006cd9f author: Chaithra Gopalareddy date: 2013-04-14 06:00:49 opt_gorder_clause was refactored not to use order_clause and to collect information directly to select->gorder_list. The ROLLUP protection code was duplicated from order_clause to the new version of opt_gorder_clause. --- mysql-test/r/func_gconcat.result | 54 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 54 insertions(+) (limited to 'mysql-test/r/func_gconcat.result') diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 9014450bef3..bc059f6384e 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -1200,3 +1200,57 @@ Warning 1260 Row 3 was cut by GROUP_CONCAT() Warning 1260 Row 5 was cut by GROUP_CONCAT() DROP TABLE t1; SET group_concat_max_len= DEFAULT; +# +# Start of 10.2 tests +# +# +# MDEV-10124 Incorrect usage of CUBE/ROLLUP and ORDER BY with GROUP_CONCAT(a ORDER BY a) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP; +a GROUP_CONCAT(a ORDER BY a) +10 10 +20 20 +30 30 +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +10 10 +20 20 +30 30 +NULL 10,20,30 +DROP VIEW v1; +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +DROP VIEW v1; +SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +DROP VIEW v1; +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'); +(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') +10,20,30 +CREATE VIEW v1 AS +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'); +SELECT * FROM v1; +Name_exp_1 +10,20,30 +DROP VIEW v1; +DROP TABLE t1; +# +# End of 10.2 tests +# -- cgit v1.2.1