← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/func_in.test

  • Committer: Toru Maesaka
  • Date: 2008-07-17 05:59:20 UTC
  • mto: (202.1.1 toru)
  • mto: This revision was merged to the branch mainline in revision 204.
  • Revision ID: dev@torum.net-20080717055920-10okif50x6nh7b1d
forgot to bzr-add new files in the previous push

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(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 int(5),
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 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 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