Subqueries that Run Once

Accept scalar, IN and EXISTS subqueries in a SELECT, check that a scalar or IN subquery selects one column, run a subquery that names nothing outside itself once and reuse its result, with NULL for no row, 01709 for two rows, NOT IN's NULL rule and EXISTS stopping at its first row.

Not run yet · 9 tests

Why this stage exists

"Users who have never ordered" is one sentence in English and, so far, an awkward LEFT JOIN with IS NULL in droid. "Orders above the average" needs the average first, as a value in a WHERE. Both are natural as a query inside a query:

select name from users where id not in (select user_id from orders);
select id from orders where total > (select avg(total) from orders);

The NOT IN trap

Start with the most famous trap in SQL. Add one order whose user_id is NULL, and the first query above returns no rows at all, even though cy and di have no orders.

The nulls stage already explains why. NOT IN (1, 2, NULL) means id != 1 AND id != 2 AND id != NULL, and id != NULL is unknown for every id. AND with an unknown is never true, so no row passes. A subquery's values follow exactly the IN-list rules of the in-between stage, NULLs included. The next stage shows that NOT EXISTS does not have this problem.

Three forms

Form	Gives
`(select ...)`	its one value; NULL when it has no row; `01709` when it has two
`x [not] in (select ...)`	as an IN list of its values
`[not] exists (select ...)`	whether it has at least one row: true or false, never unknown

A scalar or IN subquery must select one column, 01710. An EXISTS subquery may select anything, because only whether a row comes matters, so EXISTS asks its plan for one row and stops, as LIMIT 1 would.

Run once

(select avg(total) from orders) mentions nothing of the outer query. Its answer is the same for every order, so run it once, the first time it is needed, and keep the value. The plan shows the subquery as an extra child of the node that evaluates it:

select id from orders where total > (select avg(total) from orders)

Filter (total > (subquery 1))

↑ 3 rows

SeqScan (table=orders)

ran

Subquery (1, once)

Project (cols=[AVG(total)])

Aggregate (aggs=[AVG(total)])

↑ 3 rows, once

SeqScan (table=orders)

rows_examined = 3 + 3. In explain analyze the subquery's nodes print loops=1.

The algorithm in words

Checking a subquery

A SELECT inside a SELECT.

1.Number the subqueries 1, 2, ... in the order their (SELECT is written, nested ones included.
2.Check each as a SELECT, with its own FROM, at the place where its expression is checked.
3.A scalar or IN subquery must select exactly one column (01710). EXISTS may select anything.

Evaluating it once

Run on first use, keep the result.

1.The first time the expression holding the subquery is evaluated, run the subquery's plan.
2.Scalar: no row gives NULL, one row gives its value, a second row fails the statement with 01709.
3.IN: collect the values. x IN them is true if one equals x; otherwise unknown if x is NULL or a value is NULL; otherwise false. NOT IN is NOT of that.
4.EXISTS: ask the plan for one row; true if it gives one.
5.Keep the result, and use it for every later row without running the plan again.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 77.

Input	Output
`select id from orders where total > (select total from orders where user_id = 1);`	`[ERROR:01709] ...`
`select id from orders where total > (select id, total from orders where id = 10);`	`[ERROR:01710] ...`
`select id, (select total from orders where id = 99) from orders where id = 10;`	`id \| (subquery 1)`, `10 \| NULL`
`explain select id from orders where total > (select avg(total) from orders);`	the tree below

[PLAN] select id from orders where total > (select avg(total) from orders)
[PLAN]   └── Project (cols=[id])
[PLAN]       └── Filter (total > (subquery 1))
[PLAN]           ├── SeqScan (table=orders)
[PLAN]           └── Subquery (1, once)
[PLAN]               └── Project (cols=[AVG(total)])
[PLAN]                   └── Aggregate (aggs=[AVG(total)])
[PLAN]                       └── SeqScan (table=orders)

What you should see when done

shell

$droid --db sub.db
Welcome to droid!
>create table t (id int primary key, n int);
Create table statement executed successfully
>insert into t (id, n) values (1, 10);
Insert statement executed successfully (1 rows)
>insert into t (id, n) values (2, 30);
Insert statement executed successfully (1 rows)
>select id from t where n > (select avg(n) from t);
id
2
(1 rows)
[EXEC] rows_examined=4
>select id from t where n > (select n from t);
[ERROR:01709] a scalar subquery returned more than one row
>.exit
exiting.. good bye!

Common bugs in this stage

Running the subquery for every row. It does not depend on the row.
An error for a scalar subquery with no row. It is NULL.
Reading every row for EXISTS. One is enough.
Treating NOT IN as "not equal to any". With a NULL in the set it is never true.
Taking the first of two rows of a scalar subquery. Two rows is 01709.

Language notes

The page above is the same for every language. These are the only language-specific hints.

C: give the subquery's expression node a done flag and a slot for its cached value or value array.
C++: a std::optional<Value> (or a vector of values for IN) cached in the expression node.
Rust: OnceCell holds a result computed on first use.
Zig: a ?Value field filled the first time the node is evaluated.

Loading starter code…

No run yet

9 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.