[#MDEV-8468] CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

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).

[MDEV-8468] CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces Created: 2015-07-15 Updated: 2015-09-23 Resolved: 2015-09-17
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	5.5, 10.0, 10.1
Fix Version/s:	10.1.8

[MDEV-8468] CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces Created: 2015-07-15 Updated: 2015-09-23 Resolved: 2015-09-17