Bug #49480: WHERE using YEAR columns returns unexpected results

Merge the fix from 5.1-bugteam to 5.1-main

2 files changed, 51 insertions, 102 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index fd5eca8911a..d99748b87a0 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -956,40 +956,9 @@ int Arg_comparator::set_cmp_func(Item_result_field *owner_arg,
     if (agg_item_set_converter(coll, owner->func_name(),
                                b, 1, MY_COLL_CMP_CONV, 1))
       return 1;
-  } else if (type != ROW_RESULT && ((*a)->field_type() == MYSQL_TYPE_YEAR ||
-             (*b)->field_type() == MYSQL_TYPE_YEAR))
-  {
-    is_nulls_eq= is_owner_equal_func();
-    year_as_datetime= FALSE;
-
-    if ((*a)->is_datetime())
-    {
-      year_as_datetime= TRUE;
-      get_value_a_func= &get_datetime_value;
-    } else if ((*a)->field_type() == MYSQL_TYPE_YEAR)
-      get_value_a_func= &get_year_value;
-    else
-    {
-      /*
-        Because convert_constant_item is called only for EXECUTE in PS mode
-        the value of get_value_x_func set in PREPARE might be not
-        valid for EXECUTE.
-      */
-      get_value_a_func= NULL;
-    }
-
-    if ((*b)->is_datetime())
-    {
-      year_as_datetime= TRUE;
-      get_value_b_func= &get_datetime_value;
-    } else if ((*b)->field_type() == MYSQL_TYPE_YEAR)
-      get_value_b_func= &get_year_value;
-    else
-      get_value_b_func= NULL;
-
-    func= &Arg_comparator::compare_year;
-    return 0;
   }
+  else if (try_year_cmp_func(type))
+    return 0;
 
   a= cache_converted_constant(thd, a, &a_cache, type);
   b= cache_converted_constant(thd, b, &b_cache, type);
@@ -997,6 +966,45 @@ int Arg_comparator::set_cmp_func(Item_result_field *owner_arg,
 }
 
 
+/*
+  Helper function to call from Arg_comparator::set_cmp_func()
+*/
+
+bool Arg_comparator::try_year_cmp_func(Item_result type)
+{
+  if (type == ROW_RESULT)
+    return FALSE;
+
+  bool a_is_year= (*a)->field_type() == MYSQL_TYPE_YEAR;
+  bool b_is_year= (*b)->field_type() == MYSQL_TYPE_YEAR;
+
+  if (!a_is_year && !b_is_year)
+    return FALSE;
+
+  if (a_is_year && b_is_year)
+  {
+    get_value_a_func= &get_year_value;
+    get_value_b_func= &get_year_value;
+  }
+  else if (a_is_year && (*b)->is_datetime())
+  {
+    get_value_a_func= &get_year_value;
+    get_value_b_func= &get_datetime_value;
+  }
+  else if (b_is_year && (*a)->is_datetime())
+  {
+    get_value_b_func= &get_year_value;
+    get_value_a_func= &get_datetime_value;
+  }
+  else
+    return FALSE;
+
+  is_nulls_eq= is_owner_equal_func();
+  func= &Arg_comparator::compare_datetime;
+
+  return TRUE;
+}
+
 /**
   Convert and cache a constant.
 
@@ -1147,7 +1155,7 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
 
 
 /*
-  Retrieves YEAR value of 19XX form from given item.
+  Retrieves YEAR value of 19XX-00-00 00:00:00 form from given item.
 
   SYNOPSIS
     get_year_value()
@@ -1159,7 +1167,9 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
 
   DESCRIPTION
     Retrieves the YEAR value of 19XX form from given item for comparison by the
-    compare_year() function.
+    compare_datetime() function.
+    Converts year to DATETIME of form YYYY-00-00 00:00:00 for the compatibility
+    with the get_datetime_value function result.
 
   RETURN
     obtained value
@@ -1186,6 +1196,9 @@ get_year_value(THD *thd, Item ***item_arg, Item **cache_arg,
   if (value <= 1900)
     value+= 1900;
 
+  /* Convert year to DATETIME of form YYYY-00-00 00:00:00 (YYYY0000000000). */
+  value*= 10000000000LL;
+
   return value;
 }
 
@@ -1615,67 +1628,6 @@ int Arg_comparator::compare_e_row()
 }
 
 
-/**
-  Compare values as YEAR.
-
-  @details
-    Compare items as YEAR for EQUAL_FUNC and for other comparison functions.
-    The YEAR values of form 19XX are obtained with help of the get_year_value()
-    function.
-    If one of arguments is of DATE/DATETIME type its value is obtained
-    with help of the get_datetime_value function. In this case YEAR values
-    prior to comparison are converted to the ulonglong YYYY-00-00 00:00:00
-    DATETIME form.
-    If an argument type neither YEAR nor DATE/DATEIME then val_int function
-    is used to obtain value for comparison.
-
-  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
-       0   a == b or at least one of items is null
-       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| 0 | 0 | 0 |-1/0/1|
-*/
-
-int Arg_comparator::compare_year()
-{
-  bool a_is_null, b_is_null;
-  ulonglong val1= get_value_a_func ?
-                    (*get_value_a_func)(thd, &a, &a_cache, *b, &a_is_null) :
-                    (*a)->val_int();
-  ulonglong val2= get_value_b_func ?
-                    (*get_value_b_func)(thd, &b, &b_cache, *a, &b_is_null) :
-                    (*b)->val_int();
-  if (!(*a)->null_value)
-  {
-    if (!(*b)->null_value)
-    {
-      if (set_null)
-        owner->null_value= 0;
-      /* Convert year to DATETIME of form YYYY-00-00 00:00:00 when necessary. */
-      if((*a)->field_type() == MYSQL_TYPE_YEAR && year_as_datetime)
-        val1*= 10000000000LL;
-      if((*b)->field_type() == MYSQL_TYPE_YEAR && year_as_datetime)
-        val2*= 10000000000LL;
-
-      if (val1 < val2)	return is_nulls_eq ? 0 : -1;
-      if (val1 == val2)   return is_nulls_eq ? 1 : 0;
-      return is_nulls_eq ? 0 : 1;
-    }
-  }
-  if (set_null)
-    owner->null_value= is_nulls_eq ? 0 : 1;
-  return (is_nulls_eq && (*a)->null_value == (*b)->null_value) ? 1 : 0;
-}
-
-
 void Item_func_truth::fix_length_and_dec()
 {
   maybe_null= 0;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 52af6a31c0c..d4542dac820 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -42,24 +42,22 @@ class Arg_comparator: public Sql_alloc
   bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
   bool set_null;                   // TRUE <=> set owner->null_value
                                    //   when one of arguments is NULL.
-  bool year_as_datetime;           // TRUE <=> convert YEAR value to
-                                   // the YYYY-00-00 00:00:00 DATETIME
-                                   // format. See compare_year.
   enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
                             CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
   longlong (*get_value_a_func)(THD *thd, Item ***item_arg, Item **cache_arg,
                                Item *warn_item, bool *is_null);
   longlong (*get_value_b_func)(THD *thd, Item ***item_arg, Item **cache_arg,
                                Item *warn_item, bool *is_null);
+  bool try_year_cmp_func(Item_result type);
 public:
   DTCollation cmp_collation;
   /* Allow owner function to use string buffers. */
   String value1, value2;
 
   Arg_comparator(): thd(0), a_cache(0), b_cache(0), set_null(0),
-    year_as_datetime(0), get_value_a_func(0), get_value_b_func(0) {};
+    get_value_a_func(0), get_value_b_func(0) {};
   Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
-    a_cache(0), b_cache(0), set_null(0), year_as_datetime(0),
+    a_cache(0), b_cache(0), set_null(0),
     get_value_a_func(0), get_value_b_func(0) {};
 
   int set_compare_func(Item_result_field *owner, Item_result type);
@@ -101,7 +99,6 @@ public:
   int compare_real_fixed();
   int compare_e_real_fixed();
   int compare_datetime();        // compare args[0] & args[1] as DATETIMEs
-  int compare_year();
 
   static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
                                                       ulonglong *const_val_arg);