← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

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

  • Committer: Brian Aker
  • Date: 2010-04-12 07:43:55 UTC
  • mfrom: (1455.3.13 drizzle-pbxt-6)
  • Revision ID: brian@gaz-20100412074355-udi9dwjlcnmz0oz6
Merge PBXT

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/pbxt/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 t2      ALL     NULL    NULL    NULL    NULL    10      Using where
 
35
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    3       
 
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(100), pk2 char(100), pk3 char(100),
 
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 t3      ALL     NULL    NULL    NULL    NULL    10      Using where
 
53
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    3       
 
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
drop table t3;
 
62
create table t3 (
 
63
a int, 
 
64
b int,
 
65
key(b),
 
66
pk1 char(100), pk2 char(100),
 
67
primary key(pk1, pk2)
 
68
) engine=innodb;
 
69
insert into t3 select 
 
70
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a 
 
71
from t0 A, t0 B where B.a <5;
 
72
explain select * from t3 where b in (select a from t0);
 
73
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
74
1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    50      Using where
 
75
2       SUBQUERY        t0      ALL     NULL    NULL    NULL    NULL    10      
 
76
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 
77
a       b       pk1     pk2
 
78
0       0       0       0
 
79
1       1       1       1
 
80
10      10      10      10
 
81
11      11      11      11
 
82
12      12      12      12
 
83
13      13      13      13
 
84
2       2       2       2
 
85
3       3       3       3
 
86
4       4       4       4
 
87
5       5       5       5
 
88
6       6       6       6
 
89
7       7       7       7
 
90
8       8       8       8
 
91
9       9       9       9
 
92
set join_buffer_size= @save_join_buffer_size;
 
93
set max_heap_table_size= @save_max_heap_table_size;
 
94
explain select * from t1 where a in (select b from t2);
 
95
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
96
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       Using where
 
97
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    10      
 
98
select * from t1;
 
99
a       b
 
100
1       1
 
101
1       1
 
102
2       2
 
103
select * from t1 where a in (select b from t2);
 
104
a       b
 
105
1       1
 
106
1       1
 
107
2       2
 
108
drop table t1, t2, t3;
 
109
set @save_join_buffer_size = @@join_buffer_size;
 
110
set join_buffer_size=8000;
 
111
Warnings:
 
112
Error   1292    Truncated incorrect join_buffer_size value: '8000'
 
113
create table t1 (a int, filler1 varbinary(20), filler2 varbinary(20));
 
114
insert into t1 select a, 'filler123456', 'filler123456' from t0;
 
115
insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
 
116
create table t2 as select * from t1;
 
117
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
118
insert into t1 values (2, 'duplicate ok', 'duplicate ok');
 
119
insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 
120
insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 
121
insert into t2 values (19, 'duplicate ok', 'duplicate ok');
 
122
explain select 
 
123
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
124
from t1 t0 where a in (select a from t2 it);
 
125
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
126
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    32      Using where
 
127
2       SUBQUERY        it      ALL     NULL    NULL    NULL    NULL    22      
 
128
select 
 
129
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
130
from t1 t0 where a in (select a from t2 it);
 
131
a       mid(filler1, 1,10)      Z
 
132
0       filler1234      1
 
133
1       filler1234      1
 
134
2       filler1234      1
 
135
3       filler1234      1
 
136
4       filler1234      1
 
137
5       filler1234      1
 
138
6       filler1234      1
 
139
7       filler1234      1
 
140
8       filler1234      1
 
141
9       filler1234      1
 
142
10      filler1234      1
 
143
11      filler1234      1
 
144
12      filler1234      1
 
145
13      filler1234      1
 
146
14      filler1234      1
 
147
15      filler1234      1
 
148
16      filler1234      1
 
149
17      filler1234      1
 
150
18      filler1234      1
 
151
19      filler1234      1
 
152
2       duplicate       1
 
153
18      duplicate       1
 
154
explain select 
 
155
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
156
from t2 t0 where a in (select a from t1 it);
 
157
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
158
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    22      Using where
 
159
2       SUBQUERY        it      ALL     NULL    NULL    NULL    NULL    32      
 
160
select 
 
161
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
162
from t2 t0 where a in (select a from t1 it);
 
163
a       mid(filler1, 1,10)      length(filler1)=length(filler2)
 
164
0       filler1234      1
 
165
1       filler1234      1
 
166
2       filler1234      1
 
167
3       filler1234      1
 
168
4       filler1234      1
 
169
5       filler1234      1
 
170
6       filler1234      1
 
171
7       filler1234      1
 
172
8       filler1234      1
 
173
9       filler1234      1
 
174
10      filler1234      1
 
175
11      filler1234      1
 
176
12      filler1234      1
 
177
13      filler1234      1
 
178
14      filler1234      1
 
179
15      filler1234      1
 
180
16      filler1234      1
 
181
17      filler1234      1
 
182
18      filler1234      1
 
183
19      filler1234      1
 
184
3       duplicate       1
 
185
19      duplicate       1
 
186
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
187
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
 
188
explain select 
 
189
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
190
from t1 t0 where a in (select a from t2 it);
 
191
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
192
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    52      Using where
 
193
2       SUBQUERY        it      ALL     NULL    NULL    NULL    NULL    22      
 
194
select 
 
195
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 
196
from t1 t0 where a in (select a from t2 it);
 
197
a       mid(filler1, 1,10)      Z
 
198
0       filler1234      1
 
199
1       filler1234      1
 
200
2       filler1234      1
 
201
3       filler1234      1
 
202
4       filler1234      1
 
203
5       filler1234      1
 
204
6       filler1234      1
 
205
7       filler1234      1
 
206
8       filler1234      1
 
207
9       filler1234      1
 
208
10      filler1234      1
 
209
11      filler1234      1
 
210
12      filler1234      1
 
211
13      filler1234      1
 
212
14      filler1234      1
 
213
15      filler1234      1
 
214
16      filler1234      1
 
215
17      filler1234      1
 
216
18      filler1234      1
 
217
19      filler1234      1
 
218
2       duplicate       1
 
219
18      duplicate       1
 
220
explain select 
 
221
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
222
from t2 t0 where a in (select a from t1 it);
 
223
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
224
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    22      Using where
 
225
2       SUBQUERY        it      ALL     NULL    NULL    NULL    NULL    52      
 
226
select 
 
227
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 
228
from t2 t0 where a in (select a from t1 it);
 
229
a       mid(filler1, 1,10)      length(filler1)=length(filler2)
 
230
0       filler1234      1
 
231
1       filler1234      1
 
232
2       filler1234      1
 
233
3       filler1234      1
 
234
4       filler1234      1
 
235
5       filler1234      1
 
236
6       filler1234      1
 
237
7       filler1234      1
 
238
8       filler1234      1
 
239
9       filler1234      1
 
240
10      filler1234      1
 
241
11      filler1234      1
 
242
12      filler1234      1
 
243
13      filler1234      1
 
244
14      filler1234      1
 
245
15      filler1234      1
 
246
16      filler1234      1
 
247
17      filler1234      1
 
248
18      filler1234      1
 
249
19      filler1234      1
 
250
3       duplicate       1
 
251
19      duplicate       1
 
252
drop table t1, t2;
 
253
create table t1 (a int, b int, key(a));
 
254
create table t2 (a int, b int, key(a));
 
255
create table t3 (a int, b int, key(a));
 
256
insert into t1 select a,a from t0;
 
257
insert into t2 select a,a from t0;
 
258
insert into t3 select a,a from t0;
 
259
t2 and t3 must be use 'ref', not 'ALL':
 
260
explain select * 
 
261
from t0 where a in
 
262
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 
263
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
264
1       PRIMARY t0      ALL     NULL    NULL    NULL    NULL    10      Using where
 
265
2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    10      
 
266
2       SUBQUERY        t2      ref     a       a       5       test.t1.a       1       
 
267
2       SUBQUERY        t3      ref     a       a       5       test.t1.a       1       
 
268
drop table t0, t1,t2,t3;
 
269
CREATE TABLE t1 (
 
270
ID int NOT NULL auto_increment,
 
271
Name char(35) NOT NULL default '',
 
272
Country char(3) NOT NULL default '',
 
273
Population int NOT NULL default '0',
 
274
PRIMARY KEY  (ID),
 
275
INDEX (Population),
 
276
INDEX (Country) 
 
277
);
 
278
CREATE TABLE t2 (
 
279
Code char(3) NOT NULL default '',
 
280
Name char(52) NOT NULL default '',
 
281
SurfaceArea float(10,2) NOT NULL default '0.00',
 
282
Population int NOT NULL default '0',
 
283
Capital int default NULL,
 
284
PRIMARY KEY  (Code),
 
285
UNIQUE INDEX (Name),
 
286
INDEX (Population)
 
287
);
 
288
CREATE TABLE t3 (
 
289
Country char(3) NOT NULL default '',
 
290
Language char(30) NOT NULL default '',
 
291
Percentage float(3,1) NOT NULL default '0.0',
 
292
PRIMARY KEY  (Country, Language),
 
293
INDEX (Percentage)
 
294
);
 
295
EXPLAIN
 
296
SELECT Name FROM t2 
 
297
WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
 
298
AND
 
299
t2.Code IN (SELECT Country FROM t3 
 
300
WHERE Language='English' AND Percentage > 10 AND
 
301
t2.Population > 100000);
 
302
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
303
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    16      Using where
 
304
3       DEPENDENT SUBQUERY      t3      unique_subquery PRIMARY,Percentage      PRIMARY 136     func,const      1       Using where
 
305
2       SUBQUERY        t1      ALL     Population      NULL    NULL    NULL    30      Using where
 
306
DROP TABLE t1,t2,t3;
 
307
CREATE TABLE t1 (
 
308
Code char(3) NOT NULL DEFAULT '',
 
309
Name char(52) NOT NULL DEFAULT '',
 
310
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
 
311
Region char(26) NOT NULL DEFAULT '',
 
312
SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
 
313
IndepYear int DEFAULT NULL,
 
314
Population int NOT NULL DEFAULT '0',
 
315
LifeExpectancy float(3,1) DEFAULT NULL,
 
316
GNP float(10,2) DEFAULT NULL,
 
317
GNPOld float(10,2) DEFAULT NULL,
 
318
LocalName char(45) NOT NULL DEFAULT '',
 
319
GovernmentForm char(45) NOT NULL DEFAULT '',
 
320
HeadOfState char(60) DEFAULT NULL,
 
321
Capital int DEFAULT NULL,
 
322
Code2 char(2) NOT NULL DEFAULT '',
 
323
PRIMARY KEY (Code)
 
324
);
 
325
CREATE TABLE t2 (
 
326
ID int NOT NULL, 
 
327
Name char(35) NOT NULL DEFAULT '',
 
328
CountryCode char(3) NOT NULL DEFAULT '',
 
329
Population int NOT NULL DEFAULT '0',
 
330
PRIMARY KEY (ID),
 
331
KEY CountryCode (CountryCode)
 
332
);
 
333
Fill the table with test data
 
334
This must not use LooseScan:
 
335
EXPLAIN SELECT Name FROM t1 
 
336
WHERE t1.Code IN (
 
337
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 
338
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
339
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    31      Using where
 
340
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    125     Using where
 
341
SELECT Name FROM t1 
 
342
WHERE t1.Code IN (
 
343
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 
344
Name
 
345
drop table t1, t2;
 
346
#
 
347
# MySQL BUG #42742: crash in setup_sj_materialization, Copy_field::set
 
348
# 
 
349
create table t3 ( c1 date) engine=innodb;
 
350
insert into t3 values ('2009-10-22'),('2142-10-22');
 
351
create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
 
352
select * from t3;
 
353
c1
 
354
2009-10-22
 
355
2142-10-22
 
356
explain select 1 from t2 where  c2 in (select 1 from t3, t2) and  c1 in (select convert(c6,char(1)) from t2);
 
357
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
358
1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    1       Using where
 
359
3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       
 
360
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1       
 
361
2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    2       Using join buffer
 
362
drop table t2, t3;