diff options
author | Igor Babaev <igor@askmonty.org> | 2017-05-17 14:29:13 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2017-05-17 14:29:13 -0700 |
commit | 7e9716310261bce124585cbffd85f3402390ec9d (patch) | |
tree | 34a2d56c8c7b67e070590dad1a272c2ffa71890d /mysql-test | |
parent | 934b8312817d4e8e0387fae0bd9cca3ffafbc7de (diff) | |
download | mariadb-git-7e9716310261bce124585cbffd85f3402390ec9d.tar.gz |
Fixed the bug mdev-12817/mdev-12820.
This patch is a correction of the patch for bug mdev-12670.
With the current code handling semi-joins the following must
be taken into account.
Conversion of an IN subquery predicate into semi-join
has to be blocked if the predicate occurs:
(a) in the ON expression of an outer join
(b) in the ON expression of an inner join embedded directly
or indirectly in the inner nest of an outer join.
The patch for mdev-12670 blocked conversion to semi-joins only
in the case (a), but not in the case (b). This patch blocks
the conversion in both cases.
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/subselect_sj.result | 54 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 54 | ||||
-rw-r--r-- | mysql-test/t/subselect_sj.test | 40 |
3 files changed, 148 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index c14b82f274e..74384141998 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3099,4 +3099,58 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) drop table t1,t2; set optimizer_switch= @tmp_mdev12675; +# +# MDEV-12817: subquery NOT subject to semi-join optimizations +# in ON expression of INNER JOIN +# +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +c1 +1 +2 +EXPLAIN EXTENDED SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1 +# mdev-12820 +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +c1 c2 c4 +1 NULL NULL +2 NULL NULL +EXPLAIN EXTENDED SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1 +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 33fd4c519ff..47dbdd782b5 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3113,6 +3113,60 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) drop table t1,t2; set optimizer_switch= @tmp_mdev12675; +# +# MDEV-12817: subquery NOT subject to semi-join optimizations +# in ON expression of INNER JOIN +# +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +c1 +1 +2 +EXPLAIN EXTENDED SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where (1 = `test`.`t4`.`c4`)))))) where 1 +# mdev-12820 +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +c1 c2 c4 +1 NULL NULL +2 NULL NULL +EXPLAIN EXTENDED SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1 +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 5e9a2c88f22..acee1a67d63 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2801,5 +2801,45 @@ select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); drop table t1,t2; set optimizer_switch= @tmp_mdev12675; +--echo # +--echo # MDEV-12817: subquery NOT subject to semi-join optimizations +--echo # in ON expression of INNER JOIN +--echo # + +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); + +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); + +let $q1= +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); + +eval $q1; +eval EXPLAIN EXTENDED $q1; + +let $q2= +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); + +--echo # mdev-12820 +eval $q2; +eval EXPLAIN EXTENDED $q2; + +DROP TABLE t1,t2,t3,t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; |