revision-id: 393cf51c045878c717ee7e17478755d093675802 (mariadb-10.3.26-139-g393cf51c045) parent(s): 2c9bf0ae8758b2c46ea5e02d1ea3d3ab5cab63b2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-25 10:32:09 +0300 message: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker The optimizer removes redundant GROUP BY operations. If GROUP BY element is a subselect, it is "eliminated". However one must not eliminate the item if it is used both in the select list and in the GROUP BY, like so: select (select ... ) as SUBQ from ... group by SUBQ Do not eliminate such items. --- mysql-test/main/subselect4.result | 50 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 32 +++++++++++++++++++++++++ sql/sql_select.cc | 11 ++++++++- 3 files changed, 92 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 156e78e7778..456b9bcd829 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2786,4 +2786,54 @@ id select_type table type possible_keys key key_len ref rows Extra set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +# +# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +) +1 +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +) +1 +DROP TABLE t1; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index a5fcc507905..ab311d3e505 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2308,4 +2308,36 @@ set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +--echo # +--echo # MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY f + ); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY 1 + ); + +DROP TABLE t1; + --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b4e6c505261..d53c592ff7b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -596,7 +596,16 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) { for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next) { - (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Do not remove the item if it is used in select list and then referred + from GROUP BY clause by its name or number. Example: + + select (select ... ) as SUBQ ... group by SUBQ + + Here SUBQ cannot be removed. + */ + if (!ord->in_field_list) + (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty();