Bug #80775 "Out of range value for column XXX" in DML is missleading
Submitted: 17 Mar 2016 9:37 Modified: 23 Mar 2016 8:49
Reporter: Jan Kahoun Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.24-72.2-log, 5.6.29, 5.7.11 OS:Ubuntu (14.04.4)
Assigned to: CPU Architecture:Any

[17 Mar 2016 9:37] Jan Kahoun
Description:
When you call stored routine and there is any problem (type checking, overflow...) with its parameters or variables you get missleading message like "Out of range value for column 'id' at row 1" The main problem is with the word 'column' in message, because in reality it's not 'column' but 'parameter'. Imagine that you run UPDATE, which invokes trigger, which call procedure, which do INSERT that invokes another trigger... It can not be so easy find what is wrong.

How to repeat:
SET SESSION sql_mode = 'strict_all_tables';

DROP TABLE IF EXISTS `TestTable`;

CREATE TABLE `TestTable` (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	`testTableId` int unsigned NOT NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
) ENGINE=InnoDB;

DELIMITER $$

DROP PROCEDURE IF EXISTS `InsertId`$$

CREATE
   
    PROCEDURE `InsertId`(IN id INT)
    BEGIN
	INSERT INTO TestTable SET testTableId = id;		
    END$$

DELIMITER ;

/*
Error Code: 1264
Out of range value for column 'id' at row 1
*/
CALL InsertId(9223372036854775807);

Suggested fix:
Change message. Add name of a stored routine and use word 'parameter' instead of 'column'. For example:

Out of range value for parameter 'id' in stored routine InsertId
[23 Mar 2016 8:49] Umesh Shastry
Hello Jan Kahoun,

Thank you for the report and test case.

Thanks,
Umesh