BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly

- Make conditions like "date_col $CMP$ 'datetime-const'" range-sargable


mysql-test/r/range.result:
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Testcase
mysql-test/t/range.test:
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Testcase
sql/field.cc:
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Added comments

1 files changed, 26 insertions, 1 deletions

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7a51dbbe76c..5d8bba69422 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -4414,6 +4414,7 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
     {
       tree= new (alloc) SEL_ARG(field, 0, 0);
       tree->type= SEL_ARG::IMPOSSIBLE;
+      goto end;
     }
     else
     {
@@ -4422,8 +4423,32 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
         for the cases like int_field > 999999999999999999999999 as well.
       */
       tree= 0;
+      if (err == 3 && field->type() == FIELD_TYPE_DATE && 
+          (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC || 
+           type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+      {
+        /*
+          We were saving DATETIME into a DATE column, the conversion went ok
+          but a non-zero time part was cut off.
+
+          In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
+          values. Index over a DATE column uses DATE comparison. Changing 
+          from one comparison to the other is possible:
+
+          datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
+          datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
+
+          datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
+          datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
+
+          but we'll need to convert '>' to '>=' and '<' to '<='. This will
+          be done together with other types at the end of this function
+          (grep for field_is_equal_to_item)
+        */
+      }
+      else
+        goto end;
     }
-    goto end;
   } 
   if (err < 0)
   {