Is this the NULL filter post?

Opposite direction. WHERE status = 'active' drops NULLs, which is correct for 'status is active' and wrong for 'not cancelled.' COALESCE(status, 'active') = 'active' keeps the NULLs and labels them active. One under-counts, one invents a value.

Isn't COALESCE(amount, 0) fine in a SUM?

Often yes, if blank money should count as zero — and SUM already skips NULL, so COALESCE inside SUM usually changes nothing. COALESCE inside a WHERE or a CASE that picks a business status is the dangerous one. It changes who is in the set.

What about empty string?

COALESCE(status, 'active') does not touch ''. A blank status stays blank and fails = 'active'. You can still be wrong in both directions: NULLs counted as active, blanks counted as missing. Check both.

Why does the model write COALESCE?

It treated NULL as 'missing, so assume the default.' Apps sometimes default status in the UI. The database did not. The query should not invent the default unless you said the unset rows are active.

What should I ask for instead?

COUNT(*) WHERE status = 'active', plus COUNT(*) WHERE status IS NULL, plus COUNT(*) WHERE status = ''. Three numbers. Then decide which bucket 'active customers' includes.

COALESCE NULL as Active SQL | Silent Over-Count

You ask for "active customers." The number is higher than the admin screen. Status filters look right if you squint: WHERE COALESCE(status, 'active') = 'active'. Every customer who never got a status just became active.

COALESCE invents a value, then the filter believes it

-- NULL passes, because it was rewritten to 'active' first
WHERE COALESCE(status, 'active') = 'active'

-- NULL does not pass
WHERE status = 'active'

status = 'active' leaves NULL out. That's three-valued logic, and it's the right behavior when you asked for rows whose status is active. The NULL filter post is about the other English reading — "not cancelled" — where != also drops NULL.

COALESCE is a third bug. It does not drop NULL and it does not leave NULL unknown. It replaces NULL with a guess, then compares the guess. Unset, imported, and half-migrated rows all count.

Same shape, other columns:

-- "has a plan" includes people with no plan
WHERE COALESCE(plan, 'solo') = 'solo'

-- "cancelled this month" includes people who never cancelled,
-- if the model fills a missing timestamp with now()
WHERE COALESCE(cancelled_at, NOW()) >= DATE_TRUNC('month', NOW())

The second one is nasty: a NULL cancelled_at becomes "cancelled right now." Soft delete is the cousin that forgets deleted_at entirely. This one notices the NULL and fills it in wrong.

COALESCE(amount, 0) inside SUM is usually harmless — SUM already skips NULL. The damage is in WHERE, CASE, and GROUP BY COALESCE(...).

How to catch it

Show the WHERE. If COALESCE wraps the filtered column, remove it
and report three counts:
  status = 'active'
  status IS NULL
  status = ''

If the COALESCE version is bigger than status = 'active' by about the NULL count, the default was invented.

The prompt that heads this off

Do not COALESCE a status, plan, or timestamp just so a comparison succeeds.
NULL is its own bucket. Give me the match count, the NULL count, and the
blank-string count. Only treat NULL as a default if I explicitly say so.

The short version

COALESCE(status, 'active') does not mean "active, and also tell me about blanks." It means "call the blanks active." If the active count jumped and the SQL contains COALESCE, the missing statuses were filled in.

Read-only MCP gateway: mcpserver.design.

Related reading: