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

Numeric CAST produce different warnings for strings literals vs functions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0, 10.1
    • 10.1.8
    • OTHER
    • None
    • 10.1.8-3

    Description

      Explicit CAST to DOUBLE:

      SELECT CAST('x0' AS DOUBLE), CAST(COALESCE('x1') AS DOUBLE), CAST(CONCAT('x2') AS DOUBLE);
      SHOW WARNINGS;

      returns

      +----------------------+--------------------------------+------------------------------+
      | CAST('x0' AS DOUBLE) | CAST(COALESCE('x1') AS DOUBLE) | CAST(CONCAT('x2') AS DOUBLE) |
      +----------------------+--------------------------------+------------------------------+
      |                    0 |                              0 |                            0 |
      +----------------------+--------------------------------+------------------------------+
      1 row in set, 1 warning (0.05 sec)
       
      +---------+------+----------------------------------------+
      | Level   | Code | Message                                |
      +---------+------+----------------------------------------+
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'x0' |
      +---------+------+----------------------------------------+
      1 row in set (0.00 sec)

      Notice, the string literal produced a warning, while the functions did not.
      The same thing happens in implicit CAST to DOUBLE:

      SELECT 0+'x0', 0+CONCAT('x1'), 0+COALESCE('x2');
      SHOW WARNINGS;

      Casting a prepare statement argument does not produce a warning:

      PREPARE stmt FROM 'SELECT CAST(? AS DOUBLE)';
      SET @tmp='0x';
      EXECUTE stmt USING @tmp;

      Explicit decimal CAST in the same scenario:

      SELECT CAST('x0' AS DECIMAL) AS x0, CAST(COALESCE('x1') AS DECIMAL) AS x1, CAST(CONCAT('x2') AS DECIMAL) AS x2;
      SHOW WARNINGS;

      returns

      +----+----+----+
      | x0 | x1 | x2 |
      +----+----+----+
      |  0 |  0 |  0 |
      +----+----+----+
      1 row in set, 3 warnings (0.00 sec)
       
      +---------+------+---------------------------------------------------------+
      | Level   | Code | Message                                                 |
      +---------+------+---------------------------------------------------------+
      | Warning | 1292 | Truncated incorrect DECIMAL value: 'x0'                 |
      | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
      | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
      +---------+------+---------------------------------------------------------+
      3 rows in set (0.00 sec)

      Notice, different warnings. The warning for the string literal looks better.

      Implicit CAST to INT works even differently:

      SELECT LEFT('a','x0') AS x0, LEFT('a',CONCAT('x1')) AS x1, LEFT('a',COALESCE('x2'));
      SHOW WARNINGS;

      +----+----+--------------------------+
      | x0 | x1 | LEFT('a',COALESCE('x2')) |
      +----+----+--------------------------+
      |    |    |                          |
      +----+----+--------------------------+
      1 row in set, 2 warnings (0.00 sec)
       
      +---------+------+-----------------------------------------+
      | Level   | Code | Message                                 |
      +---------+------+-----------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: 'x0' |
      | Warning | 1292 | Truncated incorrect INTEGER value: 'x0' |
      +---------+------+-----------------------------------------+
      2 rows in set (0.00 sec)

      Notice, the string literal produced two warnings, while the functions produced no warnings.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              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.