← Back to branch summary

~ubuntu-branches/ubuntu/trusty/drizzle/trusty

« back to all changes in this revision

Viewing changes to tests/t/func_in.test

  • Committer: Bazaar Package Importer
  • Author(s): Monty Taylor
  • Date: 2010-03-18 12:12:31 UTC
  • Revision ID: james.westby@ubuntu.com-20100318121231-k6g1xe6cshbwa0f8
Tags: upstream-2010.03.1347
Import upstream version 2010.03.1347

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/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 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),
 
59
b char(1),
 
60
c char(1)
 
61
);
 
62
insert into t1 values ('A','B','C');
 
63
insert into t1 values ('a','c','c');
 
64
select * from t1 where a in (b);
 
65
select * from t1 where a in (b,c);
 
66
select * from t1 where 'a' in (a,b,c);
 
67
select * from t1 where 'a' in (a);
 
68
select * from t1 where a in ('a');
 
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);
 
73
drop table t1;
 
74
 
 
75
# Bug#7834 Illegal mix of collations in IN operator
 
76
create table t1 (a char(10) not null);
 
77
insert into t1 values ('a'),('b'),('c');
 
78
select a from t1 where a IN ('a','b','c') order by a;
 
79
drop table t1;
 
80
 
 
81
select '1.0' in (1,2);
 
82
select 1 in ('1.0',2);
 
83
select 1 in (1,'2.0');
 
84
select 1 in ('1.0',2.0);
 
85
select 1 in (1.0,'2.0');
 
86
select 1 in ('1.1',2);
 
87
select 1 in ('1.1',2.0);
 
88
 
 
89
# Test case for bug #6365
 
90
 
 
91
create table t1 (a char(2));
 
92
insert into t1 values ('aa'), ('bb');
 
93
select * from t1 where a in (NULL, 'aa');
 
94
drop table t1;
 
95
 
 
96
# BUG#13419
 
97
create table t1 (id int, key(id));
 
98
insert into t1 values (1),(2),(3);
 
99
select count(*) from t1 where id not in (1);
 
100
select count(*) from t1 where id not in (1,2);
 
101
drop table t1;
 
102
 
 
103
 
 
104
#
 
105
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
 
106
# result
 
107
#
 
108
# The problem was in the IN() function that ignored maybe_null flags
 
109
# of all arguments except the first (the one _before_ the IN
 
110
# keyword, '1' in the test case below).
 
111
#
 
112
--disable_warnings
 
113
DROP TABLE IF EXISTS t1;
 
114
--enable_warnings
 
115
 
 
116
CREATE TABLE t1 SELECT 1 IN (2, NULL);
 
117
--echo SELECT should return NULL.
 
118
SELECT * FROM t1;
 
119
 
 
120
DROP TABLE t1;
 
121
 
 
122
 
 
123
--echo End of 4.1 tests
 
124
 
 
125
 
 
126
#
 
127
# Bug #11885: WHERE condition with NOT IN (one element)          
 
128
#             
 
129
 
 
130
CREATE TABLE t1 (a int PRIMARY KEY);
 
131
INSERT INTO t1 VALUES (44), (45), (46);
 
132
 
 
133
SELECT * FROM t1 WHERE a IN (45);
 
134
SELECT * FROM t1 WHERE a NOT IN (0, 45);
 
135
SELECT * FROM t1 WHERE a NOT IN (45);
 
136
 
 
137
DROP TABLE t1;
 
138
 
 
139
# BUG#15872: Excessive memory consumption of range analysis of NOT IN
 
140
# I have disabled the EXPLAIN because we must use Innodb with this test.
 
141
create table t1 (a int);
 
142
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
 
143
create temporary table t2 (a int, filler char(200), key(a)) engine=myisam;
 
144
 
 
145
insert into t2 select C.a*2,   'no'  from t1 A, t1 B, t1 C;
 
146
insert into t2 select C.a*2+1, 'yes' from t1 C;
 
147
 
 
148
explain 
 
149
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
 
150
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
 
151
 
 
152
# 
 
153
explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
 
154
explain select * from t2 force index(a) where a <> 2;
 
155
 
 
156
drop table t2;
 
157
 
 
158
#
 
159
# Repeat the test for DATETIME
 
160
#
 
161
create table t2 (a datetime, filler char(200), key(a));
 
162
 
 
163
insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
 
164
               'no'  from t1 A, t1 B, t1 C where C.a % 2 = 0;
 
165
 
 
166
insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
 
167
               'yes' from t1 C;
 
168
 
 
169
explain 
 
170
select * from t2 where a NOT IN (
 
171
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
 
172
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
 
173
select * from t2 where a NOT IN (
 
174
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
 
175
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
 
176
drop table t2;
 
177
 
 
178
#
 
179
# Repeat the test for CHAR(N)
 
180
#
 
181
create table t2 (a varchar(10), filler char(200), key(a));
 
182
 
 
183
insert into t2 select 'foo', 'no' from t1 A, t1 B;
 
184
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
 
185
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;
 
186
 
 
187
insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), 
 
188
  ('barbas','1'), ('bazbazbay', '1'),('zz','1');
 
189
 
 
190
explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');
 
191
 
 
192
drop table t2;
 
193
 
 
194
#
 
195
# Repeat for DECIMAL
 
196
#
 
197
create table t2 (a decimal(10,5), filler char(200), key(a));
 
198
 
 
199
insert into t2 select 345.67890, 'no' from t1 A, t1 B;
 
200
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
 
201
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;
 
202
 
 
203
insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), 
 
204
  (55555,'1'), (77777, '1');
 
205
 
 
206
explain
 
207
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
 
208
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
 
209
 
 
210
drop table t2;
 
211
 
 
212
# Try a very big IN-list
 
213
create table t2 (a int, key(a), b int);
 
214
insert into t2 values (1,1),(2,2);
 
215
 
 
216
set @cnt= 1; 
 
217
set @str="update t2 set b=1 where a not in (";
 
218
select count(*) from (
 
219
  select @str:=concat(@str, @cnt:=@cnt+1, ",") 
 
220
  from t1 A, t1 B, t1 C, t1 D) Z;
 
221
 
 
222
set @str:=concat(@str, "10000)");
 
223
select substr(@str, 1, 50);
 
224
set @str=NULL;
 
225
 
 
226
drop table t2;
 
227
drop table t1;
 
228
 
 
229
# BUG#19618: Crash in range optimizer for 
 
230
#   "unsigned_keypart NOT IN(negative_number,...)" 
 
231
#   (introduced in fix BUG#15872) 
 
232
create table t1 (
 
233
  some_id int,
 
234
  key (some_id)
 
235
);
 
236
insert into t1 values (1),(2);
 
237
select some_id from t1 where some_id not in(2,-1);
 
238
select some_id from t1 where some_id not in(-4,-1,-4);
 
239
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 
240
 
 
241
#
 
242
# BUG#24261: crash when WHERE contains NOT IN ('<negative value>') for column type
 
243
#
 
244
 
 
245
select some_id from t1 where some_id not in('-1', '0');
 
246
 
 
247
drop table t1;
 
248
 
 
249
#
 
250
# BUG#20420: optimizer reports wrong keys on left join with IN
 
251
#
 
252
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
 
253
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
 
254
 
 
255
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
 
256
INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
 
257
 
 
258
CREATE TABLE t3 (a int PRIMARY KEY);
 
259
INSERT INTO t3 VALUES (1),(2),(3),(4);
 
260
 
 
261
CREATE TABLE t4 (a int PRIMARY KEY,b int);
 
262
INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
 
263
       (1003,1003),(1004,1004);
 
264
 
 
265
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
 
266
  JOIN t1 ON t3.a=t1.a 
 
267
  JOIN t2 ON t3.a=t2.a
 
268
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 
269
 
 
270
SELECT STRAIGHT_JOIN * FROM t3 
 
271
  JOIN t1 ON t3.a=t1.a 
 
272
  JOIN t2 ON t3.a=t2.a
 
273
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 
274
 
 
275
EXPLAIN SELECT STRAIGHT_JOIN 
 
276
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
 
277
  FROM t3, t1, t2
 
278
  WHERE t3.a=t1.a AND t3.a=t2.a;
 
279
 
 
280
SELECT STRAIGHT_JOIN 
 
281
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
 
282
  FROM t3, t1, t2
 
283
  WHERE t3.a=t1.a AND t3.a=t2.a;
 
284
 
 
285
DROP TABLE t1,t2,t3,t4;  
 
286
 
 
287
#
 
288
# BUG#19342: IN works incorrectly for BIGINT values
 
289
#
 
290
CREATE TABLE t1(a BIGINT);
 
291
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
 
292
 
 
293
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
 
294
SELECT * FROM t1 WHERE a IN (-1, -2);
 
295
 
 
296
CREATE TABLE t2 (a BIGINT);
 
297
insert into t2 values(13491727406643098568),
 
298
       (0x0fffffefffffffff),
 
299
       (0x0ffffffeffffffff),
 
300
       (0x0fffffffefffffff),
 
301
       (0x0ffffffffeffffff),
 
302
       (0x0fffffffffefffff),
 
303
       (0x0ffffffffffeffff),
 
304
       (0x0fffffffffffefff),
 
305
       (0x0ffffffffffffeff),
 
306
       (0x0fffffffffffffef),
 
307
       (0x0ffffffffffffffe),
 
308
       (0x0fffffffffffffff),
 
309
       (0x2000000000000000),
 
310
       (0x2000000000000001),
 
311
       (0x2000000000000002),
 
312
       (0x2000000000000300),
 
313
       (0x2000000000000400),
 
314
       (0x2000000000000401),
 
315
       (0x2000000000004001),
 
316
       (0x2000000000040001),
 
317
       (0x2000000000400001),
 
318
       (0x2000000004000001),
 
319
       (0x2000000040000001),
 
320
       (0x2000000400000001),
 
321
       (0x2000004000000001),
 
322
       (0x2000040000000001);
 
323
 
 
324
SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
 
325
 
 
326
SELECT HEX(a) FROM t2 WHERE a IN
 
327
  (0xBB3C3E98175D33C8,
 
328
   0x2fffffffffffffff,
 
329
   0x2000000000000000,
 
330
   0x2000000000000400,
 
331
   0x2000000000000401,
 
332
   42);
 
333
 
 
334
SELECT HEX(a) FROM t2 WHERE a IN 
 
335
  (0x7fffffffffffffff, 
 
336
   0x2000000000000001);
 
337
SELECT HEX(a) FROM t2 WHERE a IN 
 
338
  (0x2ffffffffffffffe, 
 
339
   0x2fffffffffffffff);
 
340
SELECT HEX(a) FROM t2 WHERE a IN 
 
341
  (0x2ffffffffffffffe, 
 
342
   0x2fffffffffffffff,
 
343
   'abc');
 
344
 
 
345
CREATE TABLE t3 (a BIGINT);
 
346
INSERT INTO t3 VALUES (9223372036854775551);
 
347
 
 
348
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
 
349
 
 
350
CREATE TABLE t4 (a DATE);
 
351
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
 
352
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
 
353
 
 
354
DROP TABLE t1,t2,t3,t4;
 
355
 
 
356
#
 
357
# BUG#27362: IN with a decimal expression that may return NULL
 
358
#
 
359
 
 
360
CREATE TABLE t1 (id int not null);
 
361
INSERT INTO t1 VALUES (1),(2);
 
362
 
 
363
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
 
364
 
 
365
DROP TABLE t1;
 
366
 
 
367
--echo End of 5.0 tests
 
368
 
 
369
 
 
370
#
 
371
# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result 
 
372
#
 
373
create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;
 
374
insert into t1 values ('a'),('b'),('1');
 
375
select f1 from t1 where f1 in ('a',1);
 
376
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
 
377
create index t1f1_idx on t1(f1);
 
378
select f1 from t1 where f1 in ('a',1);
 
379
explain select f1 from t1 where f1 in ('a',1);
 
380
select f1 from t1 where f1 in ('a','b');
 
381
explain select f1 from t1 where f1 in ('a','b');
 
382
select f1 from t1 where f1 in (2,1);
 
383
explain select f1 from t1 where f1 in (2,1);
 
384
create TEMPORARY table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
 
385
insert into t2 values(0),(1),(2);
 
386
select f2 from t2 where f2 in ('a',2);
 
387
explain select f2 from t2 where f2 in ('a',2);
 
388
select f2 from t2 where f2 in ('a','b');
 
389
explain select f2 from t2 where f2 in ('a','b');
 
390
select f2 from t2 where f2 in (1,'b');
 
391
explain select f2 from t2 where f2 in (1,'b');
 
392
drop table t1, t2;
 
393
 
 
394
#
 
395
# Bug #31075: crash in get_func_mm_tree
 
396
#
 
397
 
 
398
create table t1 (a datetime, key(a));
 
399
insert into t1 values (),(),(),(),(),(),(),(),(),();
 
400
select a from t1 where a not in (a,a,a) group by a;
 
401
drop table t1;
 
402
 
 
403
--echo End of 5.1 tests