← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/func_in.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/func_in.test
 
1
# Initialise
 
2
--disable_warnings
 
3
drop table if exists t1, t2;
 
4
--enable_warnings
 
5
#
 
6
# test of IN (NULL)
 
7
#
 
8
 
 
9
select 1 in (1,2,3);
 
10
select 10 in (1,2,3);
 
11
select NULL in (1,2,3);
 
12
select 1 in (1,NULL,3);
 
13
select 3 in (1,NULL,3);
 
14
select 10 in (1,NULL,3);
 
15
select 1.5 in (1.5,2.5,3.5);
 
16
select 10.5 in (1.5,2.5,3.5);
 
17
select NULL in (1.5,2.5,3.5);
 
18
select 1.5 in (1.5,NULL,3.5);
 
19
select 3.5 in (1.5,NULL,3.5);
 
20
select 10.5 in (1.5,NULL,3.5);
 
21
 
 
22
CREATE TABLE t1 (a int, b int, c int);
 
23
insert into t1 values (1,2,3), (1,NULL,3);
 
24
select 1 in (a,b,c) from t1;
 
25
select 3 in (a,b,c) from t1;
 
26
select 10 in (a,b,c) from t1;
 
27
select NULL in (a,b,c) from t1;
 
28
drop table t1;
 
29
CREATE TABLE t1 (a float, b float, c float);
 
30
insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
 
31
select 1.5 in (a,b,c) from t1;
 
32
select 3.5 in (a,b,c) from t1;
 
33
select 10.5 in (a,b,c) from t1;
 
34
drop table t1;
 
35
CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
 
36
insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
 
37
select 'A' in (a,b,c) from t1;
 
38
select 'EFD' in (a,b,c) from t1;
 
39
select 'XSFGGHF' in (a,b,c) from t1;
 
40
drop table t1;
 
41
 
 
42
CREATE TABLE t1 (field char(1));
 
43
INSERT INTO t1 VALUES ('A'),(NULL);
 
44
SELECT * from t1 WHERE field IN (NULL);
 
45
SELECT * from t1 WHERE field NOT IN (NULL);
 
46
SELECT * from t1 where field = field;
 
47
SELECT * from t1 where field <=> field;
 
48
DELETE FROM t1 WHERE field NOT IN (NULL);
 
49
SELECT * FROM t1;
 
50
drop table t1;
 
51
 
 
52
create table t1 (id int(10) primary key);
 
53
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
 
54
select * from t1 where id in (2,5,9);
 
55
drop table t1;
 
56
 
 
57
create table t1 (
 
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
 
61
);
 
62
insert into t1 values ('A','B','C');
 
63
insert into t1 values ('a','c','c');
 
64
--error 1267
 
65
select * from t1 where a in (b);
 
66
--error 1270
 
67
select * from t1 where a in (b,c);
 
68
--error 1271
 
69
select * from t1 where 'a' in (a,b,c);
 
70
select * from t1 where 'a' in (a);
 
71
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);
 
76
drop table t1;
 
77
 
 
78
set names utf8;
 
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;
 
82
drop table t1;
 
83
# Bug#7834 Illegal mix of collations in IN operator
 
84
create table t1 (a char(10) character set latin1 not null);
 
85
insert into t1 values ('a'),('b'),('c');
 
86
select a from t1 where a IN ('a','b','c') order by a;
 
87
drop table t1;
 
88
set names latin1;
 
89
 
 
90
select '1.0' in (1,2);
 
91
select 1 in ('1.0',2);
 
92
select 1 in (1,'2.0');
 
93
select 1 in ('1.0',2.0);
 
94
select 1 in (1.0,'2.0');
 
95
select 1 in ('1.1',2);
 
96
select 1 in ('1.1',2.0);
 
97
 
 
98
# Test case for bug #6365
 
99
 
 
100
create table t1 (a char(2) character set binary);
 
101
insert into t1 values ('aa'), ('bb');
 
102
select * from t1 where a in (NULL, 'aa');
 
103
drop table t1;
 
104
 
 
105
# BUG#13419
 
106
create table t1 (id int, key(id));
 
107
insert into t1 values (1),(2),(3);
 
108
select count(*) from t1 where id not in (1);
 
109
select count(*) from t1 where id not in (1,2);
 
110
drop table t1;
 
111
 
 
112
 
 
113
#
 
114
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
 
115
# result
 
116
#
 
117
# The problem was in the IN() function that ignored maybe_null flags
 
118
# of all arguments except the first (the one _before_ the IN
 
119
# keyword, '1' in the test case below).
 
120
#
 
121
--disable_warnings
 
122
DROP TABLE IF EXISTS t1;
 
123
--enable_warnings
 
124
 
 
125
CREATE TABLE t1 SELECT 1 IN (2, NULL);
 
126
--echo SELECT should return NULL.
 
127
SELECT * FROM t1;
 
128
 
 
129
DROP TABLE t1;
 
130
 
 
131
 
 
132
--echo End of 4.1 tests
 
133
 
 
134
 
 
135
#
 
136
# Bug #11885: WHERE condition with NOT IN (one element)          
 
137
#             
 
138
 
 
139
CREATE TABLE t1 (a int PRIMARY KEY);
 
140
INSERT INTO t1 VALUES (44), (45), (46);
 
141
 
 
142
SELECT * FROM t1 WHERE a IN (45);
 
143
SELECT * FROM t1 WHERE a NOT IN (0, 45);
 
144
SELECT * FROM t1 WHERE a NOT IN (45);
 
145
 
 
146
DROP TABLE t1;
 
147
 
 
148
# BUG#15872: Excessive memory consumption of range analysis of NOT IN
 
149
create table t1 (a int);
 
150
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));
 
152
 
 
153
insert into t2 select C.a*2,   'no'  from t1 A, t1 B, t1 C;
 
154
insert into t2 select C.a*2+1, 'yes' from t1 C;
 
155
 
 
156
explain 
 
157
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
 
158
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
 
159
 
 
160
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
 
161
explain select * from t2 force index(a) where a <> 2;
 
162
 
 
163
drop table t2;
 
164
 
 
165
#
 
166
# Repeat the test for DATETIME
 
167
#
 
168
create table t2 (a datetime, filler char(200), key(a));
 
169
 
 
170
insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
 
171
               'no'  from t1 A, t1 B, t1 C where C.a % 2 = 0;
 
172
 
 
173
insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
 
174
               'yes' from t1 C;
 
175
 
 
176
explain 
 
177
select * from t2 where a NOT IN (
 
178
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
 
179
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
 
180
select * from t2 where a NOT IN (
 
181
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
 
182
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
 
183
drop table t2;
 
184
 
 
185
#
 
186
# Repeat the test for CHAR(N)
 
187
#
 
188
create table t2 (a varchar(10), filler char(200), key(a));
 
189
 
 
190
insert into t2 select 'foo', 'no' from t1 A, t1 B;
 
191
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
 
192
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
 
193
 
 
194
insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), 
 
195
  ('barbas','1'), ('bazbazbay', '1'),('zz','1');
 
196
 
 
197
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
 
198
 
 
199
drop table t2;
 
200
 
 
201
#
 
202
# Repeat for DECIMAL
 
203
#
 
204
create table t2 (a decimal(10,5), filler char(200), key(a));
 
205
 
 
206
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
 
207
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
 
208
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
 
209
 
 
210
insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), 
 
211
  (55555,'1'), (77777, '1');
 
212
 
 
213
explain
 
214
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
 
215
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
 
216
 
 
217
drop table t2;
 
218
 
 
219
# Try a very big IN-list
 
220
create table t2 (a int, key(a), b int);
 
221
insert into t2 values (1,1),(2,2);
 
222
 
 
223
set @cnt= 1; 
 
224
set @str="update t2 set b=1 where a not in (";
 
225
select count(*) from (
 
226
  select @str:=concat(@str, @cnt:=@cnt+1, ",") 
 
227
  from t1 A, t1 B, t1 C, t1 D) Z;
 
228
 
 
229
set @str:=concat(@str, "10000)");
 
230
select substr(@str, 1, 50);
 
231
set @str=NULL;
 
232
 
 
233
drop table t2;
 
234
drop table t1;
 
235
 
 
236
# BUG#19618: Crash in range optimizer for 
 
237
#   "unsigned_keypart NOT IN(negative_number,...)" 
 
238
#   (introduced in fix BUG#15872) 
 
239
create table t1 (
 
240
  some_id smallint(5) unsigned,
 
241
  key (some_id)
 
242
);
 
243
insert into t1 values (1),(2);
 
244
select some_id from t1 where some_id not in(2,-1);
 
245
select some_id from t1 where some_id not in(-4,-1,-4);
 
246
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 
247
 
 
248
#
 
249
# BUG#24261: crash when WHERE contains NOT IN ('<negative value>') for unsigned column type
 
250
#
 
251
 
 
252
select some_id from t1 where some_id not in('-1', '0');
 
253
 
 
254
drop table t1;
 
255
 
 
256
#
 
257
# BUG#20420: optimizer reports wrong keys on left join with IN
 
258
#
 
259
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
 
260
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
 
261
 
 
262
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
 
263
INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
 
264
 
 
265
CREATE TABLE t3 (a int PRIMARY KEY);
 
266
INSERT INTO t3 VALUES (1),(2),(3),(4);
 
267
 
 
268
CREATE TABLE t4 (a int PRIMARY KEY,b int);
 
269
INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
 
270
       (1003,1003),(1004,1004);
 
271
 
 
272
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
 
273
  JOIN t1 ON t3.a=t1.a 
 
274
  JOIN t2 ON t3.a=t2.a
 
275
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 
276
 
 
277
SELECT STRAIGHT_JOIN * FROM t3 
 
278
  JOIN t1 ON t3.a=t1.a 
 
279
  JOIN t2 ON t3.a=t2.a
 
280
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 
281
 
 
282
EXPLAIN SELECT STRAIGHT_JOIN 
 
283
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
 
284
  FROM t3, t1, t2
 
285
  WHERE t3.a=t1.a AND t3.a=t2.a;
 
286
 
 
287
SELECT STRAIGHT_JOIN 
 
288
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
 
289
  FROM t3, t1, t2
 
290
  WHERE t3.a=t1.a AND t3.a=t2.a;
 
291
 
 
292
DROP TABLE t1,t2,t3,t4;  
 
293
 
 
294
#
 
295
# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values
 
296
#
 
297
CREATE TABLE t1(a BIGINT UNSIGNED);
 
298
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
 
299
 
 
300
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
 
301
SELECT * FROM t1 WHERE a IN (-1, -2);
 
302
 
 
303
CREATE TABLE t2 (a BIGINT UNSIGNED);
 
304
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);
 
330
 
 
331
SELECT HEX(a) FROM t2 WHERE a IN 
 
332
  (CAST(0xBB3C3E98175D33C8 AS UNSIGNED),
 
333
   42);
 
334
 
 
335
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),
 
341
   42);
 
342
 
 
343
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 
 
350
  (0x7ffffffffffffffe, 
 
351
   0x7fffffffffffffff,
 
352
   'abc');
 
353
 
 
354
CREATE TABLE t3 (a BIGINT UNSIGNED);
 
355
INSERT INTO t3 VALUES (9223372036854775551);
 
356
 
 
357
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
 
358
 
 
359
CREATE TABLE t4 (a DATE);
 
360
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
 
361
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
 
362
 
 
363
DROP TABLE t1,t2,t3,t4;
 
364
 
 
365
#
 
366
# BUG#27362: IN with a decimal expression that may return NULL
 
367
#
 
368
 
 
369
CREATE TABLE t1 (id int not null);
 
370
INSERT INTO t1 VALUES (1),(2);
 
371
 
 
372
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
 
373
 
 
374
DROP TABLE t1;
 
375
 
 
376
--echo End of 5.0 tests
 
377
 
 
378
 
 
379
#
 
380
# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result 
 
381
#
 
382
create table t1(f1 char(1));
 
383
insert into t1 values ('a'),('b'),('1');
 
384
select f1 from t1 where f1 in ('a',1);
 
385
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
 
386
create index t1f1_idx on t1(f1);
 
387
select f1 from t1 where f1 in ('a',1);
 
388
explain select f1 from t1 where f1 in ('a',1);
 
389
select f1 from t1 where f1 in ('a','b');
 
390
explain select f1 from t1 where f1 in ('a','b');
 
391
select f1 from t1 where f1 in (2,1);
 
392
explain select f1 from t1 where f1 in (2,1);
 
393
create table t2(f2 int, index t2f2(f2));
 
394
insert into t2 values(0),(1),(2);
 
395
select f2 from t2 where f2 in ('a',2);
 
396
explain select f2 from t2 where f2 in ('a',2);
 
397
select f2 from t2 where f2 in ('a','b');
 
398
explain select f2 from t2 where f2 in ('a','b');
 
399
select f2 from t2 where f2 in (1,'b');
 
400
explain select f2 from t2 where f2 in (1,'b');
 
401
drop table t1, t2;
 
402
 
 
403
#
 
404
# Bug #31075: crash in get_func_mm_tree
 
405
#
 
406
 
 
407
create table t1 (a time, key(a));
 
408
insert into t1 values (),(),(),(),(),(),(),(),(),();
 
409
select a from t1 where a not in (a,a,a) group by a;
 
410
drop table t1;
 
411
 
 
412
--echo End of 5.1 tests