List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:March 24 2011 9:27am
Subject:bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4252 to 4253)
Bug#51070
View as plain text  
 4253 Ole John Aske	2011-03-24
      Cherry picked fix for bug#51070 to mysql-5.1-telco-7.0.
      
      Required as this fix has already been cherrypicked to the SPJ branch,
      and we want to minimize the diff between SPJ and main telco branches.
      
      See: http://lists.mysql.com/commits/117686 for original commit message.

    modified:
      mysql-test/r/subselect4.result
      mysql-test/t/subselect4.test
      sql/item_cmpfunc.cc
      sql/item_subselect.cc
 4252 Ole John Aske	2011-03-23
      Cherry picked & backported fix for bug#11764737:
       'Optimizer is overly eager to request ordered access.'
      
        1)  Ensure that JOIN_TAB::sorted and QUICK_SELECT_I::sorted is
            requested only when strict ordering is required from the 
            handler - Either as a result of the query specifying 
            ORDER/GROUP BY, or the handler being a source in a 
            QUICK access method which require the sources to be ordered
            
        2a Call handler::ha_index_init(int idx, bool sorted) 
           with 'sorted==false' in any access methods requesting a 
           single row (HA_READ_KEY_EXACT) (join_read_key(), .....
            
        2b) Else: Always use above 'sorted' attributes as arguments
            to handler::ha_index_init() and other handler methods 
            having a 'bool sorted' argument(::read_range_first())
      
      Furthermore the function 'disable_sorted_access(JOIN_TAB* join_tab)'
      has been introduced which collect all logic for turning off sort requirement. 
     @ mysql-test/r/partition.result
        Accept changed result order for this (unordered) result set.

    modified:
      mysql-test/r/partition.result
      sql/ha_ndbcluster.cc
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-08-05 10:42:14 +0000
+++ b/mysql-test/r/subselect4.result	2011-03-24 09:27:05 +0000
@@ -77,6 +77,92 @@ 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;
+# 
+# Bug#51070: Query with a NOT IN subquery predicate returns a wrong
+# result set
+# 
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL );
+CREATE TABLE t2 ( c INT, d INT );
+INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 );
+CREATE TABLE t3 ( e INT, f INT );
+INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL );
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (2), (3);
+CREATE TABLE t5 ( a INT );
+INSERT INTO t5 VALUES (NULL), (2);
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	PRIMARY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+a	b
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL;
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN;
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN;
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+a	b
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	PRIMARY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
+a	b
+EXPLAIN
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	PRIMARY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
+c	d
+EXPLAIN
+SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	PRIMARY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
+SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
+e	f
+EXPLAIN
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	PRIMARY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
+c	d
+SELECT * FROM t1 WHERE ( a, b ) NOT IN 
+( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 );
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 );
+a	b
+1	NULL
+2	NULL
+SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 );
+a	b
+1	NULL
+2	NULL
+DROP TABLE t1, t2, t3, t4, t5;
 #
 # End of 5.1 tests.
 #

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-08-05 10:42:14 +0000
+++ b/mysql-test/t/subselect4.test	2011-03-24 09:27:05 +0000
@@ -74,6 +74,68 @@ 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 # 
+--echo # Bug#51070: Query with a NOT IN subquery predicate returns a wrong
+--echo # result set
+--echo # 
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL );
+
+CREATE TABLE t2 ( c INT, d INT );
+INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 );
+
+CREATE TABLE t3 ( e INT, f INT );
+INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL );
+
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (2), (3);
+
+CREATE TABLE t5 ( a INT );
+INSERT INTO t5 VALUES (NULL), (2);
+
+--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
+SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL;
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN;
+SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN;
+
+SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 );
+
+--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
+SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
+
+--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
+
+--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
+SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
+
+--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
+SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
+
+SELECT * FROM t1 WHERE ( a, b ) NOT IN 
+  ( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 );
+
+SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 );
+
+SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 );
+
+DROP TABLE t1, t2, t3, t4, t5;
+
 
 --echo #
 --echo # End of 5.1 tests.

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-01-14 08:54:47 +0000
+++ b/sql/item_cmpfunc.cc	2011-03-24 09:27:05 +0000
@@ -1752,6 +1752,76 @@ bool Item_in_optimizer::fix_fields(THD *
 }
 
 
+/**
+   The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
+   predicates. The implementation works as follows.
+
+   For the current value of the outer expression
+   
+   - If it contains only NULL values, the original (before rewrite by the
+     Item_in_subselect rewrite methods) inner subquery is non-correlated and
+     was previously executed, there is no need to re-execute it, and the
+     previous return value is returned.
+
+   - If it contains NULL values, check if there is a partial match for the
+     inner query block by evaluating it. For clarity we repeat here the
+     transformation previously performed on the sub-query. The expression
+
+     <tt>
+     ( oc_1, ..., oc_n ) 
+     \<in predicate\>
+     ( SELECT ic_1, ..., ic_n
+       FROM \<table\>
+       WHERE \<inner where\> 
+     )
+     </tt>
+
+     was transformed into
+     
+     <tt>
+     ( oc_1, ..., oc_n ) 
+     \<in predicate\>
+     ( SELECT ic_1, ..., ic_n 
+       FROM \<table\> 
+       WHERE \<inner where\> AND ... ( ic_k = oc_k OR ic_k IS NULL ) 
+       HAVING ... NOT ic_k IS NULL
+     )
+     </tt>
+
+     The evaluation will now proceed according to special rules set up
+     elsewhere. These rules include:
+
+     - The HAVING NOT \<inner column\> IS NULL conditions added by the
+       aforementioned rewrite methods will detect whether they evaluated (and
+       rejected) a NULL value and if so, will cause the subquery to evaluate
+       to NULL. 
+
+     - The added WHERE and HAVING conditions are present only for those inner
+       columns that correspond to outer column that are not NULL at the moment.
+     
+     - If there is an eligible index for executing the subquery, the special
+       access method "Full scan on NULL key" is employed which ensures that
+       the inner query will detect if there are NULL values resulting from the
+       inner query. This access method will quietly resort to table scan if it
+       needs to find NULL values as well.
+
+     - Under these conditions, the sub-query need only be evaluated in order to
+       find out whether it produced any rows.
+     
+       - If it did, we know that there was a partial match since there are
+         NULL values in the outer row expression.
+
+       - If it did not, the result is FALSE or UNKNOWN. If at least one of the
+         HAVING sub-predicates rejected a NULL value corresponding to an outer
+         non-NULL, and hence the inner query block returns UNKNOWN upon
+         evaluation, there was a partial match and the result is UNKNOWN.
+
+   - If it contains no NULL values, the call is forwarded to the inner query
+     block.
+
+     @see Item_in_subselect::val_bool()
+     @see Item_is_not_null_test::val_int()
+ */
 longlong Item_in_optimizer::val_int()
 {
   bool tmp;
@@ -1805,7 +1875,7 @@ longlong Item_in_optimizer::val_int()
           all_left_cols_null= false;
       }
 
-      if (!((Item_in_subselect*)args[1])->is_correlated && 
+      if (!item_subs->is_correlated && 
           all_left_cols_null && result_for_null_param != UNKNOWN)
       {
         /* 
@@ -1819,8 +1889,11 @@ longlong Item_in_optimizer::val_int()
       else 
       {
         /* The subquery has to be evaluated */
-        (void) args[1]->val_bool_result();
-        null_value= !item_subs->engine->no_rows();
+        (void) item_subs->val_bool_result();
+        if (item_subs->engine->no_rows())
+          null_value= item_subs->null_value;
+        else
+          null_value= TRUE;
         if (all_left_cols_null)
           result_for_null_param= null_value;
       }

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-01-14 13:36:47 +0000
+++ b/sql/item_subselect.cc	2011-03-24 09:27:05 +0000
@@ -50,7 +50,7 @@ Item_subselect::Item_subselect():
     item value is NULL if select_subselect not changed this value
     (i.e. some rows will be found returned)
   */
-  null_value= 1;
+  null_value= TRUE;
 }
 
 
@@ -430,9 +430,9 @@ void Item_maxmin_subselect::print(String
 
 void Item_singlerow_subselect::reset()
 {
-  null_value= 1;
+  null_value= TRUE;
   if (value)
-    value->null_value= 1;
+    value->null_value= TRUE;
 }
 
 
@@ -577,7 +577,7 @@ double Item_singlerow_subselect::val_rea
   DBUG_ASSERT(fixed == 1);
   if (!exec() && !value->null_value)
   {
-    null_value= 0;
+    null_value= FALSE;
     return value->val_real();
   }
   else
@@ -592,7 +592,7 @@ longlong Item_singlerow_subselect::val_i
   DBUG_ASSERT(fixed == 1);
   if (!exec() && !value->null_value)
   {
-    null_value= 0;
+    null_value= FALSE;
     return value->val_int();
   }
   else
@@ -606,7 +606,7 @@ String *Item_singlerow_subselect::val_st
 {
   if (!exec() && !value->null_value)
   {
-    null_value= 0;
+    null_value= FALSE;
     return value->val_str(str);
   }
   else
@@ -621,7 +621,7 @@ my_decimal *Item_singlerow_subselect::va
 {
   if (!exec() && !value->null_value)
   {
-    null_value= 0;
+    null_value= FALSE;
     return value->val_decimal(decimal_value);
   }
   else
@@ -636,7 +636,7 @@ bool Item_singlerow_subselect::val_bool(
 {
   if (!exec() && !value->null_value)
   {
-    null_value= 0;
+    null_value= FALSE;
     return value->val_bool();
   }
   else
@@ -654,7 +654,7 @@ Item_exists_subselect::Item_exists_subse
   bool val_bool();
   init(select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
-  null_value= 0; //can't be NULL
+  null_value= FALSE; //can't be NULL
   maybe_null= 0; //can't be NULL
   value= 0;
   DBUG_VOID_RETURN;
@@ -817,15 +817,14 @@ double Item_in_subselect::val_real()
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
-  null_value= 0;
+  null_value= was_null= FALSE;
   if (exec())
   {
     reset();
-    null_value= 1;
     return 0;
   }
   if (was_null && !value)
-    null_value= 1;
+    null_value= TRUE;
   return (double) value;
 }
 
@@ -838,15 +837,14 @@ longlong Item_in_subselect::val_int()
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
-  null_value= 0;
+  null_value= was_null= FALSE;
   if (exec())
   {
     reset();
-    null_value= 1;
     return 0;
   }
   if (was_null && !value)
-    null_value= 1;
+    null_value= TRUE;
   return value;
 }
 
@@ -859,16 +857,15 @@ String *Item_in_subselect::val_str(Strin
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
-  null_value= 0;
+  null_value= was_null= FALSE;
   if (exec())
   {
     reset();
-    null_value= 1;
     return 0;
   }
   if (was_null && !value)
   {
-    null_value= 1;
+    null_value= TRUE;
     return 0;
   }
   str->set((ulonglong)value, &my_charset_bin);
@@ -879,20 +876,14 @@ String *Item_in_subselect::val_str(Strin
 bool Item_in_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
-  null_value= 0;
+  null_value= was_null= FALSE;
   if (exec())
   {
     reset();
-    /* 
-      Must mark the IN predicate as NULL so as to make sure an enclosing NOT
-      predicate will return FALSE. See the comments in 
-      subselect_uniquesubquery_engine::copy_ref_key for further details.
-    */
-    null_value= 1;
     return 0;
   }
   if (was_null && !value)
-    null_value= 1;
+    null_value= TRUE;
   return value;
 }
 
@@ -903,16 +894,15 @@ my_decimal *Item_in_subselect::val_decim
     method should not be used
   */
   DBUG_ASSERT(0);
-  null_value= 0;
+  null_value= was_null= FALSE;
   DBUG_ASSERT(fixed == 1);
   if (exec())
   {
     reset();
-    null_value= 1;
     return 0;
   }
   if (was_null && !value)
-    null_value= 1;
+    null_value= TRUE;
   int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
   return decimal_value;
 }

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4252 to 4253)Bug#51070Ole John Aske24 Mar