From: Date: June 30 2008 11:57am
Subject: bzr commit into mysql-5.0 branch (ramil:2642) Bug#37526
List-Archive: http://lists.mysql.com/commits/48725
X-Bug: 37526
Message-Id: <200806300957.m5U9v76M005614@ram.home>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///home/ram/mysql/b37526/

 2642 Ramil Kalimullin	2008-06-30
      Fix for bug#37526: asymertic operator <=> in trigger
      
      Problem: <=> operator may return wrong results 
      comparing NULL and a DATE/DATETIME/TIME value.
      
      Fix: properly check NULLs.
modified:
  mysql-test/r/type_datetime.result
  mysql-test/t/type_datetime.test
  sql/item_cmpfunc.cc

per-file messages:
  sql/item_cmpfunc.cc
    Fix for bug#37526: asymertic operator <=> in trigger
    
    If is_nulls_eq is TRUE Arg_comparator::compare_datetime() 
    should return 1 only if both arguments are NULL.
=== modified file 'mysql-test/r/type_datetime.result'
--- a/mysql-test/r/type_datetime.result	2007-12-08 21:05:00 +0000
+++ b/mysql-test/r/type_datetime.result	2008-06-30 09:57:02 +0000
@@ -560,4 +560,11 @@ select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
 drop table t1,t2;
+SELECT CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) nd, 
+CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) dn;
+nd	dn
+0	0
+Warnings:
+Warning	1292	Truncated incorrect datetime value: 'NULL'
+Warning	1292	Truncated incorrect datetime value: 'NULL'
 End of 5.0 tests

=== modified file 'mysql-test/t/type_datetime.test'
--- a/mysql-test/t/type_datetime.test	2007-12-13 10:52:49 +0000
+++ b/mysql-test/t/type_datetime.test	2008-06-30 09:57:02 +0000
@@ -388,4 +388,10 @@ where id in (select id from t2 as x1 whe
 
 drop table t1,t2;
 
+#
+# Bug #37526: asymertic operator <=> in trigger
+#
+SELECT CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) nd, 
+  CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) dn;
+
 --echo End of 5.0 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2008-03-28 18:02:27 +0000
+++ b/sql/item_cmpfunc.cc	2008-06-30 09:57:02 +0000
@@ -973,12 +973,12 @@ get_datetime_value(THD *thd, Item ***ite
 
 int Arg_comparator::compare_datetime()
 {
-  bool is_null= FALSE;
+  bool a_is_null, b_is_null;
   ulonglong a_value, b_value;
 
   /* Get DATE/DATETIME/TIME value of the 'a' item. */
-  a_value= (*get_value_func)(thd, &a, &a_cache, *b, &is_null);
-  if (!is_nulls_eq && is_null)
+  a_value= (*get_value_func)(thd, &a, &a_cache, *b, &a_is_null);
+  if (!is_nulls_eq && a_is_null)
   {
     if (owner)
       owner->null_value= 1;
@@ -986,14 +986,15 @@ int Arg_comparator::compare_datetime()
   }
 
   /* Get DATE/DATETIME/TIME value of the 'b' item. */
-  b_value= (*get_value_func)(thd, &b, &b_cache, *a, &is_null);
-  if (is_null)
+  b_value= (*get_value_func)(thd, &b, &b_cache, *a, &b_is_null);
+  if (a_is_null || b_is_null)
   {
     if (owner)
       owner->null_value= is_nulls_eq ? 0 : 1;
-    return is_nulls_eq ? 1 : -1;
+    return (is_nulls_eq && (a_is_null == b_is_null)) ? 1 : 0;
   }
 
+  /* Here we have two not-NULL values. */
   if (owner)
     owner->null_value= 0;