106
create table t1 (id int(10) primary key);
106
create table t1 (id int primary key);
107
107
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
108
108
select * from t1 where id in (2,5,9);
114
114
create table t1 (
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
119
insert into t1 values ('A','B','C');
120
120
insert into t1 values ('a','c','c');
121
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
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
125
select * from t1 where 'a' in (a,b,c);
126
ERROR HY000: Illegal mix of collations for operation ' IN '
127
129
select * from t1 where 'a' in (a);
135
select * from t1 where 'a' collate latin1_general_ci in (a,b,c);
137
select * from t1 where 'a' collate utf8_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);
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);
146
148
id select_type table type possible_keys key key_len ref rows filtered Extra
147
149
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);
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);
161
154
insert into t1 values ('a'),('b'),('c');
162
155
select a from t1 where a IN ('a','b','c') order by a;
227
create table t1 (a int);
219
create table t1 (a int) ENGINE=MYISAM;
228
220
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));
221
create table t2 (a int, filler char(200), key(a)) ENGINE=MYISAM;
230
222
insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
231
223
insert into t2 select C.a*2+1, 'yes' from t1 C;
235
227
1 SIMPLE t2 range a a 5 NULL 12 Using index condition; Using MRR
236
228
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
248
5795
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
249
5796
id select_type table type possible_keys key key_len ref rows Extra
262
5809
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
263
5810
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
264
5811
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
5812
1 SIMPLE t2 range a a 9 NULL 20 Using index condition; Using MRR
266
5813
select * from t2 where a NOT IN (
267
5814
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
268
5815
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
286
5833
('barbas','1'), ('bazbazbay', '1'),('zz','1');
287
5834
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
288
5835
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
5836
1 SIMPLE t2 range a a 43 NULL 5 Using index condition; Using MRR
291
5838
create table t2 (a decimal(10,5), filler char(200), key(a));
292
5839
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
298
5845
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
299
5846
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
5847
1 SIMPLE t2 range a a 7 NULL 4 Using index condition; Using MRR
301
5848
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
359
5906
id select_type table type possible_keys key key_len ref rows Extra
360
5907
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
361
5908
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
5909
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
363
5910
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
364
5911
SELECT STRAIGHT_JOIN * FROM t3
365
5912
JOIN t1 ON t3.a=t1.a
377
5924
id select_type table type possible_keys key key_len ref rows Extra
378
5925
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
379
5926
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
5927
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer
381
5928
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
382
5929
SELECT STRAIGHT_JOIN
383
5930
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
389
5936
DROP TABLE t1,t2,t3,t4;
390
CREATE TABLE t1(a BIGINT UNSIGNED);
391
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
5937
CREATE TABLE t1(a BIGINT);
5938
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
392
5939
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
394
5941
SELECT * FROM t1 WHERE a IN (-1, -2);
396
CREATE TABLE t2 (a BIGINT UNSIGNED);
5943
CREATE TABLE t2 (a BIGINT);
397
5944
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),
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);
428
5972
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),
5973
(0xBB3C3E98175D33C8,
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
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,
460
CREATE TABLE t3 (a BIGINT UNSIGNED);
6000
CREATE TABLE t3 (a BIGINT);
461
6001
INSERT INTO t3 VALUES (9223372036854775551);
462
6002
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
473
6013
INSERT INTO t1 VALUES (1),(2);
474
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
477
6020
End of 5.0 tests
478
create table t1(f1 char(1));
6021
create table t1(f1 char(1)) ENGINE=MYISAM;
479
6022
insert into t1 values ('a'),('b'),('1');
480
6023
select f1 from t1 where f1 in ('a',1);
485
Warning 1292 Truncated incorrect DOUBLE value: 'b'
486
6027
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
487
6028
f1 case f1 when 'a' then '+' when 1 then '-' end
492
Warning 1292 Truncated incorrect DOUBLE value: 'b'
493
6032
create index t1f1_idx on t1(f1);
494
6033
select f1 from t1 where f1 in ('a',1);
499
Warning 1292 Truncated incorrect DOUBLE value: 'b'
500
6037
explain select f1 from t1 where f1 in ('a',1);
501
6038
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
6039
1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
503
6040
select f1 from t1 where f1 in ('a','b');
507
6044
explain select f1 from t1 where f1 in ('a','b');
508
6045
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
6046
1 SIMPLE t1 index t1f1_idx t1f1_idx 7 NULL 3 Using where; Using index
510
6047
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
6050
explain select f1 from t1 where f1 in (2,1);
517
6051
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));
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;
520
6054
insert into t2 values(0),(1),(2);
521
6055
select f2 from t2 where f2 in ('a',2);