How a LEFT JOIN Lost a $1,000 Invoice at Month-End

An invoice for $1,000 is open on September 30. On October 1 the customer pays $400. Run the September report again and the invoice disappears. A customer who paid nothing still shows up just fine.

You can reproduce this with three invoices and one payment in SQLite. Amounts are in US cents. The dates are fixed-width YYYY-MM-DD business dates. applied_on is the day a receipt was applied to this particular invoice.

CREATE TABLE invoices ( id TEXT PRIMARY KEY, issued_on TEXT NOT NULL, total_cents INTEGER NOT NULL ); CREATE TABLE applications ( invoice_id TEXT NOT NULL REFERENCES invoices(id), applied_on TEXT NOT NULL, amount_cents INTEGER NOT NULL ); INSERT INTO invoices VALUES ('AR-1001', '2026-09-01', 100000), ('AR-1002', '2026-09-12', 25000), ('AR-1003', '2026-10-03', 15000); INSERT INTO applications VALUES ('AR-1001', '2026-10-01', 40000); 

The row that goes missing

Bind :as_of to the string 2026-09-30. This version returns only AR-1002, the $250 invoice that has never had a payment.

SELECT i.id, i.total_cents - COALESCE(SUM(a.amount_cents), 0) AS open_cents FROM invoices AS i LEFT JOIN applications AS a ON a.invoice_id = i.id WHERE i.issued_on <= :as_of AND (a.applied_on <= :as_of OR a.applied_on IS NULL) GROUP BY i.id, i.total_cents ORDER BY i.id; 

The join found a real payment for AR-1001, so its date is October 1. The WHERE clause drops that row. SQL doesn’t then go back and manufacture a second row with a null payment date. That’s why adding OR ... IS NULL preserves AR-1002 but loses AR-1001.

Give the payment cutoff to the join

SELECT i.id, i.total_cents - COALESCE(SUM(a.amount_cents), 0) AS open_cents FROM invoices AS i LEFT JOIN applications AS a ON a.invoice_id = i.id AND a.applied_on <= :as_of WHERE i.issued_on <= :as_of GROUP BY i.id, i.total_cents ORDER BY i.id; 

Now the October receipt can’t match in September. The invoice still contributes a row, and COALESCE supplies the zero to subtract. On October 1 the same receipt qualifies, including on the cutoff day itself.

For a regression test, run the September query, insert a payment dated October 1, and run September again. Assert that the entire result set is unchanged. Then advance the cutoff and assert that exactly one balance falls by 40,000 cents. A test that checks only today’s balance passes with either query and misses the bug.

AI disclosure. This article and its SQL example were generated with AI. The queries and regression checks were executed on SQLite 3.53.0.