← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/func_in.test

  • Committer: Andy Lester
  • Date: 2008-08-09 05:23:39 UTC
  • mto: (266.1.29 use-replace-funcs)
  • mto: This revision was merged to the branch mainline in revision 287.
  • Revision ID: andy@petdance.com-20080809052339-iafoesszmesweq6b
use NULL, not 0

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 where A.a < 10;
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 where A.a < 10;
184
 
insert into t2 select 'barbar', 'no' from t1 A, t1 B where A.a < 10;
185
 
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B where A.a < 10;
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 where A.a < 10;
200
 
insert into t2 select 43245.34, 'no' from t1 A, t1 B where A.a < 10;
201
 
insert into t2 select 64224.56344, 'no' from t1 A, t1 B where A.a < 10;
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  where A.a < 10) 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
 
--sorted_result
266
 
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
267
 
  JOIN t1 ON t3.a=t1.a 
268
 
  JOIN t2 ON t3.a=t2.a
269
 
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
270
 
 
271
 
SELECT STRAIGHT_JOIN * FROM t3 
272
 
  JOIN t1 ON t3.a=t1.a 
273
 
  JOIN t2 ON t3.a=t2.a
274
 
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
275
 
 
276
 
--sorted_result
277
 
EXPLAIN SELECT STRAIGHT_JOIN 
278
 
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
279
 
  FROM t3, t1, t2
280
 
  WHERE t3.a=t1.a AND t3.a=t2.a;
281
 
 
282
 
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
 
DROP TABLE t1,t2,t3,t4;  
288
 
 
289
 
#
290
 
# BUG#19342: IN works incorrectly for BIGINT values
291
 
#
292
 
CREATE TABLE t1(a BIGINT);
293
 
INSERT INTO t1 VALUES (0x0FFFFFFFFFFFFFFF);
294
 
 
295
 
SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
296
 
SELECT * FROM t1 WHERE a IN (-1, -2);
297
 
 
298
 
CREATE TABLE t2 (a BIGINT UNSIGNED);
299
 
insert into t2 values(13491727406643098568),
300
 
       (0x0fffffefffffffff),
301
 
       (0x0ffffffeffffffff),
302
 
       (0x0fffffffefffffff),
303
 
       (0x0ffffffffeffffff),
304
 
       (0x0fffffffffefffff),
305
 
       (0x0ffffffffffeffff),
306
 
       (0x0fffffffffffefff),
307
 
       (0x0ffffffffffffeff),
308
 
       (0x0fffffffffffffef),
309
 
       (0x0ffffffffffffffe),
310
 
       (0x0fffffffffffffff),
311
 
       (0x2000000000000000),
312
 
       (0x2000000000000001),
313
 
       (0x2000000000000002),
314
 
       (0x2000000000000300),
315
 
       (0x2000000000000400),
316
 
       (0x2000000000000401),
317
 
       (0x2000000000004001),
318
 
       (0x2000000000040001),
319
 
       (0x2000000000400001),
320
 
       (0x2000000004000001),
321
 
       (0x2000000040000001),
322
 
       (0x2000000400000001),
323
 
       (0x2000004000000001),
324
 
       (0x2000040000000001);
325
 
 
326
 
SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
327
 
 
328
 
SELECT HEX(a) FROM t2 WHERE a IN
329
 
  (0xBB3C3E98175D33C8,
330
 
   0x2fffffffffffffff,
331
 
   0x2000000000000000,
332
 
   0x2000000000000400,
333
 
   0x2000000000000401,
334
 
   42);
335
 
 
336
 
SELECT HEX(a) FROM t2 WHERE a IN 
337
 
  (0x7fffffffffffffff, 
338
 
   0x2000000000000001);
339
 
SELECT HEX(a) FROM t2 WHERE a IN 
340
 
  (0x2ffffffffffffffe, 
341
 
   0x2fffffffffffffff);
342
 
SELECT HEX(a) FROM t2 WHERE a IN 
343
 
  (0x2ffffffffffffffe, 
344
 
   0x2fffffffffffffff,
345
 
   'abc');
346
 
 
347
 
CREATE TABLE t3 (a BIGINT);
348
 
INSERT INTO t3 VALUES (9223372036854775551);
349
 
 
350
 
SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
351
 
 
352
 
CREATE TABLE t4 (a DATE);
353
 
INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
354
 
SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
355
 
 
356
 
DROP TABLE t1,t2,t3,t4;
357
 
 
358
 
#
359
 
# BUG#27362: IN with a decimal expression that may return NULL
360
 
#
361
 
 
362
 
CREATE TABLE t1 (id int not null);
363
 
INSERT INTO t1 VALUES (1),(2);
364
 
 
365
 
--error ER_DIVISION_BY_ZERO
366
 
SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) );
367
 
 
368
 
DROP TABLE t1;
369
 
 
370
 
--echo End of 5.0 tests
371
 
 
372
 
 
373
 
#
374
 
# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result 
375
 
#
376
 
create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM;
377
 
insert into t1 values ('a'),('b'),('1');
378
 
select f1 from t1 where f1 in ('a',1);
379
 
select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
380
 
create index t1f1_idx on t1(f1);
381
 
select f1 from t1 where f1 in ('a',1);
382
 
explain select f1 from t1 where f1 in ('a',1);
383
 
select f1 from t1 where f1 in ('a','b');
384
 
explain select f1 from t1 where f1 in ('a','b');
385
 
select f1 from t1 where f1 in (2,1);
386
 
explain select f1 from t1 where f1 in (2,1);
387
 
create TEMPORARY table t2(f2 int, index t2f2(f2)) ENGINE=MYISAM;
388
 
insert into t2 values(0),(1),(2);
389
 
select f2 from t2 where f2 in ('a',2);
390
 
explain select f2 from t2 where f2 in ('a',2);
391
 
select f2 from t2 where f2 in ('a','b');
392
 
explain select f2 from t2 where f2 in ('a','b');
393
 
select f2 from t2 where f2 in (1,'b');
394
 
explain select f2 from t2 where f2 in (1,'b');
395
 
drop table t1, t2;
396
 
 
397
 
#
398
 
# Bug #31075: crash in get_func_mm_tree
399
 
#
400
 
 
401
 
create table t1 (a datetime, key(a));
402
 
insert into t1 values (),(),(),(),(),(),(),(),(),();
403
 
select a from t1 where a not in (a,a,a) group by a;
404
 
drop table t1;
405
 
 
406
 
--echo End of 5.1 tests