Details
Major
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).
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This fix changes lots of tests. Warning levels (Warning→Note), error numbers (1265→1366), some new warnings appear, some old warnings disappear. Let's do it in 10.1