MySQL Bugs: #54784: MIN(datetime), MAX(datetime) do not work

Bug #54784	MIN(datetime), MAX(datetime) do not work
Submitted:	24 Jun 2010 16:51	Modified:	12 Jul 2010 8:23
Reporter:	Pierre Potvin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5.3-m3-community, 5.5.6-m3	OS:	Any (Win 7 (6.1.7600), Mac OS X)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	datetime, MAX, min, regression

Description:
SELECT MIN(datetime) does not always return the minimum datetime value.  Same with MAX.

How to repeat:
Result below is 16:20, but should be 16:00.

DROP TABLE IF EXISTS time_test;
CREATE TABLE time_test(x DATETIME);
INSERT INTO time_test VALUES('2010-01-01 17:00'), ('2010-01-01 16:30'), ('2010-01-01 16:00');
INSERT INTO time_test VALUES('2010-01-01 16:10'), ('2010-01-01 16:15'), ('2010-01-01 16:20');

mysql> SELECT MIN(x) FROM time_test;
+---------------------+
| MIN(x)              |
+---------------------+
| 2010-01-01 16:20:00 |
+---------------------+
1 row in set (0.08 sec)

Suggested fix:
Temporary workaround is to cast to char:

SELECT MIN(CAST(x) AS CHAR) FROM time_test

Verified just as described on current mysql-trunk from bzr:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> DROP TABLE IF EXISTS time_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE time_test(x DATETIME);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO time_test VALUES('2010-01-01 17:00'), ('2010-01-01 16:30'),
    -> ('2010-01-01 16:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO time_test VALUES('2010-01-01 16:10'), ('2010-01-01 16:15'),
    -> ('2010-01-01 16:20');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT MIN(x) FROM time_test;
+---------------------+
| MIN(x)              |
+---------------------+
| 2010-01-01 16:20:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(x) FROM time_test;
+---------------------+
| MAX(x)              |
+---------------------+
| 2010-01-01 17:00:00 |
+---------------------+
1 row in set (0.00 sec)

Adding index helps a bit:

mysql> alter table time_test add key(x);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT MIN(x) FROM time_test;
+---------------------+
| MIN(x)              |
+---------------------+
| 2010-01-01 16:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(x) FROM time_test;
+---------------------+
| MAX(x)              |
+---------------------+
| 2010-01-01 17:00:00 |
+---------------------+
1 row in set (0.00 sec)

Also it works with 5.1, so this is a regression bug:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> DROP TABLE IF EXISTS time_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE time_test(x DATETIME);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO time_test VALUES('2010-01-01 17:00'), ('2010-01-01 16:30'), ('2010-01-01
    '> 16:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO time_test VALUES('2010-01-01 16:10'), ('2010-01-01 16:15'), ('2010-01-01
    '> 16:20');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
SELECT MIN(x) FROM time_test;+---------------------+
| MIN(x)              |
+---------------------+
| 2010-01-01 16:00:00 |
+---------------------+
1 row in set (0.01 sec)

I can confirm this bug for Server version: 5.5.4-m3-log MySQL Community Server (GPL)

show create table date_test;
+-----------+--------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                     |
+-----------+--------------------------------------------------------------------------------------------------+
| date_test | CREATE TABLE `date_test` (
  `test` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from date_test;
+---------------------+
| test                |
+---------------------+
| 2005-09-14 21:02:00 |
| 2005-10-14 21:02:00 |
| 2005-11-14 22:03:00 |
| 2005-10-31 22:02:00 |
| 2006-01-13 22:04:00 |
| 2006-03-14 22:02:00 |
| 2006-05-12 21:03:00 |
| 2006-07-14 20:02:00 |
| 2006-02-27 22:02:00 |
| 2005-10-31 22:02:00 |
| 2006-11-14 21:14:00 |
| 2005-10-31 22:02:00 |
| 2007-01-12 22:07:00 |
| 2007-03-14 21:04:00 |
| 2007-05-14 21:02:00 |
| 2007-07-13 21:03:00 |
| 2007-11-14 21:18:00 |
| 2008-01-14 22:20:00 |
| 2008-03-14 21:17:00 |
| 2008-05-14 21:17:00 |
| 2008-07-14 21:17:00 |
| 2008-11-14 21:17:00 |
| 2009-01-14 22:17:00 |
| 2009-03-13 21:17:00 |
| 2009-05-14 21:18:00 |
| 2009-07-14 21:23:00 |
| 2009-11-13 21:18:00 |
| 2010-01-14 21:22:00 |
| 2010-03-12 22:19:00 |
| 2010-05-14 21:18:00 |
| 2010-07-07 21:18:00 |
| 2010-07-07 21:18:00 |
| 2010-07-07 21:18:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
| 2010-07-05 21:53:00 |
+---------------------+
41 rows in set (0.00 sec)

mysql> select max(test) from date_test;
+---------------------+
| max(test)           |
+---------------------+
| 2010-07-05 21:53:00 |
+---------------------+
1 row in set (0.00 sec)

We are just testing alle our applications against 5.5, but with this bug all further testing is almost impossible.

This bug is classified "Non-critical". I think it is critical and should have higher priority as results reported by a SELECT are just wrong.

A duplicate of the bug#49771.