1
drop table if exists t1, t2;
8
select NULL in (1,2,3);
11
select 1 in (1,NULL,3);
14
select 3 in (1,NULL,3);
17
select 10 in (1,NULL,3);
20
select 1.5 in (1.5,2.5,3.5);
23
select 10.5 in (1.5,2.5,3.5);
26
select NULL in (1.5,2.5,3.5);
29
select 1.5 in (1.5,NULL,3.5);
32
select 3.5 in (1.5,NULL,3.5);
35
select 10.5 in (1.5,NULL,3.5);
36
10.5 in (1.5,NULL,3.5)
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;
44
select 3 in (a,b,c) from t1;
48
select 10 in (a,b,c) from t1;
52
select NULL in (a,b,c) from 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;
63
select 3.5 in (a,b,c) from t1;
67
select 10.5 in (a,b,c) from 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;
78
select 'EFD' in (a,b,c) from t1;
82
select 'XSFGGHF' in (a,b,c) from t1;
87
CREATE TABLE t1 (field char(1));
88
INSERT INTO t1 VALUES ('A'),(NULL);
89
SELECT * from t1 WHERE field IN (NULL);
91
SELECT * from t1 WHERE field NOT IN (NULL);
93
SELECT * from t1 where field = field;
96
SELECT * from t1 where field <=> field;
100
DELETE FROM t1 WHERE field NOT IN (NULL);
106
create table t1 (id int primary key);
107
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
108
select * from t1 where id in (2,5,9);
119
insert into t1 values ('A','B','C');
120
insert into t1 values ('a','c','c');
121
select * from t1 where a in (b);
123
select * from t1 where a in (b,c);
125
select * from t1 where 'a' in (a,b,c);
129
select * from t1 where 'a' in (a);
133
select * from t1 where a in ('a');
137
select * from t1 where 'a' collate utf8_general_ci in (a,b,c);
141
select * from t1 where 'a' collate utf8_bin in (a,b,c);
144
select * from t1 where 'a' in (a,b,c collate utf8_bin);
147
explain extended select * from t1 where 'a' in (a,b,c collate utf8_bin);
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
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)))
153
create table t1 (a char(10) not null);
154
insert into t1 values ('a'),('b'),('c');
155
select a from t1 where a IN ('a','b','c') order by a;
161
select '1.0' in (1,2);
164
select 1 in ('1.0',2);
167
select 1 in (1,'2.0');
170
select 1 in ('1.0',2.0);
173
select 1 in (1.0,'2.0');
176
select 1 in ('1.1',2);
179
select 1 in ('1.1',2.0);
182
create table t1 (a char(2));
183
insert into t1 values ('aa'), ('bb');
184
select * from t1 where a in (NULL, 'aa');
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);
193
select count(*) from t1 where id not in (1,2);
197
DROP TABLE IF EXISTS t1;
198
CREATE TABLE t1 SELECT 1 IN (2, NULL);
199
SELECT should return NULL.
205
CREATE TABLE t1 (a int PRIMARY KEY);
206
INSERT INTO t1 VALUES (44), (45), (46);
207
SELECT * FROM t1 WHERE a IN (45);
210
SELECT * FROM t1 WHERE a NOT IN (0, 45);
214
SELECT * FROM t1 WHERE a NOT IN (45);
219
create table t1 (a int) ENGINE=MYISAM;
220
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
221
create table t2 (a int, filler char(200), key(a)) ENGINE=MYISAM;
222
insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
223
insert into t2 select C.a*2+1, 'yes' from t1 C;
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
227
1 SIMPLE t2 range a a 5 NULL 12 Using index condition; Using MRR
228
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
5795
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
5796
id select_type table type possible_keys key key_len ref rows Extra
5797
1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR
5798
explain select * from t2 force index(a) where a <> 2;
5799
id select_type table type possible_keys key key_len ref rows Extra
5800
1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR
5802
create table t2 (a datetime, filler char(200), key(a));
5803
insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
5804
'no' from t1 A, t1 B, t1 C where C.a % 2 = 0;
5805
insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
5808
select * from t2 where a NOT IN (
5809
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
5810
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
5811
id select_type table type possible_keys key key_len ref rows Extra
5812
1 SIMPLE t2 range a a 9 NULL 20 Using index condition; Using MRR
5813
select * from t2 where a NOT IN (
5814
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
5815
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
5817
2006-04-25 10:01:00 yes
5818
2006-04-25 10:03:00 yes
5819
2006-04-25 10:05:00 yes
5820
2006-04-25 10:07:00 yes
5821
2006-04-25 10:09:00 yes
5822
2006-04-25 10:11:00 yes
5823
2006-04-25 10:13:00 yes
5824
2006-04-25 10:15:00 yes
5825
2006-04-25 10:17:00 yes
5826
2006-04-25 10:19:00 yes
5828
create table t2 (a varchar(10), filler char(200), key(a));
5829
insert into t2 select 'foo', 'no' from t1 A, t1 B;
5830
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
5831
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
5832
insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'),
5833
('barbas','1'), ('bazbazbay', '1'),('zz','1');
5834
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
5835
id select_type table type possible_keys key key_len ref rows Extra
5836
1 SIMPLE t2 range a a 43 NULL 5 Using index condition; Using MRR
5838
create table t2 (a decimal(10,5), filler char(200), key(a));
5839
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
5840
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
5841
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
5842
insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'),
5843
(55555,'1'), (77777, '1');
5845
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
5846
id select_type table type possible_keys key key_len ref rows Extra
5847
1 SIMPLE t2 range a a 7 NULL 4 Using index condition; Using MRR
5848
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
5856
create table t2 (a int, key(a), b int);
5857
insert into t2 values (1,1),(2,2);
5859
set @str="update t2 set b=1 where a not in (";
5860
select count(*) from (
5861
select @str:=concat(@str, @cnt:=@cnt+1, ",")
5862
from t1 A, t1 B, t1 C, t1 D) Z;
5865
set @str:=concat(@str, "10000)");
5866
select substr(@str, 1, 50);
5868
update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,
5876
insert into t1 values (1),(2);
5877
select some_id from t1 where some_id not in(2,-1);
5880
select some_id from t1 where some_id not in(-4,-1,-4);
5884
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
5888
select some_id from t1 where some_id not in('-1', '0');
5893
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
5894
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
5895
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
5896
INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
5897
CREATE TABLE t3 (a int PRIMARY KEY);
5898
INSERT INTO t3 VALUES (1),(2),(3),(4);
5899
CREATE TABLE t4 (a int PRIMARY KEY,b int);
5900
INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
5901
(1003,1003),(1004,1004);
5902
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
5903
JOIN t1 ON t3.a=t1.a
5904
JOIN t2 ON t3.a=t2.a
5905
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
5906
id select_type table type possible_keys key key_len ref rows Extra
5907
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
5908
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
5909
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
5910
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
5911
SELECT STRAIGHT_JOIN * FROM t3
5912
JOIN t1 ON t3.a=t1.a
5913
JOIN t2 ON t3.a=t2.a
5914
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
5920
EXPLAIN SELECT STRAIGHT_JOIN
5921
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
5923
WHERE t3.a=t1.a AND t3.a=t2.a;
5924
id select_type table type possible_keys key key_len ref rows Extra
5925
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
5926
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
5927
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
5928
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
5929
SELECT STRAIGHT_JOIN
5930
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
5932
WHERE t3.a=t1.a AND t3.a=t2.a;
5933
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
5936
DROP TABLE t1,t2,t3,t4;
5937
CREATE TABLE t1(a BIGINT);
5938
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
5939
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
5941
SELECT * FROM t1 WHERE a IN (-1, -2);
5943
CREATE TABLE t2 (a BIGINT);
5944
insert into t2 values(13491727406643098568),
5945
(0x0fffffefffffffff),
5946
(0x0ffffffeffffffff),
5947
(0x0fffffffefffffff),
5948
(0x0ffffffffeffffff),
5949
(0x0fffffffffefffff),
5950
(0x0ffffffffffeffff),
5951
(0x0fffffffffffefff),
5952
(0x0ffffffffffffeff),
5953
(0x0fffffffffffffef),
5954
(0x0ffffffffffffffe),
5955
(0x0fffffffffffffff),
5956
(0x2000000000000000),
5957
(0x2000000000000001),
5958
(0x2000000000000002),
5959
(0x2000000000000300),
5960
(0x2000000000000400),
5961
(0x2000000000000401),
5962
(0x2000000000004001),
5963
(0x2000000000040001),
5964
(0x2000000000400001),
5965
(0x2000000004000001),
5966
(0x2000000040000001),
5967
(0x2000000400000001),
5968
(0x2000004000000001),
5969
(0x2000040000000001);
5970
SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
5972
SELECT HEX(a) FROM t2 WHERE a IN
5973
(0xBB3C3E98175D33C8,
5986
SELECT HEX(a) FROM t2 WHERE a IN
5987
(0x7fffffffffffffff,
5988
0x2000000000000001);
5991
SELECT HEX(a) FROM t2 WHERE a IN
5992
(0x2ffffffffffffffe,
5993
0x2fffffffffffffff);
5995
SELECT HEX(a) FROM t2 WHERE a IN
5996
(0x2ffffffffffffffe,
6000
CREATE TABLE t3 (a BIGINT);
6001
INSERT INTO t3 VALUES (9223372036854775551);
6002
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
6004
CREATE TABLE t4 (a DATE);
6005
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
6006
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
6010
Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1
6011
DROP TABLE t1,t2,t3,t4;
6012
CREATE TABLE t1 (id int not null);
6013
INSERT INTO t1 VALUES (1),(2);
6014
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
6017
Error 1365 Division by 0
6018
Error 1365 Division by 0
6021
create table t1(f1 char(1)) ENGINE=MYISAM;
6022
insert into t1 values ('a'),('b'),('1');
6023
select f1 from t1 where f1 in ('a',1);
6027
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
6028
f1 case f1 when 'a' then '+' when 1 then '-' end
6032
create index t1f1_idx on t1(f1);
6033
select f1 from t1 where f1 in ('a',1);
6037
explain select f1 from t1 where f1 in ('a',1);
6038
id select_type table type possible_keys key key_len ref rows Extra
6039
1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
6040
select f1 from t1 where f1 in ('a','b');
6044
explain select f1 from t1 where f1 in ('a','b');
6045
id select_type table type possible_keys key key_len ref rows Extra
6046
1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
6047
select f1 from t1 where f1 in (2,1);
6050
explain select f1 from t1 where f1 in (2,1);
6051
id select_type table type possible_keys key key_len ref rows Extra
6052
1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
6053
create table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
6054
insert into t2 values(0),(1),(2);
6055
select f2 from t2 where f2 in ('a',2);
6060
Warning 1292 Truncated incorrect DOUBLE value: 'a'
6061
Warning 1292 Truncated incorrect DOUBLE value: 'a'
6062
Warning 1292 Truncated incorrect DOUBLE value: 'a'
6063
explain select f2 from t2 where f2 in ('a',2);
6064
id select_type table type possible_keys key key_len ref rows Extra
6065
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
6066
select f2 from t2 where f2 in ('a','b');
6070
Warning 1292 Truncated incorrect DOUBLE value: 'a'
6071
Warning 1292 Truncated incorrect DOUBLE value: 'b'
6072
explain select f2 from t2 where f2 in ('a','b');
6073
id select_type table type possible_keys key key_len ref rows Extra
6074
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
6076
Warning 1292 Truncated incorrect DOUBLE value: 'a'
6077
Warning 1292 Truncated incorrect DOUBLE value: 'b'
6078
select f2 from t2 where f2 in (1,'b');
6083
Warning 1292 Truncated incorrect DOUBLE value: 'b'
6084
Warning 1292 Truncated incorrect DOUBLE value: 'b'
6085
explain select f2 from t2 where f2 in (1,'b');
6086
id select_type table type possible_keys key key_len ref rows Extra
6087
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
6089
create table t1 (a time, key(a));
6090
insert into t1 values (),(),(),(),(),(),(),(),(),();
6091
select a from t1 where a not in (a,a,a) group by a;
added
removed
tests/r/func_in.result
