diff options
Diffstat (limited to 'mysql-test/main/join_outer.result')
| -rw-r--r-- | mysql-test/main/join_outer.result | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 9245111369f..6b671a0759f 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2511,4 +2511,72 @@ ORDER BY tb1.i1; v2 DROP TABLE t1,t2; # end of 5.5 tests +# +# MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 values (0),(1); +create table t3 (a int, b int, key(a)); +insert into t3 select A.a + B.a* 10 + C.a * 100, 12345 from t1 A, t1 B, t1 C; +# Uses range for table t3: +explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t3 range a a 5 NULL 5 Using where +# This must use range for table t3, too: +explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t3 range a a 5 NULL 5 Using where +# +# .. part 2: make sure condition selectivity can use the condition too. +# +alter table t3 drop key a; +set @tmp1=@@optimizer_use_condition_selectivity; +set @tmp2=@@use_stat_tables; +set @tmp3=@@histogram_size; +set use_stat_tables=preferably; +set optimizer_use_condition_selectivity=4; +set histogram_size=100; +analyze table t3 persistent for all; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +# t3.filtered is less than 100%: +explain extended select * from t1 left join t3 on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` left join `test`.`t3` on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 +# t3.filtered must less than 100%, too: +explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t3` join `test`.`t2`) on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 +drop table t1,t2,t3; +set optimizer_use_condition_selectivity= @tmp1; +set use_stat_tables= @tmp2; +set histogram_size= @tmp3; +# Another test +CREATE TABLE t1 (i1 int) ; +CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; +CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; +INSERT INTO t3 VALUES (2, NULL); +CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; +CREATE VIEW v4 AS SELECT * FROM t4; +SELECT 1 +FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 +LEFT JOIN v4 +RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk +WHERE t3.pk IN (2); +1 +drop view v4; +drop table t1,t2,t3,t4; SET optimizer_switch=@save_optimizer_switch; |