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-10-24
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          For application compatibility reasons  MySQL converts
"<autoincrement_column> IS NULL"
          predicates to "<autoincrement_column> = LAST_INSERT_ID()" in the first
SELECT following an
          INSERT regardless of whether they're top level predicates or not. This causes
wrong and
          obscure results when these predicates are combined with others on the same
columns. Fixed
          by only doing the transformation on a single top-level predicate if a special
SQL mode is
          turned on (sql_auto_is_null).
          Also made sql_auto_is_null off by default.
      
      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/mysqld.cc
      Bug#41371      Select returns 1 row with condition "col is not null and col is null"
          sql_auto_is_null now is OFF by default.
      
        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/mysqld.cc
      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-10-24 10:11:52 +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-10-24 10:11:52 +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/mysqld.cc'
--- a/sql/mysqld.cc	2008-07-26 09:39:31 +0000
+++ b/sql/mysqld.cc	2009-10-24 10:11:52 +0000
@@ -5961,7 +5961,7 @@ static void mysql_init_variables(void)
   log_error_file_ptr= log_error_file;
   language_ptr= language;
   mysql_data_home= mysql_real_data_home;
-  thd_startup_options= (OPTION_UPDATE_LOG | OPTION_AUTO_IS_NULL |
+  thd_startup_options= (OPTION_UPDATE_LOG |
 			OPTION_BIN_LOG | OPTION_QUOTE_SHOW_CREATE |
 			OPTION_SQL_NOTES);
   protocol_version= PROTOCOL_VERSION;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2007-07-31 06:00:19 +0000
+++ b/sql/sql_select.cc	2009-10-24 10:11:52 +0000
@@ -4784,16 +4784,56 @@ optimize_cond(THD *thd, COND *conds, Ite
 
 
 /*
+  Handles the reqursive job for remove_eq_conds()
+
   Remove const and eq items. Return new item, or NULL if no condition
   cond_value is set to according:
   COND_OK    query is possible (field = constant)
   COND_TRUE  always true	( 1 = 1 )
   COND_FALSE always false	( 1 = 2 )
+
+  SYNPOSIS
+    remove_eq_conds()
+    thd 			THD environment
+    cond                        the condition to handle
+    cond_value                  the resulting value of the condition
+
+  RETURN
+    *COND with the simplified condition
 */
 
-COND *
-remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+static COND *
+internal_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)
+  {
+    /*
+      datetime_field IS NULL has to be modified to
+      datetime_field == 0
+    */
+
+    Item_func_isnull *func=(Item_func_isnull*) cond;
+    Item **args= func->arguments();
+    if (args[0]->type() == Item::FIELD_ITEM)
+    {
+      Field *field=((Item_field*) args[0])->field;
+      /* fix to replace 'NULL' dates with '0' (shreeve@stripped) */
+      if (((field->type() == FIELD_TYPE_DATE) ||
+           (field->type() == FIELD_TYPE_DATETIME)) &&
+          (field->flags & NOT_NULL_FLAG) &&
!field->table->maybe_null)
+      {
+	COND *new_cond;
+	if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
+	{
+	  cond=new_cond;
+	  cond->fix_fields(thd, 0, &cond);
+	}
+      }
+    }
+    *cond_value= Item::COND_OK;
+    return cond;
+  }
   if (cond->type() == Item::COND_ITEM)
   {
     bool and_level= ((Item_cond*) cond)->functype()
@@ -4806,7 +4846,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=internal_remove_eq_conds(thd, item, &tmp_cond_value);
       if (!new_item)
 	li.remove();
       else if (item != new_item)
@@ -4852,8 +4892,52 @@ 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
+}
+
+
+/*
+  Remove const and eq items. Return new item, or NULL if no condition
+  cond_value is set to according:
+  COND_OK    query is possible (field = constant)
+  COND_TRUE  always true	( 1 = 1 )
+  COND_FALSE always false	( 1 = 2 )
+
+  SYNPOSIS
+    remove_eq_conds()
+    thd 			THD environment
+    cond                        the condition to handle
+    cond_value                  the resulting value of the condition
+
+  NOTES
+    calls the inner_remove_eq_conds to check all the tree reqursively
+
+  RETURN
+    *COND with the simplified condition
+*/
+
+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:
@@ -4863,6 +4947,8 @@ remove_eq_conds(THD *thd, COND *cond, It
       SELECT * from table_name where auto_increment_column IS NULL
       This will be changed to:
       SELECT * from table_name where auto_increment_column = LAST_INSERT_ID
+
+      That's applicable if the IS NULL condition is the only one.
     */
 
     Item_func_isnull *func=(Item_func_isnull*) cond;
@@ -4909,25 +4995,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 internal_remove_eq_conds(thd, cond, cond_value);     // Point at next and level
 }
 
 /*


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