Merge branch '10.3' into 10.4

1 files changed, 108 insertions, 2 deletions

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index f965fab0e7a..88780179a36 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -748,7 +748,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -983,7 +983,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -2776,6 +2776,112 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 drop table t1;
 #
+# MDEV-24910: TVC containing subquery used as a subselect
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+create table t2 (b int) engine=myisam;
+insert into t2 values (1), (2);
+select (values ((select 2))) from t2;
+(values ((select 2)))
+2
+2
+explain select (values ((select 2))) from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1249	Select 3 was reduced during optimization
+prepare stmt from "select (values ((select 2))) from t2";
+execute stmt;
+(values ((select 2)))
+2
+2
+execute stmt;
+(values ((select 2)))
+2
+2
+deallocate prepare stmt;
+select (values ((select * from t1 where a > 10))) from t2;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+explain select (values ((select * from t1 where a > 10))) from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+prepare stmt from "select (values ((select * from t1 where a > 10))) from t2";
+execute stmt;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+execute stmt;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+deallocate prepare stmt;
+create table t3 (a int);
+insert into t3 values
+(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8);
+create view v1 as select count(a) as c from t3 group by a;
+select
+(values ((select * from t3 where a in (select * from v1))));
+(values ((select * from t3 where a in (select * from v1))))
+1
+explain select
+(values ((select * from t3 where a in (select * from v1))));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+6	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	11	
+3	SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	
+5	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
+prepare stmt from "select
+(values ((select * from t3 where a in (select * from v1))))";
+execute stmt;
+(values ((select * from t3 where a in (select * from v1))))
+1
+execute stmt;
+(values ((select * from t3 where a in (select * from v1))))
+1
+deallocate prepare stmt;
+select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))));
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+explain select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+6	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	11	Using where
+3	SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	
+5	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
+prepare stmt from "select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))";
+execute stmt;
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+execute stmt;
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+deallocate prepare stmt;
+drop view v1;
+drop table t1,t2,t3;
+#
 # End of 10.3 tests
 #
 #