Why does a valid zero reading switch to its backup?

Prepare the report input

Original rule prefers a recorded primary value, including zero, and uses backup only when primary is NULL. NULLIF(primary_value,0) destroys that distinction before COALESCE.

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 readings(id TEXT PRIMARY KEY,primary_value INTEGER,backup_value INTEGER); INSERT INTO readings VALUES('A',0,8),('B',NULL,5),('C',3,9);

Compare misleading and corrected results

SELECT id,COALESCE(NULLIF(primary_value,0),backup_value) AS selected_value FROM readings ORDER BY id;

SELECT id,COALESCE(primary_value,backup_value) AS selected_value FROM readings ORDER BY id;

A should retain zero but the misleading query selects eight. B legitimately uses five because its primary is unknown; C keeps three.

Check a fresh boundary case

Add D with both inputs NULL. Its selected value remains NULL, not a fabricated zero.

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 readings VALUES('D',NULL,NULL);
SELECT id,COALESCE(primary_value,backup_value) AS selected_value FROM readings ORDER BY id;