Bug #11546 Bad error message from inserting out of range values, SQL_MODE='traditional'
Submitted: 24 Jun 2005 11:22 Modified: 20 Mar 2006 8:59
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.x, 5.1.x OS:Linux (linux/win32 (assuming all))
Assigned to: Ramil Kalimullin CPU Architecture:Any

[24 Jun 2005 11:22] Jon Stephens
Description:
See subject.

This problem appears to be limited to numeric types and BIT, but I did not exhaustively test all MySQL column types. However, CHAR, VARCHAR, and TINYTEXT worked correctly in this regard when I tested on the versions/platforms indicated.

Behaviour observed with binary releases 5.0.7-max/win32 (Windows 2000 Server) and 5.0.7-standard/linux (SuSE 9.2/kernel 2.6.8-24.11-default).

For background info, see http://bugs.mysql.com/bug.php?id=10539

How to repeat:
mysql> USE test;
Database changed
mysql> SET @@SQL_MODE='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c TINYINT);
Query OK, 0 rows affected (0.04 sec)

#  wrong error message:

mysql> INSERT INTO t1 VALUES (1000);
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1
mysql> SELECT * FROM t1;
Empty set (0.01 sec)

mysql> CREATE TABLE t2 (c CHAR(5));
Query OK, 0 rows affected (0.07 sec)

#  correct error message:

mysql> INSERT INTO t2 VALUES ('aaaaaa');
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> SELECT * FROM t2;
Empty set (0.00 sec) 

#  wrong error message:

mysql> CREATE TABLE t3 (c BIT(8));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t3 VALUES (b'1111111100000000');
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1
mysql> SELECT BIN(c)+0 FROM t3;
Empty set (0.00 sec) 

mysql> CREATE TABLE t4 (c INT);
Query OK, 0 rows affected (0.04 sec)

#  wrong error message:

mysql> INSERT INTO t4 VALUES (1000000000000);
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1

mysql> SELECT * FROM t4;
Empty set (0.00 sec) 

mysql> CREATE TABLE t5 (c DECIMAL(5,2));
Query OK, 0 rows affected (0.07 sec)

# wrong error message - unless this is correct behaviour for strict SQL mode?

mysql> INSERT INTO t5 VALUES (123.456);
Query OK, 1 row affected, 1 warning (0.01 sec)

#  wrong error message, correct behaviour

mysql> INSERT INTO t5 VALUES (1234.56);
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1
mysql> SELECT * FROM t5;
+--------+
| c      |
+--------+
| 123.46 |
+--------+
1 row in set (0.00 sec)

Suggested fix:
When attempting to insert out of range values, the values should not be inserted; the action should raise:

ERROR 1406 (22001): Data too long for column '[name]' at row [#]

and NOT an error 1264 or warning.
[24 Jun 2005 14:09] Miguel Solorzano
Thank you for the bug report.
[1 Jul 2005 9:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26579
[26 Jul 2005 7:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27582
[26 Jul 2005 7:26] Ramil Kalimullin
fixed in 5.0.11
[3 Aug 2005 21:20] Mike Hillyer
Documented in 5.0.11 changelog: 

<listitem><para>Corrected inaccurate error message when inserting out of range data under <literal>TRADITIONAL</literal> SQL mode. (Bug #11546)</para></listitem>
[10 Oct 2005 16:37] Jon Stephens
I am re-opening, as an incorrect error message is still being displayed:

mysql> CREATE TABLE c1 (col INT);
Query OK, 0 rows affected (0.01 sec)

#  this is correct:
mysql> INSERT INTO c1 VALUES (9999999999);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'col' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

#  change mode:
mysql> SET @@SQL_MODE='traditional';
Query OK, 0 rows affected (0.00 sec)

#  bad error message:
mysql> INSERT INTO c1 VALUES (9999999999);
ERROR 1264 (22003): Out of range value adjusted for column 'col' at row 1

-----

In strict mode, we should NOT say that the out range value was "adjusted", as this implies that it was still accepted. The error message should make it clear that the value was NOT inserted into the table - in other words that the value was *rejected*.

Observed in 5.0.13-rc-Max on CentOS 4.1, 5.0.11-max-nt on Win2K Server SP 4, and 5.1.2-alpha (pulled 2005-10-08 from BitKeeper) on SuSE 9.3 and SuSE 10.0.
[10 Oct 2005 16:55] Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@SQL_MODE='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c TINYINT);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES (1000);
ERROR 1264 (22003): Out of range value adjusted for column 'c' at row 1
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
[20 Oct 2005 13:57] Russell Turner
I am using  5.0.13-rc-nt on Windows 2000 and am getting the "ERROR 1264: Out of range value adjusted for column ..." error while using LOAD DATA INFILE. 
To reproduce: copy a file to load into your data\db directory.
Countries.txt
-------------------
"CountryId","Population","Title"
1,,"Australia"
--------------------

use test;

--drop table countries;

CREATE TABLE `countries` (
  `CountryId` smallint(6) NOT NULL auto_increment,
  `Population` bigint(20) default NULL,
  `Title` varchar(50) default NULL,
  PRIMARY KEY  (`CountryId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--Returns
--Query OK, 0 rows affected (0.05) sec

LOAD DATA INFILE 'Countries.txt' INTO TABLE Countries
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

--Returns
--ERROR 1264: Out of range value adjusted for column 'Population' at row 1