MySQL Bugs: #11546: Bad error message from inserting out of range values, SQL

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

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.

Thank you for the bug report.

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

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

fixed in 5.0.11

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>

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.

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)

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