From: Roy Lyseng Date: May 5 2011 8:12am Subject: bzr push into mysql-trunk branch (roy.lyseng:3367 to 3368) Bug#56881 Bug#11764086 List-Archive: http://lists.mysql.com/commits/136721 X-Bug: 56881,11764086 Message-Id: <20110505081224.D13C51F4@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ()). 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 ( 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 3367 Roy Lyseng 2011-05-02 Bug#12407753: Time to compare a row is missing in cost calculation of semi-join Stage 1: Change cost factors to be multiplicative Basically, this patch changes the constant values TIME_FOR_COMPARE and TIME_FOR_COMPARE_ROWID to be the inverse of their previous values and gives them names that are more similar to other cost factors. As a consequence, all cost calculations involving these constants are changed from divisions to multiplications. sql/filesort.cc Replaced cost divisions with multiplications. sql/filesort_utils.cc Replaced cost divisions with multiplications. sql/ha_ndbcluster.cc Replaced cost divisions with multiplications. Removed redundant cast to (double). sql/handler.cc Replaced cost divisions with multiplications. Removed redundant cast to (double). sql/opt_range.cc Replaced cost divisions with multiplications. Removed redundant casts to (double). sql/sql_const.h Replaced definition of TIME_FOR_COMPARE with ROW_EVALUATE_COST. Replaced definition of TIME_FOR_COMPARE_ROWID with ROWID_COMPARE_COST. sql/sql_select.cc Replaced cost divisions with multiplications. Removed redundant casts to (double). sql/uniques.cc Replaced cost divisions with multiplications. modified: sql/filesort.cc sql/filesort_utils.cc sql/ha_ndbcluster.cc sql/handler.cc sql/opt_range.cc sql/sql_const.h sql/sql_select.cc sql/uniques.cc === 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 \ [NOT] IN \ 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, No bundle (reason: useless for push emails).