SQLite

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

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.