52
create table t1 (id int(10) primary key);
52
create table t1 (id int primary key);
53
53
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
54
54
select * from t1 where id in (2,5,9);
58
a char(1) character set latin1 collate latin1_general_ci,
59
b char(1) character set latin1 collate latin1_swedish_ci,
60
c char(1) character set latin1 collate latin1_danish_ci
62
62
insert into t1 values ('A','B','C');
63
63
insert into t1 values ('a','c','c');
65
64
select * from t1 where a in (b);
67
65
select * from t1 where a in (b,c);
69
66
select * from t1 where 'a' in (a,b,c);
70
67
select * from t1 where 'a' in (a);
71
68
select * from t1 where a in ('a');
72
select * from t1 where 'a' collate latin1_general_ci in (a,b,c);
73
select * from t1 where 'a' collate latin1_bin in (a,b,c);
74
select * from t1 where 'a' in (a,b,c collate latin1_bin);
75
explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin);
69
select * from t1 where 'a' collate utf8_general_ci in (a,b,c);
70
select * from t1 where 'a' collate utf8_bin in (a,b,c);
71
select * from t1 where 'a' in (a,b,c collate utf8_bin);
72
explain extended select * from t1 where 'a' in (a,b,c collate utf8_bin);
79
create table t1 (a char(10) character set utf8 not null);
80
insert into t1 values ('bbbb'),(_koi8r'����'),(_latin1'����');
81
select a from t1 where a in ('bbbb',_koi8r'����',_latin1'����') order by a;
83
75
# Bug#7834 Illegal mix of collations in IN operator
84
create table t1 (a char(10) character set latin1 not null);
76
create table t1 (a char(10) not null);
85
77
insert into t1 values ('a'),('b'),('c');
86
78
select a from t1 where a IN ('a','b','c') order by a;
90
81
select '1.0' in (1,2);
91
82
select 1 in ('1.0',2);
148
139
# BUG#15872: Excessive memory consumption of range analysis of NOT IN
149
create table t1 (a int);
140
create table t1 (a int) ENGINE=MYISAM;
150
141
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
151
create table t2 (a int, filler char(200), key(a));
142
create table t2 (a int, filler char(200), key(a)) ENGINE=MYISAM;
153
144
insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C;
154
145
insert into t2 select C.a*2+1, 'yes' from t1 C;
295
286
# BUG#19342: IN works incorrectly for BIGINT values
297
CREATE TABLE t1(a BIGINT UNSIGNED);
298
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
288
CREATE TABLE t1(a BIGINT);
289
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
300
291
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
301
292
SELECT * FROM t1 WHERE a IN (-1, -2);
303
CREATE TABLE t2 (a BIGINT UNSIGNED);
294
CREATE TABLE t2 (a BIGINT);
304
295
insert into t2 values(13491727406643098568),
305
(0x7fffffefffffffff),
306
(0x7ffffffeffffffff),
307
(0x7fffffffefffffff),
308
(0x7ffffffffeffffff),
309
(0x7fffffffffefffff),
310
(0x7ffffffffffeffff),
311
(0x7fffffffffffefff),
312
(0x7ffffffffffffeff),
313
(0x7fffffffffffffef),
314
(0x7ffffffffffffffe),
315
(0x7fffffffffffffff),
316
(0x8000000000000000),
317
(0x8000000000000001),
318
(0x8000000000000002),
319
(0x8000000000000300),
320
(0x8000000000000400),
321
(0x8000000000000401),
322
(0x8000000000004001),
323
(0x8000000000040001),
324
(0x8000000000400001),
325
(0x8000000004000001),
326
(0x8000000040000001),
327
(0x8000000400000001),
328
(0x8000004000000001),
329
(0x8000040000000001);
296
(0x0fffffefffffffff),
297
(0x0ffffffeffffffff),
298
(0x0fffffffefffffff),
299
(0x0ffffffffeffffff),
300
(0x0fffffffffefffff),
301
(0x0ffffffffffeffff),
302
(0x0fffffffffffefff),
303
(0x0ffffffffffffeff),
304
(0x0fffffffffffffef),
305
(0x0ffffffffffffffe),
306
(0x0fffffffffffffff),
307
(0x2000000000000000),
308
(0x2000000000000001),
309
(0x2000000000000002),
310
(0x2000000000000300),
311
(0x2000000000000400),
312
(0x2000000000000401),
313
(0x2000000000004001),
314
(0x2000000000040001),
315
(0x2000000000400001),
316
(0x2000000004000001),
317
(0x2000000040000001),
318
(0x2000000400000001),
319
(0x2000004000000001),
320
(0x2000040000000001);
331
SELECT HEX(a) FROM t2 WHERE a IN
332
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
322
SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
335
324
SELECT HEX(a) FROM t2 WHERE a IN
336
(CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
337
CAST(0x7fffffffffffffff AS UNSIGNED),
338
CAST(0x8000000000000000 AS UNSIGNED),
339
CAST(0x8000000000000400 AS UNSIGNED),
340
CAST(0x8000000000000401 AS UNSIGNED),
343
332
SELECT HEX(a) FROM t2 WHERE a IN
344
(CAST(0x7fffffffffffffff AS UNSIGNED),
345
CAST(0x8000000000000001 AS UNSIGNED));
346
SELECT HEX(a) FROM t2 WHERE a IN
347
(CAST(0x7ffffffffffffffe AS UNSIGNED),
348
CAST(0x7fffffffffffffff AS UNSIGNED));
349
SELECT HEX(a) FROM t2 WHERE a IN
335
SELECT HEX(a) FROM t2 WHERE a IN
338
SELECT HEX(a) FROM t2 WHERE a IN
354
CREATE TABLE t3 (a BIGINT UNSIGNED);
343
CREATE TABLE t3 (a BIGINT);
355
344
INSERT INTO t3 VALUES (9223372036854775551);
357
346
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
380
369
# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result
382
create table t1(f1 char(1));
371
create table t1(f1 char(1)) ENGINE=MYISAM;
383
372
insert into t1 values ('a'),('b'),('1');
384
373
select f1 from t1 where f1 in ('a',1);
385
374
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
390
379
explain select f1 from t1 where f1 in ('a','b');
391
380
select f1 from t1 where f1 in (2,1);
392
381
explain select f1 from t1 where f1 in (2,1);
393
create table t2(f2 int, index t2f2(f2));
382
create table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
394
383
insert into t2 values(0),(1),(2);
395
384
select f2 from t2 where f2 in ('a',2);
396
385
explain select f2 from t2 where f2 in ('a',2);