Fix bug lp:817384

This bug is a special case of lp:813447.

Analysis:
Constant optimization finds that the condition t2.a = 1
can be used to access the primary key of table 't2'. As
a result both outer table t1,t2 are considered as constant
when we reach the execution phase. At the same time, during
constant optimization, the IN predicate is not evaluated
because it is expensive.

When execution of the outer query reaches do_select(),
control flow enter the branch:
if (join->table_count == join->const_tables)
{ ... }
This branch checks only the WHERE and HAVING clauses,
but doesn't check the ON clauses of the query. Since the
IN predicate was not evaluated during optimization, it is
not evaluated at all, thus execution doesn't detect that
the ON clause is FALSE.

Solution:
Similar to the patch for bug lp:813447, exclude system
tables from constant substitution based on unique key
lookups if there is an expensive ON condition on the
inner table.

1 files changed, 32 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index b5d26970d3a..cc3723734d8 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1561,3 +1561,35 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
 DROP TABLE t1,t2,t3;
+#
+# LP bug #817384 Wrong result with outer join + subquery in ON
+# clause +unique key
+#
+CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ;
+INSERT INTO t1 VALUES (1,'b');
+CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1,'a');
+create table t3 (c1 char(1), c2 char(2));
+insert into t3 values ('c','d');
+insert into t3 values ('c','d');
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+b
+NULL
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+b
+NULL
+DROP TABLE t1,t2,t3;