|
CREATE OR REPLACE TABLE t1 (a CHAR(10));
|
INSERT INTO t1 VALUES ('x');
|
SELECT CAST(a AS DECIMAL(10,2)) FROM t1;
|
SHOW WARNINGS;
|
+---------+------+---------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------------+
|
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
|
| Warning | 1292 | Truncated incorrect DECIMAL value: 'x ' |
|
+---------+------+---------------------------------------------------------+
|
Notice two warnings. Just one truncation wanrning should be enough, the second one.
The same problem is repeatable with VARCHAR:
CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
|
INSERT INTO t1 VALUES ('x');
|
SELECT CAST(a AS DECIMAL(10,2)) FROM t1;
|
SHOW WARNINGS;
|
+---------+------+---------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------------+
|
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
|
| Warning | 1292 | Truncated incorrect DECIMAL value: 'x' |
|
+---------+------+---------------------------------------------------------+
|
The same problem is repeatable with TEXT:
CREATE OR REPLACE TABLE t1 (a TEXT);
|
INSERT INTO t1 VALUES ('x');
|
SELECT CAST(a AS DECIMAL(10,2)) FROM t1;
|
SHOW WARNINGS;
|
+---------+------+---------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------------+
|
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
|
| Warning | 1292 | Truncated incorrect DECIMAL value: 'x' |
|
+---------+------+---------------------------------------------------------+
|
CAST to other numeric data types works fine
CAST(AS DOUBLE) correctly returns one warning:
CREATE OR REPLACE TABLE t1 (a CHAR(10));
|
INSERT INTO t1 VALUES ('x');
|
SELECT CAST(a AS DOUBLE) FROM t1;
|
SHOW WARNINGS;
|
+---------+------+------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x ' |
|
+---------+------+------------------------------------------------+
|
CAST(AS INT) also correctly returns one warning:
CREATE OR REPLACE TABLE t1 (a CHAR(10));
|
INSERT INTO t1 VALUES ('x');
|
SELECT CAST(a AS INT) FROM t1;
|
SHOW WARNINGS;
|
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: 'x' |
|
+---------+------+----------------------------------------+
|
|