← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/t/subselect3.test

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/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 UNSIGNED 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