NULL Isn't Equal to NULL

Medium

· Database

Incident

SEV-2

BUG-3VL

Priority: High · reported by analytics

Time to fix: 20 min

Failing checks on the shipped build

  • null filters use IS NULL

    expected [1, 3] but got []

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Inspired by

SQL three-valued logic

Inspired by the SQL lesson everyone learns in production: WHERE col = NULL matches nothing, ever — three-valued logic demands IS NULL. The report for "orders without a shipping date" has been returning zero rows for a month.

where_builder.py generates the clause; the locked engine follows real SQL semantics.

Code map

# Builds a WHERE clause for a single field.

def build_where(field, value):
    return {"clause": field + " = ?", "params": [value]}

Sign in to edit the file, run the tests and submit a fix

A free account — no card, no trial clock. You come straight back here.

NULL Isn't Equal to NULL — Python Bug Hunt

Inspired by the SQL lesson everyone learns in production: WHERE col = NULL matches nothing, ever — three-valued logic demands IS NULL.

  • Language: Python
  • Layer: Database
  • Difficulty: Medium
  • Concepts: SQL, NULL Semantics
  • Modelled on: SQL three-valued logic
  • Visible tests: null filters use IS NULL; concrete values still match with equals
  • Reward: 50 XP for a complete fix

Incident

  • Severity: SEV-2 (priority: High)
  • Ticket: BUG-3VL
  • Reported by: analytics
  • Time to fix: 20 minutes, the target for a medium incident

Failing checks on the shipped build

  • null filters use IS NULL — expected [1, 3] but got []

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Briefing

Inspired by the SQL lesson everyone learns in production: WHERE col = NULL matches nothing, ever — three-valued logic demands IS NULL. The report for "orders without a shipping date" has been returning zero rows for a month.

where_builder.py generates the clause; the locked engine follows real SQL semantics.

Bug report

BUG-3VL · Priority: High · Reported by: analytics

build_where(field, value):

  • value is None -> {"clause": "<field> IS NULL", "params": []}
  • otherwise -> {"clause": "<field> = ?", "params": [value]}

Observed: None becomes "= ?" with a NULL param — the engine (correctly) matches nothing, and ops thinks every order shipped.

Logs

[report] unshipped orders: 0 (warehouse says: definitely not zero)

The code as shipped

src/sql/where_builder.py (editable)

# Builds a WHERE clause for a single field.

def build_where(field, value):
    return {"clause": field + " = ?", "params": [value]}

Read-only context: src/sql/sqlengine.py.

Open the hunt to start the incident clock, edit the files, run the visible tests and submit against the hidden ones, then write the root cause down. More Python bug hunts.