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

CAST from double to UNSIGNED does not work well

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.3, 10.4
    • Component/s: Data types
    • Labels:
      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

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

                Dates

                • Created:
                  Updated: