revision-id: 31fabc1f4d6518d0102d6c2b9075f71198d5ca58 (mariadb-10.3.35-73-g31fabc1) parent(s): 1848804840f5595f982c4cd502ba2112f6dd7911 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-21 14:57:35 -0700 message: MDEV-29139 Crash when using ANY predicand with redundant subquery in GROUP BY clause This bug could cause a crash of the server when executing queries containing ANY/ALL predicands with redundant subqueries in GROUP BY clauses. These subqueries are eliminated by remove_redundant_subquery_clause() together with elimination of GROUP BY list containing these subqueries. However the references to the elements of the GROUP BY remained in the JOIN::all_fields list of the right operand of of the ALL/ANY predicand. Later these references confused make_aggr_tables_info() when forming proper execution structures after ALL/ANY predicands had been replaced with expressions containing MIN/MAX set functions. The patch just removes these references from JOIN::all_fields list used by the subquery of the ALL/ANY predicand when its GROUP BY clause is eliminated. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/subselect4.result | 57 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 39 +++++++++++++++++++++++++++ sql/sql_select.cc | 18 ++++++++++++- 3 files changed, 113 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index ba5a6bb..c374040 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2981,4 +2981,61 @@ ANALYZE } DROP TABLE t1; # End of 10.2 tests +# +# MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +# +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); +explain extended select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +a +3 +2 +prepare stmt from "select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1))"; +execute stmt; +a +3 +2 +execute stmt; +a +3 +2 +deallocate prepare stmt; +explain extended select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +a +1 +2 +explain extended select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +a +3 +2 +drop table t1,t2,t3; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index aeef884..2faede5 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2438,4 +2438,43 @@ DROP TABLE t1; --echo # End of 10.2 tests + +--echo # +--echo # MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); + +let $q1= +select a from t1 + where a >= any (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q1; +eval $q1; + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select a from t1 + where a <= all (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q2; +eval $q2; + +let $q3= +select a from t1 + where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +eval explain extended $q3; +eval $q3; + +drop table t1,t2,t3; + --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0803e78..049d976 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -544,6 +544,7 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, return false; } + /** The following clauses are redundant for subqueries: @@ -605,7 +606,22 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) Here SUBQ cannot be removed. */ if (!ord->in_field_list) + { (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Remove from the JOIN::all_fields list any reference to the elements + of the eliminated GROUP BY list unless it is 'in_field_list'. + This is needed in order not to confuse JOIN::make_aggr_tables_info() + when it constructs different structure for execution phase. + */ + List_iterator<Item> li(subq_select_lex->join->all_fields); + Item *item; + while ((item= li++)) + { + if (item == *ord->item) + li.remove(); + } + } } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty(); @@ -2874,7 +2890,7 @@ bool JOIN::make_aggr_tables_info() DBUG_ENTER("JOIN::make_aggr_tables_info"); const bool has_group_by= this->group; - + sort_and_group_aggr_tab= NULL; if (group_optimized_away)