From f2cbc014d98a927ed7038f55a25c3d288de10f51 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 May 2012 09:35:46 +0300 Subject: fix for LP bug#994392 The not_null_tables() of Item_func_not_all and Item_in_optimizer was inherited from Item_func by mistake. It made the optimizer think that subquery predicates with ALL/ANY/IN were null-rejecting. This could trigger invalid conversions of outer joins into inner joins. --- mysql-test/r/join_outer.result | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/r/join_outer.result') diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index e7636573201..12989fdb036 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1596,3 +1596,27 @@ f 1 DROP TABLE t1; End of 5.1 tests +# +# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery +# predicate in WHERE condition. +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(9); +CREATE TABLE t2(b INT); +INSERT INTO t2 VALUES(8); +CREATE TABLE t3(c INT); +INSERT INTO t3 VALUES(3); +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +drop table t1,t2,t3; +End of 5.2 tests -- cgit v1.2.1