summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj.result54
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result54
-rw-r--r--mysql-test/t/subselect_sj.test40
-rw-r--r--sql/opt_subselect.cc21
4 files changed, 168 insertions, 1 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;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 645afa6744a..84e06fda852 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1103,7 +1103,26 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
do
{
embedded= embedding;
- if (test(embedded->outer_join))
+ bool block_conversion_to_sj= false;
+ if (embedded->on_expr)
+ {
+ /*
+ Conversion of an IN subquery predicate into semi-join
+ is blocked now if the predicate occurs:
+ - in the ON expression of an outer join
+ - in the ON expression of an inner join embedded directly
+ or indirectly in the inner nest of an outer join
+ */
+ for (TABLE_LIST *tl= embedded; tl; tl= tl->embedding)
+ {
+ if (tl->outer_join)
+ {
+ block_conversion_to_sj= true;
+ break;
+ }
+ }
+ }
+ if (block_conversion_to_sj)
{
Item *cond= embedded->on_expr;
if (!cond)
View Lorry Controller Status