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

CAST from double to UNSIGNED does not work well

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.4(EOL)
    • Data types
    • None

    Description

      This script:

      SELECT CAST(10000000000000000000e0 AS UNSIGNED) AS a,
             CAST(10000000000000000000.0 AS UNSIGNED) AS b,
             CAST(10000000000000000000   AS UNSIGNED) AS c;
      

      returns a wrong result, without warnings:

      +---------------------+----------------------+----------------------+
      | a                   | b                    | c                    |
      +---------------------+----------------------+----------------------+
      | 9223372036854775807 | 10000000000000000000 | 10000000000000000000 |
      +---------------------+----------------------+----------------------+
      

      Notice, the value for a is wrong. It truncated the value to the maximum positive signed value.
      The expected result would be to return 10000000000000000000 for all columns.

      If I rewrite the script slightly, it still returns the same wrong result for a, but now with a warning:

      SELECT CAST(CAST(10000000000000000000 AS DOUBLE) AS UNSIGNED) AS a,
             CAST(CAST(10000000000000000000 AS DECIMAL(30,0)) AS UNSIGNED) AS b,
             CAST(CAST(10000000000000000000 AS UNSIGNED) AS UNSIGNED) AS c;
      SHOW WARNINGS;
      

      +--------------------------------------------------------+
      | cast(cast(10000000000000000000 as double) as unsigned) |
      +--------------------------------------------------------+
      |                                    9223372036854775808 |
      +--------------------------------------------------------+
      

      +-------+------+-------------------------------------------------------------------------+
      | Level | Code | Message                                                                 |
      +-------+------+-------------------------------------------------------------------------+
      | Note  | 1105 | Cast to unsigned converted negative integer to it's positive complement |
      +-------+------+-------------------------------------------------------------------------+
      

      If I rewrite the script using table columns, it also returns the same wrong result, with a warning:

      CREATE OR REPLACE TABLE t1 (a DOUBLE, b DECIMAL(30,0), c BIGINT UNSIGNED);
      INSERT INTO t1 VALUES (10000000000000000000,10000000000000000000,10000000000000000000);
      SELECT CAST(a AS UNSIGNED) AS a,
             CAST(b AS UNSIGNED) AS b,
             CAST(c AS UNSIGNED) AS c
        FROM t1;
      SHOW WARNINGS;
      

      +---------------------+----------------------+----------------------+
      | a                   | b                    | c                    |
      +---------------------+----------------------+----------------------+
      | 9223372036854775807 | 10000000000000000000 | 10000000000000000000 |
      +---------------------+----------------------+----------------------+
      

      +---------+------+-------------------------------------------+
      | Level   | Code | Message                                   |
      +---------+------+-------------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: '1e19' |
      +---------+------+-------------------------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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