Why does forcing an integer counter to REAL lose one tick?

Prepare the report input

Original extreme fixture adds integer 9007199254740992 and one, well within signed 64-bit total range. Multiplying by 1.0 forces REAL before summing and loses the one. Keep the integer SUM; cast the finished value to text for this large-value transport.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE counters(id TEXT PRIMARY KEY,ticks INTEGER NOT NULL); INSERT INTO counters VALUES('A',9007199254740992),('B',1);

Compare misleading and corrected results

SELECT CAST(CAST(SUM(1.0*ticks) AS INTEGER) AS TEXT) AS total_ticks FROM counters;

SELECT CAST(SUM(ticks) AS TEXT) AS total_ticks FROM counters;

Bad text total ends in 992; correct total ends in 993. The input preview also casts ticks to text so a JavaScript number conversion cannot blur the displayed integer.

Check a fresh boundary case

Add integer −1. The corrected total returns to 9007199254740992.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO counters VALUES('C',-1);
SELECT CAST(SUM(ticks) AS TEXT) AS total_ticks FROM counters;