bk commit into 5.0 tree (evgen:1.2514) BUG#28778

[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-06-06 00:25:06+04:00, evgen@stripped +3 -0
  Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
  integer constants.
  
  This bug is introduced by the fix for bug#16377. Before the fix the 
  Item_func_between::fix_length_and_dec method converted the second and third
  arguments to the type of the first argument if they were constant and the first
  argument is of the DATE/DATETIME type. That approach worked well for integer
  constants and sometimes produced bad result for string constants. The fix for
  the bug#16377 wrongly removed that code at all and as a result of this the
  comparison of a DATETIME field and an integer constant was carried out in a
  wrong way and sometimes led to wrong result sets.
  
  Now the Item_func_between::fix_length_and_dec method converts the second and
  third arguments to the type of the first argument if they are constant, the
  first argument is of the DATE/DATETIME type and the DATETIME comparator isn't
  applicable.

  mysql-test/r/type_datetime.result@stripped, 2007-06-06 00:25:01+04:00, evgen@stripped
+16 -0
    Added a test case for the bug#28778: Wrong result of BETWEEN when comparing a DATETIME
    field with an integer constants.

  mysql-test/t/type_datetime.test@stripped, 2007-06-06 00:25:02+04:00, evgen@stripped
+11 -0
    Added a test case for the bug#28778: Wrong result of BETWEEN when comparing a DATETIME
    field with an integer constants.

  sql/item_cmpfunc.cc@stripped, 2007-06-06 00:24:11+04:00, evgen@stripped +17 -0
    Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
    integer constants.
    Now the Item_func_between::fix_length_and_dec method converts the second and
    third arguments to the type of the first argument if they are constant, the
    first argument is of the DATE/DATETIME type and the DATETIME comparator isn't
    applicable.

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

--- 1.254/sql/item_cmpfunc.cc	2007-05-30 00:32:55 +04:00
+++ 1.255/sql/item_cmpfunc.cc	2007-06-06 00:24:11 +04:00
@@ -1755,6 +1755,23 @@ void Item_func_between::fix_length_and_d
     ge_cmp.set_datetime_cmp_func(args, args + 1);
     le_cmp.set_datetime_cmp_func(args, args + 2);
   }
+  else if (args[0]->real_item()->type() == FIELD_ITEM &&
+           thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+           thd->lex->sql_command != SQLCOM_SHOW_CREATE)
+  {
+    Field *field=((Item_field*) (args[0]->real_item()))->field;
+    if (field->can_be_compared_as_longlong())
+    {
+      /*
+        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.
+    }
+  }
 }
 
 

--- 1.45/mysql-test/r/type_datetime.result	2007-05-21 22:50:02 +04:00
+++ 1.46/mysql-test/r/type_datetime.result	2007-06-06 00:25:01 +04:00
@@ -411,3 +411,19 @@ if(@bug28261 = f1, '', @bug28261:= f1)
 2001-01-01
 2002-02-02
 drop table t1;
+create table t1(f1 datetime);
+insert into t1 values('2001-01-01'),('2002-02-02');
+select * from t1 where f1 between 20020101 and 20070101000000;
+f1
[phone]:00:00
+select * from t1 where f1 between 2002010 and 20070101000000;
+f1
[phone]:00:00
[phone]:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '2002010' for column 'f1' at row 1
+select * from t1 where f1 between 20020101 and 2007010100000;
+f1
+Warnings:
+Warning	1292	Incorrect datetime value: '2007010100000' for column 'f1' at row 1
+drop table t1;

--- 1.31/mysql-test/t/type_datetime.test	2007-05-21 22:50:02 +04:00
+++ 1.32/mysql-test/t/type_datetime.test	2007-06-06 00:25:02 +04:00
@@ -271,3 +271,14 @@ select if(@bug28261 = f1, '', @bug28261:
 select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
 select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
 drop table t1;
+
+#
+# Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
+#            integer constants.
+#
+create table t1(f1 datetime);
+insert into t1 values('2001-01-01'),('2002-02-02');
+select * from t1 where f1 between 20020101 and 20070101000000;
+select * from t1 where f1 between 2002010 and 20070101000000;
+select * from t1 where f1 between 20020101 and 2007010100000;
+drop table t1;