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);
226
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45);
228
View Create View character_set_client collation_connection
229
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
236
create table t1 (a int);
237
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
238
create table t2 (a int, filler char(200), key(a));
239
insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
240
insert into t2 select C.a*2+1, 'yes' from t1 C;
242
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t2 range a a 5 NULL 12 Using where
245
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
257
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
258
id select_type table type possible_keys key key_len ref rows Extra
259
1 SIMPLE t2 range a a 5 NULL 912 Using where
260
explain select * from t2 force index(a) where a <> 2;
261
id select_type table type possible_keys key key_len ref rows Extra
262
1 SIMPLE t2 range a a 5 NULL 912 Using where
264
create table t2 (a datetime, filler char(200), key(a));
265
insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
266
'no' from t1 A, t1 B, t1 C where C.a % 2 = 0;
267
insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
270
select * from t2 where a NOT IN (
271
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
272
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
273
id select_type table type possible_keys key key_len ref rows Extra
274
1 SIMPLE t2 range a a 9 NULL 18 Using where
275
select * from t2 where a NOT IN (
276
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
277
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
279
2006-04-25 10:01:00 yes
280
2006-04-25 10:03:00 yes
281
2006-04-25 10:05:00 yes
282
2006-04-25 10:07:00 yes
283
2006-04-25 10:09:00 yes
284
2006-04-25 10:11:00 yes
285
2006-04-25 10:13:00 yes
286
2006-04-25 10:15:00 yes
287
2006-04-25 10:17:00 yes
288
2006-04-25 10:19:00 yes
290
create table t2 (a varchar(10), filler char(200), key(a));
291
insert into t2 select 'foo', 'no' from t1 A, t1 B;
292
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
293
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
294
insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'),
295
('barbas','1'), ('bazbazbay', '1'),('zz','1');
296
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
297
id select_type table type possible_keys key key_len ref rows Extra
298
1 SIMPLE t2 range a a 13 NULL 7 Using where
300
create table t2 (a decimal(10,5), filler char(200), key(a));
301
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
302
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
303
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
304
insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'),
305
(55555,'1'), (77777, '1');
307
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
308
id select_type table type possible_keys key key_len ref rows Extra
309
1 SIMPLE t2 range a a 7 NULL 7 Using where
310
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
318
create table t2 (a int, key(a), b int);
319
insert into t2 values (1,1),(2,2);
321
set @str="update t2 set b=1 where a not in (";
322
select count(*) from (
323
select @str:=concat(@str, @cnt:=@cnt+1, ",")
324
from t1 A, t1 B, t1 C, t1 D) Z;
327
set @str:=concat(@str, "10000)");
328
select substr(@str, 1, 50);
330
update t2 set b=1 where a not in (2,3,4,5,6,7,8,9,
333
deallocate prepare s;
338
some_id smallint(5) unsigned,
341
insert into t1 values (1),(2);
342
select some_id from t1 where some_id not in(2,-1);
345
select some_id from t1 where some_id not in(-4,-1,-4);
349
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
353
select some_id from t1 where some_id not in('-1', '0');
358
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
359
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
360
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
361
INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
362
CREATE TABLE t3 (a int PRIMARY KEY);
363
INSERT INTO t3 VALUES (1),(2),(3),(4);
364
CREATE TABLE t4 (a int PRIMARY KEY,b int);
365
INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
366
(1003,1003),(1004,1004);
367
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
370
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
371
id select_type table type possible_keys key key_len ref rows Extra
372
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
373
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
374
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
375
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
376
SELECT STRAIGHT_JOIN * FROM t3
379
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
385
EXPLAIN SELECT STRAIGHT_JOIN
386
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
388
WHERE t3.a=t1.a AND t3.a=t2.a;
389
id select_type table type possible_keys key key_len ref rows Extra
390
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
391
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
392
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
393
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
395
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
397
WHERE t3.a=t1.a AND t3.a=t2.a;
398
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
401
DROP TABLE t1,t2,t3,t4;
402
CREATE TABLE t1(a BIGINT UNSIGNED);
403
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
404
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
406
SELECT * FROM t1 WHERE a IN (-1, -2);
408
CREATE TABLE t2 (a BIGINT UNSIGNED);
409
insert into t2 values(13491727406643098568),
410
(0x7fffffefffffffff),
411
(0x7ffffffeffffffff),
412
(0x7fffffffefffffff),
413
(0x7ffffffffeffffff),
414
(0x7fffffffffefffff),
415
(0x7ffffffffffeffff),
416
(0x7fffffffffffefff),
417
(0x7ffffffffffffeff),
418
(0x7fffffffffffffef),
419
(0x7ffffffffffffffe),
420
(0x7fffffffffffffff),
421
(0x8000000000000000),
422
(0x8000000000000001),
423
(0x8000000000000002),
424
(0x8000000000000300),
425
(0x8000000000000400),
426
(0x8000000000000401),
427
(0x8000000000004001),
428
(0x8000000000040001),
429
(0x8000000000400001),
430
(0x8000000004000001),
431
(0x8000000040000001),
432
(0x8000000400000001),
433
(0x8000004000000001),
434
(0x8000040000000001);
435
SELECT HEX(a) FROM t2 WHERE a IN
436
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
440
SELECT HEX(a) FROM t2 WHERE a IN
441
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
442
CAST(0x7fffffffffffffff AS UNSIGNED),
443
CAST(0x8000000000000000 AS UNSIGNED),
444
CAST(0x8000000000000400 AS UNSIGNED),
445
CAST(0x8000000000000401 AS UNSIGNED),
453
SELECT HEX(a) FROM t2 WHERE a IN
454
(CAST(0x7fffffffffffffff AS UNSIGNED),
455
CAST(0x8000000000000001 AS UNSIGNED));
459
SELECT HEX(a) FROM t2 WHERE a IN
460
(CAST(0x7ffffffffffffffe AS UNSIGNED),
461
CAST(0x7fffffffffffffff AS UNSIGNED));
465
SELECT HEX(a) FROM t2 WHERE a IN
472
CREATE TABLE t3 (a BIGINT UNSIGNED);
473
INSERT INTO t3 VALUES (9223372036854775551);
474
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
476
CREATE TABLE t4 (a DATE);
477
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
478
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
482
Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1
483
DROP TABLE t1,t2,t3,t4;
484
CREATE TABLE t1 (id int not null);
485
INSERT INTO t1 VALUES (1),(2);
486
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
490
create table t1(f1 char(1));
491
insert into t1 values ('a'),('b'),('1');
492
select f1 from t1 where f1 in ('a',1);
497
Warning 1292 Truncated incorrect DOUBLE value: 'b'
498
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
499
f1 case f1 when 'a' then '+' when 1 then '-' end
504
Warning 1292 Truncated incorrect DOUBLE value: 'b'
505
create index t1f1_idx on t1(f1);
506
select f1 from t1 where f1 in ('a',1);
511
Warning 1292 Truncated incorrect DOUBLE value: 'b'
512
explain select f1 from t1 where f1 in ('a',1);
513
id select_type table type possible_keys key key_len ref rows Extra
514
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
515
select f1 from t1 where f1 in ('a','b');
519
explain select f1 from t1 where f1 in ('a','b');
520
id select_type table type possible_keys key key_len ref rows Extra
521
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
522
select f1 from t1 where f1 in (2,1);
526
Warning 1292 Truncated incorrect DOUBLE value: 'a'
527
Warning 1292 Truncated incorrect DOUBLE value: 'b'
528
explain select f1 from t1 where f1 in (2,1);
529
id select_type table type possible_keys key key_len ref rows Extra
530
1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
531
create table t2(f2 int, index t2f2(f2));
532
insert into t2 values(0),(1),(2);
533
select f2 from t2 where f2 in ('a',2);
538
Warning 1292 Truncated incorrect DOUBLE value: 'a'
539
Warning 1292 Truncated incorrect DOUBLE value: 'a'
540
Warning 1292 Truncated incorrect DOUBLE value: 'a'
541
explain select f2 from t2 where f2 in ('a',2);
542
id select_type table type possible_keys key key_len ref rows Extra
543
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
544
select f2 from t2 where f2 in ('a','b');
548
Warning 1292 Truncated incorrect DOUBLE value: 'a'
549
Warning 1292 Truncated incorrect DOUBLE value: 'b'
550
explain select f2 from t2 where f2 in ('a','b');
551
id select_type table type possible_keys key key_len ref rows Extra
552
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
554
Warning 1292 Truncated incorrect DOUBLE value: 'a'
555
Warning 1292 Truncated incorrect DOUBLE value: 'b'
556
select f2 from t2 where f2 in (1,'b');
561
Warning 1292 Truncated incorrect DOUBLE value: 'b'
562
Warning 1292 Truncated incorrect DOUBLE value: 'b'
563
explain select f2 from t2 where f2 in (1,'b');
564
id select_type table type possible_keys key key_len ref rows Extra
565
1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
567
create table t1 (a time, key(a));
568
insert into t1 values (),(),(),(),(),(),(),(),(),();
569
select a from t1 where a not in (a,a,a) group by a;
572
create table t1 (id int);
573
select * from t1 where NOT id in (select null union all select 1);
575
select * from t1 where NOT id in (null, 1);
578
CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
579
INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1);
580
SELECT CASE AVG (c0) WHEN c1 * c2 THEN 1 END FROM t1;
581
CASE AVG (c0) WHEN c1 * c2 THEN 1 END
583
SELECT CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1;
584
CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END
586
SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
587
CASE c1 WHEN c1 + 1 THEN 1 END ABS(AVG(c0))
590
CREATE TABLE t1(a TEXT, b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL);
591
INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1);
592
INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2);
593
SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a);
595
SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b);
598
SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c);
601
SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d);
604
SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e);
607
SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN
608
((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d));
612
# Bug #44139: Table scan when NULL appears in IN clause
616
c_decimal DECIMAL(5,2) NOT NULL,
617
c_float FLOAT(5, 2) NOT NULL,
618
c_bit BIT(10) NOT NULL,
619
c_date DATE NOT NULL,
620
c_datetime DATETIME NOT NULL,
621
c_timestamp TIMESTAMP NOT NULL,
622
c_time TIME NOT NULL,
623
c_year YEAR NOT NULL,
624
c_char CHAR(10) NOT NULL,
625
INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date),
626
INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year),
628
INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
629
INSERT INTO t1 (c_int) SELECT 0 FROM t1;
630
INSERT INTO t1 (c_int) SELECT 0 FROM t1;
631
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
632
id select_type table type possible_keys key key_len ref rows Extra
633
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
634
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3);
635
id select_type table type possible_keys key key_len ref rows Extra
636
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
637
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
638
id select_type table type possible_keys key key_len ref rows Extra
639
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
640
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL);
641
id select_type table type possible_keys key key_len ref rows Extra
642
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
643
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL);
644
id select_type table type possible_keys key key_len ref rows Extra
645
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
646
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL);
647
id select_type table type possible_keys key key_len ref rows Extra
648
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
649
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3);
650
id select_type table type possible_keys key key_len ref rows Extra
651
1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where
652
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3);
653
id select_type table type possible_keys key key_len ref rows Extra
654
1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where
655
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL);
656
id select_type table type possible_keys key key_len ref rows Extra
657
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
658
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL);
659
id select_type table type possible_keys key key_len ref rows Extra
660
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
661
EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3);
662
id select_type table type possible_keys key key_len ref rows Extra
663
1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where
664
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3);
665
id select_type table type possible_keys key key_len ref rows Extra
666
1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where
667
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL);
668
id select_type table type possible_keys key key_len ref rows Extra
669
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
670
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL);
671
id select_type table type possible_keys key key_len ref rows Extra
672
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
673
EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3);
674
id select_type table type possible_keys key key_len ref rows Extra
675
1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where
676
EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3);
677
id select_type table type possible_keys key key_len ref rows Extra
678
1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where
679
EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL);
680
id select_type table type possible_keys key key_len ref rows Extra
681
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
682
EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL);
683
id select_type table type possible_keys key key_len ref rows Extra
684
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
685
EXPLAIN SELECT * FROM t1 WHERE c_date
686
IN ('2009-09-01', '2009-09-02', '2009-09-03');
687
id select_type table type possible_keys key key_len ref rows Extra
688
1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where
689
EXPLAIN SELECT * FROM t1 WHERE c_date
690
IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03');
691
id select_type table type possible_keys key key_len ref rows Extra
692
1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where
693
EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL);
694
id select_type table type possible_keys key key_len ref rows Extra
695
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
696
EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL);
697
id select_type table type possible_keys key key_len ref rows Extra
698
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
699
EXPLAIN SELECT * FROM t1 WHERE c_datetime
700
IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
701
id select_type table type possible_keys key key_len ref rows Extra
702
1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where
703
EXPLAIN SELECT * FROM t1 WHERE c_datetime
704
IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
705
id select_type table type possible_keys key key_len ref rows Extra
706
1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where
707
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL);
708
id select_type table type possible_keys key key_len ref rows Extra
709
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
710
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL);
711
id select_type table type possible_keys key key_len ref rows Extra
712
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
713
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
714
IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
715
id select_type table type possible_keys key key_len ref rows Extra
716
1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where
717
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
718
IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
719
id select_type table type possible_keys key key_len ref rows Extra
720
1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where
721
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL);
722
id select_type table type possible_keys key key_len ref rows Extra
723
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
724
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL);
725
id select_type table type possible_keys key key_len ref rows Extra
726
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
727
EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3);
728
id select_type table type possible_keys key key_len ref rows Extra
729
1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where
730
EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3);
731
id select_type table type possible_keys key key_len ref rows Extra
732
1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where
733
EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL);
734
id select_type table type possible_keys key key_len ref rows Extra
735
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
736
EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL);
737
id select_type table type possible_keys key key_len ref rows Extra
738
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
739
EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3');
740
id select_type table type possible_keys key key_len ref rows Extra
741
1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where
742
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3');
743
id select_type table type possible_keys key key_len ref rows Extra
744
1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where
745
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL);
746
id select_type table type possible_keys key key_len ref rows Extra
747
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
748
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL);
749
id select_type table type possible_keys key key_len ref rows Extra
750
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
added
removed
mysql-test/r/func_in.result
