BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)

- The bug was caused by outer join being incorrectly converted into inner because of 
  invalid return values of Item_direct_view_ref::not_null_tables().
- Provided a correct Item_direct_view_ref::not_null_tables() function.

4 files changed, 49 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 50d2ea1e940..4e184f1ceec 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4352,3 +4352,23 @@ WHERE t4.a >= v1.a);
 a	a
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin) 
+#
+CREATE TABLE t1 ( a int, b int );
+INSERT INTO t1 VALUES (0,0),(0,0);
+CREATE TABLE t2 ( a int, b int );
+INSERT IGNORE INTO t2 VALUES (1,0),(1,0);
+CREATE TABLE t3 ( b int );
+INSERT IGNORE INTO t3 VALUES (0),(0);
+CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
+SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+a	b	a	b
+NULL	NULL	1	0
+NULL	NULL	1	0
+SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+a	b	a	b
+NULL	NULL	1	0
+NULL	NULL	1	0
+DROP VIEW v2;
+DROP TABLE t1, t2, t3;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 034f8a4ca6c..7486ffc38f8 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4286,3 +4286,25 @@ SELECT * FROM v1, t2
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+
+
+--echo #
+--echo # BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin) 
+--echo #
+
+CREATE TABLE t1 ( a int, b int );
+INSERT INTO t1 VALUES (0,0),(0,0);
+
+CREATE TABLE t2 ( a int, b int );
+INSERT IGNORE INTO t2 VALUES (1,0),(1,0);
+
+CREATE TABLE t3 ( b int );
+INSERT IGNORE INTO t3 VALUES (0),(0);
+
+CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
+SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+
+SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ;
+
+DROP VIEW v2;
+DROP TABLE t1, t2, t3;
diff --git a/sql/item.cc b/sql/item.cc
index 4fcffcfbc2a..d0992a379e0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9106,6 +9106,12 @@ table_map Item_direct_view_ref::used_tables() const
          (view->merged ? (*ref)->used_tables() : view->table->map); 
 }
 
+table_map Item_direct_view_ref::not_null_tables() const		
+{
+  return get_depended_from() ? 
+         0 :
+         (view->merged ? (*ref)->not_null_tables() : view->table->map); 
+}
 
 /*
   we add RAND_TABLE_BIT to prevent moving this item from HAVING to WHERE
diff --git a/sql/item.h b/sql/item.h
index 99eabdd81ab..8cacd231fbf 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2919,6 +2919,7 @@ public:
   Item *equal_fields_propagator(uchar *arg);
   Item *replace_equal_field(uchar *arg);
   table_map used_tables() const;	
+  table_map not_null_tables() const;
   bool walk(Item_processor processor, bool walk_subquery, uchar *arg)
   { 
     return (*ref)->walk(processor, walk_subquery, arg) ||