Why does a negative offset enter the wrong unit bucket?

Prepare the report input

Original bucket k means k≤value<k+1. CAST to INTEGER truncates toward zero, so −1.2 becomes −1 instead of required −2. Subtract one from the truncated integer only when the original value is below it. Values here are small finite offsets.

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 offsets(id TEXT PRIMARY KEY,value REAL NOT NULL); INSERT INTO offsets VALUES('A',-1.2),('B',1.2),('C',-1.0);

Compare misleading and corrected results

SELECT id,CAST(value AS INTEGER) AS bucket FROM offsets ORDER BY id;

SELECT id,CAST(value AS INTEGER)-CASE WHEN value<CAST(value AS INTEGER) THEN 1 ELSE 0 END AS bucket FROM offsets ORDER BY id;

−1.2 belongs in [−2,−1), 1.2 in [1,2), and exact −1 stays in [−1,0). The correction preserves exact integer boundaries.

Check a fresh boundary case

Add zero; its bucket is zero rather than minus one.

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 offsets VALUES('D',0);
SELECT id,CAST(value AS INTEGER)-CASE WHEN value<CAST(value AS INTEGER) THEN 1 ELSE 0 END AS bucket FROM offsets ORDER BY id;