summaryrefslogtreecommitdiff
path: root/mysql-test/main/derived_opt.test
blob: eccf4c13020778cf3b9515a29b8eabe37785b1fc (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
# Initialize
--disable_warnings
drop table if exists t0,t1,t2,t3;
drop database if exists test1;
--enable_warnings

set @exit_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=on,derived_with_keys=on';
# The 'default' value within the scope of this test:
set @save_optimizer_switch=@@optimizer_switch;

CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
CREATE TABLE t3 (a int not null, b char (10) not null);
insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
select * from t1 as x1, (select * from t1) as x2;
explain select * from t1 as x1, (select * from t1) as x2;
drop table if exists  t2,t3;

CREATE TABLE t2 (a int not null);
insert into t2 values(1);
select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
drop table t1, t2;

create table t1(a int not null, t char(8), index(a));
--disable_query_log
begin;
let $1 = 10000;
while ($1)
 {
  eval insert into t1 values ($1,'$1'); 
  dec $1;
 }
commit;
--enable_query_log
SELECT * FROM (SELECT * FROM t1) as b ORDER BY a  ASC LIMIT 0,20;
explain select count(*) from t1 as tt1, (select * from t1) as tt2;
drop table t1;

#
# test->used_keys test for derived tables
#
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);

SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;

explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
drop table t1,t2;

#
# deived tables with subquery inside all by one table
#
create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1)
);
insert into t1 VALUES(1,1,1), (2,2,1);
select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
drop table t1;

create table t1 (a int);
insert into t1 values (1),(2);
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
drop table t1;

#
# "Using index" in explain
#
create table t2 (a int, b int, primary key (a));
insert into t2 values (1,7),(2,7);
explain select a from t2 where a>1;
explain select a from (select a from t2 where a>1) tt;
drop table t2;

#
# prepared EXPLAIN
#
create table t1 
(
  c1  tinyint, c2  smallint, c3  mediumint, c4  int,
  c5  integer, c6  bigint, c7  float, c8  double,
  c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
  c13 date, c14 datetime, c15 timestamp, c16 time,
  c17 year, c18 bit, c19 bool, c20 char,
  c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
  c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
  c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
  c32 set('monday', 'tuesday', 'wednesday')
) engine = MYISAM ;
create table t2 like t1;

set @save_optimizer_switch=@@optimizer_switch;
set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";

set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
execute stmt1 ;
explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
deallocate prepare stmt1;
drop tables t1,t2;

set @@optimizer_switch=@save_optimizer_switch;

--echo #
--echo #  LP bug #793436: query with a derived table for which optimizer proves
--echo #                  that it contains not more than 1 row
--echo #

CREATE TABLE t1 (a int, KEY (a)) ;
INSERT INTO t1 VALUES (3), (1);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (3);

EXPLAIN
SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;
SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;

DROP TABLE t1,t2;

--echo #
--echo #  LP bug #800518: crash with a query over a derived table
--echo #                  when a min/max optimization is applied
--echo #

CREATE TABLE t1 (a int, b int, c varchar(10), INDEX idx(a,b)) ;
INSERT INTO t1 VALUES 
  (100, 3, 'xxx'), (200, 7, 'yyyyyyy'), (100, 1, 't'),
  (200, 4, 'aaaa'), (100, 3, 'eee'), (100, 5, 'zzzzz');

EXPLAIN
SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100;
SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100;

DROP TABLE t1;

--echo #
--echo #  LP bug #799499: query over a materialized view 
--echo #                  accessed by a key
--echo #

CREATE TABLE t1 (a int) ;
INSERT INTO t1 VALUES (8);

CREATE TABLE t2 (a int, b int) ;
INSERT INTO t2 VALUES
  (262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8),
  (257,200), (256, NULL), (255, 8), (249, NULL), (259, 7);

CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a;

EXPLAIN
SELECT * FROM v1, t1 WHERE v1.b=t1.a  ORDER BY v1.a;
SELECT * FROM v1, t1 WHERE v1.b=t1.a  ORDER BY v1.a;

DROP VIEW v1;
DROP TABLE t1,t2;

--echo #
--echo #  LP bug #800085: crash with a query using a simple derived table 
--echo #                  (fixed by the patch for bug 798621)
--echo #

CREATE TABLE t1 (f1 int, f2 varchar(32)) ;
INSERT INTO t1 VALUES (NULL,'j'), (8,'c');

CREATE TABLE t2 (f1 int);
INSERT INTO t2 VALUES (1), (5);

SELECT DISTINCT t.f1 FROM (SELECT * FROM t1) AS t, t2
  WHERE t.f2='s' AND t.f2 LIKE '%a%' OR t.f1<>0 ORDER BY t.f2;

DROP TABLE t1, t2;

--echo #
--echo # BUG##806524: Assertion `join->best_read < 1.7976931348623157e+308 with table_elimination=on and derived_merge=on 
--echo # 
CREATE TABLE t1 ( f4 int) ;
CREATE TABLE t2 ( f4 int) ;
CREATE TABLE t3 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;
CREATE TABLE t4 ( f2 int, f4 int) ;

SELECT *
FROM ( SELECT * FROM t1 ) AS alias1
RIGHT JOIN (
        t2 AS alias2
        LEFT JOIN (
                SELECT t4.*
                FROM ( SELECT * FROM t3 ) AS SQ1_alias1
                RIGHT JOIN t4
                ON t4.f2 = SQ1_alias1.f1
        ) AS alias3
        ON alias3.f4 != 0
) ON alias3.f4 != 0;

drop table t1,t2,t3,t4;

--echo #
--echo # LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal
--echo # Fix: force materialization in case of conflict
--echo #
SET optimizer_switch='derived_merge=on';
CREATE TABLE t1 ( i INT );
INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) );
drop table t1;
set optimizer_switch=@save_optimizer_switch;

--echo #
--echo # MDEV-3801 Reproducible sub select join crash on 5.3.8 and 5.3.9 
--echo #

CREATE TABLE t1 (
  pk int(10) unsigned NOT NULL AUTO_INCREMENT,
  a char(2) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY a (a)
) ENGINE=MyISAM;
INSERT INTO t1 (a)
VALUES (NULL),(NULL),(NULL),('AB'),(NULL),('CD'),(NULL),(NULL);
INSERT INTO t1 SELECT NULL, a1.a FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;

CREATE TABLE t2 (
  pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
INSERT INTO t2 SELECT NULL FROM t2 a1, t2 a2, t2 a3, t2 a4, t2 a5;

CREATE TABLE t3 (
  pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM;
INSERT INTO t3 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
INSERT INTO t3 SELECT NULL FROM t3 a1, t3 a2, t3 a3, t3 a4, t3 a5;

CREATE TABLE t4 (
  a char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (a)
) ENGINE=MyISAM;
INSERT INTO t4 VALUES ('CD');

set @@tmp_table_size=8192;

--replace_column 9 #
EXPLAIN
SELECT * FROM t3 AS tx JOIN t2 AS ty ON (tx.pk = ty.pk)
WHERE
  tx.pk IN
  (SELECT *
   FROM (SELECT DISTINCT ta.pk
         FROM t3 AS ta
              JOIN t2 AS tb ON (ta.pk = tb.pk)
              JOIN t1 AS tc ON (tb.pk = tc.pk)
              JOIN t4 AS td ON tc.a = td.a) tu)
limit 10;

SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk)
WHERE
  tX.pk IN
  (SELECT *
   FROM (SELECT DISTINCT tA.pk
         FROM t3 AS tA
              JOIN t2 AS tB ON (tA.pk = tB.pk)
              JOIN t1 AS tC ON (tB.pk = tC.pk)
              JOIN t4 AS tD ON tC.a = tD.a) tU)
limit 10;

drop table t1, t2, t3, t4;

--echo #
--echo # MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled
--echo #
create database test1;
use test1;

set @tmp_jcl= @@join_cache_level;
set @tmp_os= @@optimizer_switch;
set join_cache_level=8;
set optimizer_switch='mrr=on,mrr_sort_keys=on';

CREATE TABLE t0 (
  f1 bigint(20) DEFAULT NULL,
  f2 char(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two');

CREATE TABLE t1 (
  f1 decimal(64,30) DEFAULT NULL,
  f2 varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES 
(NULL,'numeric column is NULL'),
(0.000000000000000000000000000000,NULL),
(5.000000000000000000000000000000,'five'),
(1.000000000000000000000000000000,'one'),
(3.000000000000000000000000000000,'three');

CREATE TABLE t2 (
  f1 double DEFAULT NULL,
  f2 varbinary(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three');

create VIEW v0  AS select f1,f2 from t1 ;

let $cnt= 27; 
while ($cnt)
{
# i runs from 1 to 27
  let $i= `select 28 - $cnt`;
  let $prev=`select $i - 1`;

# rem = i mod 3
  let $rem= `select MOD($i, 3)`;
# view uses $i, $prev and $rem:
  eval create VIEW v$i AS select tab1_v$i.f1,tab1_v$i.f2 from t$rem tab1_v$i join v$prev tab2 on tab1_v$i.f1 = tab2.f1 and tab1_v$i.f2 = tab2.f2;
  dec $cnt;
}

EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27;
--echo # This used to hang forever:
EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27;

use test;
drop database test1;
set join_cache_level=@tmp_jcl;
set optimizer_switch=@tmp_os;


--echo #
--echo # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost()
--echo #
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (a int, b int, c text);
insert into t2
select
  A.a + B.a* 10,
  A.a + B.a* 10,
  'blob-data'
from t1 A, t1 B;

set @tmp_jcl= @@join_cache_level;
set @tmp_os= @@optimizer_switch;
set join_cache_level=6;
set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on';
explain
select * from
  t1 join
  (select * from t2 order by a limit 1000) as D1
where
  D1.a= t1.a;

set join_cache_level=@tmp_jcl;
set optimizer_switch=@tmp_os;
drop table t1, t2;

--echo #
--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4
--echo #

CREATE TABLE t1 (
  id int NOT NULL,
  amount decimal DEFAULT NULL,
PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id int NOT NULL,
  name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
);

INSERT INTO t1 VALUES
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);

INSERT INTO t2 VALUES
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);

set @save_optimizer_switch= @@optimizer_switch;
set optimizer_switch='split_materialized=off';

set join_cache_level=4;

EXPLAIN
SELECT t2.id,t2.name,t.total_amt
  FROM  t2
        LEFT JOIN
        (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
        ON t2.id=t.id
  WHERE t2.id < 3;

set join_cache_level=default;

set optimizer_switch= @save_optimizer_switch;

DROP TABLE t1,t2;

# The following command must be the last one the file 
set optimizer_switch=@exit_optimizer_switch;