Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
According to the SQL standard,
- if CAST from numeric data types to [VAR]CHAR truncates data, an error is issue.
- however, when CAST from [VAR]CHAR to [VAR]CHAR truncates the data, a warning (non an error) is issued !
11) If TD is fixed-length character string, then let LTD be the length in characters of TD.
a) If SD is exact numeric, then:
2) If the length in characters LY of Y is equal to LTD, then TV is Y.
3) If the length in characters LY of Y is less than LTD, then TV is Y extended on the right by
LTD–LY <space>s.
4) Otherwise, an exception condition is raised: data exception — string data, right truncation
d) If SD is fixed-length character string, variable-length character string, or large object character string, then
ii) If the length in characters of SV is larger than LTD, then TV is the first LTD characters of SV.
If any of the remaining characters of SV are non-<space> characters, then a completion condition
is raised: warning — string data, right truncation.
12) If TD is variable-length character string or large object character string, then let MLTD be the maximum length in characters of TD.
a) If SD is exact numeric, then:
2) If the length in characters LY of Y is less than or equal to MLTD, then TV is Y.
3) Otherwise, an exception condition is raised: data exception — string data, right truncation.d) If SD is fixed-length character string, variable-length character string, or large object character string, then
ii) If the length in characters of SV is larger than MLTD, then TV is the first MLTD characters of
SV. If any of the remaining characters of SV are non-<space> characters, then a completion
condition is raised: warning — string data, right truncation.
In sql_mode=STRICT_ALL_TABLES this is not always true.
This script correctly returns a warning as expected:
SET sql_mode=STRICT_ALL_TABLES; |
SELECT CAST('xxx' AS CHAR(1)); |
However, this script rejects the INSERT and returns an error instead of the warning:
SET sql_mode=STRICT_ALL_TABLES; |
CREATE OR REPLACE TABLE t1 (a VARCHAR(1)); |
INSERT INTO t1 VALUES (CAST('abc' AS CHAR(1))); |
ERROR 1292 (22007): Truncated incorrect CHAR(1) value: 'abc'
|
The same error is returned on UPDATE:
SET sql_mode=STRICT_ALL_TABLES; |
CREATE OR REPLACE TABLE t1 (a VARCHAR(3)); |
INSERT INTO t1 VALUES ('xxx'); |
UPDATE t1 SET a=CAST(a AS CHAR(1)); |
This script also rejects the assignment and returns the same error:
SET sql_mode=STRICT_ALL_TABLES; |
DELIMITER $$
|
BEGIN NOT ATOMIC |
DECLARE a VARCHAR(30); |
SET a=CAST('xxx' AS CHAR(1)); |
END; |
$$
|
DELIMITER ;
|
ERROR 1292 (22007): Truncated incorrect CHAR(1) value: 'xxx'
|
The problem happens because in sql_mode=STRICT_ALL_TABLES the server automatically escalates warnings to errors on INSERT and UPDATE, as well as on assignments to SP variables.
There should not be errors on INSERT, UPDATE, SP assignments in the above examples.
The explicit CAST from [VAR]CHAR to [VAR]CHAR should only issue warnings on truncation (without escalation to errors) and then send good data further to the destination (e.g. INSERT, UPDATE, assignments).
Note, escalating from warnings to errors for CAST from numeric or temporal to [VAR]CHAR is OK.