← Back to branch summary

~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

« back to all changes in this revision

Viewing changes to mysql-test/r/subselect3.result

  • 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/r/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   11
 
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   11
 
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   29
 
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      ref     a       a       5       test.t1.b       1       100.00  Using where
 
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       1       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    2       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    2       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     10      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
ff      2
 
448
aa      1
 
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     5       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
cc      5
 
611
aa      1
 
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 index condition; 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 UNSIGNED 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; Using where
 
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