SQL traps · Chapter 8 of 16

LIMIT and OFFSET with no order

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
  • 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: a member exported twice on purpose

    Write repro.sql. It runs in a fresh, empty database where export.sql has just made the table users and the function members_page. Add five members. Read page 1, with pages of 2, into a temporary table. Then update a member who is on page 1 (a profile edit, such as a new bio), read pages 2 and 3 into the same table, and end with a SELECT that gives one value: how many different members the table holds.

    The check runs your file twice, each time in a fresh database: beside the original export.sql it must give 4 (one member read twice, one never), and beside a fixed export.sql it must give 5. So it has to really page through with an edit in between: a file that only says SELECT 4 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    export.sql: every member once

    Fix the function members_page in export.sql so the pages are stable. Every page lists its members in id order, page 1 first: with seven members and pages of 3, page 1 is the first three by id, page 2 the next three, page 3 the last one, and a page past the end has no rows. Reading every page gives every member exactly once, also when members edit their profiles between one page and the next.

    Keep the table users as it is, and keep the function's name, its arguments (page, size) and its columns: id, username, email. The check uses its own members, and members who joined in the same import share one created_at, so an order that can tie is as unstable as no order at all.

  3. Task 3

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

    Write a SQL test file, export.test.sql, that would have caught this bug before the export shipped. 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 export.sql has just made the table and the function: beside the original export.sql at least one test must fail; beside the fixed export.sql every test must pass and nothing may raise an error; beside a hotfix that orders by created_at at least one test must fail.

    So add members who joined in the same import (one INSERT, all with the same created_at), page through them with a profile edit in between, and test that every member was read exactly once: nobody missing and nobody twice. Run it with node run.mjs export.test.sql.

What you will learn

  • repro.sql: a member exported twice on purpose
  • export.sql: every member once
  • export.test.sql: a test that would have caught it

More in Everyday query traps

Build "LIMIT and OFFSET with no order" 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.