AI counted a SQL column. Did it count every record, or only non-NULL values?

COUNT(*) counts rows; COUNT(value) excludes NULL in SQLite. An empty string and zero are different cases and still count as non-NULL.

Before asking AI to count “filled” records, define that word. A missing database value, an empty text string and a recorded zero do not automatically have the same meaning.

Our four-record test

In an in-memory SQLite table, A held the text done, B held NULL, C held an empty string and D held numeric zero. COUNT(*) returned 4; COUNT(value) returned 3. The latter counted both the empty string and zero, so it did not answer “three useful responses”.

Inspect the cases separately

Our SELECT count with WHERE value IS NULL returned 1, and a separate empty-string comparison returned 1. WHERE value = NULL returned 0; it is not the NULL test we intended. These queries were executed in SQLite 3.53.1, not a production database.

Give AI a counting definition

Suggested input: “Count all records, NULL values, empty text and other values separately. Explain what the report should treat as missing before combining categories. Preserve legitimate zeros.” Check the generated query against a small known fixture.

A value can exist without being valid

Non-NULL counts say nothing about whether the value is accurate, permitted or complete for your task. Spaces, sentinel codes and invalid entries may need separate rules. Do not turn missing values into zero merely to make a total look complete.

Published October 14, 2026. Only invented rows and SELECT results were tested in Python’s existing in-memory SQLite. No real data, other SQL engine, business reporting rule or AI coding service was tested.

Sources: SQLite · COUNT · SQLite · NULL expressions.