View Ticket
Ticket Hash: afdc5a29dc169779af2cf6581ff1bc1fed715edd
Title: Lossless conversion when casting a large TEXT number to NUMERIC is not performed
Status: Closed Type: Documentation
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Wont_Fix
Last Modified: 2019-06-11 16:02:57
Version Found In:
User Comments:
mrigger added on 2019-06-11 14:06:08:

Consider the following statement:

SELECT CAST('8.2250617031974513E18' AS NUMERIC); -- expected: 8225061703197451300, unexpected: 8.22506170319745e+18

I would expect that the floating-point number is parsed/converted to an INTEGER value, since the corresponding value would fit into a 64 bit integer. Instead, the value is stored as an imprecise floating-point number that, when cast back, does not yield the same value:

SELECT CAST(CAST('8.2250617031974513E18' AS NUMERIC) AS INT); -- 8225061703197451264

When not using an E notation, the value is converted to an INTEGER as expected:

SELECT typeof(CAST('8225061703197451300' AS NUMERIC)); -- integer

The conversion also works as expected for small numbers:

SELECT CAST('8.225061703197E12' AS NUMERIC); -- 8225061703197

drh added on 2019-06-11 16:02:57:

The CAST to NUMERIC documentation has been clarified at https://www.sqlite.org/docsrc/info/7a51b32537ac7e95 in an attempt to show why this is a non-issue.