SQLite query checks should compare literals with bound values

A query optimization is useful only if it preserves the answer. SQLite's July 2025 correction shows why a regression check should compare literal and parameterized forms, including the number of result rows. It also shows why the expected answer needs scrutiny: in the original report, the result the reporter distrusted was the one the maintainer identified as correct.

Looking back from November 2026, the historical fix is in SQLite 3.50.3, released on 17 July 2025. The release note names an incorrect answer caused by over-optimization of an AND operator and links to the original forum report. It is a specific correctness correction, not evidence that every patch release should be treated as suspect.

Follow the report through the correction

On 1 July 2025, ChiZhang reported a difference in SQLite 3.50.2. The example used an empty table and a query combining MAX with AND, with zero supplied as a bound parameter. The reporter expected no result rows and saw a row containing zero. A corresponding literal-zero query produced the expected empty result.

Richard Hipp simplified the case to two queries over an empty table: SELECT max(x) AND 0 FROM empty and its equivalent using a parameter bound to zero. His reply said the second result was correct. That is the crucial reading of the primary discussion. Repeating the reporter's expectation as SQL semantics would reverse the lesson of the fix.

The SQLite SELECT documentation, used here as a current reference, explains aggregate queries without GROUP BY: they produce one result row even when there are no input rows. The aggregate documentation defines max on empty input as NULL; the expression documentation defines AND with a false operand as zero, including with a NULL operand. In this case, the result is a row containing zero. No rows and one row containing zero are observably different results.

These statements describe the documented semantics and the maintainer's reproducer. The queries have not been executed for this article. The dated release note supplies the connection from the July 1 discussion to the July 17 correction; it does not require inventing a test result or inferring an unrelated join defect from a search result.

Count rows before comparing their contents

A business application might interpret no result as an absent summary and a returned zero as a completed summary with nothing to count. That is an illustrative consequence, not a claim about an affected customer. The distinction can matter even if the visible user interface eventually displays the same numeral for both cases.

For a proposed regression check, preserve result shape as well as scalar values. Check the number of rows and the values the application receives. If an adapter converts missing rows or null values into a default, inspect that boundary separately. An application-level default can conceal a database difference rather than establish that two queries are equivalent.

Empty inputs deserve deliberate inclusion because they exercise the boundary between rows entering an aggregate and rows returned by it. A fixture with one ordinary row might never expose that distinction. Adding a large random dataset is not necessarily a better investigation than retaining the small empty-table case that makes the semantic disagreement clear.

The expected answer should come from the query's meaning and applicable documentation. Differential testing can show that two forms disagree, but it cannot alone tell you which is right. If an old version and a new version return different answers, majority voting across versions is also insufficient. A longstanding result can be wrong; a changed answer can be a correction.

Preserve the application's parameter boundary

Replacing a parameter with a literal is useful for comparison, but it changes what is known during preparation. The point is to expose assumptions about equivalent results, not to rewrite the production query into string interpolation. A diagnostic convenience should not become a new application query-construction policy.

A small reproducer should record the exact SQL, binding values and types, schema and engine version. Include only the data needed to demonstrate the difference. If the problem depends on a particular extension or build option, retain that fact too. These details let a maintainer examine the claim without receiving real customer data or a copy of a production database.

There is a useful business advantage to this precision. A clear reproducer can distinguish a database defect from an adapter conversion or an incorrect expectation in the product. That can prevent time being spent changing application behavior to accommodate the wrong premise. It does not guarantee a particular response time or imply that every defect is easy to isolate.

When an optimizer is involved, keep plan inspection subordinate to result equivalence. A plan can explain why two executions follow different paths, but an attractive plan does not justify changing the answer. Performance comparison becomes useful after the intended result is established. No query plan or timing was collected for this retrospective.

A narrow fix does not justify a blanket freeze

The strongest counterargument is the cost of expanding release checks. A small application cannot compare every possible query form after every database update. That is reasonable. The response is to retain the relevant reproducer and add a focused application check where the semantic boundary matters, rather than demand an exhaustive database-engine test suite.

Likewise, the existence of one optimizer defect does not show that avoiding patches improves reliability. The July patch is itself evidence of a correction. Decide whether an update is relevant to the deployed engine and query patterns, then preserve enough evidence to explain the decision. A blanket freeze would ignore the cost of keeping known incorrect behavior.

This subject differs from browser SAHPool rollback compatibility: that concerns whether clients can reopen stored data through a particular persistence adapter. Here, the engine can open the data and still return a wrong answer for a query form. The failure boundary and the useful check are different.

For custom software development, keep correctness evidence close to the query that serves the business rule. Compare bound and literal forms where the investigation calls for it, verify the expected shape, and retain the minimal case. The July report is valuable because it narrowed the disagreement enough to correct both the implementation and the initial expectation.