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

CAST(string_expr AS SIGNED) does not understand scientific notation

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.2(EOL)
    • OTHER
    • None

    Description

      Explicit cast from string to DOUBLE and DECIMAL understands scientific number notation.
      Explicit cast from string to SIGNED and UNSIGNED does not understand.

      SELECT CAST('1.9e19' AS DOUBLE) AS c1,
      		 
             CAST('1.9e19' AS DECIMAL(30,1)) AS c2,
      		 
             CAST('1.9e19' AS SIGNED) AS c3,
      		 
             CAST('1.9e19' AS UNSIGNED) AS c4;


      +--------+------------------------+----+----+
      		 
      | c1     | c2                     | c3 | c4 |
      		 
      +--------+------------------------+----+----+
      		 
      | 1.9e19 | 19000000000000000000.0 |  1 |  1 |
      		 
      +--------+------------------------+----+----+


      SELECT COLUMN_GET(COLUMN_CREATE(0, '1.9e19'), 0 AS DOUBLE) AS c1,
      		 
             COLUMN_GET(COLUMN_CREATE(0, '1.9e19'), 0 AS DECIMAL(30,1)) AS c2,
      		 
             COLUMN_GET(COLUMN_CREATE(0, '1.9e19'), 0 AS SIGNED) AS c3,
      		 
             COLUMN_GET(COLUMN_CREATE(0, '1.9e19'), 0 AS UNSIGNED) AS c4;


      +--------+------------------------+------+------+
      		 
      | c1     | c2                     | c3   | c4   |
      		 
      +--------+------------------------+------+------+
      		 
      | 1.9e19 | 19000000000000000000.0 |    1 |    1 |
      		 
      +--------+------------------------+------+------+

      Note, implicit cast in INSERT understands scientific notation:

      CREATE OR REPLACE TABLE t1 (a BIGINT);
      		 
      INSERT INTO t1 VALUES ('1.9e19');
      		 
      SELECT * FROM t1;


      +---------------------+
      		 
      | a                   |
      		 
      +---------------------+
      		 
      | 9223372036854775807 |
      		 
      +---------------------+

      Explicit CAST to SIGNED/UNSIGNED should be fixed to understand scientific notation.

      Attachments

        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.