A fix for bug when comparing a datetime column with timestamp

values with BETWEEN clause

4 files changed, 26 insertions, 0 deletions

diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
index 8cfae44b9dd..ef93096478f 100644
--- a/mysql-test/r/func_test.result
+++ b/mysql-test/r/func_test.result
@@ -46,6 +46,20 @@ select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
 select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL;
 1 XOR 1	1 XOR 0	0 XOR 1	0 XOR 0	NULL XOR 1	1 XOR NULL	0 XOR NULL
 0	1	1	0	NULL	NULL	NULL
+drop table if exists t1,t2;
+CREATE TABLE t1 ( start datetime default NULL) TYPE=MyISAM;
+INSERT INTO t1 VALUES ('2002-10-21 00:00:00');
+INSERT INTO t1 VALUES ('2002-10-28 00:00:00');
+INSERT INTO t1 VALUES ('2002-11-04 00:00:00');
+CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL) TYPE=MyISAM;
+INSERT INTO t2 VALUES (20021029165106,20021105164731);
+select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
+start	ctime1	ctime2
+2002-11-04 00:00:00	20021029165106	20021105164731
+select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
+start	ctime1	ctime2
+2002-11-04 00:00:00	20021029165106	20021105164731
+drop table if exists t1,t2;
 select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;
 5 between 0 and 10 between 0 and 1	(5 between 0 and 10) between 0 and 1
 0	1
diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test
index f5ad2e21c73..1486e5bcca8 100644
--- a/mysql-test/t/func_test.test
+++ b/mysql-test/t/func_test.test
@@ -17,6 +17,16 @@ select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,
 select -1.49 or -1.49,0.6 or 0.6;
 select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
 select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL;
+drop table if exists t1,t2;
+CREATE TABLE t1 ( start datetime default NULL) TYPE=MyISAM;
+INSERT INTO t1 VALUES ('2002-10-21 00:00:00');
+INSERT INTO t1 VALUES ('2002-10-28 00:00:00');
+INSERT INTO t1 VALUES ('2002-11-04 00:00:00');
+CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL) TYPE=MyISAM;
+INSERT INTO t2 VALUES (20021029165106,20021105164731);
+select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
+select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
+drop table if exists t1,t2;
 
 #
 # Wrong usage of functions
diff --git a/sql/field.h b/sql/field.h
index 4290f99ea3e..de9e98290e7 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -544,6 +544,7 @@ public:
   enum Item_result result_type () const { return field_length == 8 || field_length == 14 ? INT_RESULT : STRING_RESULT; }
   enum_field_types type() const { return FIELD_TYPE_TIMESTAMP;}
   enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
+  enum Item_result cmp_type () const { return INT_RESULT; }
   void store(const char *to,uint length);
   void store(double nr);
   void store(longlong nr);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 79d695eea1e..42cd0a2ee4f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -360,6 +360,7 @@ void Item_func_between::fix_length_and_dec()
   if (args[0]->type() == FIELD_ITEM)
   {
     Field *field=((Item_field*) args[0])->field;
+    cmp_type=field->cmp_type();
     if (field->store_for_compare())
     {
       if (convert_constant_item(field,&args[1]))