Merge 10.4 into 10.5

1 files changed, 63 insertions, 6 deletions

diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 070be6dcf19..54716a83b69 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -1677,7 +1677,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f
 f1	f2
 8	8
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'u'
+Warning	1292	Truncated incorrect DECIMAL value: 'u'
 EXPLAIN
 SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1711,7 +1711,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f
 f1	f2
 8	8
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'u'
+Warning	1292	Truncated incorrect DECIMAL value: 'u'
 EXPLAIN
 SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1903,8 +1903,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN
 (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
 col_int_key
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'v'
-Warning	1292	Truncated incorrect DOUBLE value: 'r'
+Warning	1292	Truncated incorrect DECIMAL value: 'v'
+Warning	1292	Truncated incorrect DECIMAL value: 'r'
 set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT col_int_key
@@ -1928,8 +1928,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN
 (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
 col_int_key
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'v'
-Warning	1292	Truncated incorrect DOUBLE value: 'r'
+Warning	1292	Truncated incorrect DECIMAL value: 'v'
+Warning	1292	Truncated incorrect DECIMAL value: 'r'
 drop table t1, t2;
 set @@optimizer_switch = @old_optimizer_switch;
 #
@@ -2985,6 +2985,63 @@ 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
 #
 # MDEV-19134: EXISTS() slower if ORDER BY is defined