bzr commit into mysql-6.0 branch (ramil:2694)

[Ramil Kalimullin]

#At file:///home/ram/mysql/mysql-6.0-5.1.29-rc/

 2694 Ramil Kalimullin	2008-09-09 [merge]
      Merge
modified:
  mysql-test/r/type_datetime.result
  mysql-test/t/type_datetime.test
  sql/item_cmpfunc.cc

=== modified file 'mysql-test/r/type_datetime.result'
--- a/mysql-test/r/type_datetime.result	2007-12-10 16:24:46 +0000
+++ b/mysql-test/r/type_datetime.result	2008-09-09 15:55:45 +0000
@@ -562,6 +562,29 @@ where id in (select id from t2 as x1 whe
 id	cur_date
 set @@optimizer_switch='';
 drop table t1,t2;
+SELECT 
+CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, 
+CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2,
+CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3,
+CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, 
+CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5,
+CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6,
+CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, 
+CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8,
+CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9;
+n1	n2	n3	n4	n5	n6	n7	n8	n9
+0	0	1	NULL	NULL	NULL	NULL	NULL	NULL
+Warnings:
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
+Warning	1292	Incorrect datetime value: 'NULL'
 End of 5.0 tests
 set @org_mode=@@sql_mode;
 create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');

=== modified file 'mysql-test/t/type_datetime.test'
--- a/mysql-test/t/type_datetime.test	2007-12-13 12:59:16 +0000
+++ b/mysql-test/t/type_datetime.test	2008-09-09 15:55:45 +0000
@@ -389,6 +389,22 @@ where id in (select id from t2 as x1 whe
 set @@optimizer_switch='';
 drop table t1,t2;
 
+
+#
+# Bug #37526: asymertic operator <=> in trigger
+#
+SELECT 
+  CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, 
+  CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2,
+  CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3,
+  CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, 
+  CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5,
+  CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6,
+  CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, 
+  CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8,
+  CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9;
+
+
 --echo End of 5.0 tests
 #
 # Test of storing datetime into date fields

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2008-09-08 10:03:52 +0000
+++ b/sql/item_cmpfunc.cc	2008-09-09 15:55:45 +0000
@@ -1028,19 +1028,24 @@ get_datetime_value(THD *thd, Item ***ite
        1    if items are equal or both are null
        0    otherwise
     If is_nulls_eq is FALSE:
-      -1   a < b or one of items is null
+      -1   a < b or at least one item is null
        0   a == b
        1   a > b
+    See the table:
+    is_nulls_eq | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
+    a_is_null   | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 |
+    b_is_null   | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
+    result      | 1 | 0 | 0 |0/1|-1 |-1 |-1 |-1/0/1|
 */
 
 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;
@@ -1048,14 +1053,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;
   }
 
+  /* Here we have two not-NULL values. */
   if (owner)
     owner->null_value= 0;