Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result

  
Problem: caching 00000000-00000099 dates as integer values we're 
improperly shifting them up twice in the get_datetime_value().
  
Fix: don't shift cached DATETIME values up for the second time.


mysql-test/r/type_date.result:
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
    - test result.
mysql-test/t/type_date.test:
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
    - test case.
sql/item.h:
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
    - Item_cache::field_type() method added.
    - new Item_cache(enum_field_types) and Item_cache_int(enum_field_types) 
      constructors added.
sql/item_cmpfunc.cc:
  Fix for bug #32021: Using Date 000-00-01 in WHERE causes wrong result
    - don't shift cached DATETIME values for the second time in the
      get_datetime_value():
        creating new Item_cache_int set DATETIME filed type,
        check the type before shifting.

4 files changed, 39 insertions, 4 deletions

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 904db1a14d0..392260edb55 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -224,3 +224,10 @@ ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
 SET SQL_MODE=DEFAULT;
 DROP TABLE t1,t2;
 End of 5.0 tests
+create table t1 (a date, primary key (a))engine=memory;
+insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01');
+select * from t1 where a between '0000-00-01' and '0000-00-02';
+a
+0000-00-01
+drop table t1;
+End of 5.1 tests
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index b21f21d2f3d..14854406eb8 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -194,3 +194,13 @@ SET SQL_MODE=DEFAULT;
 DROP TABLE t1,t2;
 
 --echo End of 5.0 tests
+
+#
+# Bug#32021: Using Date 000-00-01 in WHERE causes wrong result
+#
+create table t1 (a date, primary key (a))engine=memory;
+insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01');
+select * from t1 where a between '0000-00-01' and '0000-00-02';
+drop table t1;
+
+--echo End of 5.1 tests
diff --git a/sql/item.h b/sql/item.h
index 6990d9ed021..379eb8a24be 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2620,8 +2620,20 @@ class Item_cache: public Item
 protected:
   Item *example;
   table_map used_table_map;
+  enum enum_field_types cached_field_type;
 public:
-  Item_cache(): example(0), used_table_map(0) {fixed= 1; null_value= 1;}
+  Item_cache(): 
+    example(0), used_table_map(0), cached_field_type(MYSQL_TYPE_STRING) 
+  {
+    fixed= 1; 
+    null_value= 1;
+  }
+  Item_cache(enum_field_types field_type_arg):
+    example(0), used_table_map(0), cached_field_type(field_type_arg)
+  {
+    fixed= 1;
+    null_value= 1;
+  }
 
   void set_used_tables(table_map map) { used_table_map= map; }
 
@@ -2637,6 +2649,7 @@ public:
   };
   virtual void store(Item *)= 0;
   enum Type type() const { return CACHE_ITEM; }
+  enum_field_types field_type() const { return cached_field_type; }
   static Item_cache* get_cache(const Item *item);
   table_map used_tables() const { return used_table_map; }
   virtual void keep_array() {}
@@ -2652,6 +2665,8 @@ protected:
   longlong value;
 public:
   Item_cache_int(): Item_cache(), value(0) {}
+  Item_cache_int(enum_field_types field_type_arg):
+    Item_cache(field_type_arg), value(0) {}
 
   void store(Item *item);
   void store(Item *item, longlong val_arg);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 639788d65bc..883e48f3d50 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -933,12 +933,15 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
   {
     value= item->val_int();
     *is_null= item->null_value;
+    enum_field_types f_type= item->field_type();
     /*
       Item_date_add_interval may return MYSQL_TYPE_STRING as the result
       field type. To detect that the DATE value has been returned we
-      compare it with 1000000L - any DATE value should be less than it.
+      compare it with 100000000L - any DATE value should be less than it.
+      Don't shift cached DATETIME values up for the second time.
     */
-    if (item->field_type() == MYSQL_TYPE_DATE || value < 100000000L)
+    if (f_type == MYSQL_TYPE_DATE ||
+        (f_type != MYSQL_TYPE_DATETIME && value < 100000000L))
       value*= 1000000L;
   }
   else
@@ -975,7 +978,7 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
   if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
       ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
   {
-    Item_cache_int *cache= new Item_cache_int();
+    Item_cache_int *cache= new Item_cache_int(MYSQL_TYPE_DATETIME);
     /* Mark the cache as non-const to prevent re-caching. */
     cache->set_used_tables(1);
     cache->store(item, value);