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