Subqueries that Run Once

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

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:

Output contract for this stage

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

[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

$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.