SQL traps · Chapter 15 of 16

An UPDATE that hits every row

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

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

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: every post hidden on purpose

    Write repro.sql. It runs in a fresh, empty database where moderation.sql has just made the tables posts and reports and the function hide_reported(). Add three posts. Add one report on the second post with the status 'upheld', as a moderator did yesterday. Run the job with SELECT hide_reported(). End with a SELECT that gives one value: how many posts are hidden.

    The check runs your file twice, each time in a fresh database: beside the original moderation.sql it must give 3 (every post hidden), and beside a fixed moderation.sql it must give 1. So it has to really run the job: a file that only says SELECT 3 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    moderation.sql: only reported posts are hidden

    Fix hide_reported() in moderation.sql so it hides exactly the posts that have an upheld report. A post with one or more upheld reports is hidden. A post with only open or dismissed reports, or no reports at all, stays visible, and a post hidden already stays hidden. It returns how many posts it hid just now: a post with two upheld reports counts once, a post that was hidden already is not counted again, and a second run returns 0.

    Keep the two tables as they are, and keep hide_reported's name, no arguments and its integer result. The check uses its own posts and reports, and a fix that hides posts whose reports were dismissed or are still open is as wrong as the bug.

  3. Task 3

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

    Write a SQL test file, moderation.test.sql, that would have caught this bug before the job ran on production. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file four times, each in a fresh database where a version of moderation.sql has just made the tables: beside the original moderation.sql at least one test must fail; beside the fixed moderation.sql every test must pass and nothing may raise an error; beside a rewrite with EXISTS that still hides every post at least one test must fail; and beside a fix that hides a post for any report, dismissed ones too, at least one test must fail.

    So add posts and reports, run hide_reported(), and test every side: a post with an upheld report is hidden, a post nobody reported stays visible, and a post whose report was dismissed stays visible. Run it with node run.mjs moderation.test.sql.

What you will learn

  • repro.sql: every post hidden on purpose
  • moderation.sql: only reported posts are hidden
  • moderation.test.sql: a test that would have caught it

More in Time

Build "An UPDATE that hits every row" 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.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.