Hi,

We have identified an issue in SQLite version 3.47.0.

Issue: Constant Doubling in REAL with Specific Prefixes

According to the official documentation, integers greater than 9223372036854775807 are treated as floating point integers (REAL, as an 8-byte IEEE floating point number). This suggests SQLite can handle numbers within the 8B IEEE float range. Here’s the relevant part of the documentation:

• https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes
• https://www.sqlite.org/lang_expr.html#literal_values_constants

However, we've observed that some integers that can be represented by REAL are incorrectly doubled.

Steps to Reproduce:

The issue can be reproduced with the following minimized query:

SELECT 18446744073709551488;

Expected Result:

1.84467440737096e+19

The constant 18446744073709551488 should be treated as a floating point number, possibly with some precision loss.

Actual result (SQLite, version 3.48.0, as of 2024-10-02 13:26:17):

3.68934881474191e+19

SQLite returns a doubled float representation, despite the number being representable with an 8B IEEE float. This is quite far from a precision loss.

Bug Pattern:

Upon further analysis, we found that integers satisfying the following conditions are doubled:

1. Exceeding 9223372036854775807, thus treated as REAL
2. Prefixed with 18446744073709550592 ~ 18446744073709551609

Here are additional test cases supporting this:

-- Erroneous range: 18446744073709550592 ~ 18446744073709551609
sqlite> SELECT 18446744073709550591;  -- 0xfffffffffffffbff
1.84467440737095e+19
sqlite> SELECT 18446744073709550592;  -- 0xfffffffffffffc00
3.68934881474191e+19
...
sqlite> SELECT 18446744073709551488;  -- 0xffffffffffffff80
3.68934881474191e+19
...
sqlite> SELECT 18446744073709551609; -- 0xfffffffffffffff9
3.68934881474191e+19
sqlite> SELECT 18446744073709551610; -- 0xfffffffffffffffa
1.84467440737096e+19

-- Another erroneous range: 184467440737095505920 ~ 184467440737095516099
sqlite> SELECT 184467440737095505919; -- 0x9ffffffffffffd7ff
1.84467440737096e+20
sqlite> SELECT 184467440737095505920; -- 0x9ffffffffffffd800
3.68934881474191e+20
...
sqlite> SELECT 184467440737095505990; -- 0x9ffffffffffffd846
3.68934881474191e+20
...
sqlite> SELECT 184467440737095516099; -- 0x9ffffffffffffffc3
3.68934881474191e+20
sqlite> SELECT 184467440737095516100; -- 0x9ffffffffffffffc4
1.84467440737096e+20

-- Another erroneous range: 184467440737095505920000000 ~ 184467440737095516099999999
sqlite> SELECT 184467440737095505920000000;
3.68934881474191e+26
sqlite> SELECT 184467440737095516099999999;
3.68934881474191e+26

Bug-Introducing Commit:

We identified the bug-introducing commit (with default options):

• https://sqlite.org/src/info/761d8fd18b0ee868 (2024-10-02 13:26:17)
• https://github.com/sqlite/sqlite/commit/92e71573ce9a2895c5948a2dc304c5be3cd3f986

The issue appeared only after this commit, which removed the LONG DOUBLE support.

Thank you for your time and attention to this matter. We look forward to your response.

Best regards.