← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_mat.test

  • Committer: Brian Aker
  • Date: 2009-07-12 00:49:18 UTC
  • mfrom: (1063.9.51 brian-tmp-fix)
  • Revision ID: brian@gaz-20090712004918-chprmyj387ex6l8a
Merge Stewart

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect_mat.test
7
7
drop table if exists t1, t2, t3, t1i, t2i, t3i;
8
8
--enable_warnings
9
9
 
10
 
create table t1 (a1 char(8), a2 char(8)) ENGINE=MyISAM;
11
 
create table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
12
 
create table t3 (c1 char(8), c2 char(8)) ENGINE=MyISAM;
 
10
create table t1 (a1 char(8), a2 char(8));
 
11
create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
 
12
create table t3 (c1 char(8), c2 char(8));
13
13
 
14
14
insert into t1 values ('1 - 00', '2 - 00');
15
15
insert into t1 values ('1 - 01', '2 - 01');
27
27
insert into t3 values ('1 - 04', '2 - 04');
28
28
 
29
29
# Indexed columns
30
 
create table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;
31
 
create table t2i (b1 char(8), b2 char(8)) ENGINE=MyISAM;
32
 
create table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;
 
30
create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;
 
31
create table t2i (b1 char(8), b2 char(8));
 
32
create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;
33
33
create index it1i1 on t1i (a1);
34
34
create index it1i2 on t1i (a2);
35
35
create index it1i3 on t1i (a1, a2);
49
49
# force the use of materialization
50
50
set @@optimizer_switch=no_semijoin;
51
51
 
52
 
/******************************************************************************
53
 
* Simple tests.
54
 
******************************************************************************/
55
 
# non-indexed nullable fields
56
 
explain extended
57
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
58
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
59
 
 
60
 
explain extended
61
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
62
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
63
 
 
64
 
explain extended
65
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
66
 
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
67
 
 
 
52
--echo /********************************************************************
 
53
--echo  * Simple tests.
 
54
--echo  ********************************************************************/
 
55
--echo # non-indexed nullable fields
 
56
--replace_column 9 #
 
57
explain extended
 
58
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
 
59
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
 
60
 
 
61
--replace_column 9 #
 
62
explain extended
 
63
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
 
64
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
 
65
 
 
66
--replace_column 9 #
 
67
explain extended
 
68
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
 
69
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
 
70
 
 
71
--replace_column 9 #
68
72
explain extended
69
73
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
70
74
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
71
75
 
72
76
# indexed columns
73
 
explain extended
74
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
75
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
76
 
 
77
 
explain extended
78
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
79
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
80
 
 
81
 
explain extended
82
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
83
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
84
 
 
85
 
explain extended
86
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
87
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
88
 
 
 
77
--replace_column 9 #
 
78
explain extended
 
79
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 
80
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 
81
 
 
82
--replace_column 9 #
 
83
explain extended
 
84
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
 
85
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
 
86
 
 
87
--replace_column 9 #
 
88
explain extended
 
89
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 
90
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 
91
 
 
92
--replace_column 9 #
 
93
explain extended
 
94
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
 
95
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
 
96
 
 
97
--replace_column 9 #
89
98
explain extended
90
99
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
91
100
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
92
101
 
93
102
# materialize the result of ORDER BY
94
103
# non-indexed fields
 
104
--replace_column 9 #
95
105
explain extended
96
106
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
97
107
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
98
108
# indexed fields
 
109
--replace_column 9 #
99
110
explain extended
100
111
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
101
112
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
102
113
 
103
114
# nested subqueries, views
104
 
explain extended
105
 
select * from t1
106
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
107
 
      (a1, a2) in (select c1, c2 from t3
108
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
109
 
select * from t1
110
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
111
 
      (a1, a2) in (select c1, c2 from t3
112
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
113
 
 
114
 
explain extended
115
 
select * from t1i
116
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
117
 
      (a1, a2) in (select c1, c2 from t3i
118
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
119
 
select * from t1i
120
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
121
 
      (a1, a2) in (select c1, c2 from t3i
122
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
123
 
 
 
115
--replace_column 9 #
 
116
explain extended
 
117
select * from t1
 
118
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
 
119
      (a1, a2) in (select c1, c2 from t3
 
120
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
121
select * from t1
 
122
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
 
123
      (a1, a2) in (select c1, c2 from t3
 
124
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
125
 
 
126
--replace_column 9 #
 
127
explain extended
 
128
select * from t1i
 
129
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 
130
      (a1, a2) in (select c1, c2 from t3i
 
131
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
132
select * from t1i
 
133
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 
134
      (a1, a2) in (select c1, c2 from t3i
 
135
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 
136
 
 
137
--replace_column 9 #
124
138
explain extended
125
139
select * from t1
126
140
where (a1, a2) in (select b1, b2 from t2
136
150
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
137
151
 
138
152
# as above with correlated innermost subquery
 
153
--replace_column 9 #
139
154
explain extended
140
155
select * from t1
141
156
where (a1, a2) in (select b1, b2 from t2
152
167
 
153
168
 
154
169
# multiple levels of nesting subqueries, unions
 
170
--replace_column 9 #
155
171
explain extended
156
172
(select * from t1
157
173
where (a1, a2) in (select b1, b2 from t2
181
197
 
182
198
 
183
199
# UNION of subqueries as a subquery (thus it is not computed via materialization)
 
200
--replace_column 9 #
184
201
explain extended
185
202
select * from t1
186
203
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
191
208
      (a1, a2) in (select c1, c2 from t3
192
209
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
193
210
# as above, with a join conditon between the outer references
 
211
--replace_column 9 #
194
212
explain extended
195
213
select * from t1, t3
196
214
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
204
222
       a1 = c1;
205
223
 
206
224
 
207
 
/******************************************************************************
208
 
* Negative tests, where materialization should not be applied.
209
 
******************************************************************************/
210
 
# UNION in a subquery
 
225
--echo /*********************************************************************
 
226
--echo * Negative tests, where materialization should not be applied.
 
227
--echo **********************************************************************/
 
228
--echo # UNION in a subquery
 
229
--replace_column 9 #
211
230
explain extended
212
231
select * from t3
213
232
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
215
234
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
216
235
 
217
236
# correlation
 
237
--replace_column 9 #
218
238
explain extended
219
239
select * from t1
220
240
where (a1, a2) in (select b1, b2 from t2
224
244
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
225
245
 
226
246
# subquery has no tables
 
247
--replace_column 9 #
227
248
explain extended
228
249
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
229
250
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
237
258
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
238
259
 
239
260
/* GROUP BY clause */
240
 
create table columns (col int key) ENGINE=MyISAM;
 
261
create temporary table columns (col int key) ENGINE=MyISAM;
241
262
insert into columns values (1), (2);
242
263
 
243
 
explain extended
244
 
select * from t1 group by (select col from columns limit 1);
245
 
select * from t1 group by (select col from columns limit 1);
246
 
 
247
 
explain extended
248
 
select * from t1 group by (a1 in (select col from columns));
249
 
select * from t1 group by (a1 in (select col from columns));
250
 
 
251
 
/* ORDER BY clause */
 
264
--replace_column 9 #
 
265
explain extended
 
266
select * from t1 group by (select col from columns limit 1);
 
267
select * from t1 group by (select col from columns limit 1);
 
268
 
 
269
--replace_column 9 #
 
270
explain extended
 
271
select * from t1 group by (a1 in (select col from columns));
 
272
select * from t1 group by (a1 in (select col from columns));
 
273
 
 
274
--echo /* ORDER BY clause */
 
275
--replace_column 9 #
252
276
explain extended
253
277
select * from t1 order by (select col from columns limit 1);
254
278
select * from t1 order by (select col from columns limit 1);
268
292
set @blob_len = 16;
269
293
set @suffix_len = @blob_len - @prefix_len;
270
294
 
271
 
create table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;
272
 
create table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;
273
 
create table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;
 
295
create temporary table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;
 
296
create temporary table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;
 
297
create temporary table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;
274
298
 
275
299
insert into t1_16 values
276
300
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
361
385
set @blob_len = 512;
362
386
set @suffix_len = @blob_len - @prefix_len;
363
387
 
364
 
create table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;
365
 
create table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;
366
 
create table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;
 
388
create temporary table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;
 
389
create temporary table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;
 
390
create temporary table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;
367
391
 
368
392
insert into t1_512 values
369
393
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
443
467
set @blob_len = 1024;
444
468
set @suffix_len = @blob_len - @prefix_len;
445
469
 
446
 
create table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;
447
 
create table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;
448
 
create table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;
 
470
create temporary table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;
 
471
create temporary table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;
 
472
create temporary table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;
449
473
 
450
474
insert into t1_1024 values
451
475
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
525
549
set @blob_len = 1025;
526
550
set @suffix_len = @blob_len - @prefix_len;
527
551
 
528
 
create table t1_1025 (a1 blob, a2 blob) ENGINE=MyISAM;
529
 
create table t2_1025 (b1 blob, b2 blob) ENGINE=MyISAM;
530
 
create table t3_1025 (c1 blob, c2 blob) ENGINE=MyISAM;
 
552
create temporary table t1_1025 (a1 blob, a2 blob) ENGINE=MyISAM;
 
553
create temporary table t2_1025 (b1 blob, b2 blob) ENGINE=MyISAM;
 
554
create temporary table t3_1025 (c1 blob, c2 blob) ENGINE=MyISAM;
531
555
 
532
556
insert into t1_1025 values
533
557
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
609
633
set @@optimizer_switch=no_semijoin;
610
634
 
611
635
# Test that default values of Cached_item are not used for comparison
612
 
create table t1 (s1 int) ENGINE=MyISAM;
613
 
create table t2 (s2 int) ENGINE=MyISAM;
 
636
create temporary table t1 (s1 int) ENGINE=MyISAM;
 
637
create temporary table t2 (s2 int) ENGINE=MyISAM;
614
638
insert into t1 values (5),(1),(0);
615
639
insert into t2 values (0), (1);
616
640
select s2 from t2 where s2 in (select s1 from t1);
617
641
drop table t1, t2;
618
642
 
619
 
create table t1 (a int not null, b int not null) ENGINE=MyISAM;
620
 
create table t2 (c int not null, d int not null) ENGINE=MyISAM;
621
 
create table t3 (e int not null) ENGINE=MyISAM;
 
643
create temporary table t1 (a int not null, b int not null) ENGINE=MyISAM;
 
644
create temporary table t2 (c int not null, d int not null) ENGINE=MyISAM;
 
645
create temporary table t3 (e int not null) ENGINE=MyISAM;
622
646
 
623
647
# the first outer row has no matching inner row
624
648
insert into t1 values (1,10);