MySQL Bugs: #5912: Traditional mode: CAST allows invalid value

Bug #5912	Traditional mode: CAST allows invalid value
Submitted:	5 Oct 2004 23:23	Modified:	9 Jun 2014 16:59
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:		CPU Architecture:	Any

Description:
When sql_mode='traditional', data that must be truncated 
to fit into a column must be rejected, rather than truncated 
and accepted. But a CAST of a number that is too large 
for a given string column is being truncated and accepted. 
That is, if a column is defined as CHAR(3) and an attempt 
is made to INSERT the number 1000, using an explicit CAST 
to a CHAR(3) value, the action should be rejected because 
the number would have to be truncated to 100 to fit in the 
column, thus losing significant digits. 
 
A second problem: MySQL is returning the incorrect 
SQLSTATE when the same situation happens with an 
implicit cast. That is, if I attempt to insert the number  
1000 into a CHAR(3) column, the server correctly 
rejects the operation. But it does so with an SQLSTATE 
of 01000, rather than 22001 string data right truncation. 
 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 char(3)) engine=innodb; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t1 values(1000); 
ERROR 1265 (01000): Data truncated for column 'col1' at row 1 
-- This is the correct result, but the SQLSTATE should be 22001 string data right truncation  
 
mysql> select * from t1; 
Empty set (0.00 sec) 
 
mysql> insert into t1 values(cast(1000 as char(4))); 
ERROR 1265 (01000): Data truncated for column 'col1' at row 1 
-- Again, correct action, but incorrect SQLSTATE 
 
mysql> select * from t1; 
Empty set (0.00 sec) 
 
mysql> insert into t1 values(cast(1000 as char(3))); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect result. The INSERT should be rejected with 
SQLSTATE 22001 string data right truncation  
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
| 100  | 
+------+ 
1 row in set (0.00 sec)

Verified against latest BK source tree.

Trailing characters are also being ignored when a string is casted to a number:
 mysql> set @@sql_mode='traditional';
 Query OK, 0 rows affected (0.00 sec)
 mysql> select cast('12345abc' as signed integer);
 +------------------------------------+
 | cast('12345abc' as signed integer) |
 +------------------------------------+
 |                              12345 |
 +------------------------------------+
 1 row in set (0.00 sec)
 mysql> show warnings;
 Empty set (0.00 sec)

Here's another example, run with 5.0.3-alpha debug: 
 
mysql> set sql_mode='traditional'; 
 
mysql> select cast('a' as unsigned integer); 
+-------------------------------+ 
| cast('a' as unsigned integer) | 
+-------------------------------+ 
|                             0 | 
+-------------------------------+ 
-- This is the incorrect result. The SELECT should be rejected with 
SQLSTATE 22018 invalid character value for cast 
 
mysql>

This thing is now partly solved:

- CAST now generates error when casting wrong constant strings to integer or
  double
- CAST now generates error when casting an integer or double to a string
  when the string is too short

We still get a wrong SQLSTATE for some cases, but that can't be easily
solved so it has to be fixed later.  The reason is that we reuse the same
error text & error number for all truncation messages and as the SQLSTATE
is connected to the error number, we can't easily have different SQLSTATE's
to the same error.
To fix this we need to do the following changes:

- Add aliases to sql/share/errmsg.txt so that one error message can share
  all error strings from another error message but have unique SQLSTATE.
  This can be fixed by just changing the program that generates the error.sys
  files.

This bug was due to faulty arithmetic that was done in 5.0.
It has been fixed and are not reproducible in 5.1+ versions.
No changelog entry because it is unknown in which version the issue was corrected.