MySQL Bugs: #56962: Inconsistent 'Out of range' warning message on select

Bug #56962	Inconsistent 'Out of range' warning message on select
Submitted:	23 Sep 2010 7:26	Modified:	3 Apr 2012 17:40
Reporter:	Nirbhay Choubey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.99-m5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql-next-mr, regression

Description:
SELECT statement throws warning when a where clause compares an 'indexed'
column's value with a value greater than Max allowable value.

However, the warning message in only for the indexed column (see below).

mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `t1`(`c1` INT, `c2` INT, INDEX idx2(`c1`));
Query OK, 0 rows affected (0.24 sec)

Performing a SELECT on empty table.

mysql> SELECT * FROM t1 WHERE c1=4294967296 AND c2=2147483648 /* no rows */;
Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

While, 5.1.50 produces no warning message.

IMO, the warning message should not come at all, as the statement is not altering/modifying any field with 'Out of range-ed' data. Even if it is expected (for some reason), it show include other non-indexed columns too. (as in the above case)

How to repeat:
DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1`(`c1` INT, `c2` INT, INDEX idx2(`c1`));

SELECT * FROM t1 WHERE c1=4294967296 AND c2=2147483648 /* no rows */;

SHOW WARNINGS;

related: bug #48769

Warnings does not come with MyISAM engine.

Verified as described:

macbook-pro:next-mr 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.6.99-m5-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> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE `t1`(`c1` INT, `c2` INT, INDEX idx2(`c1`));
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> SELECT * FROM t1 WHERE c1=4294967296 AND c2=2147483648 /* no rows */;
Empty set, 1 warning (0.00 sec)

mysql> 
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

Noted in 5.6.5 changelog.

For comparisons containing out-of-range constants, the optimizer
permitted warnings to leak through to the client, even though it
accounted for the range issue internally.