bk commit into 5.0 tree (evgen:1.2440) BUG#16377

[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-27 00:40:35+04:00, evgen@stripped +6 -0
  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/r/query_cache.result@stripped, 2007-04-27 00:18:34+04:00, evgen@stripped +3
-9
    A test case result corrected after the fix for bug#16377.

  mysql-test/r/type_datetime.result@stripped, 2007-04-27 00:18:30+04:00, evgen@stripped
+35 -0
    Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN
function.

  mysql-test/t/type_datetime.test@stripped, 2007-04-27 00:18:05+04:00, evgen@stripped
+19 -0
    Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN
function.

  sql/item_cmpfunc.cc@stripped, 2007-04-27 00:34:54+04:00, evgen@stripped +67 -23
    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@stripped, 2007-04-27 00:36:55+04:00, evgen@stripped +6 -1
    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.

  sql/item_timefunc.cc@stripped, 2007-04-27 00:21:37+04:00, evgen@stripped +21 -0
    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.

# 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/16377-bug-5.0-opt-mysql

--- 1.247/sql/item_cmpfunc.cc	2007-04-27 00:13:10.000000000 +0400
+++ 1.248/sql/item_cmpfunc.cc	2007-04-27 00:34:54.000000000 +0400
@@ -692,7 +692,7 @@
         b= (Item **)&b_cache;
       }
     }
-    is_nulls_eq= owner->functype() == Item_func::EQUAL_FUNC;
+    is_nulls_eq= test(owner && owner->functype() == Item_func::EQUAL_FUNC);
     func= &Arg_comparator::compare_datetime;
     return 0;
   }
@@ -700,6 +700,21 @@
 }
 
 
+void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1)
+{
+  thd= current_thd;
+  /* A caller will handle null values by itself. */
+  owner= NULL;
+  a= a1;
+  b= b1;
+  a_type= (*a)->field_type();
+  b_type= (*b)->field_type();
+  a_cache= 0;
+  b_cache= 0;
+  is_nulls_eq= FALSE;
+  func= &Arg_comparator::compare_datetime;
+}
+
 /*
   Retrieves correct DATETIME value from given item.
 
@@ -807,7 +822,8 @@
   a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null);
   if (!is_nulls_eq && is_null)
   {
-    owner->null_value= 1;
+    if (owner)
+      owner->null_value= 1;
     return -1;
   }
 
@@ -815,11 +831,13 @@
   b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null);
   if (is_null)
   {
-    owner->null_value= is_nulls_eq ? 0 : 1;
+    if (owner)
+      owner->null_value= is_nulls_eq ? 0 : 1;
     return is_nulls_eq ? 1 : -1;
   }
 
-  owner->null_value= 0;
+  if (owner)
+    owner->null_value= 0;
 
   /* Compare values. */
   if (is_nulls_eq)
@@ -1674,8 +1692,11 @@
 
 void Item_func_between::fix_length_and_dec()
 {
-   max_length= 1;
-   THD *thd= current_thd;
+  max_length= 1;
+  THD *thd= current_thd;
+  int i;
+  bool datetime_found= FALSE;
+  compare_as_dates= TRUE;
 
   /*
     As some compare functions are generated after sql_yacc,
@@ -1690,26 +1711,29 @@
    return;
 
   /*
-    Make a special case of compare with date/time and longlong fields.
-    They are compared as integers, so for const item this time-consuming
-    conversion can be done only once, not for every single comparison
+    Detect the comparison of DATE/DATETIME items.
+    At least one of items should be a DATE/DATETIME item and other items
+    should return the STRING result.
   */
-  if (args[0]->real_item()->type() == FIELD_ITEM &&
-      thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
-      thd->lex->sql_command != SQLCOM_SHOW_CREATE)
+  for (i= 0; i < 3; i++)
   {
-    Field *field=((Item_field*) (args[0]->real_item()))->field;
-    if (field->can_be_compared_as_longlong())
+    if (args[i]->is_datetime())
     {
-      /*
-        The following can't be recoded with || as convert_constant_item
-        changes the argument
-      */
-      if (convert_constant_item(thd, field,&args[1]))
-	cmp_type=INT_RESULT;			// Works for all types.
-      if (convert_constant_item(thd, field,&args[2]))
-	cmp_type=INT_RESULT;			// Works for all types.
+      datetime_found= TRUE;
+      continue;
     }
+    if (args[i]->result_type() == STRING_RESULT)
+      continue;
+    compare_as_dates= FALSE;
+    break;
+  }
+  if (!datetime_found)
+    compare_as_dates= FALSE;
+
+  if (compare_as_dates)
+  {
+    ge_cmp.set_datetime_cmp_func(args, args + 1);
+    le_cmp.set_datetime_cmp_func(args, args + 2);
   }
 }
 
@@ -1717,7 +1741,27 @@
 longlong Item_func_between::val_int()
 {						// ANSI BETWEEN
   DBUG_ASSERT(fixed == 1);
-  if (cmp_type == STRING_RESULT)
+  if (compare_as_dates)
+  {
+    int ge_res, le_res;
+
+    ge_res= ge_cmp.compare();
+    if ((null_value= args[0]->null_value))
+      return 0;
+    le_res= le_cmp.compare();
+
+    if (!args[1]->null_value && !args[2]->null_value)
+      return (longlong) ((ge_res >= 0 && le_res <=0) != negated);
+    else if (args[1]->null_value)
+    {
+      null_value= le_res > 0;			// not null if false range.
+    }
+    else
+    {
+      null_value= ge_res < 0;
+    }
+  }
+  else if (cmp_type == STRING_RESULT)
   {
     String *value,*a,*b;
     value=args[0]->val_str(&value0);

--- 1.149/sql/item_cmpfunc.h	2007-04-27 00:13:10.000000000 +0400
+++ 1.150/sql/item_cmpfunc.h	2007-04-27 00:36:55.000000000 +0400
@@ -91,6 +91,7 @@
   static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
                                                       ulonglong *const_val_arg);
 
+  void set_datetime_cmp_func(Item **a1, Item **b1);
   static arg_cmp_func comparator_matrix [5][2];
 
   friend class Item_func;
@@ -579,8 +580,12 @@
 public:
   Item_result cmp_type;
   String value0,value1,value2;
+  /* TRUE <=> arguments will be compared as dates. */
+  bool compare_as_dates;
+  /* Comparators used for DATE/DATETIME comparison. */
+  Arg_comparator ge_cmp, le_cmp;
   Item_func_between(Item *a, Item *b, Item *c)
-    :Item_func_opt_neg(a, b, c) {}
+    :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) {}
   longlong val_int();
   optimize_type select_optimize() const { return OPTIMIZE_KEY; }
   enum Functype functype() const   { return BETWEEN; }

--- 1.141/sql/item_timefunc.cc	2007-02-22 17:59:55.000000000 +0300
+++ 1.142/sql/item_timefunc.cc	2007-04-27 00:21:37.000000000 +0400
@@ -2173,6 +2173,27 @@
   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:

--- 1.36/mysql-test/r/type_datetime.result	2007-04-27 00:10:40.000000000 +0400
+++ 1.37/mysql-test/r/type_datetime.result	2007-04-27 00:18:30.000000000 +0400
@@ -231,3 +231,38 @@
 2001-04-15	2001-04-15 00:00:00	987282000	987282000	0	1	0
 2001-05-20	2001-05-20 01:01:01	990309661	990306000	0	0	1
 drop table t1;
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+f2
[phone]:00:00
[phone]:01:01
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+f1	f2	f3
[phone]-05 00:00:00	2001-02-05 01:01:01
[phone]-09 01:01:01	2001-03-10 01:01:01
[phone]-15 00:00:00	2001-04-15 00:00:00
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+cast(f3 as date);
+f1	f2	f3
[phone]-05 00:00:00	2001-02-05 01:01:01
[phone]-09 01:01:01	2001-03-10 01:01:01
[phone]-15 00:00:00	2001-04-15 00:00:00
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+f2
[phone]:01:01
[phone]:00:00
[phone]:01:01
+select f2, f3 from t1 where '01-03-10' between f2 and f3;
+f2	f3
[phone]:01:01	2001-03-10 01:01:01
+select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
+f2
[phone]:00:00
+SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND
CURRENT_DATE();
+1
+drop table t1;

--- 1.22/mysql-test/t/type_datetime.test	2007-04-27 00:10:40.000000000 +0400
+++ 1.23/mysql-test/t/type_datetime.test	2007-04-27 00:18:05.000000000 +0400
@@ -161,3 +161,22 @@
   f1 > f2, f1 = f2, f1 < f2
   from t1;
 drop table t1;
+
+#
+# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+  cast(f3 as date);
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+select f2, f3 from t1 where '01-03-10' between f2 and f3;
+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;

--- 1.78/mysql-test/r/query_cache.result	2007-02-14 16:44:30.000000000 +0300
+++ 1.79/mysql-test/r/query_cache.result	2007-04-27 00:18:34.000000000 +0400
@@ -945,25 +945,19 @@
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
+Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050328 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050328 invalid'
+Warning	1292	Incorrect datetime value: '20050328 invalid' for column 'date' at row 0
 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid';
 COUNT(*)
 0
 Warnings:
 Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
-Warning	1292	Truncated incorrect INTEGER value: '20050327 invalid'
+Warning	1292	Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0