1# Initialize 2--disable_warnings 3drop table if exists t0,t1,t2,t3; 4drop database if exists test1; 5--enable_warnings 6 7set @exit_optimizer_switch=@@optimizer_switch; 8set optimizer_switch='derived_merge=on,derived_with_keys=on'; 9# The 'default' value within the scope of this test: 10set @save_optimizer_switch=@@optimizer_switch; 11 12CREATE TABLE t1 (a int not null, b char (10) not null); 13insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); 14CREATE TABLE t2 (a int not null, b char (10) not null); 15insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); 16CREATE TABLE t3 (a int not null, b char (10) not null); 17insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c'); 18select * from t1 as x1, (select * from t1) as x2; 19explain select * from t1 as x1, (select * from t1) as x2; 20drop table if exists t2,t3; 21 22CREATE TABLE t2 (a int not null); 23insert into t2 values(1); 24select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; 25explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; 26drop table t1, t2; 27 28create table t1(a int not null, t char(8), index(a)); 29--disable_query_log 30begin; 31let $1 = 10000; 32while ($1) 33 { 34 eval insert into t1 values ($1,'$1'); 35 dec $1; 36 } 37commit; 38--enable_query_log 39SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20; 40explain select count(*) from t1 as tt1, (select * from t1) as tt2; 41drop table t1; 42 43# 44# test->used_keys test for derived tables 45# 46create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL); 47create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL); 48insert 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); 49insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); 50 51SELECT 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; 52SELECT 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; 53 54explain 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; 55explain 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; 56drop table t1,t2; 57 58# 59# deived tables with subquery inside all by one table 60# 61create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1) 62); 63insert into t1 VALUES(1,1,1), (2,2,1); 64select 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; 65explain 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; 66drop table t1; 67 68create table t1 (a int); 69insert into t1 values (1),(2); 70select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 71explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 72drop table t1; 73 74# 75# "Using index" in explain 76# 77create table t2 (a int, b int, primary key (a)); 78insert into t2 values (1,7),(2,7); 79explain select a from t2 where a>1; 80explain select a from (select a from t2 where a>1) tt; 81drop table t2; 82 83# 84# prepared EXPLAIN 85# 86create table t1 87( 88 c1 tinyint, c2 smallint, c3 mediumint, c4 int, 89 c5 integer, c6 bigint, c7 float, c8 double, 90 c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), 91 c13 date, c14 datetime, c15 timestamp, c16 time, 92 c17 year, c18 bit, c19 bool, c20 char, 93 c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, 94 c25 blob, c26 text, c27 mediumblob, c28 mediumtext, 95 c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'), 96 c32 set('monday', 'tuesday', 'wednesday') 97) engine = MYISAM ; 98create table t2 like t1; 99 100set @save_optimizer_switch=@@optimizer_switch; 101set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 102 103set @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 ' ; 104prepare stmt1 from @stmt ; 105execute stmt1 ; 106execute stmt1 ; 107explain 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; 108deallocate prepare stmt1; 109drop tables t1,t2; 110 111set @@optimizer_switch=@save_optimizer_switch; 112 113--echo # 114--echo # LP bug #793436: query with a derived table for which optimizer proves 115--echo # that it contains not more than 1 row 116--echo # 117 118CREATE TABLE t1 (a int, KEY (a)) ; 119INSERT INTO t1 VALUES (3), (1); 120CREATE TABLE t2 (a int); 121INSERT INTO t2 VALUES (3); 122 123EXPLAIN 124SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; 125SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; 126 127DROP TABLE t1,t2; 128 129--echo # 130--echo # LP bug #800518: crash with a query over a derived table 131--echo # when a min/max optimization is applied 132--echo # 133 134CREATE TABLE t1 (a int, b int, c varchar(10), INDEX idx(a,b)) ; 135INSERT INTO t1 VALUES 136 (100, 3, 'xxx'), (200, 7, 'yyyyyyy'), (100, 1, 't'), 137 (200, 4, 'aaaa'), (100, 3, 'eee'), (100, 5, 'zzzzz'); 138 139EXPLAIN 140SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; 141SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; 142 143DROP TABLE t1; 144 145--echo # 146--echo # LP bug #799499: query over a materialized view 147--echo # accessed by a key 148--echo # 149 150CREATE TABLE t1 (a int) ; 151INSERT INTO t1 VALUES (8); 152 153CREATE TABLE t2 (a int, b int) ; 154INSERT INTO t2 VALUES 155 (262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8), 156 (257,200), (256, NULL), (255, 8), (249, NULL), (259, 7); 157 158CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; 159 160EXPLAIN 161SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; 162SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; 163 164DROP VIEW v1; 165DROP TABLE t1,t2; 166 167--echo # 168--echo # LP bug #800085: crash with a query using a simple derived table 169--echo # (fixed by the patch for bug 798621) 170--echo # 171 172CREATE TABLE t1 (f1 int, f2 varchar(32)) ; 173INSERT INTO t1 VALUES (NULL,'j'), (8,'c'); 174 175CREATE TABLE t2 (f1 int); 176INSERT INTO t2 VALUES (1), (5); 177 178SELECT DISTINCT t.f1 FROM (SELECT * FROM t1) AS t, t2 179 WHERE t.f2='s' AND t.f2 LIKE '%a%' OR t.f1<>0 ORDER BY t.f2; 180 181DROP TABLE t1, t2; 182 183--echo # 184--echo # BUG##806524: Assertion `join->best_read < 1.7976931348623157e+308 with table_elimination=on and derived_merge=on 185--echo # 186CREATE TABLE t1 ( f4 int) ; 187CREATE TABLE t2 ( f4 int) ; 188CREATE TABLE t3 ( f1 int NOT NULL , PRIMARY KEY (f1)) ; 189CREATE TABLE t4 ( f2 int, f4 int) ; 190 191SELECT * 192FROM ( SELECT * FROM t1 ) AS alias1 193RIGHT JOIN ( 194 t2 AS alias2 195 LEFT JOIN ( 196 SELECT t4.* 197 FROM ( SELECT * FROM t3 ) AS SQ1_alias1 198 RIGHT JOIN t4 199 ON t4.f2 = SQ1_alias1.f1 200 ) AS alias3 201 ON alias3.f4 != 0 202) ON alias3.f4 != 0; 203 204drop table t1,t2,t3,t4; 205 206--echo # 207--echo # LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal 208--echo # Fix: force materialization in case of conflict 209--echo # 210SET optimizer_switch='derived_merge=on'; 211CREATE TABLE t1 ( i INT ); 212INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) ); 213drop table t1; 214set optimizer_switch=@save_optimizer_switch; 215 216--echo # 217--echo # MDEV-3801 Reproducible sub select join crash on 5.3.8 and 5.3.9 218--echo # 219 220CREATE TABLE t1 ( 221 pk int(10) unsigned NOT NULL AUTO_INCREMENT, 222 a char(2) DEFAULT NULL, 223 PRIMARY KEY (pk), 224 KEY a (a) 225) ENGINE=MyISAM; 226INSERT INTO t1 (a) 227VALUES (NULL),(NULL),(NULL),('AB'),(NULL),('CD'),(NULL),(NULL); 228INSERT INTO t1 SELECT NULL, a1.a FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5; 229 230CREATE TABLE t2 ( 231 pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY 232) ENGINE=MyISAM; 233INSERT INTO t2 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); 234INSERT INTO t2 SELECT NULL FROM t2 a1, t2 a2, t2 a3, t2 a4, t2 a5; 235 236CREATE TABLE t3 ( 237 pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY 238) ENGINE=MyISAM; 239INSERT INTO t3 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); 240INSERT INTO t3 SELECT NULL FROM t3 a1, t3 a2, t3 a3, t3 a4, t3 a5; 241 242CREATE TABLE t4 ( 243 a char(2) NOT NULL DEFAULT '', 244 PRIMARY KEY (a) 245) ENGINE=MyISAM; 246INSERT INTO t4 VALUES ('CD'); 247 248set @@tmp_table_size=8192; 249 250--replace_column 9 # 251EXPLAIN 252SELECT * FROM t3 AS tx JOIN t2 AS ty ON (tx.pk = ty.pk) 253WHERE 254 tx.pk IN 255 (SELECT * 256 FROM (SELECT DISTINCT ta.pk 257 FROM t3 AS ta 258 JOIN t2 AS tb ON (ta.pk = tb.pk) 259 JOIN t1 AS tc ON (tb.pk = tc.pk) 260 JOIN t4 AS td ON tc.a = td.a) tu) 261limit 10; 262 263SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk) 264WHERE 265 tX.pk IN 266 (SELECT * 267 FROM (SELECT DISTINCT tA.pk 268 FROM t3 AS tA 269 JOIN t2 AS tB ON (tA.pk = tB.pk) 270 JOIN t1 AS tC ON (tB.pk = tC.pk) 271 JOIN t4 AS tD ON tC.a = tD.a) tU) 272limit 10; 273 274drop table t1, t2, t3, t4; 275 276--echo # 277--echo # MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled 278--echo # 279create database test1; 280use test1; 281 282set @tmp_jcl= @@join_cache_level; 283set @tmp_os= @@optimizer_switch; 284set join_cache_level=8; 285set optimizer_switch='mrr=on,mrr_sort_keys=on'; 286 287CREATE TABLE t0 ( 288 f1 bigint(20) DEFAULT NULL, 289 f2 char(50) DEFAULT NULL 290) ENGINE=MEMORY DEFAULT CHARSET=latin1; 291INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two'); 292 293CREATE TABLE t1 ( 294 f1 decimal(64,30) DEFAULT NULL, 295 f2 varchar(50) DEFAULT NULL 296) ENGINE=MEMORY DEFAULT CHARSET=latin1; 297 298INSERT INTO t1 VALUES 299(NULL,'numeric column is NULL'), 300(0.000000000000000000000000000000,NULL), 301(5.000000000000000000000000000000,'five'), 302(1.000000000000000000000000000000,'one'), 303(3.000000000000000000000000000000,'three'); 304 305CREATE TABLE t2 ( 306 f1 double DEFAULT NULL, 307 f2 varbinary(50) DEFAULT NULL 308) ENGINE=MEMORY DEFAULT CHARSET=latin1; 309INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three'); 310 311create VIEW v0 AS select f1,f2 from t1 ; 312 313let $cnt= 27; 314while ($cnt) 315{ 316# i runs from 1 to 27 317 let $i= `select 28 - $cnt`; 318 let $prev=`select $i - 1`; 319 320# rem = i mod 3 321 let $rem= `select MOD($i, 3)`; 322# view uses $i, $prev and $rem: 323 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; 324 dec $cnt; 325} 326 327EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; 328--echo # This used to hang forever: 329EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; 330 331use test; 332drop database test1; 333set join_cache_level=@tmp_jcl; 334set optimizer_switch=@tmp_os; 335 336 337--echo # 338--echo # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() 339--echo # 340create table t1(a int); 341insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 342 343create table t2 (a int, b int, c text); 344insert into t2 345select 346 A.a + B.a* 10, 347 A.a + B.a* 10, 348 'blob-data' 349from t1 A, t1 B; 350 351set @tmp_jcl= @@join_cache_level; 352set @tmp_os= @@optimizer_switch; 353set join_cache_level=6; 354set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on'; 355explain 356select * from 357 t1 join 358 (select * from t2 order by a limit 1000) as D1 359where 360 D1.a= t1.a; 361 362set join_cache_level=@tmp_jcl; 363set optimizer_switch=@tmp_os; 364drop table t1, t2; 365 366--echo # 367--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 368--echo # 369 370CREATE TABLE t1 ( 371 id int NOT NULL, 372 amount decimal DEFAULT NULL, 373PRIMARY KEY (id) 374); 375 376CREATE TABLE t2 ( 377 id int NOT NULL, 378 name varchar(50) DEFAULT NULL, 379PRIMARY KEY (id) 380); 381 382INSERT INTO t1 VALUES 383(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), 384(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); 385 386INSERT INTO t2 VALUES 387(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), 388(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); 389 390set @save_optimizer_switch= @@optimizer_switch; 391set optimizer_switch='split_materialized=off'; 392 393set join_cache_level=4; 394 395EXPLAIN 396SELECT t2.id,t2.name,t.total_amt 397 FROM t2 398 LEFT JOIN 399 (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t 400 ON t2.id=t.id 401 WHERE t2.id < 3; 402 403set join_cache_level=default; 404 405set optimizer_switch= @save_optimizer_switch; 406 407DROP TABLE t1,t2; 408 409# 410# MDEV-25182: Complex query in Store procedure corrupts results 411# 412set @save_optimizer_switch= @@optimizer_switch; 413set optimizer_switch="derived_merge=on"; 414 415CREATE TABLE t1 (id int, d2 datetime, id1 int) ; 416insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3); 417 418CREATE TABLE t2 (id int, d1 datetime, id1 int) ; 419insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2); 420 421prepare stmt from " 422SELECT * from 423 (SELECT min(d2) AS d2, min(d1) AS d1 FROM 424 (SELECT t1.d2 AS d2, (SELECT t2.d1 425 FROM t2 WHERE t1.id1 = t2.id1 426 ORDER BY t2.id DESC LIMIT 1) AS d1 427 FROM t1 428 ) dt2 429 ) ca 430 ORDER BY ca.d2;"; 431 432execute stmt; 433execute stmt; 434 435set optimizer_switch= @save_optimizer_switch; 436DROP TABLE t1, t2; 437 438--echo # 439--echo # End of 10.3 tests 440--echo # 441 442# The following command must be the last one the file 443set optimizer_switch=@exit_optimizer_switch; 444