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: | |
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
[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.