#At file:///home/rl136806/mysql/repo/mysql-trunk/ based on revid:tor.didriksen@stripped
3781 Roy Lyseng 2011-03-17
Bug#11764086 Bug#56881: Null left operand to NOT IN in WHERE clause
behaves differently than real NULL
The query that triggers this problem is an outer join query with
a NOT IN subquery predicate in the WHERE clause.
One of the tables in the outer join contains only one row, and because
it is a MyISAM table, const table optimization is applied to it.
Now the problem: not_null_tables() for the NOT IN predicate reports
that we can ignore NULL-complemented rows for table child, hence the
query is converted from a left join to an inner join. This leads
the query executor to omit the row with id 2 from the evaluation.
As for why the not_null_tables() result for NOT IN is wrong:
NOT IN may return TRUE for a row, even if the left-hand expression
to NOT IN is NULL (as in NULL NOT IN (<empty subquery>)).
This is what happens when the query executor evaluates the row with
id 2 from the parent table: There is no corresponding row in the
child table, a NULL-complemented row should be added, and the
predicate NULL NOT IN (<empty subquery> is evaluated (and it should
return TRUE).
The solution to the problem is to implement not_null_tables()
for Item_in_optimizer. If the Item_in_subselect member is "top level",
meaning that the original query is an IN predicate, return the
accumulated not-null information for the object, otherwise
(the predicate is NOT IN) return an empty set.
mysql-test/include/subquery.inc
Added test case for bug#11764086.
mysql-test/r/subquery_nomat_nosj.result
Added test results for bug#11764086.
mysql-test/r/subquery_none.result
Added test results for bug#11764086.
sql/item_cmpfunc.cc
Added implementation for Item_in_optimizer::not_null_tables().
sql/item_cmpfunc.h
Added interface for Item_in_optimizer::not_null_tables().
modified:
mysql-test/include/subquery.inc
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_none.result
sql/item_cmpfunc.cc
sql/item_cmpfunc.h
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2011-02-02 09:04:55 +0000
+++ b/mysql-test/include/subquery.inc 2011-03-17 12:49:15 +0000
@@ -4740,8 +4740,6 @@ CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN (
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
---echo End of 5.1 tests.
-
#
# Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
#
@@ -5079,7 +5077,6 @@ SELECT 1 FROM
DROP TABLE t1;
-
--echo End of 5.5 tests.
--echo #
@@ -5203,6 +5200,67 @@ WHERE (col_int_key, col_int_key) IN (
DROP TABLE bb, b, cc, c;
+--echo #
+--echo # Bug#11764086: Null left operand to NOT IN in WHERE clause
+--echo # behaves differently than real NULL
+--echo #
+
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+
+--echo # Control query (c.other is always NULL)
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ );
+
+--echo # Offending query (c.parent_id is NULL for null-complemented rows only)
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ );
+
+--echo # Some syntactic variations with IS FALSE and IS NOT TRUE
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ ) IS NOT TRUE;
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ ) IS FALSE;
+
+DROP TABLE parent, child;
+
+--echo # End of test for bug#11764086.
+
--echo End of 5.6 tests
--echo #
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-03-17 12:49:15 +0000
@@ -5900,7 +5900,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6345,6 +6344,67 @@ ORDER BY parent1.col_date_key
);
col_int_key
DROP TABLE bb, b, cc, c;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Control query (c.other is always NULL)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.6 tests
#
# BUG#46743 "Azalea processing correlated, aggregate SELECT
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_none.result 2011-03-17 12:49:15 +0000
@@ -5899,7 +5899,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6344,6 +6343,67 @@ ORDER BY parent1.col_date_key
);
col_int_key
DROP TABLE bb, b, cc, c;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Control query (c.other is always NULL)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.6 tests
#
# BUG#46743 "Azalea processing correlated, aggregate SELECT
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2011-03-17 09:47:50 +0000
+++ b/sql/item_cmpfunc.cc 2011-03-17 12:49:15 +0000
@@ -1794,6 +1794,22 @@ void Item_in_optimizer::fix_after_pullou
const_item_cache&= args[1]->const_item();
}
+
+/**
+ For a NOT IN subquery predicate (for which the Item_in_subselect member is
+ not a \"top level\" item), null values passed from outer tables must be
+ considered in the evaluation, hence return an empty set of tables.
+*/
+
+table_map Item_in_optimizer::not_null_tables() const
+{
+ if (((Item_in_subselect*)args[1])->is_top_level_item())
+ return not_null_tables_cache;
+
+ return 0;
+}
+
+
/**
The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
predicates. The implementation works as follows.
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2011-03-09 20:54:55 +0000
+++ b/sql/item_cmpfunc.h 2011-03-17 12:49:15 +0000
@@ -276,6 +276,7 @@ public:
bool fix_left(THD *thd, Item **ref);
void fix_after_pullout(st_select_lex *parent_select,
st_select_lex *removed_select, Item **ref);
+ virtual table_map not_null_tables() const;
bool is_null();
longlong val_int();
void cleanup();
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110317124915-9gyau48qebnytp07.bundle
