#At file:///home/rl136806/mysql/repo/mysql-work3/ based on revid:roy.lyseng@stripped
3368 Roy Lyseng 2011-05-05
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 adjust the value of
not_null_tables_cache for Item_in_optimizer::fix_fields().
If the Item_in_subselect member is "top level", meaning that the
original query is an IN predicate, use the accumulated not-null
information for the object, otherwise (the predicate is NOT IN)
remove the set of tables referred from the left-hand expression
from the accumulated not-null information.
mysql-test/include/subquery.inc
Added test case for bug#11764086.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
Added test results for bug#11764086.
sql/item_cmpfunc.cc
Adjusted implementation of not_null_tables_cache in
Item_in_optimizer::fix_fields().
sql/item_func.h
Added some comments for relevant Item members.
modified:
mysql-test/include/subquery.inc
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
sql/item_cmpfunc.cc
sql/item_func.h
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2011-04-15 08:11:49 +0000
+++ b/mysql-test/include/subquery.inc 2011-05-05 07:41:53 +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
#
@@ -5101,6 +5099,54 @@ SELECT 1 FROM
DROP TABLE t1;
+--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 # 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.5 tests.
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_all.result 2011-05-05 07:41:53 +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,
@@ -6242,6 +6241,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2011-04-28 11:53:14 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5904,7 +5904,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,
@@ -6246,6 +6245,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-05-05 07:41:53 +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,
@@ -6242,6 +6241,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-04-28 11:53:14 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5904,7 +5904,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,
@@ -6246,6 +6245,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_none.result 2011-05-05 07:41:53 +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,
@@ -6241,6 +6240,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result 2011-04-28 11:53:14 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5903,7 +5903,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,
@@ -6245,6 +6244,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# 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);
+# 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.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2011-04-26 08:49:10 +0000
+++ b/sql/item_cmpfunc.cc 2011-05-05 07:41:53 +0000
@@ -1764,6 +1764,17 @@ bool Item_in_optimizer::fix_fields(THD *
with_sum_func= with_sum_func || args[1]->with_sum_func;
used_tables_cache|= args[1]->used_tables();
not_null_tables_cache|= args[1]->not_null_tables();
+
+ if (!sub->is_top_level_item())
+ {
+ /*
+ This is a NOT IN subquery predicate (or equivalent). Null values passed
+ from outer tables and used in the left-hand expression of the predicate
+ must be considered in the evaluation, hence filter out these tables
+ from the set of null-rejecting tables.
+ */
+ not_null_tables_cache&= ~args[0]->not_null_tables();
+ }
const_item_cache&= args[1]->const_item();
fixed= 1;
return FALSE;
@@ -1791,6 +1802,7 @@ void Item_in_optimizer::fix_after_pullou
const_item_cache&= args[1]->const_item();
}
+
/**
The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
predicates. The implementation works as follows.
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2011-04-15 09:04:21 +0000
+++ b/sql/item_func.h 2011-05-05 07:41:53 +0000
@@ -37,7 +37,11 @@ protected:
uint allowed_arg_cols;
public:
uint arg_count;
- table_map used_tables_cache, not_null_tables_cache;
+ /// Value used in calculation of result of used_tables()
+ table_map used_tables_cache;
+ /// Value used in calculation of result of not_null_tables()
+ table_map not_null_tables_cache;
+ /// Value used in calculation of result of const_item()
bool const_item_cache;
enum Functype { UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC,
GE_FUNC,GT_FUNC,FT_FUNC,
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110505074153-x0o6dqsgd01ld9nz.bundle
