revision-id: f78fcd317a2496ac65c8995fb5c65c248e85c2d8 (mariadb-10.3.35-113-gf78fcd3) parent(s): c2300d06f7845f51db6318c2fdcbadd6becc0e89 author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-24 11:07:09 -0700 message: MDEV-29350 Crash when IN predicand is used in eliminated GROUP BY clause This bug affected some queries with an IN/ALL/ANY predicand or an EXISTS predicate whose subquery contained a GROUP BY clause that could be eliminated. If this clause used a IN/ALL/ANY predicand whose left operand was a single-value subquery then execution of the query caused a crash of the server after invokation of remove_redundant_subquery_clauses(). The crash was caused by an attempt to exclude the unit for the single-value subquery from the query tree for the second time by the function Item_subselect::eliminate_subselect_processor(). This bug had been masked by the bug MDEV-28617 until a fix for the latter that properly excluded units was pushed into 10.3. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/subselect4.result | 116 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 74 ++++++++++++++++++++++++ sql/item_subselect.cc | 3 +- 3 files changed, 192 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index c374040..c794aad 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -3038,4 +3038,120 @@ a 3 2 drop table t1,t2,t3; +# +# MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of +# IN/ALL/ANY/EXISTS subquery +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d int); +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t4 values (1), (7); +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +3 +2 +prepare stmt from "select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4))"; +execute stmt; +b +3 +2 +execute stmt; +b +3 +2 +deallocate prepare stmt; +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +b +3 +2 +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +b +3 +2 +explain extended select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where 1 +select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +2 +explain extended select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <nop>(<in_optimizer>(`test`.`t2`.`b`,(/* select#2 */ select min(`test`.`t3`.`c`) from `test`.`t3`) <= <cache>(`test`.`t2`.`b`))) +select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +3 +2 +explain extended select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <not>(<in_optimizer>(`test`.`t2`.`b`,<min>(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3`) < <cache>(`test`.`t2`.`b`))) +select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +2 +drop table t1,t2,t3,t4; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 2faede5..6aedabc 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2477,4 +2477,78 @@ eval $q3; drop table t1,t2,t3; +--echo # +--echo # MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of +--echo # IN/ALL/ANY/EXISTS subquery +--echo # + +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d int); + +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t4 values (1), (7); + +let $q1= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) in (select d from t4)); + +eval explain extended $q1; +eval $q1; + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) >= + any (select d from t4)); + +eval explain extended $q2; +eval $q2; + +let $q3= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) < + all (select d from t4)); + +eval explain extended $q3; +eval $q3; + +let $q4= +select b from t2 + where b in (select c from t3 + group by (select a from t1 where a = 1) in (select d from t4)); + +eval explain extended $q4; +eval $q4; + +let $q5= +select b from t2 + where b >= any (select c from t3 + group by (select a from t1 where a = 1) in + (select d from t4)); + +eval explain extended $q5; +eval $q5; + +let $q6= +select b from t2 + where b <= all (select c from t3 + group by (select a from t1 where a = 1) in + (select d from t4)); + +eval explain extended $q6; +eval $q6; + +drop table t1,t2,t3,t4; + --echo # End of 10.3 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 2e33a71..ee36ee2 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -379,7 +379,8 @@ bool Item_subselect::mark_as_eliminated_processor(void *arg) bool Item_subselect::eliminate_subselect_processor(void *arg) { unit->item= NULL; - unit->exclude(); + if (!unit->is_excluded()) + unit->exclude(); eliminated= TRUE; return FALSE; }