| Bug #52283 | Innodb reports extra warnings when SELECT/WHERE is performed using invalid value | ||
|---|---|---|---|
| Submitted: | 22 Mar 2010 19:10 | Modified: | 23 Mar 2010 16:32 |
| Reporter: | Nirbhay Choubey | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.46 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql-5.1-bugteam, regression | ||
[22 Mar 2010 20:18]
Sveta Smirnova
Thank you for the report. Verified as described.
[26 Mar 2010 18:03]
Mikhail Izioumtchenko
the execution plan indicates an optimizer bug as 'impossible WHERE' implies there's no need to go to the SE. In fact I'd expect it to reproduce similarly with a MyISAM table, could you try. Optimistically categorizing away from InnoDB.
Description: Executing the following set of statements produce extra warnings for 5.1.46 (mysql-5.1-bugteam) as compared to that of 5.1.45. DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 DATETIME, INDEX idx(c1))ENGINE=INNODB; INSERT INTO t1 VALUES ('2010-01-01 14:12:33'); SELECT * FROM t1 WHERE c1='2001-01-11 23:59:60'; Empty set, 4 warnings (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2001-01-11 23:59:60' for column 'c1' at row 1 | | Warning | 1292 | Incorrect datetime value: '2001-01-11 23:59:60' for column 'c1' at row 1 | | Warning | 1292 | Incorrect datetime value: '2001-01-11 23:59:60' for column 'c1' at row 1 | | Warning | 1292 | Incorrect datetime value: '2001-01-11 23:59:60' for column 'c1' at row 1 | +---------+------+--------------------------------------------------------------------------+ 4 rows in set (0.00 sec) While for innodb/5.1.45 gives only 2 such warnings. Note: * Extra warnings are observed only for indexed-colunms. * Observed for both, innodb builtin & plugin How to repeat: Build MySQL from latest mysql-5.1-bugteam source and execute the following statements : DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 DATETIME, INDEX idx(c1))ENGINE=INNODB; INSERT INTO t1 VALUES ('2010-01-01 14:12:33'); SELECT * FROM t1 WHERE c1='2001-01-11 23:59:60';