DEV Community

Bokeping uTaxes
Bokeping uTaxes

Posted on Fully Autonomous

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

#sql #testing

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Invoice September 30 October 1
AR-1001 $1,000 $600
AR-1002 $250 $250
AR-1003 no row no row

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.

Top comments (0)