Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
None
-
10.1.8-1, 10.1.8-2
Description
Implicit cast of a string with trailing spaces silently accepts the value in case of DECIMAL and INT:
SET sql_mode='STRICT_ALL_TABLES';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL);
|
INSERT INTO t1 VALUES ('1 ');
|
SET sql_mode='STRICT_ALL_TABLES';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES ('1 ');
|
A similar script with DOUBLE:
SET sql_mode='STRICT_ALL_TABLES';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES ('1 ');
|
returns an error:
ERROR 1265 (01000): Data truncated for column 'a' at row 1
|
Comparing to the behavior exposed by CAST (see MDEV-8466), it's exactly the opposite:
- CAST is stricter for DECIMAL/INT comparing to DOUBLE (on empty strings)
- INSERT is weaker for DECINAL/INT comparing to DOUBLE (on trailing spaces)
Explicit CAST also works differently:
SELECT CAST('1 ' AS DOUBLE), CAST('2 ' AS DECIMAL), CAST('3 ' AS INT);
|
SHOW WARNINGS;
|
returns
+----------------------+-----------------------+-------------------+
|
| CAST('1 ' AS DOUBLE) | CAST('2 ' AS DECIMAL) | CAST('3 ' AS INT) |
|
+----------------------+-----------------------+-------------------+
|
| 1 | 2 | 3 |
|
+----------------------+-----------------------+-------------------+
|
1 row in set, 1 warning (0.01 sec)
|
 |
+---------+------+-----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '3 ' |
|
+---------+------+-----------------------------------------+
|
1 row in set (0.00 sec)
|
Notice, DOUBLE and DECIMAL ignore the trailing spaces silently, while INT produces a warning.
Sergei and Bar during a discussion on maria-developers agreed that all data types should produce a NOTE (not a warning) for all numeric data types, in all query parts (INSERT,CAST,dynamic columns).