~drizzle-trunk/drizzle/development : contents of tests/r/func

~drizzle-trunk/drizzle/development : (revision 1340.1.5)

drop table if exists t1, t2;
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 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),
b char(1),
c char(1)
);
insert into t1 values ('A','B','C');
insert into t1 values ('a','c','c');
select * from t1 where a in (b);
a	b	c
select * from t1 where a in (b,c);
a	b	c
select * from t1 where 'a' in (a,b,c);
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 in ('a');
a	b	c
A	B	C
a	c	c
select * from t1 where 'a' collate utf8_general_ci in (a,b,c);
a	b	c
A	B	C
a	c	c
select * from t1 where 'a' collate utf8_bin in (a,b,c);
a	b	c
a	c	c
select * from t1 where 'a' in (a,b,c collate utf8_bin);
a	b	c
a	c	c
explain extended select * from t1 where 'a' in (a,b,c collate utf8_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 utf8_bin)))
drop table t1;
create table t1 (a char(10) 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;
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));
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
DROP TABLE t1;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create temporary table t2 (a int, filler char(200), key(a)) engine=myisam;
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	12	Using where
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 where
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 where
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	9	NULL	20	Using where
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	43	NULL	5	Using where
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	4	Using where
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,
set @str=NULL;
drop table t2;
drop table t1;
create table t1 (
some_id int,
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	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer
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	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer
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);
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
a
SELECT * FROM t1 WHERE a IN (-1, -2);
a
CREATE TABLE t2 (a BIGINT);
insert into t2 values(13491727406643098568),
(0x0fffffefffffffff),
(0x0ffffffeffffffff),
(0x0fffffffefffffff),
(0x0ffffffffeffffff),
(0x0fffffffffefffff),
(0x0ffffffffffeffff),
(0x0fffffffffffefff),
(0x0ffffffffffffeff),
(0x0fffffffffffffef),
(0x0ffffffffffffffe),
(0x0fffffffffffffff),
(0x2000000000000000),
(0x2000000000000001),
(0x2000000000000002),
(0x2000000000000300),
(0x2000000000000400),
(0x2000000000000401),
(0x2000000000004001),
(0x2000000000040001),
(0x2000000000400001),
(0x2000000004000001),
(0x2000000040000001),
(0x2000000400000001),
(0x2000004000000001),
(0x2000040000000001);
SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
HEX(a)
SELECT HEX(a) FROM t2 WHERE a IN
(0xBB3C3E98175D33C8,
0x2fffffffffffffff,
0x2000000000000000,
0x2000000000000400,
0x2000000000000401,
42);
HEX(a)
2000000000000000
2000000000000001
2000000000000002
2000000000000300
2000000000000400
2000000000000401
SELECT HEX(a) FROM t2 WHERE a IN 
(0x7fffffffffffffff, 
0x2000000000000001);
HEX(a)
2000000000000001
SELECT HEX(a) FROM t2 WHERE a IN 
(0x2ffffffffffffffe, 
0x2fffffffffffffff);
HEX(a)
SELECT HEX(a) FROM t2 WHERE a IN 
(0x2ffffffffffffffe, 
0x2fffffffffffffff,
'abc');
HEX(a)
CREATE TABLE t3 (a BIGINT);
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	Incorrect date value: '19772-07-29' for column 'a' at row 1
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:
Error	1365	Division by 0
DROP TABLE t1;
End of 5.0 tests
create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;
insert into t1 values ('a'),('b'),('1');
select f1 from t1 where f1 in ('a',1);
f1
a
1
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	-
create index t1f1_idx on t1(f1);
select f1 from t1 where f1 in ('a',1);
f1
1
a
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	7	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	index	t1f1_idx	t1f1_idx	7	NULL	3	Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
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	7	NULL	3	Using where; Using index
create TEMPORARY table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
insert into t2 values(0),(1),(2);
select f2 from t2 where f2 in ('a',2);
f2
0
2
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'a'
Warning	1292	Truncated incorrect DOUBLE value: 'a'
Warning	1292	Truncated incorrect DOUBLE 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 DOUBLE value: 'a'
Warning	1292	Truncated incorrect DOUBLE 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	index	t2f2	t2f2	5	NULL	3	Using where; Using index
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'a'
Warning	1292	Truncated incorrect DOUBLE value: 'b'
select f2 from t2 where f2 in (1,'b');
f2
0
1
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'b'
Warning	1292	Truncated incorrect DOUBLE 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 datetime, key(a));
insert into t1 values (),(),(),(),(),(),(),(),(),();
select a from t1 where a not in (a,a,a) group by a;
a
drop table t1;
End of 5.1 tests

1 by brian clean slate	1	drop table if exists t1, t2;
	2	select 1 in (1,2,3);
	3	1 in (1,2,3)
	4	1
	5	select 10 in (1,2,3);
	6	10 in (1,2,3)
	7	0
	8	select NULL in (1,2,3);
	9	NULL in (1,2,3)
	10	NULL
	11	select 1 in (1,NULL,3);
	12	1 in (1,NULL,3)
	13	1
	14	select 3 in (1,NULL,3);
	15	3 in (1,NULL,3)
	16	1
	17	select 10 in (1,NULL,3);
	18	10 in (1,NULL,3)
	19	NULL
	20	select 1.5 in (1.5,2.5,3.5);
	21	1.5 in (1.5,2.5,3.5)
	22	1
	23	select 10.5 in (1.5,2.5,3.5);
	24	10.5 in (1.5,2.5,3.5)
	25	0
	26	select NULL in (1.5,2.5,3.5);
	27	NULL in (1.5,2.5,3.5)
	28	NULL
	29	select 1.5 in (1.5,NULL,3.5);
	30	1.5 in (1.5,NULL,3.5)
	31	1
	32	select 3.5 in (1.5,NULL,3.5);
	33	3.5 in (1.5,NULL,3.5)
	34	1
	35	select 10.5 in (1.5,NULL,3.5);
	36	10.5 in (1.5,NULL,3.5)
	37	NULL
	38	CREATE TABLE t1 (a int, b int, c int);
	39	insert into t1 values (1,2,3), (1,NULL,3);
	40	select 1 in (a,b,c) from t1;
	41	1 in (a,b,c)
	42	1
	43	1
	44	select 3 in (a,b,c) from t1;
	45	3 in (a,b,c)
	46	1
	47	1
	48	select 10 in (a,b,c) from t1;
	49	10 in (a,b,c)
	50	0
	51	NULL
	52	select NULL in (a,b,c) from t1;
	53	NULL in (a,b,c)
	54	NULL
	55	NULL
	56	drop table t1;
	57	CREATE TABLE t1 (a float, b float, c float);
	58	insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
	59	select 1.5 in (a,b,c) from t1;
	60	1.5 in (a,b,c)
	61	1
	62	1
	63	select 3.5 in (a,b,c) from t1;
	64	3.5 in (a,b,c)
65	1
66	1
67	select 10.5 in (a,b,c) from t1;
68	10.5 in (a,b,c)
69	0
70	NULL
71	drop table t1;
72	CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
73	insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
74	select 'A' in (a,b,c) from t1;
75	'A' in (a,b,c)
76	1
77	1
78	select 'EFD' in (a,b,c) from t1;
79	'EFD' in (a,b,c)
80	1
81	1
82	select 'XSFGGHF' in (a,b,c) from t1;
83	'XSFGGHF' in (a,b,c)
84	0
85	NULL
86	drop table t1;
87	CREATE TABLE t1 (field char(1));
88	INSERT INTO t1 VALUES ('A'),(NULL);
89	SELECT * from t1 WHERE field IN (NULL);
90	field
91	SELECT * from t1 WHERE field NOT IN (NULL);
92	field
93	SELECT * from t1 where field = field;
94	field
95	A
96	SELECT * from t1 where field <=> field;
97	field
98	A
99	NULL
100	DELETE FROM t1 WHERE field NOT IN (NULL);
101	SELECT * FROM t1;
102	field
103	A
104	NULL
105	drop table t1;
512 by Brian Aker Adding back more test cases.	106	create table t1 (id int primary key);
1 by brian clean slate	107	insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
	108	select * from t1 where id in (2,5,9);
	109	id
	110	2
	111	5
	112	9
	113	drop table t1;
	114	create table t1 (
512 by Brian Aker Adding back more test cases.	115	a char(1),
	116	b char(1),
	117	c char(1)
1 by brian clean slate	118	);
	119	insert into t1 values ('A','B','C');
	120	insert into t1 values ('a','c','c');
	121	select * from t1 where a in (b);
512 by Brian Aker Adding back more test cases.	122	a b c
1 by brian clean slate	123	select * from t1 where a in (b,c);
512 by Brian Aker Adding back more test cases.	124	a b c
1 by brian clean slate	125	select * from t1 where 'a' in (a,b,c);
512 by Brian Aker Adding back more test cases.	126	a b c
	127	A B C
	128	a c c
1 by brian clean slate	129	select * from t1 where 'a' in (a);
	130	a b c
	131	A B C
	132	a c c
	133	select * from t1 where a in ('a');
	134	a b c
	135	A B C
	136	a c c
512 by Brian Aker Adding back more test cases.	137	select * from t1 where 'a' collate utf8_general_ci in (a,b,c);
1 by brian clean slate	138	a b c
	139	A B C
	140	a c c
512 by Brian Aker Adding back more test cases.	141	select * from t1 where 'a' collate utf8_bin in (a,b,c);
	142	a b c
	143	a c c
	144	select * from t1 where 'a' in (a,b,c collate utf8_bin);
	145	a b c
	146	a c c
	147	explain extended select * from t1 where 'a' in (a,b,c collate utf8_bin);
1 by brian clean slate	148	id select_type table type possible_keys key key_len ref rows filtered Extra
	149	1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
	150	Warnings:
512 by Brian Aker Adding back more test cases.	151	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 utf8_bin)))
	152	drop table t1;
	153	create table t1 (a char(10) not null);
1 by brian clean slate	154	insert into t1 values ('a'),('b'),('c');
	155	select a from t1 where a IN ('a','b','c') order by a;
	156	a
	157	a
	158	b
	159	c
	160	drop table t1;
	161	select '1.0' in (1,2);
	162	'1.0' in (1,2)
	163	1
	164	select 1 in ('1.0',2);
	165	1 in ('1.0',2)
	166	1
	167	select 1 in (1,'2.0');
	168	1 in (1,'2.0')
	169	1
	170	select 1 in ('1.0',2.0);
	171	1 in ('1.0',2.0)
	172	1
	173	select 1 in (1.0,'2.0');
	174	1 in (1.0,'2.0')
	175	1
	176	select 1 in ('1.1',2);
	177	1 in ('1.1',2)
	178	0
	179	select 1 in ('1.1',2.0);
	180	1 in ('1.1',2.0)
	181	0
512 by Brian Aker Adding back more test cases.	182	create table t1 (a char(2));
1 by brian clean slate	183	insert into t1 values ('aa'), ('bb');
	184	select * from t1 where a in (NULL, 'aa');
	185	a
	186	aa
	187	drop table t1;
	188	create table t1 (id int, key(id));
	189	insert into t1 values (1),(2),(3);
	190	select count(*) from t1 where id not in (1);
	191	count(*)
	192	2
	193	select count(*) from t1 where id not in (1,2);
	194	count(*)
	195	1
	196	drop table t1;
	197	DROP TABLE IF EXISTS t1;
	198	CREATE TABLE t1 SELECT 1 IN (2, NULL);
	199	SELECT should return NULL.
	200	SELECT * FROM t1;
	201	1 IN (2, NULL)
	202	NULL
	203	DROP TABLE t1;
	204	End of 4.1 tests
	205	CREATE TABLE t1 (a int PRIMARY KEY);
	206	INSERT INTO t1 VALUES (44), (45), (46);
	207	SELECT * FROM t1 WHERE a IN (45);
	208	a
	209	45
	210	SELECT * FROM t1 WHERE a NOT IN (0, 45);
	211	a
	212	44
	213	46
	214	SELECT * FROM t1 WHERE a NOT IN (45);
	215	a
	216	44
	217	46
	218	DROP TABLE t1;
1063.9.3 by Brian Aker Partial fix for tests for tmp	219	create table t1 (a int);
1 by brian clean slate	220	insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1063.9.51 by Stewart Smith fix func_in test to include explain for some MRR queries again.	221	create temporary table t2 (a int, filler char(200), key(a)) engine=myisam;
1 by brian clean slate	222	insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
1 by brian clean slate	223	insert into t2 select C.a*2+1, 'yes' from t1 C;
1063.9.51 by Stewart Smith fix func_in test to include explain for some MRR queries again.	224	explain
	225	select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
	226	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	227	1 SIMPLE t2 range a a 5 NULL 12 Using where
1 by brian clean slate	228	select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
1 by brian clean slate	229	a filler
520.1.2 by Brian Aker Disabling ICP, its buggy.	230	1 yes
	231	3 yes
	232	5 yes
	233	7 yes
	234	9 yes
	235	11 yes
	236	13 yes
	237	15 yes
	238	17 yes
1 by brian clean slate	239	19 yes
1063.9.51 by Stewart Smith fix func_in test to include explain for some MRR queries again.	240	explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
	241	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	242	1 SIMPLE t2 range a a 5 NULL 912 Using where
1063.9.51 by Stewart Smith fix func_in test to include explain for some MRR queries again.	243	explain select * from t2 force index(a) where a <> 2;
	244	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	245	1 SIMPLE t2 range a a 5 NULL 912 Using where
1 by brian clean slate	246	drop table t2;
	247	create table t2 (a datetime, filler char(200), key(a));
	248	insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
	249	'no' from t1 A, t1 B, t1 C where C.a % 2 = 0;
	250	insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
	251	'yes' from t1 C;
	252	explain
	253	select * from t2 where a NOT IN (
	254	'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
	255	'2006-04-25 10:06:00', '2006-04-25 10:08:00');
	256	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	257	1 SIMPLE t2 range a a 9 NULL 20 Using where
1 by brian clean slate	258	select * from t2 where a NOT IN (
	259	'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
	260	'2006-04-25 10:06:00', '2006-04-25 10:08:00');
	261	a filler
	262	2006-04-25 10:01:00 yes
	263	2006-04-25 10:03:00 yes
	264	2006-04-25 10:05:00 yes
	265	2006-04-25 10:07:00 yes
	266	2006-04-25 10:09:00 yes
	267	2006-04-25 10:11:00 yes
	268	2006-04-25 10:13:00 yes
	269	2006-04-25 10:15:00 yes
	270	2006-04-25 10:17:00 yes
	271	2006-04-25 10:19:00 yes
	272	drop table t2;
	273	create table t2 (a varchar(10), filler char(200), key(a));
	274	insert into t2 select 'foo', 'no' from t1 A, t1 B;
	275	insert into t2 select 'barbar', 'no' from t1 A, t1 B;
	276	insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
	277	insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'),
	278	('barbas','1'), ('bazbazbay', '1'),('zz','1');
	279	explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
	280	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	281	1 SIMPLE t2 range a a 43 NULL 5 Using where
1 by brian clean slate	282	drop table t2;
	283	create table t2 (a decimal(10,5), filler char(200), key(a));
	284	insert into t2 select 345.67890, 'no' from t1 A, t1 B;
	285	insert into t2 select 43245.34, 'no' from t1 A, t1 B;
	286	insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
	287	insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'),
	288	(55555,'1'), (77777, '1');
	289	explain
	290	select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
	291	id select_type table type possible_keys key key_len ref rows Extra
1100.1.1 by Brian Aker Disable MRR	292	1 SIMPLE t2 range a a 7 NULL 4 Using where
1 by brian clean slate	293	select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
	294	a filler
	295	0.00000 1
	296	22334.12300 1
	297	33333.00000 1
	298	55555.00000 1
	299	77777.00000 1
	300	drop table t2;
	301	create table t2 (a int, key(a), b int);
	302	insert into t2 values (1,1),(2,2);
	303	set @cnt= 1;
	304	set @str="update t2 set b=1 where a not in (";
	305	select count(*) from (
	306	select @str:=concat(@str, @cnt:=@cnt+1, ",")
	307	from t1 A, t1 B, t1 C, t1 D) Z;
	308	count(*)
	309	10000
	310	set @str:=concat(@str, "10000)");
	311	select substr(@str, 1, 50);
	312	substr(@str, 1, 50)
	313	update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,
	314	set @str=NULL;
	315	drop table t2;
	316	drop table t1;
	317	create table t1 (
512 by Brian Aker Adding back more test cases.	318	some_id int,
1 by brian clean slate	319	key (some_id)
	320	);
	321	insert into t1 values (1),(2);
	322	select some_id from t1 where some_id not in(2,-1);
	323	some_id
	324	1
	325	select some_id from t1 where some_id not in(-4,-1,-4);
	326	some_id
	327	1
	328	2
	329	select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
	330	some_id
	331	1
	332	2
	333	select some_id from t1 where some_id not in('-1', '0');
	334	some_id
	335	1
	336	2
	337	drop table t1;
	338	CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
	339	INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
	340	CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
	341	INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
	342	CREATE TABLE t3 (a int PRIMARY KEY);
	343	INSERT INTO t3 VALUES (1),(2),(3),(4);
	344	CREATE TABLE t4 (a int PRIMARY KEY,b int);
	345	INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
	346	(1003,1003),(1004,1004);
	347	EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
	348	JOIN t1 ON t3.a=t1.a
	349	JOIN t2 ON t3.a=t2.a
	350	JOIN t4 WHERE t4.a IN (t1.b, t2.b);
	351	id select_type table type possible_keys key key_len ref rows Extra
	352	1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
	353	1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
512 by Brian Aker Adding back more test cases.	354	1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
1 by brian clean slate	355	1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
	356	SELECT STRAIGHT_JOIN * FROM t3
	357	JOIN t1 ON t3.a=t1.a
	358	JOIN t2 ON t3.a=t2.a
	359	JOIN t4 WHERE t4.a IN (t1.b, t2.b);
	360	a a b a b a b
	361	3 3 1 3 2 1 1
	362	3 3 1 3 2 2 2
	363	4 4 1 4 2 1 1
	364	4 4 1 4 2 2 2
	365	EXPLAIN SELECT STRAIGHT_JOIN
	366	(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
	367	FROM t3, t1, t2
	368	WHERE t3.a=t1.a AND t3.a=t2.a;
	369	id select_type table type possible_keys key key_len ref rows Extra
	370	1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
	371	1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
512 by Brian Aker Adding back more test cases.	372	1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
1 by brian clean slate	373	2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
	374	SELECT STRAIGHT_JOIN
	375	(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
	376	FROM t3, t1, t2
	377	WHERE t3.a=t1.a AND t3.a=t2.a;
	378	(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
	379	3
	380	3
	381	DROP TABLE t1,t2,t3,t4;
512 by Brian Aker Adding back more test cases.	382	CREATE TABLE t1(a BIGINT);
512 by Brian Aker Adding back more test cases.	383	INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
1 by brian clean slate	384	SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
	385	a
	386	SELECT * FROM t1 WHERE a IN (-1, -2);
	387	a
512 by Brian Aker Adding back more test cases.	388	CREATE TABLE t2 (a BIGINT);
1 by brian clean slate	389	insert into t2 values(13491727406643098568),
512 by Brian Aker Adding back more test cases.	390	(0x0fffffefffffffff),
	391	(0x0ffffffeffffffff),
	392	(0x0fffffffefffffff),
	393	(0x0ffffffffeffffff),
	394	(0x0fffffffffefffff),
	395	(0x0ffffffffffeffff),
	396	(0x0fffffffffffefff),
	397	(0x0ffffffffffffeff),
	398	(0x0fffffffffffffef),
	399	(0x0ffffffffffffffe),
	400	(0x0fffffffffffffff),
	401	(0x2000000000000000),
	402	(0x2000000000000001),
	403	(0x2000000000000002),
	404	(0x2000000000000300),
	405	(0x2000000000000400),
	406	(0x2000000000000401),
	407	(0x2000000000004001),
	408	(0x2000000000040001),
	409	(0x2000000000400001),
	410	(0x2000000004000001),
	411	(0x2000000040000001),
	412	(0x2000000400000001),
	413	(0x2000004000000001),
	414	(0x2000040000000001);
	415	SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
1 by brian clean slate	416	HEX(a)
1 by brian clean slate	417	SELECT HEX(a) FROM t2 WHERE a IN
512 by Brian Aker Adding back more test cases.	418	(0xBB3C3E98175D33C8,
	419	0x2fffffffffffffff,
	420	0x2000000000000000,
	421	0x2000000000000400,
	422	0x2000000000000401,
1 by brian clean slate	423	42);
1 by brian clean slate	424	HEX(a)
512 by Brian Aker Adding back more test cases.	425	2000000000000000
	426	2000000000000001
	427	2000000000000002
	428	2000000000000300
	429	2000000000000400
	430	2000000000000401
	431	SELECT HEX(a) FROM t2 WHERE a IN
	432	(0x7fffffffffffffff,
	433	0x2000000000000001);
	434	HEX(a)
	435	2000000000000001
	436	SELECT HEX(a) FROM t2 WHERE a IN
	437	(0x2ffffffffffffffe,
	438	0x2fffffffffffffff);
	439	HEX(a)
	440	SELECT HEX(a) FROM t2 WHERE a IN
	441	(0x2ffffffffffffffe,
	442	0x2fffffffffffffff,
1 by brian clean slate	443	'abc');
1 by brian clean slate	444	HEX(a)
512 by Brian Aker Adding back more test cases.	445	CREATE TABLE t3 (a BIGINT);
1 by brian clean slate	446	INSERT INTO t3 VALUES (9223372036854775551);
	447	SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
	448	HEX(a)
	449	CREATE TABLE t4 (a DATE);
	450	INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
	451	SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
	452	a
	453	1972-02-06
	454	Warnings:
	455	Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1
	456	DROP TABLE t1,t2,t3,t4;
	457	CREATE TABLE t1 (id int not null);
	458	INSERT INTO t1 VALUES (1),(2);
	459	SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
	460	id
512 by Brian Aker Adding back more test cases.	461	Warnings:
512 by Brian Aker Adding back more test cases.	462	Error 1365 Division by 0
1 by brian clean slate	463	DROP TABLE t1;
1 by brian clean slate	464	End of 5.0 tests
1063.9.3 by Brian Aker Partial fix for tests for tmp	465	create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;
1 by brian clean slate	466	insert into t1 values ('a'),('b'),('1');
	467	select f1 from t1 where f1 in ('a',1);
	468	f1
	469	a
	470	1
	471	select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
	472	f1 case f1 when 'a' then '+' when 1 then '-' end
	473	a +
	474	b NULL
	475	1 -
	476	create index t1f1_idx on t1(f1);
	477	select f1 from t1 where f1 in ('a',1);
	478	f1
	479	1
	480	a
	481	explain select f1 from t1 where f1 in ('a',1);
	482	id select_type table type possible_keys key key_len ref rows Extra
512 by Brian Aker Adding back more test cases.	483	1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
1 by brian clean slate	484	select f1 from t1 where f1 in ('a','b');
	485	f1
	486	a
	487	b
	488	explain select f1 from t1 where f1 in ('a','b');
	489	id select_type table type possible_keys key key_len ref rows Extra
512 by Brian Aker Adding back more test cases.	490	1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
1 by brian clean slate	491	select f1 from t1 where f1 in (2,1);
	492	f1
	493	1
	494	explain select f1 from t1 where f1 in (2,1);
	495	id select_type table type possible_keys key key_len ref rows Extra
512 by Brian Aker Adding back more test cases.	496	1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
1063.9.3 by Brian Aker Partial fix for tests for tmp	497	create TEMPORARY table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
1 by brian clean slate	498	insert into t2 values(0),(1),(2);
	499	select f2 from t2 where f2 in ('a',2);
	500	f2
	501	0
	502	2
	503	Warnings:
	504	Warning 1292 Truncated incorrect DOUBLE value: 'a'
	505	Warning 1292 Truncated incorrect DOUBLE value: 'a'
	506	Warning 1292 Truncated incorrect DOUBLE value: 'a'
	507	explain select f2 from t2 where f2 in ('a',2);
	508	id select_type table type possible_keys key key_len ref rows Extra
	509	1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
	510	select f2 from t2 where f2 in ('a','b');
	511	f2
	512	0
	513	Warnings:
	514	Warning 1292 Truncated incorrect DOUBLE value: 'a'
	515	Warning 1292 Truncated incorrect DOUBLE value: 'b'
	516	explain select f2 from t2 where f2 in ('a','b');
	517	id select_type table type possible_keys key key_len ref rows Extra
	518	1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
	519	Warnings:
	520	Warning 1292 Truncated incorrect DOUBLE value: 'a'
	521	Warning 1292 Truncated incorrect DOUBLE value: 'b'
	522	select f2 from t2 where f2 in (1,'b');
	523	f2
	524	0
	525	1
	526	Warnings:
	527	Warning 1292 Truncated incorrect DOUBLE value: 'b'
	528	Warning 1292 Truncated incorrect DOUBLE value: 'b'
	529	explain select f2 from t2 where f2 in (1,'b');
	530	id select_type table type possible_keys key key_len ref rows Extra
	531	1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
	532	drop table t1, t2;
896.5.1 by Jay Pipes Removes the TIME column type and related time functions.	533	create table t1 (a datetime, key(a));
1 by brian clean slate	534	insert into t1 values (),(),(),(),(),(),(),(),(),();
	535	select a from t1 where a not in (a,a,a) group by a;
	536	a
	537	drop table t1;
	538	End of 5.1 tests