Reading from a read

The two tables this page reads:

DEFINE TABLE users (name string, city string);
DEFINE TABLE orders (total int, who string);
CREATE users = { name: 'ada', city: 'london' };
CREATE users = { name: 'grace', city: 'york' };
CREATE orders = { total: 7, who: 'ada' };

Anywhere a table can be read, a parenthesised read can stand instead:

SELECT * FROM (SELECT * FROM users WHERE city = 'london' LIMIT 100);

The inner read runs, its answer is held, and the outer read treats that as its source. The plan says so:

(1 record(s), via materialised)

That word is the one to know. Materialised means the records were collected into memory before the outer read saw them — unlike a scan or an index seek, which stream.

The inner read must state a ceiling

SELECT * FROM (SELECT * FROM users WHERE city = 'london');

Refused — UnexpectedToken.

error: expected `LIMIT n` on the inner read — a materialised source states how
much it may hold, so that a truncated answer is never mistaken for a whole one,
found `)`

This is the rule the rest of the page is about, and it is worth being plain about why it exists.

A source that is collected into memory has to stop somewhere. There are three things a database can do about that, and two of them are bad. It can collect without limit, and fall over on the day the table grew — a failure that arrives in production, at the worst size, having passed every test. Or it can impose a private ceiling and quietly answer about a prefix, which is worse, because the answer looks complete: a count is a number, a MAX is a value, and neither carries a mark saying of the first ten thousand.

The third option is to make the author say it. LIMIT on a materialised read is not a restriction, it is a declaration — this many is enough for the question I am asking — and it moves the decision to the one person who knows the answer.

And it says when the ceiling bit

Declaring a bound would be no better than a private one if you never learned it had been reached. So:

SELECT * FROM (SELECT * FROM users LIMIT 2);

1: { city: 'london', name: 'ada' }
2: { city: 'york', name: 'grace' }
(2 record(s), via materialised)
note: the materialised source reached its ceiling of 2, so this answers about a
prefix of what it would hold unbounded

The note travels with the answer, not to a log. It fires on reaching the ceiling, so it can fire when nothing was actually lost — two records under a LIMIT 2 is indistinguishable, from inside, from the first two of a thousand. That is the safe direction for a warning to be wrong in.

It fires no matter what the outer read then does with the records:

SELECT * FROM (SELECT * FROM users ORDER BY name LIMIT 2) WHERE name = 'ada';

1: { city: 'london', name: 'ada' }
(1 record(s), via materialised)
note: the materialised source reached its ceiling of 2, so this answers about a
prefix of what it would hold unbounded

One record answered and the note is still there, correctly. The outer WHERE filtered a prefix; whether the record it was looking for was inside that prefix is exactly what you cannot tell from the answer.

Joining a read

A materialised read can be one side of a join, and AS names it so the two sides do not collide:

SELECT * FROM users JOIN (SELECT * FROM orders WHERE total > 5 LIMIT 100) AS big
  ON users.name = big.who;

1: { big: { total: 7, who: 'ada' }, users: { city: 'london', name: 'ada' } }

AS is for exactly this. Outside a join there is nothing for the name to distinguish, so it is refused there rather than accepted and ignored.

A self-join is two reads of one table

Naming both sides is what makes it possible to say which is which:

SELECT * FROM (SELECT * FROM users LIMIT 100) AS a
  JOIN (SELECT * FROM users LIMIT 100) AS b
  ON a.city = b.city;

1: { a: { city: 'london', name: 'ada' }, b: { city: 'london', name: 'ada' } }
2: { a: { city: 'york', name: 'grace' }, b: { city: 'york', name: 'grace' } }

Note that every record matched itself, which is nearly never what a self-join is for — add the condition that excludes it, usually a comparison on the two ids.

What to reach for instead

A materialised read holds records in memory, so it is the wrong tool when the inner answer is large and the outer read only needs a little of it. Two alternatives usually apply:

• Push the condition down. SELECT * FROM (SELECT * FROM users LIMIT 1000) WHERE city = 'london' collects a thousand records to keep a few; writing the WHERE on the inner read collects the few.
• If you are following a link somebody wrote down rather than matching on a shared value, that is a traversal and not a read of a read — see Graphs.