diff options
Diffstat (limited to 'mysql-test/t/subselect_sj2_mat.test')
| -rw-r--r-- | mysql-test/t/subselect_sj2_mat.test | 305 |
1 files changed, 0 insertions, 305 deletions
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test deleted file mode 100644 index cfb6c8c2819..00000000000 --- a/mysql-test/t/subselect_sj2_mat.test +++ /dev/null @@ -1,305 +0,0 @@ -# -# Run subselect_sj2.test with subquery materialization. -# -set optimizer_switch='materialization=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; - ---source t/subselect_sj2.test - -set optimizer_switch=default; -select @@optimizer_switch like '%materialization=on%'; - ---echo # ---echo # BUG#906385: EXPLAIN EXTENDED crashes in TABLE_LIST::print with limited max_join_size ---echo # -CREATE TABLE t1 ( a INT ); -CREATE TABLE t2 ( b INT ); - -INSERT INTO t1 VALUES (1),(2); -INSERT INTO t2 VALUES - (1),(2),(3),(4),(5), - (6),(7),(8),(9),(10), - (11),(12),(13),(14),(15), - (16),(17),(18),(19),(20); - -set @tmp_906385=@@max_join_size; -SET max_join_size = 80; - ---error ER_TOO_BIG_SELECT -EXPLAIN EXTENDED -SELECT COUNT(*) FROM t1 -WHERE a IN - ( SELECT b FROM t2 GROUP BY b ) - AND ( 6 ) IN - ( SELECT MIN( t2.b ) FROM t2 alias1, t2 ); - -DROP TABLE t1, t2; -set max_join_size= @tmp_906385; - ---echo # ---echo # mdev-3995: Wrong result for semijoin with materialization ---echo # - -set @save_optimizer_switch=@@optimizer_switch; - -CREATE TABLE t1 ( - cat_id int(10) unsigned NOT NULL, - PRIMARY KEY (cat_id) -) ENGINE=MyISAM; - -INSERT INTO t1 VALUES -(709411),(709412),(709413),(709414),(709416),(709417),(709418),(709419),(709421),(709422), -(709424),(709425),(709427),(709428),(709429),(709431),(709432),(709433),(709434),(709435), -(709438),(709439),(709441),(709442),(709443),(709444),(709445),(709446),(709447),(709450), -(709451),(709454),(709455),(709456),(709457),(709459),(709460),(709461),(709462),(709463), -(709464),(709465),(709467),(709469),(709470),(709471),(709472),(709473),(709474),(709475), -(709476),(709477),(709478),(709479),(709480),(709481),(709483),(709484),(709485),(709487), -(709490),(709491),(709492),(709493),(709494),(709495),(709496),(709497),(709498),(709499), -(709500),(709501),(709502),(709503),(709504),(709505),(709506),(709507),(709509),(709510), -(709511),(709512),(709513),(709514),(709515),(709516),(709517),(709518),(709519),(709520), -(709521),(709522),(709523),(709524),(709525),(709526),(709527),(709528),(709529),(709530), -(709531),(709532),(709533),(709534),(709535),(709536),(709537),(709538),(709539),(709540), -(709541),(709542),(709543),(709544),(709545),(709546),(709548),(709549),(709551),(709552), -(709553),(709555),(709556),(709557),(709558),(709559),(709560),(709561),(709562),(709563), -(709564),(709565),(709566),(709567),(709568),(709569),(709570),(709571),(709572),(709573), -(709574),(709575),(709576),(709577),(709578),(709579),(709580),(709581),(709582),(709583), -(709584),(709585),(709586),(709587),(709588),(709590),(709591),(709592),(709593),(709594), -(709595),(709596),(709597),(709598),(709600),(709601),(709602),(709603),(709604),(709605), -(709606),(709608),(709609),(709610),(709611),(709612),(709613),(709614),(709615),(709616), -(709617),(709618),(709619),(709620),(709621),(709622),(709623),(709624),(709625),(709626), -(709627),(709628),(709629),(709630),(709631),(709632),(709633),(709634),(709635),(709637), -(709638),(709639),(709640),(709641),(709642),(709643),(709644),(709645),(709646),(709649), -(709650),(709651),(709652),(709653),(709654),(709655),(709656),(709657),(709658),(709659); - -CREATE TABLE t2 ( - cat_id int(10) NOT NULL, - KEY cat_id (cat_id) -) ENGINE=MyISAM; - -INSERT INTO t2 VALUES -(708742),(708755),(708759),(708761),(708766),(708769),(708796),(708798),(708824),(708825), -(708838),(708844),(708861),(708882),(708887),(708889),(708890),(709586),(709626); - -CREATE TABLE t3 ( - sack_id int(10) unsigned NOT NULL, - kit_id tinyint(3) unsigned NOT NULL DEFAULT '0', - cat_id int(10) unsigned NOT NULL, - PRIMARY KEY (sack_id,kit_id,cat_id) -) ENGINE=MyISAM; - -INSERT INTO t3 VALUES -(33479,6,708523),(33479,6,708632),(33479,6,709085),(33479,6,709586),(33479,6,709626); - -CREATE TABLE t4 ( - cat_id int(10) unsigned NOT NULL, - KEY cat_id (cat_id) -) ENGINE=MyISAM; - -INSERT INTO t4 (cat_id) SELECT cat_id from t2; - -set optimizer_switch='materialization=off'; - -EXPLAIN -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t2) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t2) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - -set optimizer_switch='materialization=on'; - -EXPLAIN -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t4) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t4) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - -EXPLAIN -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t2) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - -SELECT count(*) FROM t1, t3 - WHERE t1.cat_id = t3.cat_id AND - t3.cat_id IN (SELECT cat_id FROM t2) AND - t3.sack_id = 33479 AND t3.kit_id = 6; - - -DROP TABLE t1,t2,t3,t4; - -set optimizer_switch=@save_optimizer_switch; - ---echo # ---echo # mdev-3913: LEFT JOIN with materialized multi-table IN subquery in WHERE ---echo # - -set @save_optimizer_switch=@@optimizer_switch; - -CREATE TABLE t1 (a1 char(1), b1 char(1), index idx(b1,a1)); -INSERT INTO t1 VALUES ('f','c'),('d','m'),('g','y'); -INSERT INTO t1 VALUES ('f','c'),('d','m'),('g','y'); - -CREATE TABLE t2 (a2 char(1), b2 char(1)); -INSERT INTO t2 VALUES ('y','y'),('y','y'),('w','w'); - -CREATE TABLE t3 (a3 int); -INSERT INTO t3 VALUES (8),(6); - -CREATE TABLE t4 (a4 char(1), b4 char(1)); -INSERT INTO t4 VALUES ('y','y'),('y','y'),('w','w'); - -set optimizer_switch='materialization=off'; - -EXPLAIN EXTENDED -SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) - WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); -SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) - WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); - -set optimizer_switch='materialization=on'; - -EXPLAIN EXTENDED -SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) - WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); -SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) - WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); - -DROP TABLE t1,t2,t3,t4; - -set optimizer_switch=@save_optimizer_switch; - ---echo # ---echo # mdev-4172: LEFT JOIN with materialized multi-table IN subquery in WHERE ---echo # and OR in ON condition ---echo # - -set @save_optimizer_switch=@@optimizer_switch; - -CREATE TABLE t1 (a1 int, c1 varchar(1)); -INSERT t1 VALUES (7,'v'), (3,'y'); - -CREATE TABLE t2 (c2 varchar(1)); -INSERT INTO t2 VALUES ('y'), ('y'); - -CREATE TABLE t3 (c3 varchar(1)); -INSERT INTO t3 VALUES - ('j'), ('v'), ('c'), ('m'), ('d'), - ('d'), ('y'), ('t'), ('d'), ('s'); - -CREATE TABLE t4 (a4 int, c4 varchar(1)); -INSERT INTO t4 SELECT * FROM t1; - -set optimizer_switch='materialization=off'; - -EXPLAIN EXTENDED -SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') - WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); -SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') - WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); - -set optimizer_switch='materialization=on'; - -EXPLAIN EXTENDED -SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') - WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); -SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') - WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); - -DROP TABLE t1,t2,t3,t4; - -set optimizer_switch=@save_optimizer_switch; - ---echo # ---echo # mdev-4177: materialization of a subquery whose WHERE condition is OR ---echo # formula with two disjucts such that the second one is always false ---echo # - -set @save_optimizer_switch=@@optimizer_switch; -set @save_join_cache_level=@@join_cache_level; - -CREATE TABLE t1 (i1 int) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1), (7), (4), (8), (4); - -CREATE TABLE t2 (i2 int) ENGINE=MyISAM; -INSERT INTO t2 VALUES (7), (5); - -CREATE TABLE t3 (i3 int) ENGINE=MyISAM; -INSERT INTO t3 VALUES (7), (2), (9); - -set join_cache_level=3; - -set optimizer_switch='materialization=off,semijoin=off'; - -EXPLAIN EXTENDED -SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); -SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); - -set optimizer_switch='materialization=on,semijoin=on'; - -EXPLAIN EXTENDED -SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); -SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); - -EXPLAIN EXTENDED -SELECT * FROM t1 - WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); -SELECT * FROM t1 - WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); -SELECT * FROM t1 - WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 7 AND i3 = i2 OR 1=2); - -DROP TABLE t1,t2,t3; - -set join_cache_level= @save_join_cache_level; -set optimizer_switch=@save_optimizer_switch; - ---echo # ---echo # mdev-7791: materialization of a semi-join subquery + ---echo # RAND() in WHERE ---echo # (materialized table is accessed last) ---echo # - -set @save_optimizer_switch=@@optimizer_switch; -set optimizer_switch='materialization=on'; - -create table t1(i int); -insert into t1 values (1), (2), (3), (7), (9), (10); -create table t2(i int); -insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); - -select * from t1 where (rand() < 0) and i in (select i from t2); -explain extended -select * from t1 where (rand() < 0) and i in (select i from t2); - -drop table t1,t2; -set optimizer_switch=@save_optimizer_switch; - ---echo # ---echo # mdev-12855: materialization of a semi-join subquery + ORDER BY ---echo # - -CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES ('qux'),('foo'); -CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; -INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); - -let $q= -SELECT f1 FROM t1 -WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) -HAVING f1 != 'foo' -ORDER BY f1; - -eval $q; -eval explain $q; - -DROP TABLE t1,t2; |