Why does max(a,b) hide an available reading when the other is NULL?

Prepare the report input

Original report asks for the highest recorded value among two optional inputs, not an asserted peak of unseen data. SQLite multi-argument max returns NULL when any argument is NULL. Handle the missing-input branches explicitly; both missing remains NULL.

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,a INTEGER,b INTEGER); INSERT INTO readings VALUES('A',5,NULL),('B',2,8),('C',NULL,NULL);

Compare misleading and corrected results

SELECT id,max(a,b) AS known_max FROM readings ORDER BY id;

SELECT id,CASE WHEN a IS NULL THEN b WHEN b IS NULL THEN a ELSE max(a,b) END AS known_max FROM readings ORDER BY id;

A has known five and no b, so should show five. B shows eight; C remains unknown. Do not replace missing inputs by zero because valid recorded values may be negative.

Check a fresh boundary case

Change A’s only known reading to −2. It remains −2 rather than zero or NULL.

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.

UPDATE readings SET a=-2 WHERE id='A';
SELECT id,CASE WHEN a IS NULL THEN b WHEN b IS NULL THEN a ELSE max(a,b) END AS known_max FROM readings ORDER BY id;