Fixed LP bug #806504.

Missing initialization of the bitmap not_null_tables_cache to 0 
in the function Item_func::eval_not_null_tables caused this bug.
This function is called indirectly from the function
SELECT_LEX::update_used_tables after merging mergeable views and
derived tables into the main query. The leaf tables of resulting
query may change their bitmap numbers after this merge. That's why
the not_null_tables_cache bitmaps must be updated. Due to the bug 
mentioned above the result of the re-evaluation of the 
not_null_tables_cache turned out to be incorrect in some cases.
This could trigger an invalid conversion of outer joins into 
inner joins leading to invalid query result sets.

Also removed an implicit conversion from int to bool in the function
SELECT_LEX::update_used_tables.

4 files changed, 65 insertions, 1 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 94a7c00a3b1..9434cccec0d 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -942,3 +942,39 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #806504: right join over a view/derived table           
+#
+CREATE TABLE t1 (a int, b int) ;
+INSERT IGNORE INTO t1 VALUES (0,0);
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (0), (0);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+WHERE t.a IN (SELECT b FROM t1);
+a	a	b
+NULL	0	0
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+WHERE t.a IN (SELECT b FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` left join `test`.`t2` on((0 <> 0)) where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from `test`.`t1` where (<cache>(0) = 0))))
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+WHERE t.a IN (SELECT b FROM t1);
+a	a	b
+NULL	0	0
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+WHERE t.a IN (SELECT b FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` left join `test`.`t2` on((0 <> 0)) where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from `test`.`t1` where (<cache>(0) = 0))))
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d7055f71382..dcad590676c 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -531,3 +531,30 @@ SELECT DISTINCT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #806504: right join over a view/derived table           
+--echo #
+
+CREATE TABLE t1 (a int, b int) ;
+INSERT IGNORE INTO t1 VALUES (0,0);
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (0), (0);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 54c60ea03e1..033537092d8 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -234,6 +234,7 @@ bool
 Item_func::eval_not_null_tables(uchar *opt_arg)
 {
   Item **arg,**arg_end;
+  not_null_tables_cache= 0;
   if (arg_count)
   {		
     for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 39672d0c19b..8fef7ef2f9d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3476,7 +3476,7 @@ void SELECT_LEX::update_used_tables()
          !tl->table->maybe_null && embedding;
          embedding= embedding->embedding)
     {
-      tl->table->maybe_null= embedding->outer_join;
+      tl->table->maybe_null= test(embedding->outer_join);
     }
     if (tl->on_expr)
     {