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, 1198 insertions, 0 deletions

diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result
new file mode 100644
index 00000000..4627208d
--- /dev/null
+++ b/mysql-test/main/func_in.result
@@ -0,0 +1,1198 @@
+select 1 in (1,2,3);
+1 in (1,2,3)
+1
+select 10 in (1,2,3);
+10 in (1,2,3)
+0
+select NULL in (1,2,3);
+NULL in (1,2,3)
+NULL
+select 1 in (1,NULL,3);
+1 in (1,NULL,3)
+1
+select 3 in (1,NULL,3);
+3 in (1,NULL,3)
+1
+select 10 in (1,NULL,3);
+10 in (1,NULL,3)
+NULL
+select 1.5 in (1.5,2.5,3.5);
+1.5 in (1.5,2.5,3.5)
+1
+select 10.5 in (1.5,2.5,3.5);
+10.5 in (1.5,2.5,3.5)
+0
+select NULL in (1.5,2.5,3.5);
+NULL in (1.5,2.5,3.5)
+NULL
+select 1.5 in (1.5,NULL,3.5);
+1.5 in (1.5,NULL,3.5)
+1
+select 3.5 in (1.5,NULL,3.5);
+3.5 in (1.5,NULL,3.5)
+1
+select 10.5 in (1.5,NULL,3.5);
+10.5 in (1.5,NULL,3.5)
+NULL
+CREATE TABLE t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (1,NULL,3);
+select 1 in (a,b,c) from t1;
+1 in (a,b,c)
+1
+1
+select 3 in (a,b,c) from t1;
+3 in (a,b,c)
+1
+1
+select 10 in (a,b,c) from t1;
+10 in (a,b,c)
+0
+NULL
+select NULL in (a,b,c) from t1;
+NULL in (a,b,c)
+NULL
+NULL
+drop table t1;
+CREATE TABLE t1 (a float, b float, c float);
+insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
+select 1.5 in (a,b,c) from t1;
+1.5 in (a,b,c)
+1
+1
+select 3.5 in (a,b,c) from t1;
+3.5 in (a,b,c)
+1
+1
+select 10.5 in (a,b,c) from t1;
+10.5 in (a,b,c)
+0
+NULL
+drop table t1;
+CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
+insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
+select 'A' in (a,b,c) from t1;
+'A' in (a,b,c)
+1
+1
+select 'EFD' in (a,b,c) from t1;
+'EFD' in (a,b,c)
+1
+1
+select 'XSFGGHF' in (a,b,c) from t1;
+'XSFGGHF' in (a,b,c)
+0
+NULL
+drop table t1;
+CREATE TABLE t1 (field char(1));
+INSERT INTO t1 VALUES ('A'),(NULL);
+SELECT * from t1 WHERE field IN (NULL);
+field
+SELECT * from t1 WHERE field NOT IN (NULL);
+field
+SELECT * from t1 where field = field;
+field
+A
+SELECT * from t1 where field <=> field;
+field
+A
+NULL
+DELETE FROM t1 WHERE field NOT IN (NULL);
+SELECT * FROM t1;
+field
+A
+NULL
+drop table t1;
+create table t1 (id int(10) primary key);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
+select * from t1 where id in (2,5,9);
+id
+2
+5
+9
+drop table t1;
+create table t1 (
+a char(1) character set latin1 collate latin1_general_ci,
+b char(1) character set latin1 collate latin1_swedish_ci,
+c char(1) character set latin1 collate latin1_danish_ci
+);
+insert into t1 values ('A','B','C');
+insert into t1 values ('a','c','c');
+select * from t1 where a in (b);
+ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
+select * from t1 where a in (b,c);
+ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT), (latin1_swedish_ci,IMPLICIT), (latin1_danish_ci,IMPLICIT) for operation 'in'
+select * from t1 where 'a' in (a,b,c);
+ERROR HY000: Illegal mix of collations for operation 'in'
+select * from t1 where 'a' in (a);
+a	b	c
+A	B	C
+a	c	c
+select * from t1 where a in ('a');
+a	b	c
+A	B	C
+a	c	c
+select * from t1 where 'a' collate latin1_general_ci in (a,b,c);
+a	b	c
+A	B	C
+a	c	c
+select * from t1 where 'a' collate latin1_bin in (a,b,c);
+a	b	c
+a	c	c
+select * from t1 where 'a' in (a,b,c collate latin1_bin);
+a	b	c
+a	c	c
+explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 'a' in (`test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c` collate latin1_bin)
+drop table t1;
+set names utf8;
+create table t1 (a char(10) character set utf8 not null);
+insert into t1 values ('bbbb'),(_koi8r'ÃÃÃÃ'),(_latin1'ÄÄÄÄ');
+select a from t1 where a in ('bbbb',_koi8r'ÃÃÃÃ',_latin1'ÄÄÄÄ') order by a;
+a
+ÄÄÄÄ
+bbbb
+цццц
+drop table t1;
+create table t1 (a char(10) character set latin1 not null);
+insert into t1 values ('a'),('b'),('c');
+select a from t1 where a IN ('a','b','c') order by a;
+a
+a
+b
+c
+drop table t1;
+set names latin1;
+select '1.0' in (1,2);
+'1.0' in (1,2)
+1
+select 1 in ('1.0',2);
+1 in ('1.0',2)
+1
+select 1 in (1,'2.0');
+1 in (1,'2.0')
+1
+select 1 in ('1.0',2.0);
+1 in ('1.0',2.0)
+1
+select 1 in (1.0,'2.0');
+1 in (1.0,'2.0')
+1
+select 1 in ('1.1',2);
+1 in ('1.1',2)
+0
+select 1 in ('1.1',2.0);
+1 in ('1.1',2.0)
+0
+create table t1 (a char(2) character set binary);
+insert into t1 values ('aa'), ('bb');
+select * from t1 where a in (NULL, 'aa');
+a
+aa
+drop table t1;
+create table t1 (id int, key(id));
+insert into t1 values (1),(2),(3);
+select count(*) from t1 where id not in (1);
+count(*)
+2
+select count(*) from t1 where id not in (1,2);
+count(*)
+1
+drop table t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 SELECT 1 IN (2, NULL);
+SELECT should return NULL.
+SELECT * FROM t1;
+1 IN (2, NULL)
+NULL
+DROP TABLE t1;
+End of 4.1 tests
+CREATE TABLE t1 (a int PRIMARY KEY);
+INSERT INTO t1 VALUES (44), (45), (46);
+SELECT * FROM t1 WHERE a IN (45);
+a
+45
+SELECT * FROM t1 WHERE a NOT IN (0, 45);
+a
+44
+46
+SELECT * FROM t1 WHERE a NOT IN (45);
+a
+44
+46
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45);
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` <> 45	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+a
+44
+46
+DROP VIEW v1;
+DROP TABLE t1;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, filler char(200), key(a));
+insert into t2 select C.a*2,   'no'  from t1 A, t1 B, t1 C;
+insert into t2 select C.a*2+1, 'yes' from t1 C;
+explain 
+select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	a	a	5	NULL	11	Using index condition
+select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
+a	filler
+1	yes
+3	yes
+5	yes
+7	yes
+9	yes
+11	yes
+13	yes
+15	yes
+17	yes
+19	yes
+explain select * from t2 force index(a) 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	t2	range	a	a	5	NULL	912	Using index condition
+explain select * from t2 force index(a) where a <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	a	a	5	NULL	912	Using index condition
+drop table t2;
+create table t2 (a datetime, filler char(200), key(a));
+insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
+'no'  from t1 A, t1 B, t1 C where C.a % 2 = 0;
+insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
+'yes' from t1 C;
+explain 
+select * from t2 where a NOT IN (
+'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
+'2006-04-25 10:06:00', '2006-04-25 10:08:00');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	a	a	6	NULL	11	Using index condition
+select * from t2 where a NOT IN (
+'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
+'2006-04-25 10:06:00', '2006-04-25 10:08:00');
+a	filler
+2006-04-25 10:01:00	yes
+2006-04-25 10:03:00	yes
+2006-04-25 10:05:00	yes
+2006-04-25 10:07:00	yes
+2006-04-25 10:09:00	yes
+2006-04-25 10:11:00	yes
+2006-04-25 10:13:00	yes
+2006-04-25 10:15:00	yes
+2006-04-25 10:17:00	yes
+2006-04-25 10:19:00	yes
+drop table t2;
+create table t2 (a varchar(10), filler char(200), key(a));
+insert into t2 select 'foo', 'no' from t1 A, t1 B;
+insert into t2 select 'barbar', 'no' from t1 A, t1 B;
+insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
+insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), 
+('barbas','1'), ('bazbazbay', '1'),('zz','1');
+explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	a	a	13	NULL	6	Using index condition
+drop table t2;
+create table t2 (a decimal(10,5), filler char(200), key(a));
+insert into t2 select 345.67890, 'no' from t1 A, t1 B;
+insert into t2 select 43245.34, 'no' from t1 A, t1 B;
+insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
+insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), 
+(55555,'1'), (77777, '1');
+explain
+select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	a	a	7	NULL	6	Using index condition
+select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
+a	filler
+0.00000	1
+22334.12300	1
+33333.00000	1
+55555.00000	1
+77777.00000	1
+drop table t2;
+create table t2 (a int, key(a), b int);
+insert into t2 values (1,1),(2,2);
+set @cnt= 1;
+set @str="update t2 set b=1 where a not in (";
+select count(*) from (
+select @str:=concat(@str, @cnt:=@cnt+1, ",") 
+from t1 A, t1 B, t1 C, t1 D) Z;
+count(*)
+10000
+set @str:=concat(@str, "10000)");
+select substr(@str, 1, 50);
+substr(@str, 1, 50)
+update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,
+prepare s from @str;
+execute s;
+deallocate prepare s;
+set @str=NULL;
+drop table t2;
+drop table t1;
+create table t1 (
+some_id smallint(5) unsigned,
+key (some_id)
+);
+insert into t1 values (1),(2);
+select some_id from t1 where some_id not in(2,-1);
+some_id
+1
+select some_id from t1 where some_id not in(-4,-1,-4);
+some_id
+1
+2
+select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
+some_id
+1
+2
+select some_id from t1 where some_id not in('-1', '0');
+some_id
+1
+2
+drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a int PRIMARY KEY,b int);
+INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
+(1003,1003),(1004,1004);
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
+SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+a	a	b	a	b	a	b
+3	3	1	3	2	1	1
+3	3	1	3	2	2	2
+4	4	1	4	2	1	1
+4	4	1	4	2	2	2
+EXPLAIN SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
+SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+a
+SELECT * FROM t1 WHERE a IN (-1, -2);
+a
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+(0x7fffffefffffffff),
+(0x7ffffffeffffffff),
+(0x7fffffffefffffff),
+(0x7ffffffffeffffff),
+(0x7fffffffffefffff),
+(0x7ffffffffffeffff),
+(0x7fffffffffffefff),
+(0x7ffffffffffffeff),
+(0x7fffffffffffffef),
+(0x7ffffffffffffffe),
+(0x7fffffffffffffff),
+(0x8000000000000000),
+(0x8000000000000001),
+(0x8000000000000002),
+(0x8000000000000300),
+(0x8000000000000400),
+(0x8000000000000401),
+(0x8000000000004001),
+(0x8000000000040001),
+(0x8000000000400001),
+(0x8000000004000001),
+(0x8000000040000001),
+(0x8000000400000001),
+(0x8000004000000001),
+(0x8000040000000001);
+SELECT HEX(a) FROM t2 WHERE a IN 
+(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
+42);
+HEX(a)
+BB3C3E98175D33C8
+SELECT HEX(a) FROM t2 WHERE a IN
+(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
+CAST(0x7fffffffffffffff AS UNSIGNED),
+CAST(0x8000000000000000 AS UNSIGNED),
+CAST(0x8000000000000400 AS UNSIGNED),
+CAST(0x8000000000000401 AS UNSIGNED),
+42);
+HEX(a)
+BB3C3E98175D33C8
+7FFFFFFFFFFFFFFF
+8000000000000000
+8000000000000400
+8000000000000401
+SELECT HEX(a) FROM t2 WHERE a IN 
+(CAST(0x7fffffffffffffff AS UNSIGNED), 
+CAST(0x8000000000000001 AS UNSIGNED));
+HEX(a)
+7FFFFFFFFFFFFFFF
+8000000000000001
+SELECT HEX(a) FROM t2 WHERE a IN 
+(CAST(0x7ffffffffffffffe AS UNSIGNED), 
+CAST(0x7fffffffffffffff AS UNSIGNED));
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+SELECT HEX(a) FROM t2 WHERE a IN 
+(0x7ffffffffffffffe, 
+0x7fffffffffffffff,
+'abc');
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'abc'
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+HEX(a)
+CREATE TABLE t4 (a DATE);
+INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
+SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
+a
+1972-02-06
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '19772-07-29'
+DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (id int not null);
+INSERT INTO t1 VALUES (1),(2);
+SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
+id
+Warnings:
+Warning	1365	Division by 0
+DROP TABLE t1;
+End of 5.0 tests
+create table t1(f1 char(1));
+insert into t1 values ('a'),('b'),('1');
+select f1 from t1 where f1 in ('a',1);
+f1
+a
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
+f1	case f1 when 'a' then '+' when 1 then '-' end
+a	+
+b	NULL
+1	-
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+create index t1f1_idx on t1(f1);
+select f1 from t1 where f1 in ('a',1);
+f1
+1
+a
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+explain select f1 from t1 where f1 in ('a',1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
+select f1 from t1 where f1 in ('a','b');
+f1
+a
+b
+explain select f1 from t1 where f1 in ('a','b');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1f1_idx	t1f1_idx	2	NULL	2	Using where; Using index
+select f1 from t1 where f1 in (2,1);
+f1
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'a'
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+explain select f1 from t1 where f1 in (2,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
+create table t2(f2 int, index t2f2(f2));
+insert into t2 values(0),(1),(2);
+select f2 from t2 where f2 in ('a',2);
+f2
+0
+2
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'a'
+Warning	1292	Truncated incorrect DECIMAL value: 'a'
+Warning	1292	Truncated incorrect DECIMAL value: 'a'
+explain select f2 from t2 where f2 in ('a',2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
+select f2 from t2 where f2 in ('a','b');
+f2
+0
+Warnings:
+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	ref	t2f2	t2f2	5	const	1	Using index
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'a'
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+select f2 from t2 where f2 in (1,'b');
+f2
+0
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+Warning	1292	Truncated incorrect DECIMAL value: 'b'
+explain select f2 from t2 where f2 in (1,'b');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
+drop table t1, t2;
+create table t1 (a time, key(a));
+insert into t1 values (),(),(),(),(),(),(),(),(),();
+select a from t1 where a not in (a,a,a) group by a;
+a
+drop table t1;
+create table t1 (id int);
+select * from t1 where NOT id in (select null union all select 1);
+id
+select * from t1 where NOT id in (null, 1);
+id
+drop table t1;
+CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
+INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1);
+SELECT CASE AVG (c0) WHEN c1 * c2 THEN 1 END FROM t1;
+CASE AVG (c0) WHEN c1 * c2 THEN 1 END
+1
+SELECT CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1;
+CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END
+2
+SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
+CASE c1 WHEN c1 + 1 THEN 1 END	ABS(AVG(c0))
+NULL	1.0000
+DROP TABLE t1;
+CREATE TABLE t1(a TEXT, b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL);
+INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1);
+INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2);
+SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a);
+SUM( DISTINCT a )
+SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b);
+SUM( DISTINCT b )
+1
+SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c);
+SUM( DISTINCT c )
+1
+SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d);
+SUM( DISTINCT d )
+1.00
+SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e);
+SUM( DISTINCT e )
+1
+SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN 
+((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d));
+SUM( DISTINCT e )
+DROP TABLE t1;
+#
+# Bug #44139: Table scan when NULL appears in IN clause
+#
+CREATE TABLE t1 (
+c_int INT NOT NULL,
+c_decimal DECIMAL(5,2) NOT NULL,
+c_float FLOAT(5, 2) NOT NULL,
+c_bit BIT(10) NOT NULL,
+c_date DATE NOT NULL, 
+c_datetime DATETIME NOT NULL,
+c_timestamp TIMESTAMP NOT NULL,
+c_time TIME NOT NULL,
+c_year YEAR NOT NULL,
+c_char CHAR(10) NOT NULL,
+INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date),
+INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year),
+INDEX(c_char));
+INSERT IGNORE INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
+INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1;
+INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_int	c_int	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_int	c_int	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_int	c_int	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_int	c_int	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_decimal	c_decimal	3	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_decimal	c_decimal	3	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_float	c_float	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_float	c_float	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_bit	c_bit	2	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_bit	c_bit	2	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_date 
+IN ('2009-09-01', '2009-09-02', '2009-09-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_date	c_date	3	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_date
+IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_date	c_date	3	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_datetime
+IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_datetime	c_datetime	5	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_datetime
+IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_datetime	c_datetime	5	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_timestamp
+IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_timestamp	c_timestamp	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_timestamp
+IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_timestamp	c_timestamp	4	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_year	c_year	1	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_year	c_year	1	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_char	c_char	10	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	c_char	c_char	10	NULL	3	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
+#
+# Bug#54477: Crash on IN / CASE with NULL arguments
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2);
+SELECT 1 IN (NULL, a) FROM t1;
+1 IN (NULL, a)
+1
+NULL
+SELECT a IN (a, a) FROM t1 GROUP BY a WITH ROLLUP;
+a IN (a, a)
+1
+1
+NULL
+SELECT CASE a WHEN a THEN a END FROM t1 GROUP BY a WITH ROLLUP;
+CASE a WHEN a THEN a END
+1
+2
+NULL
+DROP TABLE t1;
+#
+# Bug #11766212  59270: NOT IN (YEAR( ... ), ... ) PRODUCES MANY VALGRIND WARNINGS
+#
+SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1);
+1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1)
+1
+#
+#
+# Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY
+#
+CREATE TABLE t1 (a INT);
+PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @a:= (1 IN (SELECT 1 FROM t1)) FROM t1)";
+EXECUTE s;
+1
+DROP TABLE t1;
+# End of test  BUG#13012483
+#
+End of 5.1 tests
+create table t1 (a bigint, b int);
+insert t1 values (1,1),(2,2),(3,3);
+select * from t1 where a in ('2.1');
+a	b
+select * from t1 where b in ('2.1');
+a	b
+select * from t1 where a='2.1';
+a	b
+select * from t1 where b='2.1';
+a	b
+select * from t1 where IF(1,a,a)='2.1';
+a	b
+drop table t1;
+#
+# End of 5.3 tests
+#
+create table t1 (a int);
+insert t1 values (1),(2),(3);
+select * from t1 where 1 in (a, name_const('a', null));
+a
+1
+drop table t1;
+#
+# End of 5.5 tests
+#
+#
+# MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL
+#
+CREATE TABLE t1
+(
+a INT(11),
+b VARCHAR(10),
+KEY (b)
+);
+INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z');
+SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A');
+a	b
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
+#
+# MDEV-8755 Equal field propagation is not performed any longer for the IN list when multiple comparison types
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+# Ok to propagate equalities into the left IN argument in case of a single comparison type
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1
+# Ok to propagate equalities into IN () list, even if multiple comparison types
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND 1 IN (1,a,'3');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1
+# Not Ok to propagate equalities into the left IN argument in case of multiple comparison types
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,'3');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` in (1,2,'3')
+DROP TABLE t1;
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result
+#
+SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32');
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+PREPARE stmt FROM "SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')";
+EXECUTE stmt;
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+EXECUTE stmt;
+TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')
+1
+DEALLOCATE PREPARE stmt;
+#
+# MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions))
+#
+CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615);
+SELECT * FROM t1 WHERE -1 IN (a,b);
+a	b
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE -1 IN (a,b)';
+EXECUTE stmt;
+a	b
+EXECUTE stmt;
+a	b
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+#
+# MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
+#
+SELECT
+TIME'00:00:00'='' AS c1_true,
+TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true,
+TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false;
+c1_true	c2_true	c3_false
+1	1	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+Warning	1292	Truncated incorrect time value: ''
+Warning	1292	Truncated incorrect time value: ''
+#
+# End of 10.3 tests
+#
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-16454 Bad results for IN with ROW
+#
+SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0));
+(18446744073709551615,0) IN ((18446744073709551614,0),(-1,0))
+0
+SELECT '0x' IN (0);
+'0x' IN (0)
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '0x'
+SELECT '0x' IN (0,1);
+'0x' IN (0,1)
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '0x'
+SELECT ('0x',1) IN ((0,1));
+('0x',1) IN ((0,1))
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '0x'
+SELECT ('0x',1) IN ((0,1),(1,1));
+('0x',1) IN ((0,1),(1,1))
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '0x'
+#
+# End of 10.4 tests
+#
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-31303: Key not used
+#
+CREATE TABLE `a` (
+`id` bigint AUTO_INCREMENT PRIMARY KEY,
+`c1` bigint unsigned,
+KEY (`c1`)
+);
+INSERT INTO `a` VALUES (1,9223382399205928659),(2,9223384207280813348),
+(3,9223385953115437234),(4,9223387250780556749),(5,9223387354282558788),
+(6,9223387603870501596),(7,9223389270813433667),(8,9223389903231468827),
+(9,9223390280789586779),(10,9223391591398222899),(11,9223391875473564350),
+(12,9223393152250049433),(13,9223393939696790223),(14,9223394417225350415),
+(15,9223397646397141015),(16,9223398025879291243),(17,9223399038671098072),
+(18,9223399534968874556),(19,9223400449518009285),(20,9223400860292643549),
+(21,9223400940692256924),(22,9223401073791948119),(23,9223402820804649616),
+(24,9223403470951992681),(25,9223405581879567267),(26,9223405754978563829),
+(27,9223405972966828221), (28, 9223372036854775808), (29, 9223372036854775807) ;
+explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775807 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	a	range	c1	c1	9	NULL	2	Using where; Using index
+explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	a	range	c1	c1	9	NULL	2	Using where; Using index
+SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775807 );
+c1
+9223372036854775807
+SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 );
+c1
+9223372036854775808
+drop table `a`;
+#
+# End of 10.5 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
+#