MySQL Bugs: #59330: HAVING applies "reverse boolean logic" to aggregate function with TIMESTAMP

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	CPU Architecture:	Any
Tags:	HAVING TIMESTAMP, regression

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';

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.

a possible fix: add MYSQL_TYPE_TIMESTAMP to the case STRING_RESULT in Item_cache::get_cache().

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 ||

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.

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)

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)

Noted in 5.5.9, 5.6.2 changelogs.

Comparisons of aggregate values with TIMESTAMP values were incorrect.