From 3769841d9e706ee018d5273d2901954b9a281c3e Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 8 Sep 2011 23:24:47 +0400 Subject: 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. --- mysql-test/r/view.result | 20 ++++++++++++++++++++ mysql-test/t/view.test | 22 ++++++++++++++++++++++ sql/item.cc | 6 ++++++ sql/item.h | 1 + 4 files changed, 49 insertions(+) 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) || -- cgit v1.2.1