← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/func_in.test

  • Committer: Brian Aker
  • Date: 2008-07-28 18:01:38 UTC
  • Revision ID: brian@tangent.org-20080728180138-q2pxlq0qiapvqsdn
Remove YEAR field type

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