Bug #61100 Incorrecte value from Select Max(date) with year smaller 100.
Submitted: 9 May 2011 12:48 Modified: 12 Apr 2018 14:20
Reporter: sqlguy me Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.8, 5.5.13 OS:Any (XP, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: regression, select max year 100 99

[9 May 2011 12:48] sqlguy me
Description:

Select max for a datetime value smaller than "0100-01-01 00:00:00" the result is wrong!

Executing the query

SELECT
    max(Testdate)
FROM
    testdata;

on table

"ID";"Testdate"
"1";"0099-01-01 00:00:00"

results to wrong value
"1999-01-01 00:00:00"

------------------------
If table contains:

"ID";"Testdate"
"1";"0100-01-01 00:00:00"

the result is correct:
"0100-01-01 00:00:00"

---------------------
Directly executing of query 

SELECT
    max('0099-01-01 00:00:00')
FROM
    testdata 

results correctly in 
"0099-01-01 00:00:00"

How to repeat:

Testtable:

CREATE TABLE IF NOT EXISTS `testdata` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Testdate` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Testdata:

INSERT INTO `testdata` (`ID`, `Testdate`) VALUES (1, '0099-01-01 00:00:00');

Testquery:

SELECT
    max(Testdate)
FROM
    testdata 

Suggested fix:

Only use datetime with year >= 100 ?
[9 May 2011 18:48] Valeriy Kravchuk
Thank you for the bug report. Indeed, this still happens in current mysql-5.5:

macbook-pro:5.5 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 9
Server version: 5.5.13-debug Source distribution

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 IF NOT EXISTS `testdata` (
    ->   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `Testdate` datetime NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO `testdata` (`ID`, `Testdate`) VALUES (1, '0099-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->     max(Testdate)
    -> FROM
    ->     testdata 
    -> 
    -> ;
+---------------------+
| max(Testdate)       |
+---------------------+
| 1999-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from testdata;
+----+---------------------+
| ID | Testdate            |
+----+---------------------+
|  1 | 0099-01-01 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> alter table testdata engine=MyISAM;
Query OK, 1 row affected (0.13 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT     max(Testdate) FROM     testdata;
+---------------------+
| max(Testdate)       |
+---------------------+
| 1999-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT   Testdate,  max(Testdate) FROM     testdata;
+---------------------+---------------------+
| Testdate            | max(Testdate)       |
+---------------------+---------------------+
| 0099-01-01 00:00:00 | 1999-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> explain SELECT     max(Testdate) FROM     testdata;
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testdata | system | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> exit

It does NOT happen with MySQL 5.1:

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.57-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> CREATE TABLE IF NOT EXISTS `testdata` (   `ID` bigint(20) NOT NULL AUTO_INCREMENT,   `Testdate` datetime NOT NULL,   PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO `testdata` (`ID`, `Testdate`) VALUES (1, '0099-01-01 00:00:00');Query OK, 1 row affected (0.00 sec)

mysql> SELECT     max(Testdate) FROM     testdata;
+---------------------+
| max(Testdate)       |
+---------------------+
| 0099-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

So, we have a regression here.
[29 Dec 2011 9:33] Arturo Buonanni
I can confirm that the bug is still present in versions 5.5.14 and 5.5.19
[27 Dec 2012 17:47] Sveta Smirnova
This bug is not repeatable with current development version 5.7.1
[12 Apr 2018 14:20] Paul DuBois
Posted by developer:
 
Fixed in 5.6.40.

MAX() for DATE values smaller than year 100 produced incorrect
results.