From d305d98a939f13dff9076187aaaafe21b35a5aec Mon Sep 17 00:00:00 2001 From: casazhang Date: Tue, 20 Sep 2022 14:17:40 +0800 Subject: [PATCH] [bugfix] GROUP_CONCAT NOT SET USED_TABLES WHEN DERIVED_MERGE. When derived_merge switch on, subquery contains group_concat() may produce wrong results, since `used_tables_cache` does not been set correctly. group_concat() function should consider add fix_after_pullput() when derived merge into outer query block. --- mysql-test/r/derived.result | 51 +++++++++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 49 +++++++++++++++++++++++++++++++++++ sql/item_sum.cc | 13 ++++++++++ sql/item_sum.h | 2 ++ 4 files changed, 115 insertions(+) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index ffcc7eb90f8..05013726bed 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1414,6 +1414,57 @@ FROM t1 DROP TABLE t1; # # +# Bug# GROUP_CONCAT NOT SET USED_TABLES WHEN DERIVED_MERGE ON. +# +CREATE TABLE `t1` (`c1` bigint(20), `c2` varchar(30), `c3` varchar(30), `c4` varchar(30), PRIMARY KEY (`c1`)); +INSERT INTO t1 VALUES(1, 'one', 'category', 'ab'); +INSERT INTO t1 VALUES(2, 'two', 'category', 'cc'); +INSERT INTO t1 VALUES(3, 'three', 'category', 'aa'); +INSERT INTO t1 VALUES(4, 'four', 'category', 'bb'); +CREATE TABLE `t2` ( `c1` varchar(10), `c2` varchar(80), PRIMARY KEY (`c1`)); +INSERT INTO t2 VALUES('fine', 'aa'); +INSERT INTO t2 VALUES('thanks', 'bb'); +SET @optimizer_switch_saved= @@optimizer_switch; +SET @@optimizer_switch="derived_merge=on"; +SELECT * FROM +( +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) +) AS D; +a b c d e +3 three aa fine aa +4 four bb thanks bb +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +a b c d e +3 three aa fine aa +4 four bb thanks bb +SET SESSION OPTIMIZER_SWITCH="derived_merge=off"; +SELECT * FROM +( +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) +) AS D; +a b c d e +3 three aa fine aa +4 four bb thanks bb +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +a b c d e +3 three aa fine aa +4 four bb thanks bb +SET @@optimizer_switch=@optimizer_switch_saved; +DROP TABLE t1; +DROP TABLE t2; +# # Bug#11808582: VALGRIND ON WL#5274: INVALID WRITE IN MC_REPLACE_STRMEM.C:493) # CREATE TABLE t1 ( diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 29c42147791..db83a967cb1 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -755,6 +755,55 @@ FROM ( DROP TABLE t1; --echo # +--echo # +--echo # Bug# GROUP_CONCAT NOT SET USED_TABLES WHEN DERIVED_MERGE ON. +--echo # + +CREATE TABLE `t1` (`c1` bigint(20), `c2` varchar(30), `c3` varchar(30), `c4` varchar(30), PRIMARY KEY (`c1`)); +INSERT INTO t1 VALUES(1, 'one', 'category', 'ab'); +INSERT INTO t1 VALUES(2, 'two', 'category', 'cc'); +INSERT INTO t1 VALUES(3, 'three', 'category', 'aa'); +INSERT INTO t1 VALUES(4, 'four', 'category', 'bb'); + +CREATE TABLE `t2` ( `c1` varchar(10), `c2` varchar(80), PRIMARY KEY (`c1`)); +INSERT INTO t2 VALUES('fine', 'aa'); +INSERT INTO t2 VALUES('thanks', 'bb'); + +SET @optimizer_switch_saved= @@optimizer_switch; +SET @@optimizer_switch="derived_merge=on"; + +SELECT * FROM +( + SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e + FROM t1, t2 + WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) +) AS D; + +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); + +SET SESSION OPTIMIZER_SWITCH="derived_merge=off"; + +SELECT * FROM +( + SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e + FROM t1, t2 + WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) +) AS D; + +SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e +FROM t1, t2 +WHERE t1.c3 = 'category' + AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); + +SET @@optimizer_switch=@optimizer_switch_saved; +DROP TABLE t1; +DROP TABLE t2; + --echo # --echo # Bug#11808582: VALGRIND ON WL#5274: INVALID WRITE IN MC_REPLACE_STRMEM.C:493) --echo # diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 3b3aa81ced3..8d27406bedf 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3821,6 +3821,19 @@ void Item_func_group_concat::print(String *str, enum_query_type query_type) str->append(STRING_WITH_LEN("\')")); } +void Item_func_group_concat::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select) +{ + // Perform pullout of arguments to aggregate function + used_tables_cache = 0; + + Item **arg, **arg_end; + for (arg = args, arg_end = args + arg_count; arg != arg_end; arg++) { + Item *const item = *arg; + item->fix_after_pullout(parent_select, removed_select); + used_tables_cache |= item->used_tables(); + } +} Item_func_group_concat::~Item_func_group_concat() { diff --git a/sql/item_sum.h b/sql/item_sum.h index 442a5c07239..cced83cb125 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -1622,6 +1622,8 @@ public: context= reinterpret_cast(cntx); return false; } + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select); }; #endif /* ITEM_SUM_INCLUDED */ -- 2.26.2