← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/pbxt/subselect3.result

  • Committer: Monty Taylor
  • Date: 2008-09-15 17:24:04 UTC
  • Revision ID: monty@inaugust.com-20080915172404-ygh6hiyu0q7qpa9x
Removed strndup calls.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/pbxt/subselect3.result
1
 
drop table if exists t0, t1, t2, t3, t4;
2
 
create table t1 (oref int, grp int, ie int) ;
3
 
insert into t1 (oref, grp, ie) values
4
 
(1, 1, 1),
5
 
(1, 1, 1),
6
 
(1, 2, NULL),
7
 
(2, 1, 3),
8
 
(3, 1, 4),
9
 
(3, 2, NULL);
10
 
create table t2 (oref int, a int);
11
 
insert into t2 values 
12
 
(1, 1),
13
 
(2, 2),
14
 
(3, 3),
15
 
(4, NULL),
16
 
(2, NULL);
17
 
select a, oref, a in (select max(ie) 
18
 
from t1 where oref=t2.oref group by grp) Z from t2;
19
 
a       oref    Z
20
 
1       1       1
21
 
2       2       0
22
 
3       3       NULL
23
 
NULL    4       0
24
 
NULL    2       NULL
25
 
explain extended
26
 
select a, oref, a in (select max(ie) 
27
 
from t1 where oref=t2.oref group by grp) Z from t2;
28
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
29
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  
30
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Using temporary; Using filesort
31
 
Warnings:
32
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
33
 
Note    1003    select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
34
 
explain extended
35
 
select a, oref from t2 
36
 
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
37
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
38
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where
39
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Using temporary; Using filesort
40
 
Warnings:
41
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
42
 
Note    1003    select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
43
 
select a, oref, a in (
44
 
select max(ie) from t1 where oref=t2.oref group by grp union
45
 
select max(ie) from t1 where oref=t2.oref group by grp
46
 
) Z from t2;
47
 
a       oref    Z
48
 
1       1       1
49
 
2       2       0
50
 
3       3       NULL
51
 
NULL    4       0
52
 
NULL    2       NULL
53
 
create table t3 (a int);
54
 
insert into t3 values (NULL), (NULL);
55
 
flush status;
56
 
select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
57
 
a in (select max(ie) from t1 where oref=4 group by grp)
58
 
0
59
 
0
60
 
show status like 'Handler_read_rnd_next';
61
 
Variable_name   Value
62
 
Handler_read_rnd_next   1
63
 
select ' ^ This must show 11' Z;
64
 
Z
65
 
 ^ This must show 11
66
 
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
67
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
68
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    2       100.00  
69
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       100.00  Using where; Using temporary; Using filesort
70
 
Warnings:
71
 
Note    1003    select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
72
 
drop table t1, t2, t3;
73
 
create table t1 (a int, oref int, key(a));
74
 
insert into t1 values 
75
 
(1, 1),
76
 
(1, NULL),
77
 
(2, 3),
78
 
(2, NULL),
79
 
(3, NULL);
80
 
create table t2 (a int, oref int);
81
 
insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
82
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
83
 
oref    a       Z
84
 
1       1       1
85
 
2       2       0
86
 
3       NULL    NULL
87
 
4       NULL    0
88
 
explain extended 
89
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
90
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
91
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    4       100.00  
92
 
2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    2       100.00  Using where; Full scan on NULL key
93
 
Warnings:
94
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
95
 
Note    1003    select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
96
 
flush status;
97
 
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
98
 
oref    a
99
 
1       1
100
 
show status like '%Handler_read_rnd_next';
101
 
Variable_name   Value
102
 
Handler_read_rnd_next   0
103
 
delete from t2;
104
 
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
105
 
flush status;
106
 
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
107
 
oref    a       Z
108
 
0       NULL    0
109
 
0       NULL    0
110
 
0       NULL    0
111
 
0       NULL    0
112
 
show status like '%Handler_read%';
113
 
Variable_name   Value
114
 
Handler_read_first      0
115
 
Handler_read_key        0
116
 
Handler_read_next       0
117
 
Handler_read_prev       0
118
 
Handler_read_rnd        0
119
 
Handler_read_rnd_next   0
120
 
select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
121
 
Z
122
 
No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
123
 
drop table t1, t2;
124
 
create table t1 (a int, b int, primary key (a));
125
 
insert into t1 values (1,1), (3,1),(100,1);
126
 
create table t2 (a int, b int);
127
 
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
128
 
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
129
 
a       b       Z
130
 
1       1       1
131
 
2       1       0
132
 
NULL    1       NULL
133
 
NULL    0       0
134
 
drop table t1, t2;
135
 
create table t1 (a int, b int, key(a));
136
 
insert into t1 values 
137
 
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
138
 
create table t2 like t1;
139
 
insert into t2 select * from t1;
140
 
update t2 set b=1;
141
 
create table t3 (a int, oref int);
142
 
insert into t3 values (1, 1), (NULL,1), (NULL,0);
143
 
select a, oref, 
144
 
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
145
 
from t3;
146
 
a       oref    Z
147
 
1       1       1
148
 
NULL    1       NULL
149
 
NULL    0       0
150
 
explain extended
151
 
select a, oref, 
152
 
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
153
 
from t3;
154
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
155
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  
156
 
2       DEPENDENT SUBQUERY      t1      ref_or_null     a       a       5       func    4       100.00  Using where; Full scan on NULL key
157
 
2       DEPENDENT SUBQUERY      t2      ALL     a       NULL    NULL    NULL    10      80.00   Using where; Using join buffer
158
 
Warnings:
159
 
Note    1276    Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
160
 
Note    1003    select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
161
 
drop table t1, t2, t3;
162
 
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
163
 
insert into t1 values 
164
 
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
165
 
create table t2 like t1;
166
 
insert into t2 select * from t1;
167
 
update t2 set b=1;
168
 
create table t3 (a int, oref int);
169
 
insert into t3 values (1, 1), (NULL,1), (NULL,0);
170
 
select a, oref, 
171
 
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
172
 
from t3;
173
 
a       oref    Z
174
 
1       1       1
175
 
NULL    1       NULL
176
 
NULL    0       0
177
 
This must show a trig_cond:
178
 
explain extended
179
 
select a, oref, 
180
 
t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
181
 
from t3;
182
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
183
 
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    3       100.00  
184
 
2       DEPENDENT SUBQUERY      t1      ref     a       a       4       func    2       100.00  Using where; Full scan on NULL key
185
 
2       DEPENDENT SUBQUERY      t2      ref     a       a       4       test.t1.b       2       100.00  Using where
186
 
Warnings:
187
 
Note    1276    Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
188
 
Note    1003    select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
189
 
drop table t1,t2,t3;
190
 
create table t1 (oref int, grp int);
191
 
insert into t1 (oref, grp) values
192
 
(1, 1),
193
 
(1, 1);
194
 
create table t2 (oref int, a int);
195
 
insert into t2 values 
196
 
(1, NULL),
197
 
(2, NULL);
198
 
select a, oref, 
199
 
a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
200
 
a       oref    Z
201
 
NULL    1       NULL
202
 
NULL    2       0
203
 
This must show a trig_cond:
204
 
explain extended
205
 
select a, oref, 
206
 
a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
207
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
208
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
209
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using temporary; Using filesort
210
 
Warnings:
211
 
Note    1276    Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
212
 
Note    1003    select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
213
 
drop table t1, t2;
214
 
create table t1 (a int, b int, primary key (a));
215
 
insert into t1 values (1,1), (3,1),(100,1);
216
 
create table t2 (a int, b int);
217
 
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
218
 
select a,b, a in (select a from t1 where t1.b = t2.b union select a from
219
 
t1 where t1.b = t2.b) Z from t2 ;
220
 
a       b       Z
221
 
1       1       1
222
 
2       1       0
223
 
NULL    1       NULL
224
 
NULL    0       0
225
 
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
226
 
a       b       Z
227
 
1       1       1
228
 
2       1       0
229
 
NULL    1       NULL
230
 
NULL    0       0
231
 
drop table t1, t2;
232
 
create table t3 (a int);
233
 
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
234
 
create table t2 (a int, b int, oref int);
235
 
insert into t2 values (NULL,1, 100), (NULL,2, 100);
236
 
create table t1 (a int, b int, c int, key(a,b));
237
 
insert into t1 select 2*A, 2*A, 100 from t3;
238
 
explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
239
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
240
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
241
 
2       DEPENDENT SUBQUERY      t1      index_subquery  a       a       5       func    4       100.00  Using where; Full scan on NULL key
242
 
Warnings:
243
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
244
 
Note    1003    select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
245
 
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
246
 
a       b       oref    Z
247
 
NULL    1       100     0
248
 
NULL    2       100     NULL
249
 
create table t4 (x int);
250
 
insert into t4 select A.a + 10*B.a from t1 A, t1 B;
251
 
explain extended 
252
 
select a,b, oref, 
253
 
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
254
 
from t2;
255
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
256
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  
257
 
2       DEPENDENT SUBQUERY      t1      ref_or_null     a       a       5       func    4       100.00  Using where; Full scan on NULL key
258
 
2       DEPENDENT SUBQUERY      t4      ALL     NULL    NULL    NULL    NULL    100     100.00  Using where; Using join buffer
259
 
Warnings:
260
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
261
 
Note    1003    select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
262
 
select a,b, oref, 
263
 
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
264
 
from t2;
265
 
a       b       oref    Z
266
 
NULL    1       100     0
267
 
NULL    2       100     NULL
268
 
drop table t1,t2,t3,t4;
269
 
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
270
 
insert into t1 (oref, grp, ie1, ie2) values
271
 
('aa', 10, 2, 1),
272
 
('aa', 10, 1, 1),
273
 
('aa', 20, 2, 1),
274
 
('bb', 10, 3, 1),
275
 
('cc', 10, 4, 2),
276
 
('cc', 20, 3, 2),
277
 
('ee', 10, 2, 1),
278
 
('ee', 10, 1, 2),
279
 
('ff', 20, 2, 2),
280
 
('ff', 20, 1, 2);
281
 
create table t2 (oref char(4), a int, b int);
282
 
insert into t2 values 
283
 
('ee', NULL, 1),
284
 
('bb', 2, 1),
285
 
('ff', 2, 2),
286
 
('cc', 3, NULL),
287
 
('bb', NULL, NULL),
288
 
('aa', 1, 1),
289
 
('dd', 1, NULL);
290
 
alter table t1 add index idx(ie1,ie2);
291
 
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 ;
292
 
oref    a       b       Z
293
 
cc      3       NULL    NULL
294
 
insert into t2 values ('new1', 10,10);
295
 
insert into t1 values ('new1', 1234, 10, NULL);
296
 
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;
297
 
oref    a       b       Z
298
 
new1    10      10      NULL
299
 
explain extended
300
 
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;
301
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
302
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    8       100.00  Using where
303
 
2       DEPENDENT SUBQUERY      t1      index_subquery  idx     idx     5       func    4       100.00  Using where; Full scan on NULL key
304
 
Warnings:
305
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
306
 
Note    1003    select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
307
 
drop table t1, t2;
308
 
create table t1 (oref char(4), grp int, ie int);
309
 
insert into t1 (oref, grp, ie) values
310
 
('aa', 10, 2),
311
 
('aa', 10, 1),
312
 
('aa', 20, NULL),
313
 
('bb', 10, 3),
314
 
('cc', 10, 4),
315
 
('cc', 20, NULL),
316
 
('ee', 10, NULL),
317
 
('ee', 10, NULL),
318
 
('ff', 20, 2),
319
 
('ff', 20, 1);
320
 
create table t2 (oref char(4), a int);
321
 
insert into t2 values 
322
 
('ee', NULL),
323
 
('bb', 2),
324
 
('ff', 2),
325
 
('cc', 3),
326
 
('aa', 1),
327
 
('dd', NULL),
328
 
('bb', NULL);
329
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
330
 
oref    a       Z
331
 
ee      NULL    NULL
332
 
bb      2       0
333
 
ff      2       1
334
 
cc      3       NULL
335
 
aa      1       1
336
 
dd      NULL    0
337
 
bb      NULL    NULL
338
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
339
 
oref    a
340
 
aa      1
341
 
ff      2
342
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
343
 
oref    a
344
 
bb      2
345
 
dd      NULL
346
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
347
 
oref    a       Z
348
 
ee      NULL    NULL
349
 
bb      2       0
350
 
ff      2       0
351
 
cc      3       NULL
352
 
aa      1       1
353
 
dd      NULL    0
354
 
bb      NULL    NULL
355
 
select oref, a from t2 where 
356
 
a in (select min(ie) from t1 where oref=t2.oref group by grp);
357
 
oref    a
358
 
aa      1
359
 
select oref, a from t2 where 
360
 
a not in (select min(ie) from t1 where oref=t2.oref group by grp);
361
 
oref    a
362
 
bb      2
363
 
ff      2
364
 
dd      NULL
365
 
update t1 set ie=3 where oref='ff' and ie=1;
366
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
367
 
grp) Z from t2;
368
 
oref    a       Z
369
 
ee      NULL    NULL
370
 
bb      2       0
371
 
ff      2       1
372
 
cc      3       NULL
373
 
aa      1       1
374
 
dd      NULL    0
375
 
bb      NULL    NULL
376
 
select oref, a from t2 where a in (select min(ie) from t1 where
377
 
oref=t2.oref group by grp);
378
 
oref    a
379
 
ff      2
380
 
aa      1
381
 
select oref, a from t2 where a not in (select min(ie) from t1 where
382
 
oref=t2.oref group by grp);
383
 
oref    a
384
 
bb      2
385
 
dd      NULL
386
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
387
 
grp having min(ie) > 1) Z from t2;
388
 
oref    a       Z
389
 
ee      NULL    0
390
 
bb      2       0
391
 
ff      2       1
392
 
cc      3       0
393
 
aa      1       0
394
 
dd      NULL    0
395
 
bb      NULL    NULL
396
 
select oref, a from t2 where a in (select min(ie) from t1 where
397
 
oref=t2.oref group by grp having min(ie) > 1);
398
 
oref    a
399
 
ff      2
400
 
select oref, a from t2 where a not in (select min(ie) from t1 where
401
 
oref=t2.oref group by grp having min(ie) > 1);
402
 
oref    a
403
 
ee      NULL
404
 
bb      2
405
 
cc      3
406
 
aa      1
407
 
dd      NULL
408
 
alter table t1 add index idx(ie);
409
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
410
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
411
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
412
 
2       DEPENDENT SUBQUERY      t1      index_subquery  idx     idx     5       func    4       Using where; Full scan on NULL key
413
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
414
 
oref    a       Z
415
 
ee      NULL    NULL
416
 
bb      2       0
417
 
ff      2       1
418
 
cc      3       NULL
419
 
aa      1       1
420
 
dd      NULL    0
421
 
bb      NULL    NULL
422
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
423
 
oref    a
424
 
aa      1
425
 
ff      2
426
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
427
 
oref    a
428
 
bb      2
429
 
dd      NULL
430
 
alter table t1 drop index idx;
431
 
alter table t1 add index idx(oref,ie);
432
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
433
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
434
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
435
 
2       DEPENDENT SUBQUERY      t1      ref_or_null     idx     idx     24      test.t2.oref,func       4       Using where; Using index; Full scan on NULL key
436
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
437
 
oref    a       Z
438
 
ee      NULL    NULL
439
 
bb      2       0
440
 
ff      2       1
441
 
cc      3       NULL
442
 
aa      1       1
443
 
dd      NULL    0
444
 
bb      NULL    NULL
445
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
446
 
oref    a
447
 
aa      1
448
 
ff      2
449
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
450
 
oref    a
451
 
bb      2
452
 
dd      NULL
453
 
explain 
454
 
select oref, a, 
455
 
a in (select min(ie) from t1 where oref=t2.oref 
456
 
group by grp having min(ie) > 1) Z 
457
 
from t2;
458
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
459
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
460
 
2       DEPENDENT SUBQUERY      t1      ref     idx     idx     19      test.t2.oref    2       Using where; Using temporary; Using filesort
461
 
select oref, a, 
462
 
a in (select min(ie) from t1 where oref=t2.oref 
463
 
group by grp having min(ie) > 1) Z 
464
 
from t2;
465
 
oref    a       Z
466
 
ee      NULL    0
467
 
bb      2       0
468
 
ff      2       1
469
 
cc      3       0
470
 
aa      1       0
471
 
dd      NULL    0
472
 
bb      NULL    NULL
473
 
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
474
 
group by grp having min(ie) > 1);
475
 
oref    a
476
 
ff      2
477
 
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
478
 
group by grp having min(ie) > 1);
479
 
oref    a
480
 
ee      NULL
481
 
bb      2
482
 
cc      3
483
 
aa      1
484
 
dd      NULL
485
 
drop table t1,t2;
486
 
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
487
 
insert into t1 (oref, grp, ie1, ie2) values
488
 
('aa', 10, 2, 1),
489
 
('aa', 10, 1, 1),
490
 
('aa', 20, 2, 1),
491
 
('bb', 10, 3, 1),
492
 
('cc', 10, 4, 2),
493
 
('cc', 20, 3, 2),
494
 
('ee', 10, 2, 1),
495
 
('ee', 10, 1, 2),
496
 
('ff', 20, 2, 2),
497
 
('ff', 20, 1, 2);
498
 
create table t2 (oref char(4), a int, b int);
499
 
insert into t2 values 
500
 
('ee', NULL, 1),
501
 
('bb', 2, 1),
502
 
('ff', 2, 2),
503
 
('cc', 3, NULL),
504
 
('bb', NULL, NULL),
505
 
('aa', 1, 1),
506
 
('dd', 1, NULL);
507
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
508
 
oref    a       b       Z
509
 
ee      NULL    1       NULL
510
 
bb      2       1       0
511
 
ff      2       2       1
512
 
cc      3       NULL    NULL
513
 
bb      NULL    NULL    NULL
514
 
aa      1       1       1
515
 
dd      1       NULL    0
516
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
517
 
oref    a       b
518
 
aa      1       1
519
 
ff      2       2
520
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
521
 
oref    a       b
522
 
bb      2       1
523
 
dd      1       NULL
524
 
select oref, a, b, 
525
 
(a,b) in (select min(ie1),max(ie2) from t1 
526
 
where oref=t2.oref group by grp) Z 
527
 
from t2;
528
 
oref    a       b       Z
529
 
ee      NULL    1       0
530
 
bb      2       1       0
531
 
ff      2       2       0
532
 
cc      3       NULL    NULL
533
 
bb      NULL    NULL    NULL
534
 
aa      1       1       1
535
 
dd      1       NULL    0
536
 
select oref, a, b from t2 where 
537
 
(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
538
 
oref    a       b
539
 
aa      1       1
540
 
select oref, a, b from t2 where
541
 
(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
542
 
oref    a       b
543
 
ee      NULL    1
544
 
bb      2       1
545
 
ff      2       2
546
 
dd      1       NULL
547
 
alter table t1 add index idx(ie1,ie2);
548
 
explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
549
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
550
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
551
 
2       DEPENDENT SUBQUERY      t1      index_subquery  idx     idx     5       func    4       Using where; Full scan on NULL key
552
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
553
 
oref    a       b       Z
554
 
ee      NULL    1       NULL
555
 
bb      2       1       0
556
 
ff      2       2       1
557
 
cc      3       NULL    NULL
558
 
bb      NULL    NULL    NULL
559
 
aa      1       1       1
560
 
dd      1       NULL    0
561
 
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
562
 
oref    a       b
563
 
aa      1       1
564
 
ff      2       2
565
 
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
566
 
oref    a       b
567
 
bb      2       1
568
 
dd      1       NULL
569
 
explain extended 
570
 
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
571
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
572
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       100.00  
573
 
2       DEPENDENT SUBQUERY      t1      index_subquery  idx     idx     5       func    4       100.00  Using where; Full scan on NULL key
574
 
Warnings:
575
 
Note    1276    Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
576
 
Note    1003    select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
577
 
drop table t1,t2;
578
 
create table t1 (oref char(4), grp int, ie int primary key);
579
 
insert into t1 (oref, grp, ie) values
580
 
('aa', 10, 2),
581
 
('aa', 10, 1),
582
 
('bb', 10, 3),
583
 
('cc', 10, 4),
584
 
('cc', 20, 5),
585
 
('cc', 10, 6);
586
 
create table t2 (oref char(4), a int);
587
 
insert into t2 values 
588
 
('ee', NULL),
589
 
('bb', 2),
590
 
('cc', 5),
591
 
('cc', 2),
592
 
('cc', NULL),
593
 
('aa', 1),
594
 
('bb', NULL);
595
 
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
596
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
597
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
598
 
2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       Using where; Full scan on NULL key
599
 
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
600
 
oref    a       Z
601
 
ee      NULL    0
602
 
bb      2       0
603
 
cc      5       1
604
 
cc      2       0
605
 
cc      NULL    NULL
606
 
aa      1       1
607
 
bb      NULL    NULL
608
 
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
609
 
oref    a
610
 
aa      1
611
 
cc      5
612
 
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
613
 
oref    a
614
 
ee      NULL
615
 
bb      2
616
 
cc      2
617
 
explain 
618
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
619
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
620
 
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    7       
621
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    6       Using where; Using temporary; Using filesort
622
 
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
623
 
oref    a       Z
624
 
ee      NULL    0
625
 
bb      2       0
626
 
cc      5       1
627
 
cc      2       0
628
 
cc      NULL    NULL
629
 
aa      1       1
630
 
bb      NULL    NULL
631
 
drop table t1,t2;
632
 
create table t1 (a int, b int);
633
 
insert into t1 values (0,0), (2,2), (3,3);
634
 
create table t2 (a int, b int);
635
 
insert into t2 values (1,1), (3,3);
636
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
637
 
a       b       Z
638
 
0       0       0
639
 
2       2       0
640
 
3       3       1
641
 
insert into t2 values (NULL,4);
642
 
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
643
 
a       b       Z
644
 
0       0       0
645
 
2       2       0
646
 
3       3       1
647
 
drop table t1,t2;
648
 
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
649
 
INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
650
 
(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
651
 
(1,9,'m');
652
 
CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
653
 
INSERT INTO t2 SELECT * FROM t1;
654
 
SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
655
 
as test FROM t1 GROUP BY a;
656
 
a       MAX(b)  test
657
 
1       9       m
658
 
2       3       h
659
 
3       4       i
660
 
SELECT * FROM t1 GROUP by t1.a
661
 
HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
662
 
HAVING MAX(t2.b+t1.a) < 10));
663
 
a       b       c
664
 
SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
665
 
a       b       c
666
 
1       3       c
667
 
2       3       h
668
 
3       3       j
669
 
1       4       d
670
 
3       4       i
671
 
1       9       m
672
 
SELECT a, MAX(b),
673
 
(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) 
674
 
LIMIT 1) 
675
 
as cnt, 
676
 
(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
677
 
as t_b,
678
 
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) 
679
 
as t_b,
680
 
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
681
 
as t_b
682
 
FROM t1 GROUP BY a;
683
 
a       MAX(b)  cnt     t_b     t_b     t_b
684
 
1       9       1       9       m       m
685
 
2       3       1       3       h       h
686
 
3       4       1       4       i       i
687
 
SELECT a, MAX(b),
688
 
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
689
 
FROM t1 GROUP BY a;
690
 
a       MAX(b)  test
691
 
1       9       m
692
 
2       3       h
693
 
3       4       i
694
 
DROP TABLE t1, t2;
695
 
CREATE TABLE t1 (a int);
696
 
CREATE TABLE t2 (b int, PRIMARY KEY(b));
697
 
INSERT INTO t1 VALUES (1), (NULL), (4);
698
 
INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
699
 
EXPLAIN EXTENDED 
700
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
701
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
702
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
703
 
1       PRIMARY t2      eq_ref  PRIMARY PRIMARY 4       test.t1.a       1       100.00  Using index
704
 
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
705
 
Warnings:
706
 
Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))
707
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
708
 
a
709
 
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
710
 
a
711
 
1
712
 
4
713
 
DROP TABLE t1,t2;
714
 
CREATE TABLE t1 (id int);
715
 
CREATE TABLE t2 (id int PRIMARY KEY);
716
 
CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
717
 
INSERT INTO t1 VALUES (2), (NULL), (3), (1);
718
 
INSERT INTO t2 VALUES (234), (345), (457);
719
 
INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
720
 
EXPLAIN
721
 
SELECT * FROM t1
722
 
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
723
 
WHERE t3.name='xxx' AND t2.id=t3.id);
724
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
725
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       Using where
726
 
2       DEPENDENT SUBQUERY      t2      eq_ref  PRIMARY PRIMARY 4       func    1       Using where; Using index; Full scan on NULL key
727
 
2       DEPENDENT SUBQUERY      t3      eq_ref  PRIMARY PRIMARY 4       func    1       Using where; Full scan on NULL key
728
 
SELECT * FROM t1
729
 
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 
730
 
WHERE t3.name='xxx' AND t2.id=t3.id);
731
 
id
732
 
2
733
 
NULL
734
 
3
735
 
1
736
 
SELECT (t1.id IN (SELECT t2.id FROM t2,t3 
737
 
WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
738
 
FROM t1;
739
 
x
740
 
0
741
 
0
742
 
0
743
 
0
744
 
DROP TABLE t1,t2,t3;
745
 
CREATE TABLE t1 (a INT NOT NULL);
746
 
INSERT INTO t1 VALUES (1),(-1), (65),(66);
747
 
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY);
748
 
INSERT INTO t2 VALUES (65),(66);
749
 
SELECT a FROM t1 WHERE a NOT IN (65,66);
750
 
a
751
 
1
752
 
-1
753
 
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
754
 
a
755
 
1
756
 
-1
757
 
EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
758
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
759
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       Using where
760
 
2       DEPENDENT SUBQUERY      t2      unique_subquery PRIMARY PRIMARY 4       func    1       Using index
761
 
DROP TABLE t1, t2;
762
 
CREATE TABLE t1 (a INT);
763
 
INSERT INTO t1 VALUES(1);
764
 
CREATE TABLE t2 (placeholder CHAR(11));
765
 
INSERT INTO t2 VALUES("placeholder");
766
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2)         FROM t1 GROUP BY t1.a;
767
 
ROW(1, 2) IN (SELECT t1.a, 2)
768
 
1
769
 
SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a;
770
 
ROW(1, 2) IN (SELECT t1.a, 2 FROM t2)
771
 
1
772
 
DROP TABLE t1, t2;
773
 
create table t1 (a int, b decimal(13, 3));
774
 
insert into t1 values (1, 0.123);
775
 
select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1;
776
 
delete from t1;
777
 
load data infile "subselect.out.file.1" into table t1;
778
 
select * from t1;
779
 
a       b
780
 
1       0.123
781
 
drop table t1;
782
 
End of 5.0 tests