revision-id: 660269c6b75d81b9ee9cbe935af00779b04bf30a (mariadb-10.3.35-73-g660269c) parent(s): 1848804840f5595f982c4cd502ba2112f6dd7911 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-20 16:00:20 -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(). However the references to them remained in the JOIN::all_fields list 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 after its GROUP BY clause has been altogether eliminated. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/subselect4.result | 45 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 33 ++++++++++++++++++++++++++++ sql/sql_select.cc | 26 +++++++++++++++++++++- sql/sql_select.h | 1 + 4 files changed, 104 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index ba5a6bb..932ca1e 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2981,4 +2981,49 @@ 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 +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..a59f4bb 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2438,4 +2438,37 @@ 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; + +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..7a66f77 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -544,6 +544,28 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, return false; } + +/** + @brief + Remove elements that are not actual anymore in the list of all fields + + @note + This is needed in order not to confuse JOIN::make_aggr_tables_info(). +*/ + +void JOIN::remove_eliminated_subqueries_from_all_fields_list() +{ + List_iterator<Item> li(all_fields); + Item *item; + while ((item= li++)) + { + if (item->type() == Item::SUBSELECT_ITEM && + ((Item_subselect *) item)->eliminated) + li.remove(); + } +} + + /** The following clauses are redundant for subqueries: @@ -1177,8 +1199,10 @@ JOIN::prepare(TABLE_LIST *tables_init, !thd->lex->is_view_context_analysis()) // 3) { remove_redundant_subquery_clauses(select_lex); + remove_eliminated_subqueries_from_all_fields_list(); } + /* Resolve the ORDER BY that was skipped, then remove it. */ if (skip_order_by && select_lex != select_lex->master_unit()->global_parameters()) @@ -2874,7 +2898,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) diff --git a/sql/sql_select.h b/sql/sql_select.h index c8ff8b2..7932b5d 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1804,6 +1804,7 @@ class JOIN :public Sql_alloc void cleanup_item_list(List<Item> &items) const; bool add_having_as_table_cond(JOIN_TAB *tab); + void remove_eliminated_subqueries_from_all_fields_list(); bool make_aggr_tables_info(); bool add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *fields); };