← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/r/subselect_sj2.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/subselect_sj2.result
 
1
drop table if exists t0, t1, t2, t3;
 
2
create table t0 (a int);
 
3
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
4
create table t1 (
 
5
a int, 
 
6
b int
 
7
);
 
8
insert into t1 values (1,1),(1,1),(2,2);
 
9
create table t2 (
 
10
a int,
 
11
b int,
 
12
key(b)
 
13
);
 
14
insert into t2 select a, a/2 from t0;
 
15
select * from t1;
 
16
a       b
 
17
1       1
 
18
1       1
 
19
2       2
 
20
select * from t2;
 
21
a       b
 
22
0       0
 
23
1       1
 
24
2       1
 
25
3       2
 
26
4       2
 
27
5       3
 
28
6       3
 
29
7       4
 
30
8       4
 
31
9       5
 
32
explain select * from t2 where b in (select a from t1);
 
33
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
34
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       Start temporary
 
35
1       PRIMARY t2      ref     b       b       5       test.t1.a       2       End temporary
 
36
select * from t2 where b in (select a from t1);
 
37
a       b
 
38
1       1
 
39
2       1
 
40
3       2
 
41
4       2
 
42
create table t3 (
 
43
a int, 
 
44
b int,
 
45
key(b),
 
46
pk1 char(200), pk2 char(200), pk3 char(200),
 
47
primary key(pk1, pk2, pk3)
 
48
) engine=innodb;
 
49
insert into t3 select a,a, a,a,a from t0;
 
50
explain select * from t3 where b in (select a from t1);
 
51
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
52
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       Start temporary
 
53
1       PRIMARY t3      ref     b       b       5       test.t1.a       1       End temporary
 
54
select * from t3 where b in (select a from t1);
 
55
a       b       pk1     pk2     pk3
 
56
1       1       1       1       1
 
57
2       2       2       2       2
 
58
set @save_max_heap_table_size= @@max_heap_table_size;
 
59
set max_heap_table_size=16384;
 
60
set @save_join_buffer_size = @@join_buffer_size;
 
61
set join_buffer_size= 8000;
 
62
Warnings:
 
63
Warning 1292    Truncated incorrect join_buffer_size value: '8000'
 
64
drop table t3;
 
65
create table t3 (
 
66
a int, 
 
67
b int,
 
68
key(b),
 
69
pk1 char(200), pk2 char(200),
 
70
primary key(pk1, pk2)
 
71
) engine=innodb;
 
72
insert into t3 select 
 
73
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a 
 
74
from t0 A, t0 B where B.a <5;
 
75
explain select * from t3 where b in (select a from t0);
 
76
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
77
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    10      Start temporary
 
78
1       PRIMARY t3      ref     b       b       5       test.t0.a       1       End temporary
 
79
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 
80
a       b       pk1     pk2
 
81
0       0       0       0
 
82
1       1       1       1
 
83
2       2       2       2
 
84
3       3       3       3
 
85
4       4       4       4
 
86
5       5       5       5
 
87
6       6       6       6
 
88
7       7       7       7
 
89
8       8       8       8
 
90
9       9       9       9
 
91
10      10      10      10
 
92
11      11      11      11
 
93
12      12      12      12
 
94
13      13      13      13
 
95
set join_buffer_size= @save_join_buffer_size;
 
96
set max_heap_table_size= @save_max_heap_table_size;
 
97
explain select * from t1 where a in (select b from t2);
 
98
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
99
1       PRIMARY t2      index   b       b       5       NULL    10      Using index; LooseScan
 
100
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       Using where; Using join buffer
 
101
select * from t1;
 
102
a       b
 
103
1       1
 
104
1       1
 
105
2       2
 
106
select * from t1 where a in (select b from t2);
 
107
a       b
 
108
1       1
 
109
1       1
 
110
2       2
 
111
drop table t1, t2, t3;
 
112
set @save_join_buffer_size = @@join_buffer_size;
 
113
set join_buffer_size= 8000;
 
114
Warnings:
 
115
Warning 1292    Truncated incorrect join_buffer_size value: '8000'
 
116
create table t1 (a int, filler1 binary(200), filler2 binary(200));
 
117
insert into t1 select a, 'filler123456', 'filler123456' from t0;
 
118
insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
 
119
create table t2 as select * from t1;
 
120
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
121
insert into t1 values (2, 'duplicate ok', 'duplicate ok');
 
122
insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 
123
insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 
124
insert into t2 values (19, 'duplicate ok', 'duplicate ok');
 
125
explain select 
 
126
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
127
from t1 ot where a in (select a from t2 it);
 
128
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
129
1       PRIMARY it      ALL     NULL    NULL    NULL    NULL    22      Start temporary
 
130
1       PRIMARY ot      ALL     NULL    NULL    NULL    NULL    32      Using where; End temporary; Using join buffer
 
131
select 
 
132
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
133
from t1 ot where a in (select a from t2 it);
 
134
a       mid(filler1, 1,10)      Z
 
135
0       filler1234      1
 
136
1       filler1234      1
 
137
2       filler1234      1
 
138
3       filler1234      1
 
139
4       filler1234      1
 
140
5       filler1234      1
 
141
6       filler1234      1
 
142
7       filler1234      1
 
143
8       filler1234      1
 
144
9       filler1234      1
 
145
10      filler1234      1
 
146
11      filler1234      1
 
147
12      filler1234      1
 
148
13      filler1234      1
 
149
14      filler1234      1
 
150
15      filler1234      1
 
151
16      filler1234      1
 
152
17      filler1234      1
 
153
18      filler1234      1
 
154
19      filler1234      1
 
155
2       duplicate       1
 
156
18      duplicate       1
 
157
explain select 
 
158
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
159
from t2 ot where a in (select a from t1 it);
 
160
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
161
1       PRIMARY ot      ALL     NULL    NULL    NULL    NULL    22      Start temporary
 
162
1       PRIMARY it      ALL     NULL    NULL    NULL    NULL    32      Using where; End temporary; Using join buffer
 
163
select 
 
164
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
165
from t2 ot where a in (select a from t1 it);
 
166
a       mid(filler1, 1,10)      length(filler1)=length(filler2)
 
167
0       filler1234      1
 
168
1       filler1234      1
 
169
2       filler1234      1
 
170
3       filler1234      1
 
171
4       filler1234      1
 
172
5       filler1234      1
 
173
6       filler1234      1
 
174
7       filler1234      1
 
175
8       filler1234      1
 
176
9       filler1234      1
 
177
10      filler1234      1
 
178
11      filler1234      1
 
179
12      filler1234      1
 
180
13      filler1234      1
 
181
14      filler1234      1
 
182
15      filler1234      1
 
183
16      filler1234      1
 
184
17      filler1234      1
 
185
18      filler1234      1
 
186
3       duplicate       1
 
187
19      filler1234      1
 
188
19      duplicate       1
 
189
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
190
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
191
explain select 
 
192
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
193
from t1 ot where a in (select a from t2 it);
 
194
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
195
1       PRIMARY it      ALL     NULL    NULL    NULL    NULL    22      Start temporary
 
196
1       PRIMARY ot      ALL     NULL    NULL    NULL    NULL    52      Using where; End temporary; Using join buffer
 
197
select 
 
198
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
199
from t1 ot where a in (select a from t2 it);
 
200
a       mid(filler1, 1,10)      Z
 
201
0       filler1234      1
 
202
1       filler1234      1
 
203
2       filler1234      1
 
204
3       filler1234      1
 
205
4       filler1234      1
 
206
5       filler1234      1
 
207
6       filler1234      1
 
208
7       filler1234      1
 
209
8       filler1234      1
 
210
9       filler1234      1
 
211
10      filler1234      1
 
212
11      filler1234      1
 
213
12      filler1234      1
 
214
13      filler1234      1
 
215
14      filler1234      1
 
216
15      filler1234      1
 
217
16      filler1234      1
 
218
17      filler1234      1
 
219
18      filler1234      1
 
220
19      filler1234      1
 
221
2       duplicate       1
 
222
18      duplicate       1
 
223
explain select 
 
224
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
225
from t2 ot where a in (select a from t1 it);
 
226
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
227
1       PRIMARY ot      ALL     NULL    NULL    NULL    NULL    22      Start temporary
 
228
1       PRIMARY it      ALL     NULL    NULL    NULL    NULL    52      Using where; End temporary; Using join buffer
 
229
select 
 
230
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
231
from t2 ot where a in (select a from t1 it);
 
232
a       mid(filler1, 1,10)      length(filler1)=length(filler2)
 
233
0       filler1234      1
 
234
1       filler1234      1
 
235
2       filler1234      1
 
236
3       filler1234      1
 
237
4       filler1234      1
 
238
5       filler1234      1
 
239
6       filler1234      1
 
240
7       filler1234      1
 
241
8       filler1234      1
 
242
9       filler1234      1
 
243
10      filler1234      1
 
244
11      filler1234      1
 
245
12      filler1234      1
 
246
13      filler1234      1
 
247
14      filler1234      1
 
248
15      filler1234      1
 
249
16      filler1234      1
 
250
17      filler1234      1
 
251
18      filler1234      1
 
252
3       duplicate       1
 
253
19      filler1234      1
 
254
19      duplicate       1
 
255
drop table t1, t2;
 
256
create table t1 (a int, b int, key(a));
 
257
create table t2 (a int, b int, key(a));
 
258
create table t3 (a int, b int, key(a));
 
259
insert into t1 select a,a from t0;
 
260
insert into t2 select a,a from t0;
 
261
insert into t3 select a,a from t0;
 
262
t2 and t3 must be use 'ref', not 'ALL':
 
263
explain select * 
 
264
from t0 where a in
 
265
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 
266
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
267
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    10      Start temporary
 
268
1       PRIMARY t1      index   NULL    a       5       NULL    10      Using index; Using join buffer
 
269
1       PRIMARY t2      ref     a       a       5       test.t1.a       1       Using index
 
270
1       PRIMARY t3      ref     a       a       5       test.t1.a       1       Using where; Using index; End temporary
 
271
drop table t0, t1,t2,t3;
 
272
CREATE TABLE t1 (
 
273
ID int(11) NOT NULL auto_increment,
 
274
Name char(35) NOT NULL default '',
 
275
Country char(3) NOT NULL default '',
 
276
Population int(11) NOT NULL default '0',
 
277
PRIMARY KEY  (ID),
 
278
INDEX (Population),
 
279
INDEX (Country) 
 
280
);
 
281
CREATE TABLE t2 (
 
282
Code char(3) NOT NULL default '',
 
283
Name char(52) NOT NULL default '',
 
284
SurfaceArea float(10,2) NOT NULL default '0.00',
 
285
Population int(11) NOT NULL default '0',
 
286
Capital int(11) default NULL,
 
287
PRIMARY KEY  (Code),
 
288
UNIQUE INDEX (Name),
 
289
INDEX (Population)
 
290
);
 
291
CREATE TABLE t3 (
 
292
Country char(3) NOT NULL default '',
 
293
Language char(30) NOT NULL default '',
 
294
Percentage float(3,1) NOT NULL default '0.0',
 
295
PRIMARY KEY  (Country, Language),
 
296
INDEX (Percentage)
 
297
);
 
298
EXPLAIN
 
299
SELECT Name FROM t2 
 
300
WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
 
301
AND
 
302
t2.Code IN (SELECT Country FROM t3 
 
303
WHERE Language='English' AND Percentage > 10 AND
 
304
t2.Population > 100000);
 
305
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
306
1       PRIMARY t1      ALL     Population,Country      NULL    NULL    NULL    30      Using where; Start temporary
 
307
1       PRIMARY t3      eq_ref  PRIMARY,Percentage      PRIMARY 33      test.t1.Country,const   1       Using index condition; Using where
 
308
1       PRIMARY t2      eq_ref  PRIMARY,Population      PRIMARY 3       test.t3.Country 1       Using index condition; Using where; End temporary
 
309
DROP TABLE t1,t2,t3;
 
310
CREATE TABLE t1 (
 
311
Code char(3) NOT NULL DEFAULT '',
 
312
Name char(52) NOT NULL DEFAULT '',
 
313
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
 
314
Region char(26) NOT NULL DEFAULT '',
 
315
SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
 
316
IndepYear smallint(6) DEFAULT NULL,
 
317
Population int(11) NOT NULL DEFAULT '0',
 
318
LifeExpectancy float(3,1) DEFAULT NULL,
 
319
GNP float(10,2) DEFAULT NULL,
 
320
GNPOld float(10,2) DEFAULT NULL,
 
321
LocalName char(45) NOT NULL DEFAULT '',
 
322
GovernmentForm char(45) NOT NULL DEFAULT '',
 
323
HeadOfState char(60) DEFAULT NULL,
 
324
Capital int(11) DEFAULT NULL,
 
325
Code2 char(2) NOT NULL DEFAULT '',
 
326
PRIMARY KEY (Code)
 
327
);
 
328
CREATE TABLE t2 (
 
329
ID int(11) NOT NULL AUTO_INCREMENT,
 
330
Name char(35) NOT NULL DEFAULT '',
 
331
CountryCode char(3) NOT NULL DEFAULT '',
 
332
District char(20) NOT NULL DEFAULT '',
 
333
Population int(11) NOT NULL DEFAULT '0',
 
334
PRIMARY KEY (ID),
 
335
KEY CountryCode (CountryCode)
 
336
);
 
337
Fill the table with test data
 
338
This must not use LooseScan:
 
339
EXPLAIN SELECT Name FROM t1 
 
340
WHERE t1.Code IN (
 
341
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 
342
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
343
1       PRIMARY t2      ALL     CountryCode     NULL    NULL    NULL    545     Using where; Start temporary
 
344
1       PRIMARY t1      eq_ref  PRIMARY PRIMARY 3       test.t2.CountryCode     1       End temporary
 
345
SELECT Name FROM t1 
 
346
WHERE t1.Code IN (
 
347
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 
348
Name
 
349
Austria
 
350
Canada
 
351
China
 
352
Czech Republic
 
353
drop table t1, t2;
 
354
CREATE TABLE t1(a INT);
 
355
CREATE TABLE t2(c INT);
 
356
CREATE PROCEDURE p1(v1 int)
 
357
BEGIN
 
358
SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2);
 
359
END
 
360
//
 
361
CREATE PROCEDURE p2(v1 int)
 
362
BEGIN
 
363
SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2);
 
364
END
 
365
//
 
366
CREATE PROCEDURE p3(v1 int)
 
367
BEGIN
 
368
SELECT 1 
 
369
FROM 
 
370
t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
 
371
t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
 
372
t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
 
373
t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
 
374
t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
 
375
t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
 
376
t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
 
377
t1 t57,t1 t58,t1 t59,t1 t60
 
378
WHERE t01.a IN (SELECT c FROM t2);
 
379
END
 
380
//
 
381
CREATE PROCEDURE p4(v1 int)
 
382
BEGIN
 
383
SELECT 1 
 
384
FROM 
 
385
t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
 
386
t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
 
387
t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
 
388
t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
 
389
t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
 
390
t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
 
391
t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
 
392
t1 t57,t1 t58,t1 t59,t1 t60
 
393
WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2);
 
394
END
 
395
//
 
396
CALL p1(1);
 
397
1
 
398
CALL p2(1);
 
399
1
 
400
CALL p3(1);
 
401
1
 
402
CALL p4(1);
 
403
1
 
404
DROP TABLE t1, t2;
 
405
DROP PROCEDURE p1;
 
406
DROP PROCEDURE p2;
 
407
DROP PROCEDURE p3;
 
408
DROP PROCEDURE p4;