List:Commits« Previous MessageNext Message »
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
View as plain text  
 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
 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 \<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,

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3367 to 3368) Bug#56881Bug#11764086Roy Lyseng5 May