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.

3 files changed, 45 insertions, 0 deletions

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
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 5e18f77c422..01f1980b4c1 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1162,3 +1162,22 @@ DROP TABLE t1;
 
 
 --echo End of 5.1 tests
+
+--echo #
+--echo # LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery
+--echo # predicate in WHERE condition.
+--echo #
+
+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);
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7);
+drop table t1,t2,t3;
+
+--echo End of 5.2 tests
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index ca3989d8b2c..5f8a7c6bd22 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -257,6 +257,7 @@ public:
   {}
   bool fix_fields(THD *, Item **);
   bool fix_left(THD *thd, Item **ref);
+  table_map not_null_tables() const { return 0; }
   bool is_null();
   longlong val_int();
   void cleanup();
@@ -473,6 +474,7 @@ public:
      show(0)
     {}
   virtual void top_level_item() { abort_on_null= 1; }
+  table_map not_null_tables() const { return 0; }
   bool top_level() { return abort_on_null; }
   longlong val_int();
   enum Functype functype() const { return NOT_ALL_FUNC; }