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(10) 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);
115
a char(1) character set latin1 collate latin1_general_ci,
116
b char(1) character set latin1 collate latin1_swedish_ci,
117
c char(1) character set latin1 collate latin1_danish_ci
119
insert into t1 values ('A','B','C');
120
insert into t1 values ('a','c','c');
121
select * from t1 where a in (b);
122
ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
123
select * from t1 where a in (b,c);
124
ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT), (latin1_swedish_ci,IMPLICIT), (latin1_danish_ci,IMPLICIT) for operation ' IN '
125
select * from t1 where 'a' in (a,b,c);
126
ERROR HY000: Illegal mix of collations for operation ' IN '
127
select * from t1 where 'a' in (a);
131
select * from t1 where a in ('a');
135
select * from t1 where 'a' collate latin1_general_ci in (a,b,c);
139
select * from t1 where 'a' collate latin1_bin in (a,b,c);
142
select * from t1 where 'a' in (a,b,c collate latin1_bin);
145
explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin);
146
id select_type table type possible_keys key key_len ref rows filtered Extra
147
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
149
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)))
152
create table t1 (a char(10) character set utf8 not null);
153
insert into t1 values ('bbbb'),(_koi8r'����'),(_latin1'����');
154
select a from t1 where a in ('bbbb',_koi8r'����',_latin1'����') order by a;
160
create table t1 (a char(10) character set latin1 not null);
161
insert into t1 values ('a'),('b'),('c');
162
select a from t1 where a IN ('a','b','c') order by a;
169
select '1.0' in (1,2);
172
select 1 in ('1.0',2);
175
select 1 in (1,'2.0');
178
select 1 in ('1.0',2.0);
181
select 1 in (1.0,'2.0');
184
select 1 in ('1.1',2);
187
select 1 in ('1.1',2.0);
190
create table t1 (a char(2) character set binary);
191
insert into t1 values ('aa'), ('bb');
192
select * from t1 where a in (NULL, 'aa');
196
create table t1 (id int, key(id));
197
insert into t1 values (1),(2),(3);
198
select count(*) from t1 where id not in (1);
201
select count(*) from t1 where id not in (1,2);
205
DROP TABLE IF EXISTS t1;
206
CREATE TABLE t1 SELECT 1 IN (2, NULL);
207
SELECT should return NULL.
213
CREATE TABLE t1 (a int PRIMARY KEY);
214
INSERT INTO t1 VALUES (44), (45), (46);
215
SELECT * FROM t1 WHERE a IN (45);
218
SELECT * FROM t1 WHERE a NOT IN (0, 45);
222
SELECT * FROM t1 WHERE a NOT IN (45);
227
create table t1 (a int);
228
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
229
create table t2 (a int, filler char(200), key(a));
230
insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
231
insert into t2 select C.a*2+1, 'yes' from t1 C;
233
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
234
id select_type table type possible_keys key key_len ref rows Extra
235
1 SIMPLE t2 range a a 5 NULL 12 Using index condition; Using MRR
236
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
248
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
249
id select_type table type possible_keys key key_len ref rows Extra
250
1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR
251
explain select * from t2 force index(a) where a <> 2;
252
id select_type table type possible_keys key key_len ref rows Extra
253
1 SIMPLE t2 range a a 5 NULL 912 Using index condition; Using MRR
255
create table t2 (a datetime, filler char(200), key(a));
256
insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
257
'no' from t1 A, t1 B, t1 C where C.a % 2 = 0;
258
insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
261
select * from t2 where a NOT IN (
262
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
263
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
264
id select_type table type possible_keys key key_len ref rows Extra
265
1 SIMPLE t2 range a a 9 NULL 18 Using index condition; Using MRR
266
select * from t2 where a NOT IN (
267
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
268
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
270
2006-04-25 10:01:00 yes
271
2006-04-25 10:03:00 yes
272
2006-04-25 10:05:00 yes
273
2006-04-25 10:07:00 yes
274
2006-04-25 10:09:00 yes
275
2006-04-25 10:11:00 yes
276
2006-04-25 10:13:00 yes
277
2006-04-25 10:15:00 yes
278
2006-04-25 10:17:00 yes
279
2006-04-25 10:19:00 yes
281
create table t2 (a varchar(10), filler char(200), key(a));
282
insert into t2 select 'foo', 'no' from t1 A, t1 B;
283
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
284
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
285
insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'),
286
('barbas','1'), ('bazbazbay', '1'),('zz','1');
287
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
288
id select_type table type possible_keys key key_len ref rows Extra
289
1 SIMPLE t2 range a a 13 NULL 7 Using index condition; Using MRR
291
create table t2 (a decimal(10,5), filler char(200), key(a));
292
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
293
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
294
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
295
insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'),
296
(55555,'1'), (77777, '1');
298
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
299
id select_type table type possible_keys key key_len ref rows Extra
300
1 SIMPLE t2 range a a 7 NULL 7 Using index condition; Using MRR
301
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
309
create table t2 (a int, key(a), b int);
310
insert into t2 values (1,1),(2,2);
312
set @str="update t2 set b=1 where a not in (";
313
select count(*) from (
314
select @str:=concat(@str, @cnt:=@cnt+1, ",")
315
from t1 A, t1 B, t1 C, t1 D) Z;
318
set @str:=concat(@str, "10000)");
319
select substr(@str, 1, 50);
321
update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,
326
some_id smallint(5) unsigned,
329
insert into t1 values (1),(2);
330
select some_id from t1 where some_id not in(2,-1);
333
select some_id from t1 where some_id not in(-4,-1,-4);
337
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
341
select some_id from t1 where some_id not in('-1', '0');
346
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
347
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
348
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
349
INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
350
CREATE TABLE t3 (a int PRIMARY KEY);
351
INSERT INTO t3 VALUES (1),(2),(3),(4);
352
CREATE TABLE t4 (a int PRIMARY KEY,b int);
353
INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
354
(1003,1003),(1004,1004);
355
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
358
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
359
id select_type table type possible_keys key key_len ref rows Extra
360
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
361
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
362
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
363
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
364
SELECT STRAIGHT_JOIN * FROM t3
367
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
373
EXPLAIN SELECT STRAIGHT_JOIN
374
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
376
WHERE t3.a=t1.a AND t3.a=t2.a;
377
id select_type table type possible_keys key key_len ref rows Extra
378
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
379
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
380
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
381
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
383
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
385
WHERE t3.a=t1.a AND t3.a=t2.a;
386
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
389
DROP TABLE t1,t2,t3,t4;
390
CREATE TABLE t1(a BIGINT UNSIGNED);
391
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
392
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
394
SELECT * FROM t1 WHERE a IN (-1, -2);
396
CREATE TABLE t2 (a BIGINT UNSIGNED);
397
insert into t2 values(13491727406643098568),
398
(0x7fffffefffffffff),
399
(0x7ffffffeffffffff),
400
(0x7fffffffefffffff),
401
(0x7ffffffffeffffff),
402
(0x7fffffffffefffff),
403
(0x7ffffffffffeffff),
404
(0x7fffffffffffefff),
405
(0x7ffffffffffffeff),
406
(0x7fffffffffffffef),
407
(0x7ffffffffffffffe),
408
(0x7fffffffffffffff),
409
(0x8000000000000000),
410
(0x8000000000000001),
411
(0x8000000000000002),
412
(0x8000000000000300),
413
(0x8000000000000400),
414
(0x8000000000000401),
415
(0x8000000000004001),
416
(0x8000000000040001),
417
(0x8000000000400001),
418
(0x8000000004000001),
419
(0x8000000040000001),
420
(0x8000000400000001),
421
(0x8000004000000001),
422
(0x8000040000000001);
423
SELECT HEX(a) FROM t2 WHERE a IN
424
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
428
SELECT HEX(a) FROM t2 WHERE a IN
429
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
430
CAST(0x7fffffffffffffff AS UNSIGNED),
431
CAST(0x8000000000000000 AS UNSIGNED),
432
CAST(0x8000000000000400 AS UNSIGNED),
433
CAST(0x8000000000000401 AS UNSIGNED),
441
SELECT HEX(a) FROM t2 WHERE a IN
442
(CAST(0x7fffffffffffffff AS UNSIGNED),
443
CAST(0x8000000000000001 AS UNSIGNED));
447
SELECT HEX(a) FROM t2 WHERE a IN
448
(CAST(0x7ffffffffffffffe AS UNSIGNED),
449
CAST(0x7fffffffffffffff AS UNSIGNED));
453
SELECT HEX(a) FROM t2 WHERE a IN
460
CREATE TABLE t3 (a BIGINT UNSIGNED);
461
INSERT INTO t3 VALUES (9223372036854775551);
462
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
464
CREATE TABLE t4 (a DATE);
465
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
466
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
470
Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1
471
DROP TABLE t1,t2,t3,t4;
472
CREATE TABLE t1 (id int not null);
473
INSERT INTO t1 VALUES (1),(2);
474
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
478
create table t1(f1 char(1));
479
insert into t1 values ('a'),('b'),('1');
480
select f1 from t1 where f1 in ('a',1);
485
Warning 1292 Truncated incorrect DOUBLE value: 'b'
486
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
487
f1 case f1 when 'a' then '+' when 1 then '-' end
492
Warning 1292 Truncated incorrect DOUBLE value: 'b'
493
create index t1f1_idx on t1(f1);
494
select f1 from t1 where f1 in ('a',1);
499
Warning 1292 Truncated incorrect DOUBLE value: 'b'
500
explain select f1 from t1 where f1 in ('a',1);
501
id select_type table type possible_keys key key_len ref rows Extra
502
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
503
select f1 from t1 where f1 in ('a','b');
507
explain select f1 from t1 where f1 in ('a','b');
508
id select_type table type possible_keys key key_len ref rows Extra
509
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
510
select f1 from t1 where f1 in (2,1);
514
Warning 1292 Truncated incorrect DOUBLE value: 'a'
515
Warning 1292 Truncated incorrect DOUBLE value: 'b'
516
explain select f1 from t1 where f1 in (2,1);
517
id select_type table type possible_keys key key_len ref rows Extra
518
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
519
create table t2(f2 int, index t2f2(f2));
520
insert into t2 values(0),(1),(2);
521
select f2 from t2 where f2 in ('a',2);
526
Warning 1292 Truncated incorrect DOUBLE value: 'a'
527
Warning 1292 Truncated incorrect DOUBLE value: 'a'
528
Warning 1292 Truncated incorrect DOUBLE value: 'a'
529
explain select f2 from t2 where f2 in ('a',2);
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
select f2 from t2 where f2 in ('a','b');
536
Warning 1292 Truncated incorrect DOUBLE value: 'a'
537
Warning 1292 Truncated incorrect DOUBLE value: 'b'
538
explain select f2 from t2 where f2 in ('a','b');
539
id select_type table type possible_keys key key_len ref rows Extra
540
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
542
Warning 1292 Truncated incorrect DOUBLE value: 'a'
543
Warning 1292 Truncated incorrect DOUBLE value: 'b'
544
select f2 from t2 where f2 in (1,'b');
549
Warning 1292 Truncated incorrect DOUBLE value: 'b'
550
Warning 1292 Truncated incorrect DOUBLE value: 'b'
551
explain select f2 from t2 where f2 in (1,'b');
552
id select_type table type possible_keys key key_len ref rows Extra
553
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
555
create table t1 (a time, key(a));
556
insert into t1 values (),(),(),(),(),(),(),(),(),();
557
select a from t1 where a not in (a,a,a) group by a;
added
removed
mysql-test/r/func_in.result
