bzr commit into mysql-4.1 branch (holyfoot:2709) Bug#41371

[Alexey Botchkov]

#At file:///home/hf/work/mysql_common/41371/ based on
revid:timothy.smith@stripped

 2709 Alexey Botchkov	2009-09-03
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
      For the AUTO_INCREMENT field, the IS NULL condition is turned into 'col ==
LAST_INSERT_ID'.
      Fixed by preventing that transformation for any other conditions than simple 'col IS
NULL'.
      
      per-file comments:
        mysql-test/r/func_isnull.result
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test result updated
      
        mysql-test/t/func_isnull.test
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          test case added
      
        sql/sql_select.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          remove_eq_conds() split in two parts - one only checks the upper condition,
          the req_remove_eq_conds() recursively checks all the condition tree.

    modified:
      mysql-test/r/func_isnull.result
      mysql-test/t/func_isnull.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/func_isnull.result'
--- a/mysql-test/r/func_isnull.result	2002-05-16 20:35:09 +0000
+++ b/mysql-test/r/func_isnull.result	2009-09-03 11:16:49 +0000
@@ -5,3 +5,8 @@ flush tables;
 select * from t1 where isnull(to_days(mydate));
 id	mydate
 drop table t1;
+CREATE TABLE t1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+INSERT INTO t1( id ) VALUES ( NULL );
+SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
+id
+DROP TABLE t1;

=== modified file 'mysql-test/t/func_isnull.test'
--- a/mysql-test/t/func_isnull.test	2005-07-28 00:22:47 +0000
+++ b/mysql-test/t/func_isnull.test	2009-09-03 11:16:49 +0000
@@ -13,3 +13,14 @@ select * from t1 where isnull(to_days(my
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug #41371    Select returns 1 row with condition "col is not null and col is null"
+#
+
+CREATE TABLE t1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+INSERT INTO t1( id ) VALUES ( NULL );
+SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
+DROP TABLE t1;
+
+# End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2007-07-31 06:00:19 +0000
+++ b/sql/sql_select.cc	2009-09-03 11:16:49 +0000
@@ -4791,8 +4791,8 @@ optimize_cond(THD *thd, COND *conds, Ite
   COND_FALSE always false	( 1 = 2 )
 */
 
-COND *
-remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+static COND *
+req_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
 {
   if (cond->type() == Item::COND_ITEM)
   {
@@ -4806,7 +4806,7 @@ remove_eq_conds(THD *thd, COND *cond, It
     Item *item;
     while ((item=li++))
     {
-      Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value);
+      Item *new_item=req_remove_eq_conds(thd, item, &tmp_cond_value);
       if (!new_item)
 	li.remove();
       else if (item != new_item)
@@ -4852,8 +4852,32 @@ remove_eq_conds(THD *thd, COND *cond, It
       return item;
     }
   }
-  else if (cond->type() == Item::FUNC_ITEM &&
-	   ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
+  else if (cond->const_item())
+  {
+    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
+    return (COND*) 0;
+  }
+  else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
+  {						// boolan compare function
+    Item *left_item=	((Item_func*) cond)->arguments()[0];
+    Item *right_item= ((Item_func*) cond)->arguments()[1];
+    if (left_item->eq(right_item,1))
+    {
+      if (!left_item->maybe_null ||
+	  ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)
+	return (COND*) 0;			// Compare of identical items
+    }
+  }
+  *cond_value=Item::COND_OK;
+  return cond;					// Point at next and level
+}
+
+
+COND *
+remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+{
+  if (cond->type() == Item::FUNC_ITEM &&
+       ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
   {
     /*
       Handles this special case for some ODBC applications:
@@ -4909,25 +4933,10 @@ remove_eq_conds(THD *thd, COND *cond, It
 	}
       }
     }
+    *cond_value= Item::COND_OK;
+    return cond;
   }
-  else if (cond->const_item())
-  {
-    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
-    return (COND*) 0;
-  }
-  else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
-  {						// boolan compare function
-    Item *left_item=	((Item_func*) cond)->arguments()[0];
-    Item *right_item= ((Item_func*) cond)->arguments()[1];
-    if (left_item->eq(right_item,1))
-    {
-      if (!left_item->maybe_null ||
-	  ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)
-	return (COND*) 0;			// Compare of identical items
-    }
-  }
-  *cond_value=Item::COND_OK;
-  return cond;					// Point at next and level
+  return req_remove_eq_conds(thd, cond, cond_value);     // Point at next and level
 }
 
 /*


Attachment: [text/bzr-bundle] bzr/[e-mail]