Case Study 1 — The Coupon That Went to Everyone

A single missing pair of parentheses in a WHERE clause turned a targeted promotion into a company-wide giveaway. This is the most common, most expensive kind of SQL bug: not a crash, but a logically wrong query that runs perfectly and does the wrong thing.

Background

An online retailer planned a win-back campaign: send a 20%-off coupon to lapsed high-value customers — specifically, gold or platinum members who hadn't ordered recently. A marketer who knew a little SQL was asked to export the target list. She wanted "gold or platinum customers, who are inactive," and wrote:

-- Intended: (gold OR platinum) AND inactive
SELECT email
FROM customers
WHERE loyalty_tier = 'gold' OR loyalty_tier = 'platinum' AND last_order_date < '2024-01-01';

The export looked plausible — a long list of emails — so it was handed to the email platform, and 20%-off coupons went out. Within a day, finance noticed an unexpected spike in discount redemptions from active customers who were buying anyway. The campaign had blown a hole in the margin.

What went wrong

The bug is operator precedence. AND binds tighter than OR, so PostgreSQL read the condition as:

   loyalty_tier = 'gold'
   OR
   ( loyalty_tier = 'platinum' AND last_order_date < '2024-01-01' )

In plain English, that query selects every gold customer (active or not), plus only the inactive platinum ones. The "inactive" filter the marketer thought applied to everyone actually applied only to the platinum branch. So thousands of perfectly active gold customers — people who were going to buy at full price — received 20% off. The query did exactly what it said; it just didn't say what she meant.

The fix is one pair of parentheses:

-- Correct: (gold OR platinum) AND inactive
SELECT email
FROM customers
WHERE (loyalty_tier = 'gold' OR loyalty_tier = 'platinum')
  AND last_order_date < '2024-01-01';

Or, cleaner still, using IN for the tier test:

SELECT email
FROM customers
WHERE loyalty_tier IN ('gold','platinum')
  AND last_order_date < '2024-01-01';

Both versions group the tier check correctly and read closer to the intent.

A second lurking bug

While reviewing, the team found a related hazard in another segment query that used "not in a list":

-- Meant: customers NOT in these three excluded tiers
SELECT email FROM customers
WHERE loyalty_tier NOT IN ('standard', 'silver', NULL);

Because the list contained NULL (someone had appended it defensively), NOT IN returned no rows at all — three-valued logic again (Chapter 3). The "exclude these" query silently excluded everyone. Had it shipped, the opposite mistake — a campaign that reached nobody — would have wasted the send. The fix was to remove the NULL and, for robustness against query-sourced lists, prefer NOT EXISTS (Chapter 9).

The analysis

  1. Logical bugs are silent and expensive. Nothing errored. The query ran, returned rows, and looked fine. The only signal was downstream (margin erosion). Correctness in SQL is not "does it run" — it's "does it answer the question I meant."

  2. Always parenthesize mixed AND/OR. It costs nothing and removes all ambiguity. A reviewer scanning A OR B AND C has to remember precedence; a reviewer scanning (A OR B) AND C or A OR (B AND C) can simply read it. Make intent explicit.

  3. Prefer IN for "one of these." loyalty_tier IN ('gold','platinum') is harder to get wrong than tier = 'gold' OR tier = 'platinum', and it reads better — but beware NULL in NOT IN.

  4. Review queries that drive real-world actions like code. A query that sends emails, applies discounts, or deletes data deserves the same scrutiny as application code: read it aloud, test it against known data, and sanity-check the row count before acting on the result. ("Does 6,000 emails match my estimate of ~400 lapsed VIPs?" would have caught this immediately.)

  5. Sanity-check the result size. The clearest tell was there all along: the marketer expected a few hundred lapsed VIPs and got thousands. A quick SELECT count(*) against expectations is the cheapest bug detector in SQL.

Discussion questions

  1. Rewrite the buggy query's logic in words, following SQL's precedence exactly. Why did active gold customers get the coupon?
  2. Show two correct versions of the intended query and explain which you'd prefer in a code review.
  3. Explain why NOT IN ('standard','silver', NULL) returned no rows. What's the safer construct?
  4. The bug had no error message. What process or check would have caught it before the emails went out?
  5. ⭐ Propose a one-line "guardrail" the marketer could run before every campaign export to sanity-check the audience size.