BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON

- Correct handling for SJ-Materialization + outer joins (details in the comments in the code)

4 files changed, 73 insertions, 2 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 7e4cf4ed9f1..f368f787407 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1797,6 +1797,20 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select 8 AS `a` from `test`.`t1` where <expr_cache><8>(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` having (<cache>(8) = <ref_null_helper>(min(`test`.`t1`.`a`))))))
 DROP TABLE t1;
+#
+# BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON
+#
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4);
+CREATE TABLE t2 ( b INT NOT NULL, c INT );
+INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1);
+SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
+WHERE a IN ( SELECT c FROM t2 );
+a	b	c
+4	4	2
+4	4	2
+4	4	4
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index bdfa834977b..f8d8505926c 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1834,6 +1834,20 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select 8 AS `a` from  <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1`) join `test`.`t1` where 1
 DROP TABLE t1;
+#
+# BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON
+#
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4);
+CREATE TABLE t2 ( b INT NOT NULL, c INT );
+INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1);
+SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
+WHERE a IN ( SELECT c FROM t2 );
+a	b	c
+4	4	2
+4	4	2
+4	4	4
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index a0d24aa53ed..b9e0beaa5e3 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1497,6 +1497,19 @@ EXPLAIN EXTENDED
 
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON
+--echo #
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4);
+CREATE TABLE t2 ( b INT NOT NULL, c INT );
+INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1);
+
+SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
+  WHERE a IN ( SELECT c FROM t2 );
+
+DROP TABLE t1,t2;
+
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 2c17e5dbff6..dc9485e0f94 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8343,9 +8343,39 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
           COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
                                               current_map, /*(tab - first_tab)*/ -1,
 					      FALSE, FALSE);
-          if (tab == first_inner_tab && tab->on_precond)
+          bool is_sjm_lookup_tab= FALSE;
+          if (tab->bush_children)
+          {
+            /*
+              'tab' is an SJ-Materialization tab, i.e. we have a join order 
+              like this:
+
+                ot1 sjm_tab LEFT JOIN ot2 ot3
+                         ^          ^
+                   'tab'-+          +--- left join we're adding triggers for
+
+              LEFT JOIN's ON expression may not have references to subquery
+              columns.  The subquery was in the WHERE clause, so IN-equality 
+              is in the WHERE clause, also.
+              However, equality propagation code may have propagated the
+              IN-equality into ON expression, and we may get things like
+
+                subquery_inner_table=const
+
+              in the ON expression. We must not check such conditions during
+              SJM-lookup, because 1) subquery_inner_table has no valid current
+              row (materialization temp.table has it instead), and 2) they
+              would be true anyway.
+            */
+            SJ_MATERIALIZATION_INFO *sjm=
+              tab->bush_children->start->emb_sj_nest->sj_mat_info;
+            if (sjm->is_used && !sjm->is_sj_scan)
+              is_sjm_lookup_tab= TRUE;
+          }
+
+          if (tab == first_inner_tab && tab->on_precond && !is_sjm_lookup_tab)
             add_cond_and_fix(thd, &tmp_cond, tab->on_precond);
-          if (tmp_cond)
+          if (tmp_cond && !is_sjm_lookup_tab)
           {
             JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
             Item **sel_cond_ref= tab < first_inner_tab ?