SQL traps · Chapter 1 of 16

NOT IN and a NULL

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Everyday query traps
  • 3 tasks
  • SQL
  • Free chapter

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.

The lesson

By the end of this case

You can tell a NOT IN that met a NULL from its symptoms, make it happen on purpose in a fresh database, fix it, and pin it down with a SQL test that fails on the bug and passes on the fix.

The ticket

MS-412: Sunday's digest went to nobody. Assigned to you by Anya. Priority: high. Since: Friday's release.

Meera, in the product channel on Sunday at 10:15 am: "The weekly digest went out to zero people. Last Sunday it reached 18,412 members, and the open rate is our best number for the investors' update on Tuesday. Nobody unsubscribed eighteen thousand people over the weekend."

The digest job asks one view who should get the email. Anya ran its question on production:

minglesphere=> SELECT count(*) FROM digest_recipients;
 count
───────
     0
(1 row)

Anya: "Friday's release shipped account deletion. When a member deletes their account, their opt-out row stays for the audit and its user_id becomes NULL. The digest's SQL has not changed in a year. Reproduce it in a fresh database first, then fix it, then make sure it can never come back. You are in digest.sql."

Three answers, not two

In SQL a comparison can be true, false or unknown, and NULL means "no value here". Comparing anything with NULL gives unknown, which Postgres prints as NULL. Even NULL compared with NULL:

SELECT NULL = NULL AS equal, NULL <> NULL AS not_equal, NULL IS NULL AS is_null;
 equal  │ not_equal │ is_null
────────┼───────────┼─────────
 (null) │ (null)    │ t

A WHERE keeps a row only when its condition is true. Unknown is not true, so the row goes, quietly. That is why city = NULL finds no one in MingleSphere's data, while city IS NULL finds tara and minglebot. IS NULL and IS DISTINCT FROM are the comparisons that always answer yes or no.

What NOT IN really asks

x NOT IN (a, b, c) is shorthand for x <> a AND x <> b AND x <> c. With a NULL in the list, one of those parts is unknown, and an AND with an unknown part can never be true:

SELECT 'Delhi' NOT IN ('Bengaluru', 'Mumbai', NULL) AS delhi_not_in;
 delhi_not_in
──────────────
 (null)

The same happens when the list comes from a subquery. Here are the members in cities where no verified member lives, in your own minglesphere database. minglebot is verified and has no city:

SELECT username, city FROM users
WHERE city NOT IN (SELECT city FROM users WHERE is_verified);
 username │ city
──────────┼──────
(0 rows)

No error, no warning: one NULL in the subquery and the answer is empty. Nothing complained in the ticket either. The view ran fine for a year because no opt-out had a NULL until Friday.

Asking "is there a row?"

NOT EXISTS (subquery) asks a different question: does the subquery return any row at all? That is always yes or no, so NULLs in the other table cannot poison it:

SELECT u.username, u.city FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM users v WHERE v.is_verified AND v.city = u.city
);

This gives 10 rows: meera, zoya, dev, tara, nikhil, sahil, lakshmi, yash, manav and minglebot. Notice tara and minglebot, whose own city is NULL: v.city = u.city is never true for them, so nothing matches and they are kept. The two forms differ on NULLs, so choose the one that says what the feature means.

Making it happen on purpose

Every run of your files gets a fresh, empty database: the case's module (digest.sql) makes its tables, then your file runs, one statement at a time, as psql -f would. node run.mjs does that and prints what your file's last SELECT gave. Nothing touches your own minglesphere database.

So the repro builds exactly the rows the ticket needs. The NULL should arrive the way Friday made it: a member opts out, then their row in users is deleted, and ON DELETE SET NULL empties the opt-out's user_id.

A test that would have caught it

A guard is a SQL file too. The lab adds two pgTAP-style functions to every fresh database: ok(condition, name) passes when the condition is true, and is(got, want, name) passes when the two are equal. Each call is one test. node run.mjs digest.test.sql prints them:

ok 1 - a member with no opt-out gets the digest
not ok 2 - a member who opted out does not get it: the condition was false
2 tests, 1 failed

A good guard fails on the bug and passes on the fix. Test both sides: a hotfix that drops the opt-out filter would also "send the digest to someone".

Your turn

Three tasks, one file each. In repro.sql, empty the digest on purpose, and run it with node run.mjs. In digest.sql, fix the view. In digest.test.sql, write the guard: the check runs it beside the original code, the fix and a hotfix that drops the opt-out filter. Then answer Anya's interview question at the bottom of the case, in your own words.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: an empty digest on purpose

    Write repro.sql. It runs in a fresh, empty database where digest.sql has just made the tables users and email_optouts and the view digest_recipients. Add four members: three who stay, and one who leaves. Opt out one of the three and the one who leaves, then delete the member who leaves from users, so their opt-out keeps a NULL user_id, as on Friday. End with a SELECT that gives one value: how many members digest_recipients lists.

    The check runs your file twice, each time in a fresh database: beside the original digest.sql it must give 0 (nobody gets the digest), and beside a fixed digest.sql it must give 2. So it has to really make the NULL: a file that only says SELECT 0 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    digest.sql: the digest goes out again

    Fix the view digest_recipients in digest.sql. It must list every member who is not deleted (deleted_at is NULL) and has no row in email_optouts. A member who opted out, once or more than once, is not listed. Opt-outs with a NULL user_id (accounts deleted since) change nothing, however many there are.

    Keep the two tables as they are, and keep the view's name and its columns: id, username, email. The check uses its own members and opt-outs, and a view that lists members who opted out is as wrong as the bug.

  3. Task 3

    digest.test.sql: a test that would have caught it

    Write a SQL test file, digest.test.sql, that would have caught this bug before the release. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of digest.sql has just made the tables: beside the original digest.sql at least one test must fail; beside the fixed digest.sql every test must pass and nothing may raise an error; beside a hotfix that drops the opt-out filter at least one test must fail.

    So add members and opt-outs, one opt-out with a NULL user_id made by deleting its member, and test both sides: a member with no opt-out is listed, and a member who opted out is not. Run it with node run.mjs digest.test.sql.

What you will learn

  • repro.sql: an empty digest on purpose
  • digest.sql: the digest goes out again
  • digest.test.sql: a test that would have caught it

More in Everyday query traps

Build "NOT IN and a NULL" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.