diff options
author | Igor Babaev <igor@askmonty.org> | 2017-05-17 16:16:54 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2017-05-17 16:16:54 -0700 |
commit | efb9f2617bde1654006a99af625859eb509d5448 (patch) | |
tree | 1b60247f430cc7c49e6fce8f40e332f0b9bc1a96 /mysql-test | |
parent | 7e9716310261bce124585cbffd85f3402390ec9d (diff) | |
download | mariadb-git-efb9f2617bde1654006a99af625859eb509d5448.tar.gz |
Fixed the bug mdev-12812.
This is another correction of the patch for bug mdev-12670.
If a derived table is merged into a select with STRAIGHT_JOIN
modifier all IN subquery predicates contained in the
specification of the derived table cannot be subject to
conversion to semi-joins.
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/derived_view.result | 21 | ||||
-rw-r--r-- | mysql-test/t/derived_view.test | 20 |
2 files changed, 41 insertions, 0 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 8af8db15319..f7062473a3f 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2617,5 +2617,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1) drop view v1; drop table t1,t2; +# +# Bug mdev-12812: mergeable derived / view with subqueries +# NOT subject to semi-join optimizations +# +CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('foo'); +CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar'),('qux'),('foo'); +SELECT STRAIGHT_JOIN * +FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; +c1 +foo +foo +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * +FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2`.`c2` from `test`.`t2` where (<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`))) +DROP TABLE t1, t2; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index cdddaf8f9d8..07fbe4980a5 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1911,6 +1911,26 @@ explain select * from v1; drop view v1; drop table t1,t2; +--echo # +--echo # Bug mdev-12812: mergeable derived / view with subqueries +--echo # NOT subject to semi-join optimizations +--echo # + +CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('foo'); + +CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar'),('qux'),('foo'); + +let $q= +SELECT STRAIGHT_JOIN * + FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1, t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; |