← 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-08-04 22:01:39 UTC
  • mto: (261.1.4 drizzle)
  • mto: This revision was merged to the branch mainline in revision 262.
  • Revision ID: monty@inaugust.com-20080804220139-fy862jc9lykayvka
Moved libdrizzle.ver.in to libdrizzle.ver.

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
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
304
 
 
305
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
306
 
 
307
 
 
308
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
309
 
 
310
 
select oref, a from t2 where 
311
 
  a in (select min(ie) from t1 where oref=t2.oref group by grp);
312
 
  
313
 
select oref, a from t2 where 
314
 
  a not in (select min(ie) from t1 where oref=t2.oref group by grp);
315
 
 
316
 
#
317
 
update t1 set ie=3 where oref='ff' and ie=1;
318
 
 
319
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
320
 
grp) Z from t2;
321
 
 
322
 
 
323
 
select oref, a from t2 where a in (select min(ie) from t1 where
324
 
oref=t2.oref group by grp);
325
 
 
326
 
select oref, a from t2 where a not in (select min(ie) from t1 where
327
 
oref=t2.oref group by grp);
328
 
 
329
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
330
 
grp having min(ie) > 1) Z from t2;
331
 
 
332
 
select oref, a from t2 where a in (select min(ie) from t1 where
333
 
oref=t2.oref group by grp having min(ie) > 1);
334
 
  
335
 
select oref, a from t2 where a not in (select min(ie) from t1 where
336
 
oref=t2.oref group by grp having min(ie) > 1);
337
 
 
338
 
#
339
 
alter table t1 add index idx(ie);
340
 
 
341
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
342
 
 
343
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
344
 
 
345
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
346
 
 
347
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
348
 
 
349
 
 
350
 
alter table t1 drop index idx;
351
 
alter table t1 add index idx(oref,ie);
352
 
 
353
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
354
 
 
355
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
356
 
 
357
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
358
 
 
359
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
360
 
 
361
 
explain 
362
 
select oref, a, 
363
 
       a in (select min(ie) from t1 where oref=t2.oref 
364
 
             group by grp having min(ie) > 1) Z 
365
 
from t2;
366
 
 
367
 
select oref, a, 
368
 
       a in (select min(ie) from t1 where oref=t2.oref 
369
 
             group by grp having min(ie) > 1) Z 
370
 
from t2;
371
 
 
372
 
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
373
 
                                   group by grp having min(ie) > 1);
374
 
  
375
 
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
376
 
                                       group by grp having min(ie) > 1);
377
 
 
378
 
drop table t1,t2;
379
 
 
380
 
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
381
 
insert into t1 (oref, grp, ie1, ie2) values
382
 
 ('aa', 10, 2, 1),
383
 
 ('aa', 10, 1, 1),
384
 
 ('aa', 20, 2, 1),
385
 
 
386
 
 ('bb', 10, 3, 1),
387
 
 
388
 
 ('cc', 10, 4, 2),
389
 
 ('cc', 20, 3, 2),
390
 
 
391
 
 ('ee', 10, 2, 1),
392
 
 ('ee', 10, 1, 2),
393
 
 
394
 
 ('ff', 20, 2, 2),
395
 
 ('ff', 20, 1, 2);
396
 
 
397
 
create table t2 (oref char(4), a int, b int);
398
 
insert into t2 values 
399
 
  ('ee', NULL, 1),
400
 
  ('bb', 2, 1),
401
 
  ('ff', 2, 2),
402
 
  ('cc', 3, NULL),
403
 
  ('bb', NULL, NULL),
404
 
  ('aa', 1, 1),
405
 
  ('dd', 1, NULL);
406
 
 
407
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
408
 
 
409
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
410
 
 
411
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
412
 
 
413
 
select oref, a, b, 
414
 
             (a,b) in (select min(ie1),max(ie2) from t1 
415
 
                       where oref=t2.oref group by grp) Z 
416
 
from t2;
417
 
 
418
 
select oref, a, b from t2 where 
419
 
  (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
420
 
  
421
 
select oref, a, b from t2 where
422
 
  (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
423
 
 
424
 
alter table t1 add index idx(ie1,ie2);
425
 
 
426
 
explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
427
 
 
428
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
429
 
 
430
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
431
 
 
432
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
433
 
 
434
 
explain extended 
435
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
436
 
 
437
 
drop table t1,t2;
438
 
 
439
 
create table t1 (oref char(4), grp int, ie int primary key);
440
 
insert into t1 (oref, grp, ie) values
441
 
 ('aa', 10, 2),
442
 
 ('aa', 10, 1),
443
 
 
444
 
 ('bb', 10, 3),
445
 
 
446
 
 ('cc', 10, 4),
447
 
 ('cc', 20, 5),
448
 
 ('cc', 10, 6);
449
 
 
450
 
create table t2 (oref char(4), a int);
451
 
insert into t2 values 
452
 
  ('ee', NULL),
453
 
  ('bb', 2),
454
 
  ('cc', 5),
455
 
  ('cc', 2),
456
 
  ('cc', NULL),
457
 
  ('aa', 1),
458
 
  ('bb', NULL);
459
 
 
460
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
461
 
 
462
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
463
 
 
464
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
465
 
 
466
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
467
 
 
468
 
explain 
469
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
470
 
 
471
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
472
 
 
473
 
drop table t1,t2;
474
 
 
475
 
#
476
 
# BUG#24420: row-based IN suqueries with aggregation when the left operand
477
 
#            of the subquery predicate may contain NULL values
478
 
#
479
 
 
480
 
create table t1 (a int, b int);
481
 
insert into t1 values (0,0), (2,2), (3,3);
482
 
create table t2 (a int, b int);
483
 
insert into t2 values (1,1), (3,3);
484
 
 
485
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
486
 
 
487
 
insert into t2 values (NULL,4);
488
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
489
 
 
490
 
drop table t1,t2;
491
 
 
492
 
#
493
 
# Bug #24484: Aggregate function used in column list subquery gives erroneous 
494
 
# error
495
 
#
496
 
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
497
 
INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
498
 
  (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
499
 
  (1,9,'m');
500
 
CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
501
 
INSERT INTO t2 SELECT * FROM t1;
502
 
 
503
 
# Gives error, but should work since it is (a, b) is the PK so only one 
504
 
# given match possible
505
 
SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
506
 
  as test FROM t1 GROUP BY a;
507
 
SELECT * FROM t1 GROUP by t1.a
508
 
  HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
509
 
                                                HAVING MAX(t2.b+t1.a) < 10));
510
 
 
511
 
SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
512
 
 
513
 
SELECT a, MAX(b),
514
 
 (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 
515
 
  LIMIT 1) 
516
 
  as cnt, 
517
 
 (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
518
 
  as t_b,
519
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
520
 
  as t_b,
521
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
522
 
  as t_b
523
 
 FROM t1 GROUP BY a;
524
 
 
525
 
SELECT a, MAX(b),
526
 
 (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
527
 
 FROM t1 GROUP BY a;
528
 
 
529
 
 
530
 
DROP TABLE t1, t2;
531
 
 
532
 
 
533
 
#
534
 
# Bug #27870: crash of an equijoin query with WHERE condition containing 
535
 
#             a subquery predicate of the form <join attr> NOT IN (SELECT ...)
536
 
#
537
 
 
538
 
CREATE TABLE t1 (a int);
539
 
CREATE TABLE t2 (b int, PRIMARY KEY(b));
540
 
INSERT INTO t1 VALUES (1), (NULL), (4);
541
 
INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
542
 
 
543
 
EXPLAIN EXTENDED 
544
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
545
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
546
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
547
 
 
548
 
DROP TABLE t1,t2;
549
 
 
550
 
#
551
 
# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL  
552
 
#
553
 
 
554
 
CREATE TABLE t1 (id int);
555
 
CREATE TABLE t2 (id int PRIMARY KEY);
556
 
CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
557
 
INSERT INTO t1 VALUES (2), (NULL), (3), (1);
558
 
INSERT INTO t2 VALUES (234), (345), (457);
559
 
INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
560
 
 
561
 
EXPLAIN
562
 
SELECT * FROM t1
563
 
  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
564
 
                        WHERE t3.name='xxx' AND t2.id=t3.id);
565
 
SELECT * FROM t1
566
 
  WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
567
 
                        WHERE t3.name='xxx' AND t2.id=t3.id);
568
 
 
569
 
SELECT (t1.id IN (SELECT t2.id FROM t2,t3 
570
 
                    WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
571
 
  FROM t1;
572
 
 
573
 
DROP TABLE t1,t2,t3;   
574
 
 
575
 
#
576
 
# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated 
577
 
# subquery 
578
 
#
579
 
CREATE TABLE t1 (a INT NOT NULL);
580
 
INSERT INTO t1 VALUES (1),(-1), (65),(66);
581
 
 
582
 
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY);
583
 
INSERT INTO t2 VALUES (65),(66);
584
 
 
585
 
SELECT a FROM t1 WHERE a NOT IN (65,66);
586
 
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
587
 
EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
588
 
 
589
 
DROP TABLE t1, t2;
590
 
 
591
 
#
592
 
# Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer:
593
 
#             Assertion failed, unexpected error message:
594
 
#             ERROR 1247 (42S22): Reference '<list ref>' not supported (forward
595
 
#             reference in item list)
596
 
#
597
 
CREATE TABLE t1 (a INT);
598
 
INSERT INTO t1 VALUES(1);
599
 
 
600
 
CREATE TABLE t2 (placeholder CHAR(11));
601
 
INSERT INTO t2 VALUES("placeholder");
602
 
 
603
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2)         FROM t1 GROUP BY t1.a;
604
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a;
605
 
 
606
 
DROP TABLE t1, t2;
607
 
 
608
 
#
609
 
# Bug 2198
610
 
#
611
 
 
612
 
create table t1 (a int, b decimal(13, 3)); 
613
 
insert into t1 values (1, 0.123);
614
 
select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1;
615
 
delete from t1;
616
 
load data infile "subselect.out.file.1" into table t1;
617
 
select * from t1;
618
 
drop table t1;
619
 
 
620
 
--echo End of 5.0 tests