Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL)
-
None
Description
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' |
|
+---------+------+----------------------------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-21445 Strange/inconsistent behavior of IN condition when mixing numbers and strings
- Closed