List:Commits« Previous MessageNext Message »
From:eugene Date:April 25 2007 9:17pm
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-26 01:17:16+04:00, evgen@stripped +14 -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() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  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.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.

  mysql-test/include/ps_conv.inc@stripped, 2007-04-26 01:09:42+04:00, evgen@stripped +2 -2
    Test case adjusted after fix for bug#27590.

  mysql-test/r/distinct.result@stripped, 2007-04-26 01:10:02+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-26 01:09:30+04:00, evgen@stripped +2 -2
    The result of the adjusted test case after fix for bug#27590.

  mysql-test/r/ps_3innodb.result@stripped, 2007-04-26 01:09:20+04:00, evgen@stripped +2 -2
    The result of the adjusted test case after fix for bug#27590.

  mysql-test/r/ps_4heap.result@stripped, 2007-04-26 01:09:10+04:00, evgen@stripped +2 -2
    The result of the adjusted test case after fix for bug#27590.

  mysql-test/r/ps_5merge.result@stripped, 2007-04-26 01:08:18+04:00, evgen@stripped +4 -4
    The result of the adjusted test case after fix for bug#27590.

  mysql-test/r/subselect.result@stripped, 2007-04-26 01:07:30+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-26 01:07:08+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-26 01:07:06+04:00, evgen@stripped +20 -0
    Added a test case for the bug#27590: Wrong DATE/DATETIME comparison.

  sql/item.cc@stripped, 2007-04-26 01:16:18+04:00, evgen@stripped +24 -0
    Bug#27590: Wrong DATE/DATETIME comparison.
    One more store() method was added to the Item_cache_int class to cache int
    values.
    The new is_datetime() function was added to the Item class. It indicates
    whether the item returns a DATE/DATETIME value.

  sql/item.h@stripped, 2007-04-26 01:14:57+04:00, evgen@stripped +3 -0
    Bug#27590: Wrong DATE/DATETIME comparison.
    One more store() method was added to the Item_cache_int class to cache int
    values.
    The new is_datetime() function was added to the Item class. It indicates
    whether the item returns a DATE/DATETIME value.

  sql/item_cmpfunc.cc@stripped, 2007-04-26 01:14:04+04:00, evgen@stripped +315 -2
    Bug#27590: Wrong DATE/DATETIME comparison.
    The compare_datetime() function is added to the Arg_comparator class.
    It implements the correct comparator for DATE/DATETIME values.
    Two supplementary functions called get_date_from_str() and get_datetime_value()
    are added. The first one extracts DATE/DATETIME value from a string and the
    second one retrieves the correct DATE/DATETIME value from an item.
    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/item_cmpfunc.h@stripped, 2007-04-26 01:10:34+04:00, evgen@stripped +17 -10
    Bug#27590: Wrong DATE/DATETIME comparison.
    The compare_datetime() function is added to the Arg_comparator class.
    It implements the correct comparator for DATE/DATETIME values.
    Two supplementary functions called get_date_from_str() and get_datetime_value()
    are added. The first one extracts DATE/DATETIME value from a string and the
    second one retrieves the correct DATE/DATETIME value from an item.
    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.

  sql/sql_select.cc@stripped, 2007-04-26 01:10:15+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.260/sql/item.cc	2007-03-28 15:35:22.000000000 +0400
+++ 1.261/sql/item.cc	2007-04-26 01:16:18.000000000 +0400
@@ -4166,6 +4166,21 @@
 }
 
 
+bool Item::is_datetime()
+{
+  switch (field_type())
+  {
+    case MYSQL_TYPE_DATE:
+    case MYSQL_TYPE_DATETIME:
+    case MYSQL_TYPE_TIMESTAMP:
+      return TRUE;
+    default:
+      break;
+  }
+  return FALSE;
+}
+
+
 /*
   Create a field to hold a string value from an item
 
@@ -6123,6 +6138,15 @@
 }
 
 
+void Item_cache_int::store(Item *item, longlong val_arg)
+{
+  value= val_arg;
+  //If we get here then the item->val_str method was called recently
+  null_value= item->null_value;
+  unsigned_flag= item->unsigned_flag;
+}
+
+
 String *Item_cache_int::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);

--- 1.225/sql/item.h	2007-04-02 12:50:16.000000000 +0400
+++ 1.226/sql/item.h	2007-04-26 01:14:57.000000000 +0400
@@ -847,6 +847,7 @@
     representation is more precise than the string one).
   */
   virtual bool result_as_longlong() { return FALSE; }
+  bool is_datetime();
 };
 
 
@@ -2406,11 +2407,13 @@
   Item_cache_int(): Item_cache(), value(0) {}
 
   void store(Item *item);
+  void store(Item *item, longlong val_arg);
   double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
   longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
   String* val_str(String *str);
   my_decimal *val_decimal(my_decimal *);
   enum Item_result result_type() const { return INT_RESULT; }
+  bool result_as_longlong() { return TRUE; }
 };
 
 

--- 1.242/sql/item_cmpfunc.cc	2007-04-04 02:59:20.000000000 +0400
+++ 1.243/sql/item_cmpfunc.cc	2007-04-26 01:14:04.000000000 +0400
@@ -326,7 +326,9 @@
     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() &&
+          !(arg_real_item->is_datetime() &&
+            args[1]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[1]))
         {
@@ -341,7 +343,9 @@
     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() &&
+          !(arg_real_item->is_datetime() &&
+            args[0]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[0]))
         {
@@ -460,6 +464,315 @@
 }
 
 
+/*
+  Convert date provided in a string to the int representation.
+
+  SYNOPSIS
+    get_date_from_str()
+    thd              Thread handle
+    str              a string to convert
+    warn_type        type of the timestamp for issuing the warning
+    warn_name        field name for issuing the warning
+    error_arg  [out] TRUE if string isn't a DATETIME or clipping occur
+
+  DESCRIPTION
+    Convert date provided in the string str to the int representation.
+    if the string contains wrong date or doesn't contain it at all
+    then the warning is issued and TRUE returned in the error_arg argument.
+    The warn_type and the warn_name arguments are used as the name and the
+    type of the field when issuing the warning.
+
+  RETURN
+    converted value.
+*/
+
+static ulonglong
+get_date_from_str(THD *thd, String *str, timestamp_type warn_type,
+                  char *warn_name, bool *error_arg)
+{
+  ulonglong value;
+  int error;
+  MYSQL_TIME l_time;
+  enum_mysql_timestamp_type ret;
+  *error_arg= TRUE;
+
+  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);
+  if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE))
+  {
+    value= TIME_to_ulonglong_datetime(&l_time);
+    *error_arg= FALSE;
+  }
+
+  if (error || *error_arg)
+  {
+    make_truncated_value_warning(thd, str->ptr(), str->length(), warn_type,
+                                 warn_name);
+    *error_arg= TRUE;
+  }
+  return value;
+}
+
+
+/*
+  Check whether compare_datetime() can be used to compare items.
+
+  SYNOPSIS
+    Arg_comparator::can_compare_as_dates()
+    a, b          [in]  items to be compared
+    const_value   [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 constant 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 (date-[int|real|decimal]/[int|real|decimal]-date)
+      the comparison is handled 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_value if it is provided.
+    If there is no constants or compare_datetime() isn't applicable then the
+    *const_value remains unchanged.
+
+  RETURN
+    the found type of date comparison
+*/
+
+enum Arg_comparator::enum_date_cmp_type
+Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
+{
+  enum enum_date_cmp_type cmp_type= CMP_DATE_NONE;
+  Item *str_arg= 0, *date_arg= 0;
+
+  if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM)
+    return CMP_DATE_NONE;
+
+  if (a->is_datetime() && b->is_datetime())
+    cmp_type= CMP_DATE_TO_DATE;
+  else if (a->is_datetime() && b->result_type() == STRING_RESULT)
+  {
+    cmp_type= CMP_DATE_TO_STR;
+    date_arg= a;
+    str_arg= b;
+  }
+  else if (b->is_datetime() && a->result_type() == STRING_RESULT)
+  {
+    cmp_type= CMP_STR_TO_DATE;
+    date_arg= b;
+    str_arg= a;
+  }
+
+  if (cmp_type != CMP_DATE_NONE)
+  {
+    if (cmp_type != CMP_DATE_TO_DATE && str_arg->const_item())
+    {
+      THD *thd= current_thd;
+      ulonglong value;
+      bool error;
+      String tmp, *str_val= 0;
+      timestamp_type t_type= (date_arg->field_type() == MYSQL_TYPE_DATE ?
+                              MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
+
+      str_val= str_arg->val_str(&tmp);
+      if (str_arg->null_value)
+        goto end;
+      value= get_date_from_str(thd, str_val, t_type, date_arg->name, &error);
+      if (error)
+        goto end;
+      if (const_value)
+        *const_value= value;
+    }
+    return cmp_type;
+  }
+end:
+  return CMP_DATE_NONE;
+}
+
+
+int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
+                                        Item **a1, Item **a2,
+                                        Item_result type)
+{
+  enum enum_date_cmp_type cmp_type;
+  ulonglong const_value;
+  a= a1;
+  b= a2;
+
+  if ((cmp_type= can_compare_as_dates(*a, *b, &const_value)))
+  {
+    thd= current_thd;
+    owner= owner_arg;
+    a_type= (*a)->field_type();
+    b_type= (*b)->field_type();
+    a_cache= 0;
+    b_cache= 0;
+
+    if (cmp_type != CMP_DATE_TO_DATE &&
+        ((*b)->const_item() || (*a)->const_item()))
+    {
+      Item_cache_int *cache= new Item_cache_int();
+      // Mark the cache as non-const to prevent re-caching
+      cache->set_used_tables(1);
+      if (!(*a)->is_datetime())
+      {
+        cache->store((*a), const_value);
+        a_cache= cache;
+        a= (Item **)&a_cache;
+      }
+      else
+      {
+        cache->store((*b), const_value);
+        b_cache= cache;
+        b= (Item **)&b_cache;
+      }
+    }
+    is_nulls_eq= owner->functype() == Item_func::EQUAL_FUNC;
+    func= &Arg_comparator::compare_datetime;
+    return 0;
+  }
+  return set_compare_func(owner_arg, type);
+}
+
+
+/*
+  Retrieves correct DATETIME value from given item.
+
+  SYNOPSIS
+    get_datetime_value()
+    thd                 thread handle
+    item_arg   [in/out] item to retrieve DATETIME value from
+    cache_arg  [in/out] pointer to place to store the caching item to
+    warn_item  [in]     item for issuing the conversion warning
+    is_null    [out]    TRUE <=> the item_arg is null
+
+  DESCRIPTION
+    Retrieves the correct DATETIME value from given item for comparison by the
+    compare_datetime() function.
+    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 by the means of the get_date_from_str() function.
+    This allows to compare correctly 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. An Item_cache_int object is used for caching values. It
+    seamlessly substitutes the original item.  The cache item is marked as
+    non-constant to prevent re-caching it again.  In order to compare
+    correctly DATE and DATETIME items the result of the former are treated as
+    a DATETIME with zero time (00:00:00).
+
+  RETURN
+    retrieved value
+*/
+
+static ulonglong
+get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+                   Item *warn_item, bool *is_null)
+{
+  ulonglong value;
+  String buf, *str= 0;
+  Item *item= **item_arg;
+
+  if (item->result_as_longlong())
+  {
+    value= item->val_int();
+    *is_null= item->null_value;
+    if (item->field_type() == MYSQL_TYPE_DATE)
+      value*= 1000000L;
+  }
+  else
+  {
+    str= item->val_str(&buf);
+    *is_null= item->null_value;
+  }
+  if (*is_null)
+    return -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 (str)
+  {
+    bool error;
+    enum_field_types f_type= warn_item->field_type();
+    timestamp_type t_type= f_type ==
+      MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+    value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
+  }
+  if (item->const_item())
+  {
+    Item_cache_int *cache= new Item_cache_int();
+    // Mark the cache as non-const to prevent re-caching
+    cache->set_used_tables(1);
+    cache->store(item, value);
+    *cache_arg= cache;
+    *item_arg= cache_arg;
+  }
+  return value;
+}
+
+/*
+  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 for both EQUAL_FUNC and from other
+    comparison functions. The correct DATETIME values are retrieved
+    by means of the get_datetime_value() function.
+
+  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()
+{
+  bool is_null= FALSE;
+  ulonglong a_value, b_value;
+
+  /* Get DATE/DATETIME value of the 'a' item. */
+  a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null);
+  if (!is_nulls_eq && is_null)
+  {
+    owner->null_value= 1;
+    return -1;
+  }
+
+  /* Get DATE/DATETIME value of the 'b' item. */
+  b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null);
+  if (is_null)
+  {
+    owner->null_value= is_nulls_eq ? 0 : 1;
+    return is_nulls_eq ? 1 : -1;
+  }
+
+  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()
 {
   String *res1,*res2;

--- 1.147/sql/item_cmpfunc.h	2007-04-04 03:11:25.000000000 +0400
+++ 1.148/sql/item_cmpfunc.h	2007-04-26 01:10:34.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
+  Item *a_cache, *b_cache;         // Cached values of a and b items
+  bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
+  enum enum_date_cmp_type { CMP_DATE_NONE= 0, CMP_DATE_TO_DATE, CMP_DATE_TO_STR,
+                            CMP_STR_TO_DATE };
 public:
   DTCollation cmp_collation;
 
-  Arg_comparator() {};
-  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
+  Arg_comparator(): thd(0), a_cache(0), b_cache(0) {};
+  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
+    a_cache(0), b_cache(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)
   {
@@ -83,6 +86,10 @@
   int compare_e_row();           // compare args[0] & args[1]
   int compare_real_fixed();
   int compare_e_real_fixed();
+  int compare_datetime();        // compare args[0] & args[1] as DATETIMEs
+
+  static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
+                                                      ulonglong *const_val_arg);
 
   static arg_cmp_func comparator_matrix [5][2];
 

--- 1.508/sql/sql_select.cc	2007-04-10 17:55:47.000000000 +0400
+++ 1.509/sql/sql_select.cc	2007-04-26 01:10:15.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) ||
+      /* 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-26 01:07:30.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-26 01:09: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-26 01:09:20.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-26 01:09:10.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-26 01:08:18.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-26 01:09:42.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-26 01:10:02.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-26 01:07:08.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-26 01:07:06.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#27590eugene25 Apr