← 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-14 21:16:59 UTC
  • mto: This revision was merged to the branch mainline in revision 388.
  • Revision ID: monty@inaugust.com-20080914211659-nhjt4mobp3uazgt0
libdrizzle.h cleanup. Removed some unused things. Started splitting header into
file-per-struct like libmemcached is.

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