Bug #59330 HAVING applies "reverse boolean logic" to aggregate function with TIMESTAMP
Submitted: 6 Jan 2011 11:06 Modified: 12 Jan 2011 18:04
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.5.7 and 5.5.8 OS:Any
Assigned to: Evgeny Potemkin
Tags: HAVING TIMESTAMP, regression
Triage: Triaged: D2 (Serious)

[6 Jan 2011 11:06] Alexander Keremidarski
Description:
... HAVING MAX(timestamp_col) reverses the result of comparison operators.
This could be related to http://bugs.mysql.com/bug.php?id=38072 in some way, but here HAVING clause is not ignored. Hence new bug report.

Doesn't exist in 5.1 series and 5.5.1, but appears in 5.5.7, 5.5.8 and 5.5 from launchpad revno: 3217 timestamp: Wed 2011-01-05 15:02:48 +0200

Run the test case below and observe the following results which are obviously wrong for TIMESTAMP column and inconsistent with the correct results for DATETIME column. Some other types I tested work as they should.

mysql> SELECT MAX(dt), MAX(ts) FROM test;
+---------------------+---------------------+
| MAX(dt)             | MAX(ts)             |
+---------------------+---------------------+
| 2011-01-06 12:34:30 | 2011-01-06 12:34:30 | 
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) < '2010-01-01 00:00:00';
+---------------------+
| MAX(ts)             |
+---------------------+
| 2011-01-06 12:34:30 | 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) < '2010-01-01 00:00:00';
Empty set (0.00 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) > '2010-01-01 00:00:00';
Empty set (0.00 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) > '2010-01-01 00:00:00';
+---------------------+
| MAX(dt)             |
+---------------------+
| 2011-01-06 12:34:30 | 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) = '2011-01-06 12:34:30';
Empty set (0.00 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) = '2011-01-06 12:34:30';
+---------------------+
| MAX(dt)             |
+---------------------+
| 2011-01-06 12:34:30 | 
+---------------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE test (dt DATETIME, ts TIMESTAMP);
INSERT INTO test VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
SELECT MAX(dt), MAX(ts) FROM test;
SELECT MAX(ts) FROM test HAVING MAX(ts) < '2010-01-01 00:00:00';
SELECT MAX(dt) FROM test HAVING MAX(dt) < '2010-01-01 00:00:00';
SELECT MAX(ts) FROM test HAVING MAX(ts) > '2010-01-01 00:00:00';
SELECT MAX(dt) FROM test HAVING MAX(dt) > '2010-01-01 00:00:00';
SELECT MAX(ts) FROM test HAVING MAX(ts) = '2011-01-06 12:34:30';
SELECT MAX(dt) FROM test HAVING MAX(dt) = '2011-01-06 12:34:30';
[6 Jan 2011 11:10] Valerii Kravchuk
Indeed, this is what we have:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test (dt DATETIME, ts TIMESTAMP);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO test VALUES('2011-01-06 12:34:30', '2011-01-06 12:34:30');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT MAX(dt), MAX(ts) FROM test;
+---------------------+---------------------+
| MAX(dt)             | MAX(ts)             |
+---------------------+---------------------+
| 2011-01-06 12:34:30 | 2011-01-06 12:34:30 |
+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) < '2010-01-01 00:00:00';
+---------------------+
| MAX(ts)             |
+---------------------+
| 2011-01-06 12:34:30 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) < '2010-01-01 00:00:00';
Empty set (0.00 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) > '2010-01-01 00:00:00';
Empty set (0.00 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) > '2010-01-01 00:00:00';
+---------------------+
| MAX(dt)             |
+---------------------+
| 2011-01-06 12:34:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(ts) FROM test HAVING MAX(ts) = '2011-01-06 12:34:30';
Empty set (0.00 sec)

mysql> SELECT MAX(dt) FROM test HAVING MAX(dt) = '2011-01-06 12:34:30';
+---------------------+
| MAX(dt)             |
+---------------------+
| 2011-01-06 12:34:30 |
+---------------------+
1 row in set (0.00 sec)

I see correct results with 5.1.54, so this is a regression bug.
[7 Jan 2011 12:50] Sergei Golubchik
a possible fix: add MYSQL_TYPE_TIMESTAMP to the case STRING_RESULT in Item_cache::get_cache().
[11 Jan 2011 15:08] Grégory Duchatelet
Sergei> I can confirm that it fix it.

Patch:
$ diff sql/item.cc ../mysql-5.5.8/sql/item.cc
7380d7379
<          item->field_type() == MYSQL_TYPE_TIMESTAMP ||
[12 Jan 2011 13:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128507

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.
[12 Jan 2011 17:22] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:epotemkin@mysql.com-20110112164700-a78e0yweznp0zkzy) (version source revid:epotemkin@mysql.com-20110112125847-zrw7jg2ge1e0p8fz) (merge vers: 5.5.9) (pib:24)
[12 Jan 2011 17:30] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:epotemkin@mysql.com-20110112172453-d202wpy4h3s11dys) (version source revid:epotemkin@mysql.com-20110112172453-d202wpy4h3s11dys) (merge vers: 5.6.2) (pib:24)
[12 Jan 2011 18:04] Paul Dubois
Noted in 5.5.9, 5.6.2 changelogs.

Comparisons of aggregate values with TIMESTAMP values were incorrect.