Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29041

Redundant truncation warning on CAST(string_column AS DECIMAL)

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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