MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:April 12 2007 3:17pm
Subject:bk commit into 5.0 tree (gkodinov:1.2445) BUG#22855
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-04-12 18:17:38+03:00, gkodinov@stripped +3 -0
  Bug#22855: Corrected the handling of conversion errors and
  NULL key values in an IN predicate that is resolved by 
  unique_subquery (through subselect_uniquesubquery_engine).
  See code comments for further explanations.

  mysql-test/r/subselect3.result@stripped, 2007-04-12 18:17:29+03:00, gkodinov@stripped +18 -0
    Bug#22855: test case

  mysql-test/t/subselect3.test@stripped, 2007-04-12 18:17:30+03:00, gkodinov@stripped +18 -0
    Bug#22855: test case

  sql/item_subselect.cc@stripped, 2007-04-12 18:17:31+03:00, gkodinov@stripped +64 -7
    Bug#22855: corrected the handling of conversion errors and
    NULL key values in IN predicate that is resolved by index
    lookup.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B22855-5.0-opt

--- 1.154/sql/item_subselect.cc	2007-03-09 07:05:05 +02:00
+++ 1.155/sql/item_subselect.cc	2007-04-12 18:17:31 +03:00
@@ -775,6 +775,11 @@ bool Item_in_subselect::val_bool()
   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;
   }
@@ -1921,8 +1926,10 @@ int subselect_uniquesubquery_engine::sca
     Copy ref key and check for null parts in it.
 
   RETURN
-    FALSE - ok, index lookup key without keys copied.
-    TRUE  - an error occured while copying the key
+    FALSE - Proceed with further processing (according to 
+    tab->table->status and null_keypart).
+    TRUE - There was a NULL in the left hand IN operator. 
+            Make the result a NULL as well.
 */
 
 bool subselect_uniquesubquery_engine::copy_ref_key()
@@ -1942,13 +1949,53 @@ bool subselect_uniquesubquery_engine::co
       function.
     */
     null_keypart= (*copy)->null_key;
-    bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
-    if (null_keypart && !top_level)
-      break;
-    if ((tab->ref.key_err) & 1 || (null_keypart && top_level))
+    if (null_keypart)
     {
+      bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
+      if (top_level)
+        /*
+          We have a NULL key value (for the left IN operand) in a top level
+          query. This (according to the truth table in WL#1110) means 
+          that there can be no partial or complete match. So we return 
+          FAIL (!= 0) to:
+            - skip the table scan in  subselect_uniquesubquery_engine::exec
+            - make Item_in_subselect::val_bool () return NULL (so the enclosing
+              NOT predicate (if present) will also return FALSE).
+        */
+        DBUG_RETURN(1)
+      else
+        /*
+          We have a NULL key value (for the left IN operand) in a non-top level
+          subquery. This (according to the truth table in WL#1110) means
+          that we must check further. So we just return OK and let the table
+          scan run in subselect_uniquesubquery_engine::exec().
+        */
+        break;
+    }
+
+    /*
+      Check if the error is equal to STORE_KEY_FATAL. This is not expressed 
+      using the store_key::store_key_result enum  because ref.key_error may 
+      hold [0, 255] and we want only the value of 1 to be considered. 
+      It cannot be expressed as a comparison either because ref.key_err is 
+      a boolean.
+      TODO: fix the variable an return types. 
+    */
+    if ((tab->ref.key_err) & 1)
+    {
+      /*
+        We know that we'll find no rows in the subqeury because we can't
+        convert a key value (from the left IN operand) we are trying to 
+        find to the type of the column we are searching it into 
+        (the right IN operand). Thus we mark the table as depleted.
+        This also means that the enclosing NOT predicate (if present) must
+        return TRUE. We implement this by returning OK in this function (and
+        later in subselect_uniquesubquery_engine::exec) to make sure
+        Item_in_subselect::val_bool() will not mark the result as NULL and
+        hence the enclosing IN predicate will return TRUE.
+      */
       tab->table->status= STATUS_NOT_FOUND;
-      DBUG_RETURN(1);
+      break;
     }
   }
   DBUG_RETURN(0);
@@ -1991,10 +2038,20 @@ int subselect_uniquesubquery_engine::exe
   int error;
   TABLE *table= tab->table;
   empty_result_set= TRUE;
+  table->status= 0;
  
   /* TODO: change to use of 'full_scan' here? */
   if (copy_ref_key())
     DBUG_RETURN(1);
+  if (table->status)
+  {
+    /* 
+      We know that there will be no rows even if we scan. 
+      Can be set in copy_ref_key.
+    */
+    ((Item_in_subselect *) item)->value= 0;
+    DBUG_RETURN(0);
+  }
 
   if (null_keypart)
     DBUG_RETURN(scan_table());

--- 1.7/mysql-test/r/subselect3.result	2007-03-28 12:09:29 +03:00
+++ 1.8/mysql-test/r/subselect3.result	2007-04-12 18:17:29 +03:00
@@ -692,3 +692,21 @@ a	MAX(b)	test
 2	3	h
 3	4	i
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(-1), (65),(66);
+CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY);
+INSERT INTO t2 VALUES (65),(66);
+SELECT a FROM t1 WHERE a NOT IN (65,66);
+a
+1
+-1
+SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+a
+1
+-1
+EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+DROP TABLE t1;
+End of 5.0 tests

--- 1.7/mysql-test/t/subselect3.test	2007-03-28 12:09:29 +03:00
+++ 1.8/mysql-test/t/subselect3.test	2007-04-12 18:17:30 +03:00
@@ -531,3 +531,21 @@ SELECT a, MAX(b),
 
 
 DROP TABLE t1, t2;
+
+#
+# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated 
+# subquery 
+#
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(-1), (65),(66);
+
+CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY);
+INSERT INTO t2 VALUES (65),(66);
+
+SELECT a FROM t1 WHERE a NOT IN (65,66);
+SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+
+DROP TABLE t1;
+
+--echo End of 5.0 tests
Thread
bk commit into 5.0 tree (gkodinov:1.2445) BUG#22855kgeorge12 Apr