MDEV-8766 Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31'

6 files changed, 210 insertions, 29 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 714b9dd1dc7..f040891141d 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -3309,7 +3309,11 @@ Item *Field_new_decimal::get_equal_const_item(THD *thd, const Context &ctx,
         DBUG_ASSERT(0);
         return const_item;
       }
-      /* Truncate or extend the decimal value to the scale of the field */
+      /*
+        Truncate or extend the decimal value to the scale of the field.
+        See comments about truncation in the same place in
+        Field_time::get_equal_const_item().
+      */
       my_decimal_round(E_DEC_FATAL_ERROR, val, decimals(), true, &val_buffer2);
       return new (thd->mem_root) Item_decimal(thd, field_name, &val_buffer2,
                                               decimals(), field_length);
@@ -5530,6 +5534,10 @@ Item *Field_temporal::get_equal_const_item_datetime(THD *thd, const Context &ctx
           const_item->get_date_with_conversion(&ltime, 0) :
           const_item->get_date(&ltime, 0))
         return NULL;
+      /*
+        See comments about truncation in the same place in
+        Field_time::get_equal_const_item().
+      */
       return new (thd->mem_root) Item_datetime_literal(thd, &ltime,
                                                        decimals());
     }
@@ -5844,8 +5852,22 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
         const_item->decimals != decimals())
     {
       MYSQL_TIME ltime;
-      if (const_item->get_date(&ltime, TIME_TIME_ONLY))
+      if (const_item->get_time_with_conversion(thd, &ltime, TIME_TIME_ONLY))
         return NULL;
+      /*
+        Note, the value returned in "ltime" can have more fractional
+        digits that decimals(). The Item_time_literal constructor will
+        truncate these digits. We could disallow propagation is such
+        cases, but it's still useful (and safe) to optimize:
+          WHERE time0_column='00:00:00.123' AND LENGTH(a)=12
+        to
+          WHERE time0_column='00:00:00.123' AND LENGTH(TIME'00:00:00')=12
+        and then to
+          WHERE FALSE
+        The original WHERE would do the full table scan (in case of no keys).
+        The optimized WHERE will return with "Impossible WHERE", without
+        having to do the full table scan.
+      */
       return new (thd->mem_root) Item_time_literal(thd, &ltime, decimals());
     }
     break;
diff --git a/sql/item.cc b/sql/item.cc
index 1bc75bdbf10..075480ba33c 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -136,6 +136,48 @@ bool Item::get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate)
 }
 
 
+/**
+  Get date/time/datetime.
+  If DATETIME or DATE result is returned, it's converted to TIME.
+*/
+bool Item::get_time_with_conversion(THD *thd, MYSQL_TIME *ltime,
+                                    ulonglong fuzzydate)
+{
+  if (get_date(ltime, fuzzydate))
+    return true;
+  if (ltime->time_type != MYSQL_TIMESTAMP_TIME)
+  {
+    MYSQL_TIME ltime2;
+    if ((thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST) &&
+        (ltime->year || ltime->day || ltime->month))
+    {
+      /*
+        Old mode conversion from DATETIME with non-zero YYYYMMDD part
+        to TIME works very inconsistently. Possible variants:
+        - truncate the YYYYMMDD part
+        - add (MM*33+DD)*24 to hours
+        - add (MM*31+DD)*24 to hours
+        Let's return NULL here, to disallow equal field propagation.
+        Note, If we start to use this method in more pieces of the code other
+        than eqial field propagation, we should probably return
+        NULL only if some flag in fuzzydate is set.
+      */
+      return (null_value= true);
+    }
+    if (datetime_to_time_with_warn(thd, ltime, &ltime2, TIME_SECOND_PART_DIGITS))
+    {
+      /*
+        Time difference between CURRENT_DATE and ltime
+        did not fit into the supported TIME range
+      */
+      return (null_value= true);
+    }
+    *ltime= ltime2;
+  }
+  return false;
+}
+
+
 /*
   For the items which don't have its own fast val_str_ascii()
   implementation we provide a generic slower version,
diff --git a/sql/item.h b/sql/item.h
index 9d4d2aee0c7..b356a1db4bb 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1188,6 +1188,39 @@ public:
   { return get_date(ltime, TIME_TIME_ONLY | TIME_INVALID_DATES); }
   // Get date with automatic TIME->DATETIME conversion
   bool get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  /*
+    Get time with automatic DATE/DATETIME to TIME conversion.
+
+    Performce a reserve operation to get_date_with_conversion().
+    Suppose:
+    - we have a set of items (typically with the native MYSQL_TYPE_TIME type)
+      whose item->get_date() return TIME1 value, and
+    - item->get_date_with_conversion() for the same Items return DATETIME1,
+      after applying time-to-datetime conversion to TIME1.
+
+    then all items (typically of the native MYSQL_TYPE_{DATE|DATETIME} types)
+    whose get_date() return DATETIME1 must also return TIME1 from
+    get_time_with_conversion()
+
+    @param thd        - the thread, its variables.old_mode is checked
+                        to decide if use simple YYYYMMDD truncation (old mode),
+                        or perform full DATETIME-to-TIME conversion with
+                        CURRENT_DATE subtraction.
+    @param[out] ltime - store the result here
+    @param fuzzydate  - flags to be used for the get_date() call.
+                        Normally, should include TIME_TIME_ONLY, to let
+                        the called low-level routines, e.g. str_to_date(),
+                        know that we prefer TIME rather that DATE/DATETIME
+                        and do less conversion outside of the low-level
+                        routines.
+
+    @returns true     - on error, e.g. get_date() returned NULL value,
+                        or get_date() returned DATETIME/DATE with non-zero
+                        YYYYMMDD part.
+    @returns false    - on success
+  */
+  bool get_time_with_conversion(THD *thd, MYSQL_TIME *ltime,
+                                ulonglong fuzzydate);
   bool get_seconds(ulonglong *sec, ulong *sec_part);
   virtual bool get_date_result(MYSQL_TIME *ltime, ulonglong fuzzydate)
   { return get_date(ltime,fuzzydate); }
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 0b87302781f..2faf7a7d347 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2756,8 +2756,6 @@ void Item_func_add_time::print(String *str, enum_query_type query_type)
 bool Item_func_timediff::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
   DBUG_ASSERT(fixed == 1);
-  longlong seconds;
-  long microseconds;
   int l_sign= 1;
   MYSQL_TIME l_time1,l_time2,l_time3;
   ErrConvTime str(&l_time3);
@@ -2774,31 +2772,7 @@ bool Item_func_timediff::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
   if (l_time1.neg != l_time2.neg)
     l_sign= -l_sign;
 
-  bzero((char *)&l_time3, sizeof(l_time3));
-  
-  l_time3.neg= calc_time_diff(&l_time1, &l_time2, l_sign,
-			      &seconds, &microseconds);
-
-  /*
-    For MYSQL_TIMESTAMP_TIME only:
-      If first argument was negative and diff between arguments
-      is non-zero we need to swap sign to get proper result.
-  */
-  if (l_time1.neg && (seconds || microseconds))
-    l_time3.neg= 1-l_time3.neg;         // Swap sign of result
-
-  /*
-    seconds is longlong, when casted to long it may become a small number
-    even if the original seconds value was too large and invalid.
-    as a workaround we limit seconds by a large invalid long number
-    ("invalid" means > TIME_MAX_SECOND)
-  */
-  set_if_smaller(seconds, INT_MAX32);
-
-  calc_time_from_sec(&l_time3, (long) seconds, microseconds);
-
-  if ((fuzzy_date & TIME_NO_ZERO_DATE) && (seconds == 0) &&
-      (microseconds == 0))
+  if (calc_time_diff(&l_time1, &l_time2, l_sign, &l_time3, fuzzy_date))
     return (null_value= 1);
 
   *ltime= l_time3;
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 199ce19450e..ccba3d16575 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -1100,6 +1100,35 @@ calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
 }
 
 
+bool calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
+                    int l_sign, MYSQL_TIME *l_time3, ulonglong fuzzydate)
+{
+  longlong seconds;
+  long microseconds;
+  bzero((char *) l_time3, sizeof(*l_time3));
+  l_time3->neg= calc_time_diff(l_time1, l_time2, l_sign,
+			       &seconds, &microseconds);
+  /*
+    For MYSQL_TIMESTAMP_TIME only:
+      If first argument was negative and diff between arguments
+      is non-zero we need to swap sign to get proper result.
+  */
+  if (l_time1->neg && (seconds || microseconds))
+    l_time3->neg= 1 - l_time3->neg;         // Swap sign of result
+
+  /*
+    seconds is longlong, when casted to long it may become a small number
+    even if the original seconds value was too large and invalid.
+    as a workaround we limit seconds by a large invalid long number
+    ("invalid" means > TIME_MAX_SECOND)
+  */
+  set_if_smaller(seconds, INT_MAX32);
+  calc_time_from_sec(l_time3, (long) seconds, microseconds);
+  return ((fuzzydate & TIME_NO_ZERO_DATE) && (seconds == 0) &&
+          (microseconds == 0));
+}
+
+
 /*
   Compares 2 MYSQL_TIME structures
 
@@ -1341,3 +1370,23 @@ time_to_datetime_with_warn(THD *thd,
   }
   return false;
 }
+
+
+bool datetime_to_time_with_warn(THD *thd, const MYSQL_TIME *dt,
+                                MYSQL_TIME *tm, uint dec)
+{
+  if (thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST)
+  {
+    *tm= *dt;
+    datetime_to_time(tm);
+    return false;
+  }
+  else /* new mode */
+  {
+    MYSQL_TIME current_date;
+    set_current_date(thd, &current_date);
+    calc_time_diff(dt, &current_date, 1, tm, 0);
+  }
+  int warnings= 0;
+  return check_time_range(tm, dec, &warnings);
+}
diff --git a/sql/sql_time.h b/sql/sql_time.h
index 8e13ee1870a..5a985710ee1 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -55,6 +55,10 @@ bool time_to_datetime(THD *thd, const MYSQL_TIME *tm, MYSQL_TIME *dt);
 bool time_to_datetime_with_warn(THD *thd,
                                 const MYSQL_TIME *tm, MYSQL_TIME *dt,
                                 ulonglong fuzzydate);
+/*
+  Simply truncate the YYYY-MM-DD part to 0000-00-00
+  and change time_type to MYSQL_TIMESTAMP_TIME
+*/
 inline void datetime_to_time(MYSQL_TIME *ltime)
 {
   DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_DATE ||
@@ -63,6 +67,39 @@ inline void datetime_to_time(MYSQL_TIME *ltime)
   ltime->year= ltime->month= ltime->day= 0;
   ltime->time_type= MYSQL_TIMESTAMP_TIME;
 }
+
+
+/**
+  Convert DATE/DATETIME to TIME(dec)
+  using CURRENT_DATE in a non-old mode,
+  or using simple truncation in old mode (OLD_MODE_ZERO_DATE_TIME_CAST).
+
+  @param      thd - the thread to get the variables.old_behaviour value from
+  @param      dt  - the DATE of DATETIME value to convert
+  @param[out] tm  - store result here
+  @param      dec - the desired scale. The fractional part of the result
+                    is checked according to this parameter before returning
+                    the conversion result. "dec" is important in the corner
+                    cases near the max/min limits.
+                    If the result is '838:59:59.999999' and the desired scale
+                    is less than 6, an error is returned.
+                    Note, dec is not important in the
+                    OLD_MODE_ZERO_DATE_TIME_CAST old mode.
+
+  - in case of OLD_MODE_ZERO_DATE_TIME_CAST
+    the TIME part is simply truncated and "false" is returned.
+  - otherwise, the result is calculated effectively similar to:
+    TIMEDIFF(dt, CAST(CURRENT_DATE AS DATETIME))
+    If the difference fits into the supported TIME range, "false" is returned,
+    otherwise a warning is issued and "true" is returned.
+
+  @return false - on success
+  @return true  - on error
+*/
+bool datetime_to_time_with_warn(THD *, const MYSQL_TIME *dt,
+                                MYSQL_TIME *tm, uint dec);
+
+
 inline void datetime_to_date(MYSQL_TIME *ltime)
 {
   DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_DATE ||
@@ -107,6 +144,30 @@ bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type,
                        INTERVAL interval);
 bool calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
                     int l_sign, longlong *seconds_out, long *microseconds_out);
+/**
+  Calculate time difference between two MYSQL_TIME values and
+  store the result as an out MYSQL_TIME value in MYSQL_TIMESTAMP_TIME format.
+
+  The result can be outside of the supported TIME range.
+  For example, calc_time_diff('2002-01-01 00:00:00', '2001-01-01 00:00:00')
+  returns '8760:00:00'. So the caller might want to do check_time_range() or
+  adjust_time_range_with_warn() on the result of a calc_time_diff() call.
+
+  @param l_time1       - the minuend (TIME/DATE/DATETIME value)
+  @param l_time2       - the subtrahend TIME/DATE/DATETIME value
+  @param l_sign        -  +1 if absolute values are to be subtracted,
+                          or -1 if absolute values are to be added.
+  @param[out] l_time3  - the result
+  @param fuzzydate     - flags
+
+  @return true         - if TIME_NO_ZERO_DATE was passed in flags and
+                         the result appeared to be '00:00:00.000000'.
+                         This is important when calc_time_diff() is called
+                         when calculating DATE_ADD(TIMEDIFF(...),...)
+  @return false        - otherwise
+*/
+bool calc_time_diff(const MYSQL_TIME *l_time1, const MYSQL_TIME *l_time2,
+                    int lsign, MYSQL_TIME *l_time3, ulonglong fuzzydate);
 int my_time_compare(const MYSQL_TIME *a, const MYSQL_TIME *b);
 void localtime_to_TIME(MYSQL_TIME *to, struct tm *from);
 void calc_time_from_sec(MYSQL_TIME *to, long seconds, long microseconds);