| Bug #53432 | SELECT MAX QUERY WITH DATE FIELD GIVES WRONG RESULTS | ||
|---|---|---|---|
| Submitted: | 5 May 2010 11:41 | Modified: | 25 May 2010 9:59 |
| Reporter: | AJIT DIXIT | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.5.5 , 5.5.4 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | regression, SELECT MAX WRONG RESULT | ||
[5 May 2010 13:42]
Valeriy Kravchuk
Verified just as described. This is what I get with 5.1.47: 77-52-4-109: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 15 Server version: 5.1.47-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> SELECT max(dt_mfg) as dt_mfg from batchtest ; +------------+ | dt_mfg | +------------+ | 2003-06-01 | +------------+ 1 row in set (0.00 sec) Now, with current mysql-trunk tree: 77-52-4-109:trunk openxs$ bin/mysql -uroot test < ../5.1/bug53432.sql 77-52-4-109: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.5-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> SELECT max(dt_mfg) as dt_mfg from batchtest ; +------------+ | dt_mfg | +------------+ | 2002-02-01 | +------------+ 1 row in set (0.00 sec) mysql> explain SELECT max(dt_mfg) as dt_mfg from batchtest ; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | batchtest | ALL | NULL | NULL | NULL | NULL | 100 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> explain extended SELECT max(dt_mfg) as dt_mfg from batchtest ; +----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | batchtest | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | | +----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select max(`test`.`batchtest`.`dt_mfg`) AS `dt_mfg` from `test`.`batchtest` 1 row in set (0.00 sec) This result is obviously wrong.
[17 May 2010 13:48]
Giuseppe Maxia
tested in mysql-trunk (rev. 3143). The bug is still repeatable.
[20 May 2010 14:04]
Guilhem Bichot
This wrong result appeared in alik@sun.com-20091212203859-fx4rx5uab47wwuzd which is a merge revision. Each of the two parents doesn't exhibit the bug (!?!).
[25 May 2010 7:45]
Alexander Barkov
The failure is repeatable with a smaller data set:
DROP TABLE IF EXISTS `batchtest`;
CREATE TABLE IF NOT EXISTS `batchtest` (
`item` char(6) NOT NULL DEFAULT '',
`batchtest` char(15) NOT NULL DEFAULT '',
`dt_mfg` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`item`,`batchtest`),
KEY `batchtest` (`batchtest`),
KEY `item` (`item`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Corp Prod batchtest Master';
INSERT INTO `batchtest` VALUES('110001', 'NS266TS15', '2002-10-01');
INSERT INTO `batchtest` VALUES('110001', 'SN0301110', '2003-06-01');
INSERT INTO `batchtest` VALUES('110002', 'NS-285TS17', '2002-11-01');
SELECT max(dt_mfg) FROM batchtest;
-> 2002-11-01
[25 May 2010 9:59]
Evgeny Potemkin
This is a duplicate of the bug#49771.
Description: I have problem with SELECT max(date) as under Data DROP TABLE IF EXISTS `batchtest`; CREATE TABLE IF NOT EXISTS `batchtest` ( `item` char(6) NOT NULL DEFAULT '', `batchtest` char(15) NOT NULL DEFAULT '', `dt_mfg` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`item`,`batchtest`), KEY `batchtest` (`batchtest`), KEY `item` (`item`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Corp Prod batchtest Master'; -- -- Dumping data for table `batchtest` -- INSERT INTO `batchtest` VALUES('110001', 'NR1153T73', '2001-08-01'); INSERT INTO `batchtest` VALUES('110001', 'NS-161TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS-164TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS-168TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110001', 'NS-169TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110001', 'NS136TS03', '2001-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS137TS03', '2001-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS150TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS151TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS156TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS161TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS162TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS163TS02', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS163TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS164TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS165TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110001', 'NS179TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110001', 'NS180TS06', '2002-04-01'); INSERT INTO `batchtest` VALUES('110001', 'NS180TS07', '2002-04-01'); INSERT INTO `batchtest` VALUES('110001', 'NS185TS07', '2002-04-01'); INSERT INTO `batchtest` VALUES('110001', 'NS195TS08', '2002-05-01'); INSERT INTO `batchtest` VALUES('110001', 'NS196TS08', '2002-05-01'); INSERT INTO `batchtest` VALUES('110001', 'NS197TS08', '2002-05-01'); INSERT INTO `batchtest` VALUES('110001', 'NS198TS08', '2002-05-01'); INSERT INTO `batchtest` VALUES('110001', 'NS208TS09', '2002-05-01'); INSERT INTO `batchtest` VALUES('110001', 'NS209TS09', '2002-06-01'); INSERT INTO `batchtest` VALUES('110001', 'NS216TS10', '2002-06-01'); INSERT INTO `batchtest` VALUES('110001', 'NS223TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS224TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS225TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS226TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS227TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS228TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS229TS11', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS237TS12', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS238TS12', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS239TS12', '2002-07-01'); INSERT INTO `batchtest` VALUES('110001', 'NS240TS13', '2002-09-01'); INSERT INTO `batchtest` VALUES('110001', 'NS241TS13', '2002-09-01'); INSERT INTO `batchtest` VALUES('110001', 'NS245TS13', '2002-09-01'); INSERT INTO `batchtest` VALUES('110001', 'NS246TS13', '2002-09-01'); INSERT INTO `batchtest` VALUES('110001', 'NS251TS14', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS252TS14', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS253TS14', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS262TS15', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS263TS15', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS264TS15', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS265TS15', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS266TS15', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'NS276TS16', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS277TS16', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS278TS16', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS281TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS282TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS283TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS288TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110001', 'NS289TS18', '2002-12-01'); INSERT INTO `batchtest` VALUES('110001', 'NS290TS18', '2002-12-01'); INSERT INTO `batchtest` VALUES('110001', 'NS291TS18', '2002-12-01'); INSERT INTO `batchtest` VALUES('110001', 'NS292TS18', '2002-12-01'); INSERT INTO `batchtest` VALUES('110001', 'NS293TS18', '2002-12-01'); INSERT INTO `batchtest` VALUES('110001', 'NS352TS14', '2002-10-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301103S', '2003-04-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301104S', '2003-04-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301105S', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301106S', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301107S', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301108', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301108S', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301109', '2003-05-01'); INSERT INTO `batchtest` VALUES('110001', 'SN0301110', '2003-06-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-157TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-158TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-159TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-160TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-166TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-170TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-284TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS-285TS17', '2002-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS125TS03', '2001-10-01'); INSERT INTO `batchtest` VALUES('110002', 'NS140TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS141TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS144TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS145TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS146TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS147TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS149TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS152TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS153TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS154TS04', '2001-11-01'); INSERT INTO `batchtest` VALUES('110002', 'NS155TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS157TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS160TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS166TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS167TS05', '2002-01-01'); INSERT INTO `batchtest` VALUES('110002', 'NS171TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110002', 'NS172TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110002', 'NS173TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110002', 'NS174TS06', '2002-02-01'); INSERT INTO `batchtest` VALUES('110002', 'NS175TS06', '2002-02-01'); The result of SELECT max(dt_mfg) as dt_mfg from batchtest ; Gives wrong result How to repeat: Make table batchtest with above test data and test Suggested fix: The query should be correct. My entire system had crashed due to this bug/behavior