← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect3.test

  • Committer: Monty Taylor
  • Date: 2008-09-16 00:00:48 UTC
  • mto: This revision was merged to the branch mainline in revision 391.
  • Revision ID: monty@inaugust.com-20080916000048-3rvrv3gv9l0ad3gs
Fixed copyright headers in drizzled/

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect3.test
1
 
--disable_warnings
2
 
drop table if exists t0, t1, t2, t3, t4;
3
 
--enable_warnings
4
 
 
5
 
#
6
 
# 1. Subquery with GROUP/HAVING
7
 
#
8
 
create table t1 (oref int, grp int, ie int) ;
9
 
insert into t1 (oref, grp, ie) values
10
 
 (1, 1, 1),
11
 
 (1, 1, 1),
12
 
 (1, 2, NULL),
13
 
 
14
 
 (2, 1, 3),
15
 
 
16
 
 (3, 1, 4),
17
 
 (3, 2, NULL);
18
 
 
19
 
# Ok, for
20
 
#   select max(ie) from t1 where oref=PARAM group by grp
21
 
# we'll have:
22
 
# PARAM      subquery result  
23
 
#   1   ->   {(1), (NULL)}    matching + NULL
24
 
#   2   ->   {(3)}            non-matching
25
 
#   3   ->   {(3), (NULL)}    non-matching + NULL
26
 
#   4   ->   {}               empty set
27
 
 
28
 
create table t2 (oref int, a int);
29
 
insert into t2 values 
30
 
  (1, 1),
31
 
  (2, 2),
32
 
  (3, 3),
33
 
  (4, NULL),
34
 
  (2, NULL);
35
 
 
36
 
# true, false, null, false, null
37
 
select a, oref, a in (select max(ie) 
38
 
  from t1 where oref=t2.oref group by grp) Z from t2;
39
 
 
40
 
# This must have a trigcond
41
 
explain extended
42
 
select a, oref, a in (select max(ie) 
43
 
  from t1 where oref=t2.oref group by grp) Z from t2;
44
 
 
45
 
# This must not have a trigcond:
46
 
explain extended
47
 
select a, oref from t2 
48
 
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
49
 
select a, oref, a in (
50
 
  select max(ie) from t1 where oref=t2.oref group by grp union
51
 
  select max(ie) from t1 where oref=t2.oref group by grp
52
 
  ) Z from t2;
53
 
 
54
 
# Non-correlated subquery, 2 NULL evaluations
55
 
create table t3 (a int);
56
 
insert into t3 values (NULL), (NULL);
57
 
flush status;
58
 
select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
59
 
show status like 'Handler_read_rnd_next';
60
 
select ' ^ This must show 11' Z;
61
 
 
62
 
# This must show trigcond:
63
 
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
64
 
 
65
 
drop table t1, t2, t3;
66
 
 
67
 
# 
68
 
#  2. Subquery handled with 'index_subquery':
69
 
# 
70
 
create table t1 (a int, oref int, key(a));
71
 
insert into t1 values 
72
 
  (1, 1),
73
 
  (1, NULL),
74
 
  (2, 3),
75
 
  (2, NULL),
76
 
  (3, NULL);
77
 
 
78
 
create table t2 (a int, oref int);
79
 
insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
80
 
 
81
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
82
 
 
83
 
# The next explain shows "using index" but that is just incorrect display
84
 
# (there is a bug filed about this).
85
 
explain extended 
86
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
87
 
 
88
 
flush status;
89
 
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
90
 
# This will only show access to t2:
91
 
show status like '%Handler_read_rnd_next';
92
 
 
93
 
# Check that repeated NULL-scans are not cached (subq. is not correlated):
94
 
delete from t2;
95
 
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
96
 
 
97
 
flush status;
98
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
99
 
show status like '%Handler_read%';
100
 
select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
101
 
 
102
 
drop table t1, t2;
103
 
 
104
 
#
105
 
# 3. Subquery handled with 'unique_index_subquery':
106
 
#
107
 
create table t1 (a int, b int, primary key (a));
108
 
insert into t1 values (1,1), (3,1),(100,1);
109
 
 
110
 
create table t2 (a int, b int);
111
 
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
112
 
 
113
 
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
114
 
 
115
 
drop table t1, t2;
116
 
 
117
 
#
118
 
# 4. Subquery that is a join, with ref access
119
 
#
120
 
create table t1 (a int, b int, key(a));
121
 
insert into t1 values 
122
 
  (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
123
 
 
124
 
create table t2 like t1;
125
 
insert into t2 select * from t1;
126
 
update t2 set b=1;
127
 
 
128
 
create table t3 (a int, oref int);
129
 
insert into t3 values (1, 1), (NULL,1), (NULL,0);
130
 
select a, oref, 
131
 
       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
132
 
from t3;
133
 
 
134
 
# This must have trigcond in WHERE and HAVING:
135
 
explain extended
136
 
select a, oref, 
137
 
       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
138
 
from t3;
139
 
 
140
 
drop table t1, t2, t3;
141
 
 
142
 
 
143
 
#
144
 
# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
145
 
#
146
 
 
147
 
# case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
148
 
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
149
 
insert into t1 values 
150
 
  (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
151
 
 
152
 
create table t2 like t1;
153
 
insert into t2 select * from t1;
154
 
update t2 set b=1;
155
 
 
156
 
create table t3 (a int, oref int);
157
 
insert into t3 values (1, 1), (NULL,1), (NULL,0);
158
 
select a, oref, 
159
 
       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
160
 
from t3;
161
 
 
162
 
--echo This must show a trig_cond:
163
 
explain extended
164
 
select a, oref, 
165
 
       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
166
 
from t3;
167
 
drop table t1,t2,t3;
168
 
 
169
 
 
170
 
# case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY
171
 
create table t1 (oref int, grp int);
172
 
insert into t1 (oref, grp) values
173
 
 (1, 1),
174
 
 (1, 1);
175
 
 
176
 
# Ok, for  
177
 
#   select count(*) from t1 group by grp having grp=PARAM
178
 
# we'll have:
179
 
#  PARAM    subuqery result
180
 
#    1  ->    {(2)}   
181
 
#    2  ->    {} - empty set
182
 
create table t2 (oref int, a int);
183
 
insert into t2 values 
184
 
  (1, NULL),
185
 
  (2, NULL);
186
 
 
187
 
select a, oref, 
188
 
       a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
189
 
 
190
 
--echo This must show a trig_cond:
191
 
explain extended
192
 
select a, oref, 
193
 
       a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
194
 
 
195
 
drop table t1, t2;
196
 
 
197
 
create table t1 (a int, b int, primary key (a));
198
 
insert into t1 values (1,1), (3,1),(100,1);
199
 
create table t2 (a int, b int);
200
 
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
201
 
 
202
 
select a,b, a in (select a from t1 where t1.b = t2.b union select a from
203
 
t1 where t1.b = t2.b) Z from t2 ;
204
 
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
205
 
drop table t1, t2;
206
 
 
207
 
 
208
 
#
209
 
# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side.
210
 
#
211
 
create table t3 (a int);
212
 
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
213
 
create table t2 (a int, b int, oref int);
214
 
insert into t2 values (NULL,1, 100), (NULL,2, 100);
215
 
 
216
 
create table t1 (a int, b int, c int, key(a,b));
217
 
insert into t1 select 2*A, 2*A, 100 from t3;
218
 
 
219
 
# First test index subquery engine
220
 
explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
221
 
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
222
 
 
223
 
# Then check that we do turn off 'ref' scans in the subquery
224
 
create table t4 (x int);
225
 
insert into t4 select A.a + 10*B.a from t1 A, t1 B;
226
 
explain extended 
227
 
  select a,b, oref, 
228
 
         (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
229
 
  from t2;
230
 
select a,b, oref, 
231
 
       (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
232
 
from t2;
233
 
 
234
 
drop table t1,t2,t3,t4;
235
 
 
236
 
# More tests for tricky multi-column cases, where some of pushed-down
237
 
# equalities are used for index lookups and some arent.
238
 
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
239
 
insert into t1 (oref, grp, ie1, ie2) values
240
 
 ('aa', 10, 2, 1),
241
 
 ('aa', 10, 1, 1),
242
 
 ('aa', 20, 2, 1),
243
 
 ('bb', 10, 3, 1),
244
 
 ('cc', 10, 4, 2),
245
 
 ('cc', 20, 3, 2),
246
 
 
247
 
 ('ee', 10, 2, 1),
248
 
 ('ee', 10, 1, 2),
249
 
 
250
 
 ('ff', 20, 2, 2),
251
 
 ('ff', 20, 1, 2);
252
 
create table t2 (oref char(4), a int, b int);
253
 
insert into t2 values 
254
 
  ('ee', NULL, 1),
255
 
  ('bb', 2, 1),
256
 
  ('ff', 2, 2),
257
 
  ('cc', 3, NULL),
258
 
  ('bb', NULL, NULL),
259
 
  ('aa', 1, 1),
260
 
  ('dd', 1, NULL);
261
 
alter table t1 add index idx(ie1,ie2);
262
 
 
263
 
# cc 3 NULL NULL
264
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
265
 
insert into t2 values ('new1', 10,10);
266
 
insert into t1 values ('new1', 1234, 10, NULL); 
267
 
# new1, 10, 10, NULL,
268
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
269
 
explain extended
270
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
271
 
drop table t1, t2;
272
 
 
273
 
# Now test different column types:
274
 
create table t1 (oref char(4), grp int, ie int);
275
 
insert into t1 (oref, grp, ie) values
276
 
 ('aa', 10, 2),
277
 
 ('aa', 10, 1),
278
 
 ('aa', 20, NULL),
279
 
 
280
 
 ('bb', 10, 3),
281
 
 
282
 
 ('cc', 10, 4),
283
 
 ('cc', 20, NULL),
284
 
 
285
 
 ('ee', 10, NULL),
286
 
 ('ee', 10, NULL),
287
 
 
288
 
 ('ff', 20, 2),
289
 
 ('ff', 20, 1);
290
 
 
291
 
create table t2 (oref char(4), a int);
292
 
insert into t2 values 
293
 
  ('ee', NULL),
294
 
  ('bb', 2),
295
 
  ('ff', 2),
296
 
  ('cc', 3),
297
 
  ('aa', 1),
298
 
  ('dd', NULL),
299
 
  ('bb', NULL);
300
 
 
301
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
302
 
 
303
 
--sorted_result
304
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
305
 
 
306
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
307
 
 
308
 
 
309
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
310
 
 
311
 
select oref, a from t2 where 
312
 
  a in (select min(ie) from t1 where oref=t2.oref group by grp);
313
 
  
314
 
select oref, a from t2 where 
315
 
  a not in (select min(ie) from t1 where oref=t2.oref group by grp);
316
 
 
317
 
#
318
 
update t1 set ie=3 where oref='ff' and ie=1;
319
 
 
320
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
321
 
grp) Z from t2;
322
 
 
323
 
 
324
 
select oref, a from t2 where a in (select min(ie) from t1 where
325
 
oref=t2.oref group by grp);
326
 
 
327
 
select oref, a from t2 where a not in (select min(ie) from t1 where
328
 
oref=t2.oref group by grp);
329
 
 
330
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
331
 
grp having min(ie) > 1) Z from t2;
332
 
 
333
 
select oref, a from t2 where a in (select min(ie) from t1 where
334
 
oref=t2.oref group by grp having min(ie) > 1);
335
 
  
336
 
select oref, a from t2 where a not in (select min(ie) from t1 where
337
 
oref=t2.oref group by grp having min(ie) > 1);
338
 
 
339
 
#
340
 
alter table t1 add index idx(ie);
341
 
 
342
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
343
 
 
344
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
345
 
 
346
 
--sorted_result
347
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
348
 
 
349
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
350
 
 
351
 
 
352
 
alter table t1 drop index idx;
353
 
alter table t1 add index idx(oref,ie);
354
 
 
355
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
356
 
 
357
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
358
 
 
359
 
--sorted_result
360
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
361
 
 
362
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
363
 
 
364
 
explain 
365
 
select oref, a, 
366
 
       a in (select min(ie) from t1 where oref=t2.oref 
367
 
             group by grp having min(ie) > 1) Z 
368
 
from t2;
369
 
 
370
 
select oref, a, 
371
 
       a in (select min(ie) from t1 where oref=t2.oref 
372
 
             group by grp having min(ie) > 1) Z 
373
 
from t2;
374
 
 
375
 
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
376
 
                                   group by grp having min(ie) > 1);
377
 
  
378
 
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
379
 
                                       group by grp having min(ie) > 1);
380
 
 
381
 
drop table t1,t2;
382
 
 
383
 
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
384
 
insert into t1 (oref, grp, ie1, ie2) values
385
 
 ('aa', 10, 2, 1),
386
 
 ('aa', 10, 1, 1),
387
 
 ('aa', 20, 2, 1),
388
 
 
389
 
 ('bb', 10, 3, 1),
390
 
 
391
 
 ('cc', 10, 4, 2),
392
 
 ('cc', 20, 3, 2),
393
 
 
394
 
 ('ee', 10, 2, 1),
395
 
 ('ee', 10, 1, 2),
396
 
 
397
 
 ('ff', 20, 2, 2),
398
 
 ('ff', 20, 1, 2);
399
 
 
400
 
create table t2 (oref char(4), a int, b int);
401
 
insert into t2 values 
402
 
  ('ee', NULL, 1),
403
 
  ('bb', 2, 1),
404
 
  ('ff', 2, 2),
405
 
  ('cc', 3, NULL),
406
 
  ('bb', NULL, NULL),
407
 
  ('aa', 1, 1),
408
 
  ('dd', 1, NULL);
409
 
 
410
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
411
 
 
412
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
413
 
 
414
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
415
 
 
416
 
select oref, a, b, 
417
 
             (a,b) in (select min(ie1),max(ie2) from t1 
418
 
                       where oref=t2.oref group by grp) Z 
419
 
from t2;
420
 
 
421
 
select oref, a, b from t2 where 
422
 
  (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
423
 
  
424
 
select oref, a, b from t2 where
425
 
  (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
426
 
 
427
 
alter table t1 add index idx(ie1,ie2);
428
 
 
429
 
explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
430
 
 
431
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
432
 
 
433
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
434
 
 
435
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
436
 
 
437
 
explain extended 
438
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
439
 
 
440
 
drop table t1,t2;
441
 
 
442
 
create table t1 (oref char(4), grp int, ie int primary key);
443
 
insert into t1 (oref, grp, ie) values
444
 
 ('aa', 10, 2),
445
 
 ('aa', 10, 1),
446
 
 
447
 
 ('bb', 10, 3),
448
 
 
449
 
 ('cc', 10, 4),
450
 
 ('cc', 20, 5),
451
 
 ('cc', 10, 6);
452
 
 
453
 
create table t2 (oref char(4), a int);
454
 
insert into t2 values 
455
 
  ('ee', NULL),
456
 
  ('bb', 2),
457
 
  ('cc', 5),
458
 
  ('cc', 2),
459
 
  ('cc', NULL),
460
 
  ('aa', 1),
461
 
  ('bb', NULL);
462
 
 
463
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
464
 
 
465
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
466
 
 
467
 
--sorted_result
468
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
469
 
 
470
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
471
 
 
472
 
explain 
473
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
474
 
 
475
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
476
 
 
477
 
drop table t1,t2;
478
 
 
479
 
#
480
 
# BUG#24420: row-based IN suqueries with aggregation when the left operand
481
 
#            of the subquery predicate may contain NULL values
482
 
#
483
 
 
484
 
create table t1 (a int, b int);
485
 
insert into t1 values (0,0), (2,2), (3,3);
486
 
create table t2 (a int, b int);
487
 
insert into t2 values (1,1), (3,3);
488
 
 
489
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
490
 
 
491
 
insert into t2 values (NULL,4);
492
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
493
 
 
494
 
drop table t1,t2;
495
 
 
496
 
#
497
 
# Bug #24484: Aggregate function used in column list subquery gives erroneous 
498
 
# error
499
 
#
500
 
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
501
 
INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
502
 
  (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
503
 
  (1,9,'m');
504
 
CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
505
 
INSERT INTO t2 SELECT * FROM t1;
506
 
 
507
 
# Gives error, but should work since it is (a, b) is the PK so only one 
508
 
# given match possible
509
 
SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
510
 
  as test FROM t1 GROUP BY a;
511
 
SELECT * FROM t1 GROUP by t1.a
512
 
  HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
513
 
                                                HAVING MAX(t2.b+t1.a) < 10));
514
 
 
515
 
SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
516
 
 
517
 
SELECT a, MAX(b),
518
 
 (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 
519
 
  LIMIT 1) 
520
 
  as cnt, 
521
 
 (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
522
 
  as t_b,
523
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
524
 
  as t_b,
525
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
526
 
  as t_b
527
 
 FROM t1 GROUP BY a;
528
 
 
529
 
SELECT a, MAX(b),
530
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
531
 
 FROM t1 GROUP BY a;
532
 
 
533
 
 
534
 
DROP TABLE t1, t2;
535
 
 
536
 
 
537
 
#
538
 
# Bug #27870: crash of an equijoin query with WHERE condition containing 
539
 
#             a subquery predicate of the form <join attr> NOT IN (SELECT ...)
540
 
#
541
 
 
542
 
CREATE TABLE t1 (a int);
543
 
CREATE TABLE t2 (b int, PRIMARY KEY(b));
544
 
INSERT INTO t1 VALUES (1), (NULL), (4);
545
 
INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
546
 
 
547
 
EXPLAIN EXTENDED 
548
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
549
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
550
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
551
 
 
552
 
DROP TABLE t1,t2;
553
 
 
554
 
#
555
 
# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL  
556
 
#
557
 
 
558
 
CREATE TABLE t1 (id int);
559
 
CREATE TABLE t2 (id int PRIMARY KEY);
560
 
CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
561
 
INSERT INTO t1 VALUES (2), (NULL), (3), (1);
562
 
INSERT INTO t2 VALUES (234), (345), (457);
563
 
INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
564
 
 
565
 
EXPLAIN
566
 
SELECT * FROM t1
567
 
  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
568
 
                        WHERE t3.name='xxx' AND t2.id=t3.id);
569
 
SELECT * FROM t1
570
 
  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
571
 
                        WHERE t3.name='xxx' AND t2.id=t3.id);
572
 
 
573
 
SELECT (t1.id IN (SELECT t2.id FROM t2,t3 
574
 
                    WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
575
 
  FROM t1;
576
 
 
577
 
DROP TABLE t1,t2,t3;   
578
 
 
579
 
#
580
 
# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated 
581
 
# subquery 
582
 
#
583
 
CREATE TABLE t1 (a INT NOT NULL);
584
 
INSERT INTO t1 VALUES (1),(-1), (65),(66);
585
 
 
586
 
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY);
587
 
INSERT INTO t2 VALUES (65),(66);
588
 
 
589
 
SELECT a FROM t1 WHERE a NOT IN (65,66);
590
 
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
591
 
EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
592
 
 
593
 
DROP TABLE t1, t2;
594
 
 
595
 
#
596
 
# Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer:
597
 
#             Assertion failed, unexpected error message:
598
 
#             ERROR 1247 (42S22): Reference '<list ref>' not supported (forward
599
 
#             reference in item list)
600
 
#
601
 
CREATE TABLE t1 (a INT);
602
 
INSERT INTO t1 VALUES(1);
603
 
 
604
 
CREATE TABLE t2 (placeholder CHAR(11));
605
 
INSERT INTO t2 VALUES("placeholder");
606
 
 
607
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2)         FROM t1 GROUP BY t1.a;
608
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a;
609
 
 
610
 
DROP TABLE t1, t2;
611
 
 
612
 
#
613
 
# Bug 2198
614
 
#
615
 
 
616
 
create table t1 (a int, b decimal(13, 3)); 
617
 
insert into t1 values (1, 0.123);
618
 
select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1;
619
 
delete from t1;
620
 
load data infile "subselect.out.file.1" into table t1;
621
 
select * from t1;
622
 
drop table t1;
623
 
 
624
 
--echo End of 5.0 tests