Merge branch '10.6' into 10.7

1 files changed, 226 insertions, 2 deletions

diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result
index 918d0481df7..175e23ec65f 100644
--- a/mysql-test/main/func_in.result
+++ b/mysql-test/main/func_in.result
@@ -1,4 +1,3 @@
-drop table if exists t1, t2;
 select 1 in (1,2,3);
 1 in (1,2,3)
 1
@@ -553,7 +552,7 @@ Warning	1292	Truncated incorrect DECIMAL value: 'a'
 Warning	1292	Truncated incorrect DECIMAL value: 'b'
 explain select f2 from t2 where f2 in ('a','b');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	t2f2	t2f2	5	NULL	1	Using where; Using index
+1	SIMPLE	t2	ref	t2f2	t2f2	5	const	1	Using index
 Warnings:
 Warning	1292	Truncated incorrect DECIMAL value: 'a'
 Warning	1292	Truncated incorrect DECIMAL value: 'b'
@@ -935,3 +934,228 @@ Warning	1292	Truncated incorrect DECIMAL value: '0x'
 #
 # End of 10.4 tests
 #
+#
+# MDEV-29662 same values in `IN` set vs equal comparison produces
+#            the different performance
+#
+CREATE TABLE t1 (a INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM t1 WHERE a IN (1,1);
+a
+1
+# 'const' access since 'a IN (1,1)' is converted to equality 'a=1'
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+EXPLAIN SELECT * FROM t1 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 WHERE a IN (1,1,2);
+a
+1
+2
+# Conversion to equality is impossible due to different values
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+SELECT * FROM t1 WHERE a IN (1,NULL,1);
+a
+1
+# Conversion to equality is impossible due to NULL in the IN list
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,NULL,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index
+SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2);
+a
+1
+3
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE a != 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a NOT IN (3,3,1,1);
+a
+2
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN (3,3,1,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+SELECT * FROM t1 WHERE a NOT IN (1,2,NULL,NULL);
+a
+# No conversion is possible since elements are not constant
+SELECT * FROM t1 WHERE a IN ((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1));
+a
+3
+EXPLAIN SELECT * FROM t1 WHERE a IN 
+((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+# There must be no conversion here:
+SELECT * FROM t1 WHERE a IN (3,2,3,3,1,2,3);
+a
+1
+2
+3
+# Prepared statement
+PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN (3,3,3)";
+EXECUTE stmt;
+a
+3
+EXECUTE stmt;
+a
+3
+DEALLOCATE PREPARE stmt;
+# Conversion to equality since SELECT 2 is evaluated as const
+SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2));
+a
+2
+EXPLAIN SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+CREATE TABLE t2 (a INT, b VARCHAR(10), PRIMARY KEY(a,b));
+INSERT INTO t2 VALUES (1,'abc'),(2,'def'),(3,'ghi');
+SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'));
+a	b
+1	abc
+# 'const' access due to conversion to equality
+EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	16	const,const	1	Using index
+SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ'));
+a	b
+2	def
+# No conversion due to different values
+EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	16	NULL	2	Using where; Using index
+SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL));
+a	b
+2	def
+# No conversion due to NULL
+EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	16	NULL	1	Using where; Using index
+SELECT * FROM t2 WHERE (a,b) NOT IN ((2,'def'),(2,'def'),(2,NULL));
+a	b
+1	abc
+3	ghi
+SELECT * FROM t2 WHERE a IN (1,1,1,1);
+a	b
+1	abc
+EXPLAIN SELECT * FROM t2 WHERE a IN (1,1,1,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using index
+EXPLAIN SELECT * FROM t2 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t2 WHERE b NOT IN ('abc','abc');
+a	b
+2	def
+3	ghi
+EXPLAIN SELECT * FROM t2 WHERE b NOT IN ('abc','abc');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	PRIMARY	16	NULL	3	Using where; Using index
+EXPLAIN SELECT * FROM t2 WHERE b != 'abc';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	PRIMARY	16	NULL	3	Using where; Using index
+# Prepared statements
+PREPARE stmt FROM "EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'))";
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	16	const,const	1	Using index
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	16	const,const	1	Using index
+DEALLOCATE PREPARE stmt;
+CREATE TABLE t3(a INT, PRIMARY KEY(a));
+INSERT INTO t3 VALUES (1),(2),(3);
+PREPARE stmt FROM "EXPLAIN SELECT * FROM t3 WHERE a IN (?,?,?)";
+EXECUTE stmt USING 1,1,1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	Using index
+EXECUTE stmt USING 2,3,4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2, t3;
+# Nested joins
+CREATE TABLE t1 (a INT, b VARCHAR(10), PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1,'abc'),(2,'def'),(3,'ghi');
+CREATE TABLE t2 (a INT, b VARCHAR(20), PRIMARY KEY(a));
+INSERT INTO t2 (a) VALUES (2),(3);
+CREATE TABLE t3 (a INT, PRIMARY KEY(a));
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a INT);
+INSERT INTO t4 VALUES (2),(3);
+# Conversion to equalities
+EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,2,2)
+AND t3.a IN (1,1,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	
+# No conversion to equalities due to different values in IN()
+EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,3)
+AND t3.a IN (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
+1	SIMPLE	t3	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
+# Conversion to equalities
+EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a)
+ON t1.a = t2.a WHERE t1.a IN (2,2,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	4	Using index
+# No conversion to equalities due to different values in IN()
+EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a)
+ON t1.a = t2.a WHERE t1.a IN (1,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	4	Using index
+# View
+CREATE VIEW v1 AS SELECT t1.*, t2.b AS t2_b FROM t1 LEFT JOIN t2
+ON t1.a = t2.a;
+EXPLAIN SELECT * FROM v1 WHERE a IN (2,2,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+EXPLAIN SELECT * FROM v1 WHERE a IN (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using index condition
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
+# Stored procedures
+CREATE PROCEDURE p1(pa INT, pb INT)
+EXPLAIN SELECT * FROM t1 WHERE a IN (pa, pb);
+CALL p1(1,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+CALL p1(2,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
+DROP TABLE t1, t2, t3, t4;
+DROP VIEW v1;
+DROP PROCEDURE p1;
+#
+# MDEV-29895 prepared view crash server (unit.conc_view)
+#
+create table t1 (username varchar(12) not null, id int(11) not null);
+create view v1 as select username from t1 where id = 0;
+prepare stmt from "select username from v1 where username in (?, ?)";
+execute stmt using "1", "1";
+username
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
+#
+# End of 10.6 tests
+#