[MDEV-29041] Redundant truncation warning on CAST(string_column AS DECIMAL) - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2
Component/s: Data types
Labels:
None

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' |

+---------+------+----------------------------------------+

blocks

MDEV-21445 Strange/inconsistent behavior of IN condition when mixing numbers and strings

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.