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>

2 files changed, 190 insertions, 0 deletions

diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index c374040e9e8..c794aade773 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 2faede5c27e..6aedabcd3f5 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