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:
None 
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 19:10] Nirbhay Choubey
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';
[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.