[MDEV-24790] CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result Created: 2021-02-05  Updated: 2021-06-10  Resolved: 2021-02-09

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.5.8, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10, 10.6.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4531 New string-to-decimal conversion impl... Closed

 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                  |
 
+---------+------+-------------------------------------------------------------+



 Comments   
Comment by Alexander Barkov [ 2021-02-05 ]

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.

Comment by Alexander Barkov [ 2021-02-05 ]

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.

Comment by Oleksandr Byelkin [ 2021-02-08 ]

OK to push in 10.2

Generated at Thu Feb 08 09:32:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.