List:Commits« Previous MessageNext Message »
From:eugene Date:April 21 2007 3:47pm
Subject:bk commit into 5.0 tree (evgen:1.2445) BUG#27590
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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-21 19:47:41+04:00, evgen@stripped +12 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared or as strings either as int. Both of
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases do
  in a wrong way thus producing wrong comparison result. Another problem
  occurs when one tried to compare DATE field with a DATETIME constant. The 
  constant was converted to DATE loosing its precision i.e. loosing time part.
  
  This fix addresses problems described above by adding the special DATE/DATETIME
  comparator. It correctly converts DATE/DATETIME string values to int when
  it's necessary, adds zero time part (00:00:00) to DATE values to compare them
  correctly to DATETIME values. Due to correct conversion the malformed
  DATETIME string values are correctly compared to other DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime(), the compare_e_datetime() and the
  compare_datetime_int() functions are added to the Arg_comparator class.
  First two functions are working as wrappers to the last one which performs
  the actual comparison.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether 2 given items can be compared by means of the
  compare_datetime() comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.

  mysql-test/include/ps_conv.inc@stripped, 2007-04-21 19:15:34+04:00, evgen@stripped +2 -2
    Test case adjusted after fix for bug#27590.

  mysql-test/r/distinct.result@stripped, 2007-04-21 19:15:31+04:00, evgen@stripped +1 -1
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/ps_2myisam.result@stripped, 2007-04-21 19:15:30+04:00, evgen@stripped +2 -2
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/ps_3innodb.result@stripped, 2007-04-21 19:15:29+04:00, evgen@stripped +2 -2
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/ps_4heap.result@stripped, 2007-04-21 19:14:57+04:00, evgen@stripped +2 -2
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/ps_5merge.result@stripped, 2007-04-21 19:14:25+04:00, evgen@stripped +4 -4
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/subselect.result@stripped, 2007-04-21 19:13:50+04:00, evgen@stripped +2 -2
    Test cases results are corrected after fix for bug#27590.

  mysql-test/r/type_datetime.result@stripped, 2007-04-21 19:13:46+04:00, evgen@stripped +39 -0
    Added a test case for the bug#27590: Wrong DATE/DATETIME comparison.

  mysql-test/t/type_datetime.test@stripped, 2007-04-21 19:13:22+04:00, evgen@stripped +20 -0
    Added a test case for the bug#27590: Wrong DATE/DATETIME comparison.

  sql/item_cmpfunc.cc@stripped, 2007-04-21 19:45:25+04:00, evgen@stripped +319 -2
    Bug#27590: Wrong DATE/DATETIME comparison.
    The new Arg_comparator::can_compare_as_dates() function is added and used
    to check whether 2 given items can be compared by means of the
    compare_datetime() comparator.
    The Arg_comparator::compare_datetime_int() function is added.
    it performs the actual DATE/DATETIME values comparison of 2 items.

  sql/item_cmpfunc.h@stripped, 2007-04-21 19:45:58+04:00, evgen@stripped +25 -9
    Bug#27590: Wrong DATE/DATETIME comparison.
    The compare_datetime(), the compare_e_datetime() and the
    compare_datetime_int() functions are added to the Arg_comparator class.
    First two functions are working as wrappers to the last one which performs
    the actual comparison.
    Few caching variables were added to the Arg_comparator class to speedup the
    DATE/DATETIME comparison.
    The new Arg_comparator::can_compare_as_dates() function is added and used
    to check whether 2 given items can be compared by means of the
    compare_datetime() comparator.

  sql/sql_select.cc@stripped, 2007-04-21 19:32:44+04:00, evgen@stripped +7 -11
    Bug#27590: Wrong DATE/DATETIME comparison.
    The test_if_equality_guarantees_uniqueness() function now uses Arg_comparator::can_compare_as_dates()
    to detect comparable DATE/DATETIME items.

# 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:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/27590-bug-5.0-opt-mysql

--- 1.242/sql/item_cmpfunc.cc	2007-04-04 02:59:20.000000000 +0400
+++ 1.243/sql/item_cmpfunc.cc	2007-04-21 19:45:25.000000000 +0400
@@ -326,7 +326,11 @@
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !((field->type() == MYSQL_TYPE_DATE ||
+             field->type() == MYSQL_TYPE_DATETIME ||
+             field->type() == MYSQL_TYPE_TIMESTAMP) &&
+            args[1]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[1]))
         {
@@ -341,7 +345,11 @@
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !((field->type() == MYSQL_TYPE_DATE ||
+             field->type() == MYSQL_TYPE_DATETIME ||
+             field->type() == MYSQL_TYPE_TIMESTAMP) &&
+            args[0]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[0]))
         {
@@ -459,6 +467,315 @@
   return 0;
 }
 
+#define A_DATE_B_STRFUNC 1
+#define B_DATE_A_STRFUNC 2
+/*
+  Check whether compare_datetime() can be used to compare items.
+
+  SYNOPSIS
+    Arg_comparator::can_compare_as_dates()
+    a, b          [in]  items to be compared
+    cmp_type_arg  [out] found type of comparison
+                        (date-date/date-str/str-date)
+    const_val_arg [out] converted value of the string constant, if any
+
+  DESCRIPTION
+    Check several cases when the DATE/DATETIME comparator should be used.
+    The following cases are checked:
+      1. Both a and b is a DATE/DATETIME field/function returning string or
+         int result.
+      2. Only a or b is a DATE/DATETIME field/function returning string or
+         int result and the other item (b or a) is an item with string result.
+         If the second item is a const one then it's checked to be convertible
+         to the DATE/DATETIME type. If the constant can't be converted to a
+         DATE/DATETIME then the compare_datetime() comparator isn't used and
+         the warning about wrong DATE/DATETIME value is issued.
+      In all other cases compare_datetime() isn't used as the comparison is
+      done well by other comparators.
+    If the datetime comparator can be used and there is a string constant
+    that was successfully converted to a DATE/DATETIME type then the result
+    of the conversion is returned in the const_val_arg if latter is provided.
+    If there is no constants then the *const_val_arg remains unchanged.
+    If the cmp_type_arg is provided then then found comparison type is
+    returned in it. Comparison type can be date-date, date-string, string-date
+    and used to correctly assign converted result of the constant value if any.
+    const_val and cmp_type is set only if the function has returned TRUE.
+
+  RETURN
+    TRUE   datetime comparator can be used to compare items.
+    FALSE  otherwise.
+*/
+
+bool Arg_comparator::can_compare_as_dates(Item *a, Item *b, int *cmp_type_arg,
+                                          ulonglong *const_val_arg)
+{
+  bool is_a_date= FALSE, is_b_date= FALSE;
+  THD *thd= current_thd;
+  bool is_null= FALSE;
+  int cmp_type= 0;
+
+  if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM)
+    return FALSE;
+
+  if (a->field_type() == MYSQL_TYPE_DATE ||
+      a->field_type() == MYSQL_TYPE_DATETIME ||
+      a->field_type() == MYSQL_TYPE_TIMESTAMP)
+    is_a_date= TRUE;
+
+  if (b->field_type() == MYSQL_TYPE_DATE ||
+      b->field_type() == MYSQL_TYPE_DATETIME ||
+      b->field_type() == MYSQL_TYPE_TIMESTAMP)
+    is_b_date= TRUE;
+
+  if ((is_a_date && is_b_date) ||
+      (is_a_date && (b->result_type() == STRING_RESULT &&
+       (cmp_type= A_DATE_B_STRFUNC))) ||
+      (is_b_date && (a->result_type() == STRING_RESULT &&
+       (cmp_type= B_DATE_A_STRFUNC))))
+  {
+    String tmp;
+    String *str_val= 0;
+
+    if (cmp_type == B_DATE_A_STRFUNC && a->const_item())
+    {
+      str_val= a->val_str(&tmp);
+      is_null= a->null_value;
+    }
+    if (cmp_type == A_DATE_B_STRFUNC && b->const_item())
+    {
+      str_val= b->val_str(&tmp);
+      is_null= b->null_value;
+    }
+    if (is_null)
+      goto end;
+
+    if (str_val)
+    {
+      MYSQL_TIME l_time;
+      int error;
+      enum_mysql_timestamp_type ret;
+      ulonglong value;
+
+      if (((ret= str_to_datetime(str_val->ptr(), str_val->length(), &l_time,
+                            (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+                             (thd->variables.sql_mode &
+                              (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+                            &error)) == MYSQL_TIMESTAMP_DATETIME ||
+          ret == MYSQL_TIMESTAMP_DATE) && !error)
+      {
+        value= TIME_to_ulonglong_datetime(&l_time);
+      }
+      else
+      {
+        // throw a warning on the wrong date
+        enum_field_types f_type= (cmp_type == A_DATE_B_STRFUNC) ?
+                                  a->field_type() : b->field_type();
+        timestamp_type t_type= f_type ==
+          MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+        make_truncated_value_warning(thd, str_val->ptr(),
+                                     str_val->length(), t_type,
+                                     (cmp_type == A_DATE_B_STRFUNC) ?
+                                      a->name : b->name);
+        goto end;
+      }
+      if (const_val_arg)
+        *const_val_arg= value;
+    }
+    if (cmp_type_arg)
+      *cmp_type_arg= cmp_type;
+
+    return TRUE;
+  }
+end:
+  return FALSE;
+}
+
+int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
+                                        Item **a1, Item **a2,
+                                        Item_result type)
+{
+  int cmp_type;
+  ulonglong const_value;
+  a= a1;
+  b= a2;
+
+  if (can_compare_as_dates(*a, *b, &cmp_type, &const_value))
+  {
+    thd= current_thd;
+    owner= owner_arg;
+
+    a_type= (*a)->field_type();
+    b_type= (*b)->field_type();
+    if (cmp_type == A_DATE_B_STRFUNC && (*b)->const_item())
+    {
+      b_value= const_value;
+      is_b_value_cached= TRUE;
+    }
+    else
+      is_b_value_cached= FALSE;
+    if (cmp_type == B_DATE_A_STRFUNC && (*a)->const_item())
+    {
+      a_value= const_value;
+      is_a_value_cached= TRUE;
+    }
+    else
+      is_a_value_cached= FALSE;
+    func= (owner->functype() == Item_func::EQUAL_FUNC) ?
+           &Arg_comparator::compare_e_datetime :
+           &Arg_comparator::compare_datetime;
+    return 0;
+  }
+  return set_compare_func(owner_arg, type);
+}
+
+
+/*
+  Compare items values as dates.
+
+  SYNOPSIS
+    Arg_comparator::compare_datetime_int()
+    is_nulls_eq   Whether to compare items for the EQUAL(<=>) func
+
+  DESCRIPTION
+    Compare items values as DATE/DATETIME. This function behaves like
+    underlying DATE/DATETIME comparison engine for both compare_datetime()
+    and compare_e_datetime() comparators.
+    If item's result can be compared as longlong then its int value is used
+    and its string value is used otherwise. Strings are always parsed and
+    converted to int values. This allows to correctly compare string dates
+    with missed insignificant zeros. If an item is a constant one then its
+    value is cached and it isn't get parsed again.
+    In order to correctly compare DATE and DATETIME items the result of the
+    former are treated as a DATETIME with zero time (00:00:00).
+
+  IMPLEMENTATION
+
+  RETURN
+    If is_nulls_eq is TRUE:
+       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
+       0   a == b
+       1   a > b
+*/
+
+int Arg_comparator::compare_datetime_int(bool is_nulls_eq)
+{
+  bool is_null= FALSE;
+  MYSQL_TIME l_time;
+  int error;
+  enum_mysql_timestamp_type ret;
+  String buf_a, buf_b;
+  String *a_str= 0, *b_str= 0;
+
+  /* Get DATE/DATETIME value of the 'a' item. */
+  if (!is_a_value_cached)
+  {
+    if ((*a)->result_as_longlong())
+    {
+      a_value= (*a)->val_int();
+      is_null= (*a)->null_value;
+      if (a_type == MYSQL_TYPE_DATE)
+        a_value*= 1000000L;
+      if ((*a)->const_item())
+        is_a_value_cached= TRUE;
+    }
+    else
+    {
+      a_str= (*a)->val_str(&buf_a);
+      is_null= (*a)->null_value;
+    }
+  }
+  if (!is_nulls_eq && is_null)
+  {
+    owner->null_value= 1;
+    return -1;
+  }
+
+  /* Get DATE/DATETIME value of the 'b' item. */
+  if (!is_b_value_cached)
+  {
+    if ((*b)->result_as_longlong())
+    {
+      b_value= (*b)->val_int();
+      is_null= (*b)->null_value;
+      if (b_type == MYSQL_TYPE_DATE)
+        b_value*= 1000000L;
+      if ((*b)->const_item())
+        is_b_value_cached= TRUE;
+    }
+    else
+    {
+      b_str= (*b)->val_str(&buf_b);
+      is_null= (*b)->null_value;
+    }
+  }
+  if (is_null)
+  {
+    owner->null_value= is_nulls_eq ? 0 : 1;
+    return is_nulls_eq ? 1 : -1;
+  }
+
+  /*
+    Convert strings to the integer DATE/DATETIME representation.
+    Even if both dates provided in strings we can't compare them directly as
+    strings as there is no warranty that they are correct and not missing
+    some insignificant zeros.
+  */
+  if (a_str || b_str)
+  {
+    int i;
+    for (i= 0; i < 2 ; i++)
+    {
+      ulonglong value= 0;
+      String *str= !i ? a_str : b_str;
+
+      if (!str)
+        continue;
+
+      if (((ret= str_to_datetime(str->ptr(), str->length(), &l_time,
+                                 (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+                                  (thd->variables.sql_mode &
+                                   (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+                                 &error)) == MYSQL_TIMESTAMP_DATETIME ||
+          ret == MYSQL_TIMESTAMP_DATE))
+        value= TIME_to_ulonglong_datetime(&l_time);
+
+      if ((ret != MYSQL_TIMESTAMP_DATETIME && ret != MYSQL_TIMESTAMP_DATE) ||
+          error)
+      {
+        enum_field_types f_type= str == a_str ? (*b)->field_type() :
+                                                (*a)->field_type();
+        timestamp_type t_type= f_type ==
+          MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+        make_truncated_value_warning(thd, str->ptr(), str->length(),
+                                     t_type,
+                                     str == a_str ? (*b)->name : (*a)->name);
+      }
+      if (str == a_str)
+      {
+        a_value= value;
+        if ((*a)->const_item())
+          is_a_value_cached= TRUE;
+      }
+      else
+      {
+        b_value= value;
+        if ((*b)->const_item())
+          is_b_value_cached= TRUE;
+      }
+    }
+  }
+
+  owner->null_value= 0;
+
+  /* Compare values. */
+  if (is_nulls_eq)
+    return (a_value == b_value);
+  return a_value < b_value ? -1 : (a_value > b_value ? 1 : 0);
+}
+
 
 int Arg_comparator::compare_string()
 {

--- 1.147/sql/item_cmpfunc.h	2007-04-04 03:11:25.000000000 +0400
+++ 1.148/sql/item_cmpfunc.h	2007-04-21 19:45:58.000000000 +0400
@@ -35,12 +35,19 @@
   Item_bool_func2 *owner;
   Arg_comparator *comparators;   // used only for compare_row()
   double precision;
+  /* Fields used in DATE/DATETIME comparison. */
+  THD *thd;
+  enum_field_types a_type, b_type;           // Types of a and b items
+  ulonglong a_value, b_value;                // Cached values of a and b items
+  /* TRUE <=> a_value,b_value contains the correct value */
+  bool is_a_value_cached, is_b_value_cached;
 
 public:
   DTCollation cmp_collation;
 
-  Arg_comparator() {};
-  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
+  Arg_comparator(): thd(0), is_a_value_cached(0), is_b_value_cached(0) {};
+  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
+    is_a_value_cached(0), is_b_value_cached(0) {};
 
   int set_compare_func(Item_bool_func2 *owner, Item_result type);
   inline int set_compare_func(Item_bool_func2 *owner_arg)
@@ -48,14 +55,10 @@
     return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
                                                      (*b)->result_type()));
   }
-  inline int set_cmp_func(Item_bool_func2 *owner_arg,
+  int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2,
-			  Item_result type)
-  {
-    a= a1;
-    b= a2;
-    return set_compare_func(owner_arg, type);
-  }
+			  Item_result type);
+
   inline int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2)
   {
@@ -84,6 +87,19 @@
   int compare_real_fixed();
   int compare_e_real_fixed();
 
+  int compare_datetime()
+  {
+    return compare_datetime_int(FALSE);
+  };
+  int compare_e_datetime()
+  {
+    return compare_datetime_int(TRUE);
+  }
+  int compare_datetime_int(bool is_nulls_eq);
+
+  static bool can_compare_as_dates(Item *a, Item *b, int *cmp_type_arg,
+                                   ulonglong *const_val_arg);
+
   static arg_cmp_func comparator_matrix [5][2];
 
   friend class Item_func;

--- 1.508/sql/sql_select.cc	2007-04-10 17:55:47.000000000 +0400
+++ 1.509/sql/sql_select.cc	2007-04-21 19:32:44.000000000 +0400
@@ -8621,17 +8621,13 @@
 test_if_equality_guarantees_uniqueness(Item *l, Item *r)
 {
   return r->const_item() &&
-    /* elements must be of the same result type */
-    (r->result_type() == l->result_type() ||
-    /* or dates compared to longs */
-     (((l->type() == Item::FIELD_ITEM &&
-        ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
-       (l->type() == Item::FUNC_ITEM &&
-        ((Item_func *)l)->result_as_longlong())) &&
-      r->result_type() == INT_RESULT))
-    /* and must have the same collation if compared as strings */
-    && (l->result_type() != STRING_RESULT ||
-        l->collation.collation == r->collation.collation);
+    /* elements must be compared as dates */
+     (Arg_comparator::can_compare_as_dates(l, r, 0, 0) ||
+      /* or of the same result type */
+      (r->result_type() == l->result_type() &&
+       /* and must have the same collation if compared as strings */
+       (l->result_type() != STRING_RESULT ||
+        l->collation.collation == r->collation.collation)));
 }
 
 /*

--- 1.182/mysql-test/r/subselect.result	2007-03-27 20:48:07.000000000 +0400
+++ 1.183/mysql-test/r/subselect.result	2007-04-21 19:13:50.000000000 +0400
@@ -394,13 +394,13 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	43	NULL	2	Using where; Using index
 Warnings:
-Note	1003	select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)
+Note	1003	select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')
 EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	t1	index	NULL	PRIMARY	43	NULL	2	Using where; Using index
 Warnings:
-Note	1003	select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note	1003	select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
 SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 date
 2002-08-03

--- 1.45/mysql-test/r/ps_2myisam.result	2007-03-02 17:23:10.000000000 +0300
+++ 1.46/mysql-test/r/ps_2myisam.result	2007-04-21 19:15:30.000000000 +0400
@@ -3070,7 +3070,7 @@
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3083,7 +3083,7 @@
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and

--- 1.48/mysql-test/r/ps_3innodb.result	2007-03-02 17:23:10.000000000 +0300
+++ 1.49/mysql-test/r/ps_3innodb.result	2007-04-21 19:15:29.000000000 +0400
@@ -3053,7 +3053,7 @@
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3066,7 +3066,7 @@
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and

--- 1.44/mysql-test/r/ps_4heap.result	2007-03-02 17:23:10.000000000 +0300
+++ 1.45/mysql-test/r/ps_4heap.result	2007-04-21 19:14:57.000000000 +0400
@@ -3054,7 +3054,7 @@
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3067,7 +3067,7 @@
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and

--- 1.45/mysql-test/r/ps_5merge.result	2007-03-02 17:23:10.000000000 +0300
+++ 1.46/mysql-test/r/ps_5merge.result	2007-04-21 19:14:25.000000000 +0400
@@ -2990,7 +2990,7 @@
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3003,7 +3003,7 @@
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6004,7 +6004,7 @@
 true
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
 c14= CAST('1991-01-01 01:01:01' as datetime) and
 c15= CAST('1991-01-01 01:01:01' as datetime) and
 c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6017,7 +6017,7 @@
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and

--- 1.6/mysql-test/include/ps_conv.inc	2007-03-02 17:23:09.000000000 +0300
+++ 1.7/mysql-test/include/ps_conv.inc	2007-04-21 19:15:34.000000000 +0400
@@ -1171,7 +1171,7 @@
 ######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -1180,7 +1180,7 @@
 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
   and c17= @arg00 ;
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
   c14= CAST('1991-01-01 01:01:01' as datetime) and
   c15= CAST('1991-01-01 01:01:01' as datetime) and
   c16= CAST('1991-01-01 01:01:01' as datetime) and

--- 1.52/mysql-test/r/distinct.result	2007-04-10 17:55:47.000000000 +0400
+++ 1.53/mysql-test/r/distinct.result	2007-04-21 19:15:31.000000000 +0400
@@ -633,7 +633,7 @@
 WHERE ADDDATE(a,1) = '2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
 INSERT INTO t2 VALUES (0xf6);
 INSERT INTO t2 VALUES ('oe');

--- 1.35/mysql-test/r/type_datetime.result	2007-04-02 12:50:16.000000000 +0400
+++ 1.36/mysql-test/r/type_datetime.result	2007-04-21 19:13:46.000000000 +0400
@@ -192,3 +192,42 @@
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
 101112.098700
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+curdate() < now()	f1 < now()	cast(f1 as date) < now()
+1	1	1
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+f1	f3
+2001-02-05	2001-02-05 01:01:01
+2001-03-10	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+f1	f3
+2001-02-05	2001-02-05 01:01:01
+2001-03-10	2001-03-10 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+f1	f2
+2001-02-05	2001-02-05 00:00:00
+2001-03-10	2001-03-09 01:01:01
+2001-04-15	2001-04-15 00:00:00
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+1
+1
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+f1 > f2, f1 = f2, f1 < f2
+from t1;
+f1	f2	UNIX_TIMESTAMP(f2)	UNIX_TIMESTAMP(f1)	f1 > f2	f1 = f2	f1 < f2
+2001-01-01	2001-01-01 01:01:01	978300061	978296400	0	0	1
+2001-02-05	2001-02-05 00:00:00	981320400	981320400	0	1	0
+2001-03-10	2001-03-09 01:01:01	984088861	984171600	1	0	0
+2001-04-15	2001-04-15 00:00:00	987282000	987282000	0	1	0
+2001-05-20	2001-05-20 01:01:01	990309661	990306000	0	0	1
+drop table t1;

--- 1.21/mysql-test/t/type_datetime.test	2007-03-22 11:25:22.000000000 +0300
+++ 1.22/mysql-test/t/type_datetime.test	2007-04-21 19:13:22.000000000 +0400
@@ -141,3 +141,23 @@
 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 
+#
+# Bug#27590: Wrong DATE/DATETIME comparison.  
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+  f1 > f2, f1 = f2, f1 < f2
+  from t1;
+drop table t1;
Thread
bk commit into 5.0 tree (evgen:1.2445) BUG#27590eugene21 Apr