← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_mat.test

  • Committer: Monty Taylor
  • Date: 2009-04-14 19:16:51 UTC
  • mto: (997.2.5 mordred)
  • mto: This revision was merged to the branch mainline in revision 994.
  • Revision ID: mordred@inaugust.com-20090414191651-ltbww6hpqks8k7qk
Clarified instructions in README.

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));
11
 
create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;
12
 
create table t3 (c1 char(8), c2 char(8));
 
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;
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 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;
 
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;
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);
46
46
insert into t2i select * from t2;
47
47
insert into t3i select * from t3;
48
48
 
49
 
--echo /********************************************************************
50
 
--echo  * Simple tests.
51
 
--echo  ********************************************************************/
52
 
--echo # non-indexed nullable fields
53
 
--replace_column 9 #
54
 
explain extended
55
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
56
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
57
 
 
58
 
--replace_column 9 #
59
 
explain extended
60
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
61
 
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
62
 
 
63
 
--replace_column 9 #
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
 
 
68
 
--replace_column 9 #
 
49
# force the use of materialization
 
50
set @@optimizer_switch=no_semijoin;
 
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
 
69
68
explain extended
70
69
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
71
70
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
72
71
 
73
72
# indexed columns
74
 
--replace_column 9 #
75
 
explain extended
76
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
77
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
78
 
 
79
 
--replace_column 9 #
80
 
explain extended
81
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
82
 
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
83
 
 
84
 
--replace_column 9 #
85
 
explain extended
86
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
87
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
88
 
 
89
 
--replace_column 9 #
90
 
explain extended
91
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
92
 
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
93
 
 
94
 
--replace_column 9 #
 
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
 
95
89
explain extended
96
90
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
97
91
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
98
92
 
99
93
# materialize the result of ORDER BY
100
94
# non-indexed fields
101
 
--replace_column 9 #
102
95
explain extended
103
96
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
104
97
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
105
98
# indexed fields
106
 
--replace_column 9 #
107
99
explain extended
108
100
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
109
101
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
110
102
 
111
103
# nested subqueries, views
112
 
--replace_column 9 #
113
 
explain extended
114
 
select * from t1
115
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
116
 
      (a1, a2) in (select c1, c2 from t3
117
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
118
 
select * from t1
119
 
where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
120
 
      (a1, a2) in (select c1, c2 from t3
121
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
122
 
 
123
 
--replace_column 9 #
124
 
explain extended
125
 
select * from t1i
126
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
127
 
      (a1, a2) in (select c1, c2 from t3i
128
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
129
 
select * from t1i
130
 
where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
131
 
      (a1, a2) in (select c1, c2 from t3i
132
 
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
133
 
 
134
 
--replace_column 9 #
 
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
 
135
124
explain extended
136
125
select * from t1
137
126
where (a1, a2) in (select b1, b2 from t2
147
136
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
148
137
 
149
138
# as above with correlated innermost subquery
150
 
--replace_column 9 #
151
139
explain extended
152
140
select * from t1
153
141
where (a1, a2) in (select b1, b2 from t2
164
152
 
165
153
 
166
154
# multiple levels of nesting subqueries, unions
167
 
--replace_column 9 #
168
155
explain extended
169
156
(select * from t1
170
157
where (a1, a2) in (select b1, b2 from t2
194
181
 
195
182
 
196
183
# UNION of subqueries as a subquery (thus it is not computed via materialization)
197
 
--replace_column 9 #
198
184
explain extended
199
185
select * from t1
200
186
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
205
191
      (a1, a2) in (select c1, c2 from t3
206
192
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
207
193
# as above, with a join conditon between the outer references
208
 
--replace_column 9 #
209
194
explain extended
210
195
select * from t1, t3
211
196
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
219
204
       a1 = c1;
220
205
 
221
206
 
222
 
--echo /*********************************************************************
223
 
--echo * Negative tests, where materialization should not be applied.
224
 
--echo **********************************************************************/
225
 
--echo # UNION in a subquery
226
 
--replace_column 9 #
 
207
/******************************************************************************
 
208
* Negative tests, where materialization should not be applied.
 
209
******************************************************************************/
 
210
# UNION in a subquery
227
211
explain extended
228
212
select * from t3
229
213
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
231
215
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
232
216
 
233
217
# correlation
234
 
--replace_column 9 #
235
218
explain extended
236
219
select * from t1
237
220
where (a1, a2) in (select b1, b2 from t2
240
223
      (a1, a2) in (select c1, c2 from t3 t3c
241
224
                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
242
225
 
243
 
DROP TABLE t1i, t2i, t3i;
244
 
 
245
226
# subquery has no tables
246
 
--replace_column 9 #
247
227
explain extended
248
228
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
249
229
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
257
237
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
258
238
 
259
239
/* GROUP BY clause */
260
 
create temporary table columns (col int key) ENGINE=MyISAM;
 
240
create table columns (col int key) ENGINE=MyISAM;
261
241
insert into columns values (1), (2);
262
242
 
263
 
--replace_column 9 #
264
 
explain extended
265
 
select * from t1 group by (select col from columns limit 1);
266
 
select * from t1 group by (select col from columns limit 1);
267
 
 
268
 
--replace_column 9 #
269
 
explain extended
270
 
select * from t1 group by (a1 in (select col from columns));
271
 
select * from t1 group by (a1 in (select col from columns));
272
 
 
273
 
--echo /* ORDER BY clause */
274
 
--replace_column 9 #
275
 
explain extended
276
 
select * from t1 order by (select col from columns limit 1);
277
 
select * from t1 order by (select col from columns limit 1);
278
 
 
279
 
DROP TABLE columns;
 
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 */
 
252
explain extended
 
253
select * from t1 order by (select col from columns limit 1);
 
254
select * from t1 order by (select col from columns limit 1);
280
255
 
281
256
/******************************************************************************
282
257
* Column types/sizes that affect materialization.
286
261
  Test that BLOBs are not materialized (except when arguments of some functions).
287
262
*/
288
263
# force materialization to be always considered
 
264
set @@optimizer_switch=no_semijoin;
289
265
set @prefix_len = 6;
290
266
 
291
 
# BLOB == 16 (small blobs that could be stored in MEMORY tables)
 
267
# BLOB == 16 (small blobs that could be stored in HEAP tables)
292
268
set @blob_len = 16;
293
269
set @suffix_len = @blob_len - @prefix_len;
294
270
 
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;
 
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;
298
274
 
299
275
insert into t1_16 values
300
276
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
385
361
set @blob_len = 512;
386
362
set @suffix_len = @blob_len - @prefix_len;
387
363
 
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;
 
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;
391
367
 
392
368
insert into t1_512 values
393
369
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
467
443
set @blob_len = 1024;
468
444
set @suffix_len = @blob_len - @prefix_len;
469
445
 
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;
 
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;
473
449
 
474
450
insert into t1_1024 values
475
451
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
549
525
set @blob_len = 1025;
550
526
set @suffix_len = @blob_len - @prefix_len;
551
527
 
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;
 
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;
555
531
 
556
532
insert into t1_1025 values
557
533
 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
630
606
/******************************************************************************
631
607
* Test the cache of the left operand of IN.
632
608
******************************************************************************/
 
609
set @@optimizer_switch=no_semijoin;
 
610
 
633
611
# Test that default values of Cached_item are not used for comparison
634
 
create temporary table t1 (s1 int) ENGINE=MyISAM;
635
 
create temporary table t2 (s2 int) ENGINE=MyISAM;
 
612
create table t1 (s1 int) ENGINE=MyISAM;
 
613
create table t2 (s2 int) ENGINE=MyISAM;
636
614
insert into t1 values (5),(1),(0);
637
615
insert into t2 values (0), (1);
638
616
select s2 from t2 where s2 in (select s1 from t1);
639
617
drop table t1, t2;
640
618
 
641
 
create temporary table t1 (a int not null, b int not null) ENGINE=MyISAM;
642
 
create temporary table t2 (c int not null, d int not null) ENGINE=MyISAM;
643
 
create temporary table t3 (e int not null) ENGINE=MyISAM;
 
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;
644
622
 
645
623
# the first outer row has no matching inner row
646
624
insert into t1 values (1,10);