The SQL query runs. Why does it return a sword?

A theatre dragon needs water before his first show. You ask the database for a metal prop. It returns a basin, a helmet and a sword.

The query ran successfully. The sword is still a terrible way to carry water.

This is the opening puzzle in A Role Without Words, the illustrated SQL story I'm building as part of SQLBrief. It gives a small, visible consequence to a familiar database mistake: checking only part of the requirement.

What the query actually asks for

Each row in props describes one item backstage. capacity_l is how many litres the item can hold. The task is to find a metal container; in this scene, the suitable containers already contain water.

This filter checks the material:

SELECT id, name, capacity_l
FROM props
WHERE material = 'metal';

It returns p1, p2 and p3. The sword belongs in that result because it is metal. PostgreSQL has no reason to reject it: we haven't asked whether it can hold anything.

Two requirements need two checks

The material must be metal, and the capacity must be greater than zero:

SELECT id, name, capacity_l
FROM props
WHERE material = 'metal'
  AND capacity_l > 0;

Now the result contains the basin and the helmet. The sword fails the capacity check. The wooden bucket fails the material check, even though it can hold more water.

That bucket is useful for checking our reasoning. If we accidentally replace AND with OR, the result includes the sword, cup and bucket too. Each passes at least one condition. The database still runs the query without an error.

Before running a filter, name one row that should stay and one that should go. Here, the helmet should stay; the sword and wooden bucket should go for different reasons. Those expectations make it much easier to notice an incomplete condition.

Try the complete example

This runs as a single PostgreSQL query. The WITH section supplies the six example rows, so you don't need to create a table. Change the filter at the bottom to compare the results. ORDER BY id keeps their display order consistent.

WITH props (id, name, material, capacity_l) AS (
  VALUES
    ('p1', 'Copper basin', 'metal', 2.0),
    ('p2', 'Knight''s helmet', 'metal', 1.0),
    ('p3', 'Prop sword', 'metal', 0.0),
    ('p4', 'Paper cup', 'paper', 0.3),
    ('p5', 'Wooden bucket', 'wood', 3.0),
    ('p6', 'Fan', 'fabric', 0.0)
)
SELECT id, name, capacity_l
FROM props
WHERE material = 'metal'
  AND capacity_l > 0
ORDER BY id;

The expected IDs are p1 and p2. Capacity describes a container, not how full it is; an inventory system outside this story would need a separate way to record the amount of water. The scene supplies that fact explicitly.

PostgreSQL's WHERE documentation explains how rows are kept when the condition is true.

Make the choice on stage

In the story, running SQL produces a table. You then choose which returned prop to hand over, and the characters react. Later puzzles use cue cards, a bell signal and a way to get a knight onto the stage.

Play the four-puzzle story. The data and editor are ready, with examples and hints. It is free, in English or Russian, and needs no account. If you try it, I'd like to know where the next step first becomes unclear.