bk commit into 5.0 tree (evgen:1.2445) BUG#28133

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-04-29 01:20:31+04:00, evgen@stripped +5 -0
  Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
  
  The IN 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 IN 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 uses either
  an object of the in_datetime class or an object of the cmp_item_datetime
  class to perform its work.
  The in_datetime class is used to find occurence of the item to be checked
  in the vector of the constant DATE/DATETIME values. The cmp_item_datetime
  class is used to compare items one by one in the DATE/DATETIME context.
  Both classes obtain values from items with help of the get_datetime_value()
  function and cache the left item if it is a constant one.

  mysql-test/r/func_in.result@stripped, 2007-04-29 01:20:23+04:00, evgen@stripped +1 -0
    A test case result is corrected after the fix for the bug#28133.

  mysql-test/r/type_datetime.result@stripped, 2007-04-29 01:20:23+04:00, evgen@stripped
+36 -0
    Added a test case for the bug#28133: Wrong DATE/DATETIME comparison in IN() function.

  mysql-test/t/type_datetime.test@stripped, 2007-04-29 01:20:21+04:00, evgen@stripped
+17 -0
    Added a test case for the bug#28133: Wrong DATE/DATETIME comparison in IN() function.

  sql/item_cmpfunc.cc@stripped, 2007-04-29 01:20:25+04:00, evgen@stripped +133 -44
    Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
    Now IN 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 uses either
    an object of the in_datetime class or an object of the cmp_item_datetime
    class to perform its work.

  sql/item_cmpfunc.h@stripped, 2007-04-29 01:20:24+04:00, evgen@stripped +49 -0
    Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
    Two DATE/DATETIME comparison classes are added.
    The in_datetime class is used to find occurence of the item to be checked
    in the vector of the constant DATE/DATETIME values. The cmp_item_datetime
    class is used to compare items one by one in the DATE/DATETIME context.
    Both classes obtain values from items with help of the get_datetime_value()
    function and cache the left item if it is a constant one.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/28133-bug-5.0-opt-mysql

--- 1.250/sql/item_cmpfunc.cc	2007-04-28 00:02:35.000000000 +0400
+++ 1.251/sql/item_cmpfunc.cc	2007-04-29 01:20:25.000000000 +0400
@@ -780,7 +780,7 @@
       MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
     value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
   }
-  if (item->const_item())
+  if (item->const_item() && cache_arg)
   {
     Item_cache_int *cache= new Item_cache_int();
     /* Mark the cache as non-const to prevent re-caching. */
@@ -2837,6 +2837,27 @@
   return (byte*) &tmp;
 }
 
+void in_datetime::set(uint pos,Item *item)
+{
+  Item **tmp= &item;
+  bool is_null;
+  struct packed_longlong *buff= &((packed_longlong*) base)[pos];
+
+  buff->val= get_datetime_value(thd, &tmp, 0, warn_item, &is_null);
+  buff->unsigned_flag= 1L;
+}
+
+byte *in_datetime::get_value(Item *item)
+{
+  bool is_null;
+  Item **tmp_item= lval_cache ? &lval_cache : &item;
+  tmp.val= get_datetime_value(thd, &tmp_item, &lval_cache, warn_item,
&is_null);
+  if (item->null_value)
+    return 0;
+  tmp.unsigned_flag= 1L;
+  return (byte*) &tmp;
+}
+
 in_double::in_double(uint elements)
   :in_vector(elements,sizeof(double),(qsort2_cmp) cmp_double, 0)
 {}
@@ -3058,6 +3079,36 @@
 }
 
 
+void cmp_item_datetime::store_value(Item *item)
+{
+  bool is_null;
+  Item **tmp_item= lval_cache ? &lval_cache : &item;
+  value= get_datetime_value(thd, &tmp_item, &lval_cache, warn_item,
&is_null);
+}
+
+
+int cmp_item_datetime::cmp(Item *arg)
+{
+  bool is_null;
+  Item **tmp_item= &arg;
+  return value !=
+    get_datetime_value(thd, &tmp_item, 0, warn_item, &is_null);
+}
+
+
+int cmp_item_datetime::compare(cmp_item *ci)
+{
+  cmp_item_datetime *l_cmp= (cmp_item_datetime *)ci;
+  return (value < l_cmp->value) ? -1 : ((value == l_cmp->value) ? 0 : 1);
+}
+
+
+cmp_item *cmp_item_datetime::make_same()
+{
+  return new cmp_item_datetime(warn_item);
+}
+
+
 bool Item_func_in::nulls_in_row()
 {
   Item **arg,**arg_end;
@@ -3133,7 +3184,10 @@
   Item **arg, **arg_end;
   uint const_itm= 1;
   THD *thd= current_thd;
-  
+  bool datetime_found= FALSE;
+  bool compare_as_dates= FALSE;
+  Item *date_arg= 0;
+
   if (agg_cmp_type(thd, &cmp_type, args, arg_count))
     return;
 
@@ -3149,58 +3203,90 @@
       break;
     }
   }
-
+  /* All DATE/DATETIME fields/functions has the STRING result type. */
+  if (cmp_type == STRING_RESULT)
+  {
+    /*
+      Check that all items has the STRING result type and at least one
+      of them is a DATE/DATETIME item.
+    */
+    for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++)
+    {
+      if (arg[0]->is_datetime())
+      {
+        datetime_found= TRUE;
+        /*
+          Internally all DATE/DATETIME values are converted to the DATETIME
+          type. So try to find a DATETIME item to issue correct warnings.
+        */
+        if (!date_arg)
+          date_arg= arg[0];
+        else if (arg[0]->field_type() == MYSQL_TYPE_DATETIME)
+        {
+          date_arg= arg[0];
+          break;
+        }
+      }
+    }
+    if (datetime_found)
+      compare_as_dates= TRUE;
+  }
   /*
     Row item with NULLs inside can return NULL or FALSE => 
     they can't be processed as static
   */
   if (const_itm && !nulls_in_row())
   {
-    /*
-      IN must compare INT/DATE/DATETIME/TIMESTAMP columns and constants
-      as int values (the same way as equality does).
-      So we must check here if the column on the left and all the constant 
-      values on the right can be compared as integers and adjust the 
-      comparison type accordingly.
-    */  
-    if (args[0]->real_item()->type() == FIELD_ITEM &&
-        thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
-        thd->lex->sql_command != SQLCOM_SHOW_CREATE &&
-        cmp_type != INT_RESULT)
+    if (compare_as_dates)
+      array= new in_datetime(date_arg, arg_count - 1);
+    else
     {
-      Field *field= ((Item_field*) (args[0]->real_item()))->field;
-      if (field->can_be_compared_as_longlong())
+      /*
+        IN must compare INT columns and constants as int values (the same
+        way as equality does).
+        So we must check here if the column on the left and all the constant 
+        values on the right can be compared as integers and adjust the 
+        comparison type accordingly.
+      */  
+      if (args[0]->real_item()->type() == FIELD_ITEM &&
+          thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+          thd->lex->sql_command != SQLCOM_SHOW_CREATE &&
+          cmp_type != INT_RESULT)
       {
-        bool all_converted= TRUE;
-        for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+        Field *field= ((Item_field*) (args[0]->real_item()))->field;
+        if (field->can_be_compared_as_longlong())
         {
-          if (!convert_constant_item (thd, field, &arg[0]))
-            all_converted= FALSE;
+          bool all_converted= TRUE;
+          for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+          {
+            if (!convert_constant_item (thd, field, &arg[0]))
+              all_converted= FALSE;
+          }
+          if (all_converted)
+            cmp_type= INT_RESULT;
         }
-        if (all_converted)
-          cmp_type= INT_RESULT;
       }
-    }
-    switch (cmp_type) {
-    case STRING_RESULT:
-      array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, 
-			  cmp_collation.collation);
-      break;
-    case INT_RESULT:
-      array= new in_longlong(arg_count-1);
-      break;
-    case REAL_RESULT:
-      array= new in_double(arg_count-1);
-      break;
-    case ROW_RESULT:
-      array= new in_row(arg_count-1, args[0]);
-      break;
-    case DECIMAL_RESULT:
-      array= new in_decimal(arg_count - 1);
-      break;
-    default:
-      DBUG_ASSERT(0);
-      return;
+      switch (cmp_type) {
+      case STRING_RESULT:
+        array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, 
+                            cmp_collation.collation);
+        break;
+      case INT_RESULT:
+        array= new in_longlong(arg_count-1);
+        break;
+      case REAL_RESULT:
+        array= new in_double(arg_count-1);
+        break;
+      case ROW_RESULT:
+        array= new in_row(arg_count-1, args[0]);
+        break;
+      case DECIMAL_RESULT:
+        array= new in_decimal(arg_count - 1);
+        break;
+      default:
+        DBUG_ASSERT(0);
+        return;
+      }
     }
     if (array && !(thd->is_fatal_error))		// If not EOM
     {
@@ -3219,7 +3305,10 @@
   }
   else
   {
-    in_item= cmp_item::get_comparator(cmp_type, cmp_collation.collation);
+    if (compare_as_dates)
+      in_item= new cmp_item_datetime(date_arg);
+    else
+      in_item= cmp_item::get_comparator(cmp_type, cmp_collation.collation);
     if (cmp_type  == STRING_RESULT)
       in_item->cmp_charset= cmp_collation.collation;
   }

--- 1.151/sql/item_cmpfunc.h	2007-04-27 21:26:45.000000000 +0400
+++ 1.152/sql/item_cmpfunc.h	2007-04-29 01:20:24.000000000 +0400
@@ -833,6 +833,7 @@
 
 class in_longlong :public in_vector
 {
+protected:
   /*
     Here we declare a temporary variable (tmp) of the same type as the
     elements of this vector. tmp is used in finding if a given value is in 
@@ -866,6 +867,30 @@
   friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
 };
 
+
+/*
+  Class for representing a vector of constant DATE/DATETIME values.
+  Values are obtained with help of the get_datetime_value() function.
+  If the left item is a constant one then its value is cached in the
+  lval_cache variable.
+*/
+class in_datetime :public in_longlong
+{
+public:
+  THD *thd;
+  /* An item used to issue warnings. */
+  Item *warn_item;
+  /* Cache for the left item. */
+  Item *lval_cache;
+
+  in_datetime(Item *warn_item_arg, uint elements)
+    :in_longlong(elements), thd(current_thd), warn_item(warn_item_arg),
+     lval_cache(0) {};
+  void set(uint pos,Item *item);
+  byte *get_value(Item *item);
+  friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
+};
+
 class in_double :public in_vector
 {
   double tmp;
@@ -986,6 +1011,30 @@
   cmp_item *make_same();
 };
 
+/*
+  Compare items in the DATETIME context.
+  Values are obtained with help of the get_datetime_value() function.
+  If the left item is a constant one then its value is cached in the
+  lval_cache variable.
+*/
+class cmp_item_datetime :public cmp_item
+{
+  ulonglong value;
+public:
+  THD *thd;
+  /* Item used for issuing warnings. */
+  Item *warn_item;
+  /* Cache for the left item. */
+  Item *lval_cache;
+
+  cmp_item_datetime(Item *warn_item_arg)
+    :thd(current_thd), warn_item(warn_item_arg), lval_cache(0) {}
+  void store_value(Item *item);
+  int cmp(Item *arg);
+  int compare(cmp_item *ci);
+  cmp_item *make_same();
+};
+
 class cmp_item_real :public cmp_item
 {
   double value;

--- 1.34/mysql-test/r/func_in.result	2007-03-22 10:05:34.000000000 +0300
+++ 1.35/mysql-test/r/func_in.result	2007-04-29 01:20:23.000000000 +0400
@@ -467,6 +467,7 @@
 INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
 SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
 a
[phone]
 Warnings:
 Warning	1292	Incorrect date value: '19772-07-29' for column 'a' at row 1
 DROP TABLE t1,t2,t3,t4;

--- 1.37/mysql-test/r/type_datetime.result	2007-04-27 00:18:30.000000000 +0400
+++ 1.38/mysql-test/r/type_datetime.result	2007-04-29 01:20:23.000000000 +0400
@@ -266,3 +266,39 @@
 SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND
CURRENT_DATE();
 1
 drop table t1;
+create table t1 (f1 date);
+insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
+select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
+f1
[phone]
[phone]
[phone]
+create table t2(f2 datetime);
+insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');
+select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');
+f2
[phone]:00:00
[phone]:34:56
+select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));
+f1	f2
[phone]-01 00:00:00
[phone]-03 12:34:56
[phone]-06 11:22:33
+select * from t1,t2 where '01-01-01' in (f1, '01-02-03');
+f1	f2
[phone]-01 00:00:00
[phone]-03 12:34:56
[phone]-06 11:22:33
+select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);
+f1	f2
[phone]-03 12:34:56
[phone]-03 12:34:56
[phone]-03 12:34:56
+create table t3(f3 varchar(20));
+insert into t3 select * from t2;
+select * from t2,t3 where f2 in (f3,'03-04-05');
+f2	f3
[phone]:00:00	2001-01-01 00:00:00
[phone]:34:56	2001-02-03 12:34:56
[phone]:22:33	2002-04-06 11:22:33
+drop table t1,t2,t3;

--- 1.23/mysql-test/t/type_datetime.test	2007-04-27 00:18:05.000000000 +0400
+++ 1.24/mysql-test/t/type_datetime.test	2007-04-29 01:20:21.000000000 +0400
@@ -180,3 +180,20 @@
 select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
 SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND
CURRENT_DATE();
 drop table t1;
+
+#
+# Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
+#
+create table t1 (f1 date);
+insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
+select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
+create table t2(f2 datetime);
+insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');
+select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');
+select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));
+select * from t1,t2 where '01-01-01' in (f1, '01-02-03');
+select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);
+create table t3(f3 varchar(20));
+insert into t3 select * from t2;
+select * from t2,t3 where f2 in (f3,'03-04-05');
+drop table t1,t2,t3;