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

CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result

Details

    Description

      This query returns a correct result:

      select cast('0e111111111' AS DECIMAL(38,0)) AS a;


      +---+
      		 
      | a |
      		 
      +---+
      		 
      | 0 |
      		 
      +---+
      		 
      1 row in set (0.000 sec)

      Let's add one more digits to the exponent:

      select cast('0e1111111111' AS DECIMAL(38,0)) AS a;


      +----------------------------------------+
      		 
      | a                                      |
      		 
      +----------------------------------------+
      		 
      | 99999999999999999999999999999999999999 |
      		 
      +----------------------------------------+
      		 
      1 row in set, 3 warnings (0.001 sec)

      Opps. Looks wrong. 0 multiplied to 10^1111111111 should still be 0.

      show warnings;


      +---------+------+-------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                     |
      		 
      +---------+------+-------------------------------------------------------------+
      		 
      | Warning | 1916 | Got overflow when converting '' to DECIMAL. Value truncated |
      		 
      | Warning | 1292 | Truncated incorrect DECIMAL value: '0e1111111111'           |
      		 
      | Warning | 1264 | Out of range value for column 'a' at row 1                  |
      		 
      +---------+------+-------------------------------------------------------------+

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MCOL-4531 New string-to-decimal conversion implementation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            A similar problem happens with a non-zero mantissa:

            select cast('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;


            +---+
            		 
            | a |
            		 
            +---+
            		 
            | 0 |
            		 
            +---+
            		 
            1 row in set, 1 warning (0.000 sec)

            Looks wrong. The expected result is 99999999999999999999999999999999999999 - the maximum possible value for the target data type.

            bar Alexander Barkov added a comment - - edited A similar problem happens with a non-zero mantissa: select cast ( '.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL (38,0)) AS a; +---+ | a | +---+ | 0 | +---+ 1 row in set, 1 warning (0.000 sec) Looks wrong. The expected result is 99999999999999999999999999999999999999 - the maximum possible value for the target data type.
            bar Alexander Barkov added a comment -

            Note, DOUBLE works fine for the same input:

            select cast('0e1111111111' AS DOUBLE) AS a;


            +------+
            		 
            | a    |
            		 
            +------+
            		 
            |    0 |
            		 
            +------+


            select cast('.00000000000000000000000000000000000001e111111111111111111111' AS DOUBLE) AS a;


            +------------------------+
            		 
            | a                      |
            		 
            +------------------------+
            		 
            | 1.7976931348623157e308 |
            		 
            +------------------------+

            Notice, it returns 0 and the maximum possible DOUBLE value. Looks correct.

            bar Alexander Barkov added a comment - Note, DOUBLE works fine for the same input: select cast ( '0e1111111111' AS DOUBLE ) AS a; +------+ | a | +------+ | 0 | +------+ select cast ( '.00000000000000000000000000000000000001e111111111111111111111' AS DOUBLE ) AS a; +------------------------+ | a | +------------------------+ | 1.7976931348623157e308 | +------------------------+ Notice, it returns 0 and the maximum possible DOUBLE value. Looks correct.
            sanja Oleksandr Byelkin added a comment -

            OK to push in 10.2

            sanja Oleksandr Byelkin added a comment - OK to push in 10.2

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.