bzr commit into mysql-5.5-bugteam branch (epotemkin:3229) Bug#59330

[Evgeny Potemkin]

#At file:///work/bzrroot/59330-bug-5.5-bugfixing/ based on revid:dao-gang.qu@stripped

 3229 Evgeny Potemkin	2011-01-12
      Bug#59330: Incorrect result when comparing an aggregate function with
      TIMESTAMP.
      
      Item_cache::get_cache wasn't treating TIMESTAMP as a DATETIME value thus
      returning string cache for items with TIMESTAMP type. This led to incorrect
      TIMESTAMP -> INT conversion and to a wrong query result.
      
      Fixed by using Item::is_datetime function to check for DATETIME type group.
     @ mysql-test/r/type_timestamp.result
        Added a test case for the bug#59330.
     @ mysql-test/t/type_timestamp.test
        Added a test case for the bug#59330.
     @ sql/item.cc
        Bug#59330: Incorrect result when comparing an aggregate function with
        TIMESTAMP.
        Item_cache::get_cache now uses is_datetime member function to detect DATETIME
        type group.

    modified:
      mysql-test/r/type_timestamp.result
      mysql-test/t/type_timestamp.test
      sql/item.cc
=== modified file 'mysql-test/r/type_timestamp.result'
--- a/mysql-test/r/type_timestamp.result	2010-09-13 07:30:10 +0000
+++ b/mysql-test/r/type_timestamp.result	2011-01-12 12:58:47 +0000
@@ -540,3 +540,32 @@ a
 2010-03-05 11:08:02
 DROP TABLE t1;
 End of Bug#50888
+#
+# Bug59330: Incorrect result when comparing an aggregate
+#           function with TIMESTAMP
+#
+CREATE TABLE t1 (dt DATETIME, ts TIMESTAMP);
+INSERT INTO t1 VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
+SELECT MAX(dt), MAX(ts) FROM t1;
+MAX(dt)	MAX(ts)
[phone]:34:30	2011-01-06 12:34:30
+SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1;
+MAX(ts) < '2010-01-01 00:00:00'
+0
+SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1;
+MAX(dt) < '2010-01-01 00:00:00'
+0
+SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1;
+MAX(ts) > '2010-01-01 00:00:00'
+1
+SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1;
+MAX(dt) > '2010-01-01 00:00:00'
+1
+SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1;
+MAX(ts) = '2011-01-06 12:34:30'
+1
+SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
+MAX(dt) = '2011-01-06 12:34:30'
+1
+DROP TABLE t1;
+End of 5.5 tests

=== modified file 'mysql-test/t/type_timestamp.test'
--- a/mysql-test/t/type_timestamp.test	2010-09-13 07:30:10 +0000
+++ b/mysql-test/t/type_timestamp.test	2011-01-12 12:58:47 +0000
@@ -377,3 +377,21 @@ SELECT a FROM t1;
 DROP TABLE t1;
 
 --echo End of Bug#50888
+
+--echo #
+--echo # Bug59330: Incorrect result when comparing an aggregate
+--echo #           function with TIMESTAMP
+--echo #
+CREATE TABLE t1 (dt DATETIME, ts TIMESTAMP);
+INSERT INTO t1 VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
+SELECT MAX(dt), MAX(ts) FROM t1;
+SELECT MAX(ts) < '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(dt) < '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(ts) > '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(dt) > '2010-01-01 00:00:00' FROM t1;
+SELECT MAX(ts) = '2011-01-06 12:34:30' FROM t1;
+SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
+DROP TABLE t1;
+
+--echo End of 5.5 tests
+

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-12-21 11:50:03 +0000
+++ b/sql/item.cc	2011-01-12 12:58:47 +0000
@@ -7370,8 +7370,7 @@ Item_cache* Item_cache::get_cache(const 
     return new Item_cache_decimal();
   case STRING_RESULT:
     /* Not all functions that return DATE/TIME are actually DATE/TIME funcs. */
-    if ((item->field_type() == MYSQL_TYPE_DATE ||
-         item->field_type() == MYSQL_TYPE_DATETIME ||
+    if ((item->is_datetime() ||
          item->field_type() == MYSQL_TYPE_TIME) &&
         (const_cast<Item*>(item))->result_as_longlong())
       return new Item_cache_datetime(item->field_type());


Attachment: [text/bzr-bundle] bzr/[e-mail]