Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 761 insertions, 0 deletions

diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
new file mode 100644
index 00000000..eaf75ed7
--- /dev/null
+++ b/mysql-test/main/opt_tvc.result
@@ -0,0 +1,761 @@
+create table t1 (a int, b int);
+insert into t1
+values (1,2), (4,6), (9,7),
+(1,1), (2,5), (7,8);
+create table t2 (a int, b int, c int);
+insert into t2
+values (1,2,3), (5,1,2), (4,3,7),
+(8,9,0), (10,7,1), (5,5,1);
+create table t3 (a int, b varchar(16), index idx(a));
+insert into t3 values
+(1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"),
+(2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"),
+(3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"),
+(1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"),
+(11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"),
+(4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"),
+(6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe");
+# optimization is not used
+select * from t1 where a in (1,2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 where a in (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2)
+# set minimum number of values in VALUEs list when optimization works to 2
+set @@in_predicate_conversion_threshold= 2;
+# single IN-predicate in WHERE-part
+select * from t1 where a in (1,2);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 where a in (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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 in
+(
+select *
+from (values (1),(2)) as tvc_0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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
+# AND-condition with IN-predicates in WHERE-part
+select * from t1
+where a in (1,2) and
+b in (1,5);
+a	b
+1	1
+2	5
+select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+)
+and b in
+(
+select *
+from (values (1),(5)) as tvc_1
+);
+a	b
+1	1
+2	5
+explain extended select * from t1
+where a in (1,2) and
+b in (1,5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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`) semi join ((values (1),(5)) `tvc_1`) where 1
+explain extended select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+)
+and b in
+(
+select *
+from (values (1),(5)) as tvc_1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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`) semi join ((values (1),(5)) `tvc_1`) where 1
+# subquery with IN-predicate
+select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+a	b
+4	6
+select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
+);
+a	b
+4	6
+explain extended select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`_col_1`
+explain extended select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+# derived table with IN-predicate
+select * from
+(
+select *
+from t1
+where a in (1,2)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+explain extended select * from
+(
+select *
+from t1
+where a in (1,2)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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
+# non-recursive CTE with IN-predicate
+with tvc_0 as
+(
+select *
+from t1
+where a in (1,2)
+)
+select * from tvc_0;
+a	b
+1	2
+1	1
+2	5
+select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+explain extended with tvc_0 as
+(
+select *
+from t1
+where a in (1,2)
+)
+select * from tvc_0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* 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
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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
+# VIEW with IN-predicate
+create view v1 as
+select *
+from t1
+where a in (1,2);
+create view v2 as
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+;
+select * from v1;
+a	b
+1	2
+1	1
+2	5
+select * from v2;
+a	b
+1	2
+1	1
+2	5
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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 v2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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
+drop view v1,v2;
+# subselect defined by derived table with IN-predicate
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`_col_1`
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+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` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1`
+# derived table with IN-predicate and group by
+select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
+# prepare statement
+prepare stmt from "select * from t1 where a in (1,2)";
+execute stmt;
+a	b
+1	2
+1	1
+2	5
+execute stmt;
+a	b
+1	2
+1	1
+2	5
+deallocate prepare stmt;
+# use inside out access from tvc rows
+set @@in_predicate_conversion_threshold= default;
+select * from t3 where a in (1,4);
+a	b
+1	abc
+1	todd
+1	sm
+4	yq
+explain extended select * from t3 where a in (1,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	range	idx	idx	5	NULL	4	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4)
+set @@in_predicate_conversion_threshold= 2;
+select * from t3 where a in (1,4);
+a	b
+1	abc
+1	todd
+1	sm
+4	yq
+explain extended select * from t3 where a in (1,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t3	ref	idx	idx	5	tvc_0._col_1	3	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`_col_1`
+# use vectors in IN predeicate
+set @@in_predicate_conversion_threshold= 4;
+select * from t1 where (a,b) in ((1,2),(3,4));
+a	b
+1	2
+explain extended select * from t1 where (a,b) in ((1,2),(3,4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1
+set @@in_predicate_conversion_threshold= 2;
+# trasformation works for the one IN predicate and doesn't work for the other
+set @@in_predicate_conversion_threshold= 5;
+select * from t2
+where (a,b) in ((1,2),(8,9)) and
+(a,c) in ((1,3),(8,0),(5,1));
+a	b	c
+1	2	3
+8	9	0
+explain extended select * from t2
+where (a,b) in ((1,2),(8,9)) and
+(a,c) in ((1,3),(8,0),(5,1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
+set @@in_predicate_conversion_threshold= 2;
+#
+# mdev-14281: conversion of NOT IN predicate into subquery predicate
+# 
+select * from t1
+where (a,b) not in ((1,2),(8,9), (5,1));
+a	b
+4	6
+9	7
+1	1
+2	5
+7	8
+select * from t1
+where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
+a	b
+4	6
+9	7
+1	1
+2	5
+7	8
+explain extended select * from t1
+where (a,b) not in ((1,2),(8,9), (5,1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	8	func,func	2	100.00	Using where; Full scan on NULL key
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
+explain extended select * from t1
+where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	8	func,func	2	100.00	Using where; Full scan on NULL key
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`2`)))))
+select * from t1
+where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
+a	b
+4	6
+1	1
+2	5
+explain extended select * from t1
+where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	8	func,func	2	100.00	Using where; Full scan on NULL key
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
+select * from t2
+where (a,c) not in ((1,2),(8,9), (5,1));
+a	b	c
+1	2	3
+5	1	2
+4	3	7
+8	9	0
+10	7	1
+explain extended select * from t2
+where (a,c) not in ((1,2),(8,9), (5,1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	8	func,func	2	100.00	Using where; Full scan on NULL key
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`)))))
+drop table t1, t2, t3;
+set @@in_predicate_conversion_threshold= default;
+#
+# MDEV-14947: conversion to TVC with only NULL values
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3), (2), (7);
+SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
+i
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
+SET in_predicate_conversion_threshold= 5;
+SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
+i
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
+SET in_predicate_conversion_threshold= default;
+DROP TABLE t1;
+#
+# MDEV-14835: conversion to TVC with BIGINT or YEAR values
+#
+SET @@in_predicate_conversion_threshold= 2;
+CREATE TABLE t1 (a BIGINT);
+CREATE TABLE t2 (y YEAR);
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (2009), (2010), (2011);
+SELECT * FROM t1 WHERE a IN ('1','5','3');
+a
+1
+3
+SELECT * FROM t2 WHERE y IN ('2009','2011');
+y
+2009
+2011
+DROP TABLE t1,t2;
+SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-17222: conversion to TVC with no names for constants
+#             conversion to TVC with the same constants in the first row
+#
+SET @@in_predicate_conversion_threshold= 2;
+CREATE TABLE t1 (f BINARY(16)) ENGINE=MYISAM;
+INSERT INTO t1 VALUES
+(x'BAE56AF2B1C2397D99D58E2A06761DDB'), (x'9B9B698BCCB939EE8F1EA56C1A2E5DAA'),
+(x'A0A1C4FE39A239BABD3E0D8985E6BEA5');
+SELECT COUNT(*) FROM t1 WHERE f IN
+(x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2',
+x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB');
+COUNT(*)
+2
+CREATE TABLE t2 (f1 BINARY(16), f2 BINARY(16)) ENGINE=MYISAM;
+INSERT INTO t2 VALUES
+(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
+(x'86052C062AAF368D84247ED0F6346A70', x'BF5C35045C6037C79E11026ABB9A3A4E');
+SELECT COUNT(*) FROM t2 WHERE (f1,f2) IN
+((x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2'),
+(x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'),
+(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
+(x'1606014E7C4A312F83EDC9D91BBFCACA', x'33F6068E56FD3A1D8326517F0D81CB5A'));
+COUNT(*)
+1
+CREATE TABLE t3 (f1 int, f2 int) ENGINE=MYISAM;
+INSERT INTO t3 VALUES (2,5), (2,3), (1,2), (7,8), (1,1);
+SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1));
+f1	f2
+1	2
+1	1
+DROP TABLE t1,t2,t3;
+SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-20900: IN predicate to IN subquery conversion causes performance regression
+#
+create table t1(a int, b int);
+insert into t1 select seq-1, seq-1 from seq_1_to_10;
+set in_predicate_conversion_threshold=2;
+explain select * from t1 where t1.a IN ("1","2","3","4");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where t1.a IN ("1","2","3","4");
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=0;
+explain select * from t1 where t1.a IN ("1","2","3","4");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where t1.a IN ("1","2","3","4");
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=2;
+explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=0;
+explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+a	b
+1	1
+2	2
+3	3
+4	4
+drop table t1;
+SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-27937: Prepared statement with ? in the list if IN predicate
+#
+set in_predicate_conversion_threshold=2;
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+id	a	b
+1	3	30
+2	7	70
+3	1	10
+deallocate prepare stmt;
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+id	a	b
+1	3	30
+2	7	70
+3	1	10
+deallocate prepare stmt;
+drop table t1;
+set in_predicate_conversion_threshold=default;
+# End of 10.3 tests