diff options
Diffstat (limited to 'mysql-test/t/subselect_sj.test')
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 171 |
1 files changed, 139 insertions, 32 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 5ae968742aa..f34cf5ba338 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -10,6 +10,17 @@ drop procedure if exists p1; set @subselect_sj_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); +if (`select @join_cache_level_for_subselect_sj_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_sj_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_sj_test; +} + # The 'default' value within the scope of this test: set @save_optimizer_switch=@@optimizer_switch; @@ -1690,6 +1701,9 @@ INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), (20,6,5,'20:58:33','r','r'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + explain SELECT alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, @@ -1720,6 +1734,8 @@ WHERE FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; +set optimizer_switch=@tmp_optimizer_switch; + drop table t1,t2, t3; set optimizer_switch=@tmp_830993; set join_buffer_size= @tmp_830993_jbs; @@ -1761,52 +1777,43 @@ DROP TABLE t1, t2, t4, t5; --echo # --echo # BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size --echo # -CREATE TABLE t1 ( f2 int) ; -CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; -INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), - (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), - (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), - (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), - (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), - (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), - (3888,20,'USA','Yonkers'); +#CREATE TABLE t1 ( f2 int) ; +#CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; +#INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), +# (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), +# (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), +# (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), +# (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), +# (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), +# (3888,20,'USA','Yonkers'); -CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; -INSERT INTO t3 VALUES (86,'USA'); +#CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; +#INSERT INTO t3 VALUES (86,'USA'); -CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; -INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); +#CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; +#INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); -CREATE TABLE t5 ( f2 int) ; +#CREATE TABLE t5 ( f2 int) ; -CREATE TABLE t6 ( f4 varchar(3)); -INSERT INTO t6 VALUES ('RUS'),('USA'); +#CREATE TABLE t6 ( f4 varchar(3)); +#INSERT INTO t6 VALUES ('RUS'),('USA'); +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); set @tmp_mjs_861147= @@max_join_size; SET max_join_size=10; set @tmp_os_861147= @@optimizer_switch; set @@optimizer_switch='semijoin=on,materialization=on'; --error ER_TOO_BIG_SELECT -SELECT * -FROM t1 -WHERE ( 1 , 3 ) IN ( - SELECT t2.f1 , MAX( t3.f3 ) - FROM t2 - JOIN t3 - WHERE t3.f4 IN ( - SELECT t4.f5 - FROM t4 - STRAIGHT_JOIN t5 - WHERE t4.f4 < t2.f5 - ) -) AND ( 'p' , 'k' ) IN ( - SELECT f4 , f4 FROM t6 -); +explain +select * from t1 where a in (select max(A.a + B.a + C.a) from t1 A, t1 B, t1 C); + set max_join_size= @tmp_mjs_861147; set optimizer_switch= @tmp_os_861147; -DROP TABLE t1,t2,t3,t4,t5,t6; +#DROP TABLE t1,t2,t3,t4,t5,t6; +drop table t1; --echo # --echo # BUG#877288: Wrong result with semijoin + materialization + multipart key @@ -1910,5 +1917,105 @@ set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #901312: materialized semijoin + right join +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (4), (1); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4), (1); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (4), (1); + +set @tmp_otimizer_switch= @@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on,materialization=on'; + +EXPLAIN +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); + +set optimizer_switch= @tmp_otimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #901709: assertion failure with record count == 0 +--echo # + +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; + +EXPLAIN +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); + +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0 +--echo # +set @opl_901399= @@optimizer_prune_level; +set @os_091399= @@optimizer_switch; +SET optimizer_prune_level=0; +SET optimizer_switch = 'materialization=off'; + +CREATE TABLE t1 ( c INT ) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (0),(1),(2),(3),(4),(5), + (6),(7),(8),(9),(10),(11),(12); +CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3,20),(2,21),(3,22); + +SELECT * +FROM t1 AS alias1, t1 AS alias2 +WHERE ( alias1.c, alias2.c ) + IN ( + SELECT alias3.a, alias3.a + FROM t2 AS alias3, t2 alias4 + WHERE alias3.b = alias4.b + ); +set optimizer_prune_level= @opl_901399; +set optimizer_switch= @os_091399; + +DROP TABLE t1,t2; + +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; |