Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.

The BETWEEN function was comparing DATE/DATETIME values either as ints or as
strings. Both methods have their disadvantages and may lead to a wrong
result.

Now BETWEEN function checks whether all of its arguments has the STRING result
types and at least one of them is a DATE/DATETIME item. If so it sets up
two Arg_comparator obects to compare with the compare_datetime() comparator
and uses them to compare such items.

Added two Arg_comparator object members and one flag to the
Item_func_between class for the correct DATE/DATETIME comparison.
The Item_func_between::fix_length_and_dec() function now detects whether
it's used for DATE/DATETIME comparison and sets up newly added Arg_comparator
objects to do this.
The Item_func_between::val_int() now uses Arg_comparator objects to perform
correct DATE/DATETIME comparison.
The owner variable of the Arg_comparator class now can be set to NULL if the
caller wants to handle NULL values by itself.
Now the Item_date_add_interval::get_date() function ajusts cached_field type according to the detected type.


mysql-test/t/type_datetime.test:
  Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
mysql-test/r/type_datetime.result:
  Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
mysql-test/r/query_cache.result:
  A test case result corrected after the fix for bug#16377.
sql/item_timefunc.cc:
  Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
  Now the Item_date_add_interval::get_date() function ajusts cached_field type according to the detected type.
sql/item_cmpfunc.cc:
  Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
  The Item_func_between::fix_length_and_dec() function now detects whether
  it's used for DATE/DATETIME comparison and sets up newly added Arg_comparator
  objects to do this.
  The Item_func_between::val_int() now uses Arg_comparators to perform correct
  DATE/DATETIME comparison.
  The owner variable of the Arg_comparator class now can be set to NULL if the
  caller wants to handle NULL values by itself.
sql/item_cmpfunc.h:
  Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
  Added two Arg_comparator object members and one flag
  to the Item_func_between class for the correct DATE/DATETIME comparison.

1 files changed, 21 insertions, 0 deletions

diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 683cd8803d6..194fb6445fa 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2173,6 +2173,27 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date)
   default:
     goto null_date;
   }
+
+  /* Adjust cached_field_type according to the detected type. */
+  if (cached_field_type == MYSQL_TYPE_STRING)
+  {
+    switch (ltime->time_type)
+    {
+    case MYSQL_TIMESTAMP_DATE:
+      cached_field_type= MYSQL_TYPE_DATE;
+      break;
+    case MYSQL_TIMESTAMP_DATETIME:
+      cached_field_type= MYSQL_TYPE_DATETIME;
+      break;
+    case MYSQL_TIMESTAMP_TIME:
+      cached_field_type= MYSQL_TYPE_TIME;
+      break;
+    default:
+      /* Shouldn't get here. */
+      DBUG_ASSERT(0);
+      break;
+    }
+  }
   return 0;					// Ok
 
 invalid_date: