The Project Operator and Aliases

Why this stage exists

select name, id from users; has worked since The Core Engine, but only because SELECT picked columns out of a row by name. A query's output is more than a choice of columns: select price * qty as total, select -balance, select age > 30. Each output column is computed, and each needs a name to print in the header. The next stages need it too: aggregates such as count(*) live in the select list, and ORDER BY and GROUP BY talk about what it computes.

So the select list becomes a list of expressions, and computing it becomes an operator of its own: Project. It sits at the top of the plan, above the scan and the filter, and turns each row they hand up into the row the user sees.

Where Project sits

The order of the operators is the order of the work. The scan reads rows, the Filter keeps the ones the WHERE allows, and Project computes the output from what is left:

>explain select name, id * 2 as dbl from t where a = 6;
[PLAN] select name, id * 2 as dbl from t where a = 6
[PLAN]   └── Project (cols=[name, id * 2 AS dbl])
[PLAN]       └── Filter (a = 6)
[PLAN]           └── SeqScan (table=t)

Two things follow from that order. First, an item is only ever evaluated for a row the WHERE kept, so select a / b from t where b != 0; never divides by zero. Second, an alias does not exist yet when the WHERE runs. select id * 2 as dbl from t where dbl > 2; asks the Filter for a column the table does not have, and is 00402. If the alias is also the name of a real column, the WHERE means the real column. Real databases agree on this: the select list is computed last, even though it is written first.

select * keeps its old plan, with no Project node: every column, unchanged, in table order, is what the scan already hands up.

Naming an output column

Every column of the result needs a header. There are three cases:

Using the printed form for an unnamed item means every header is defined by a rule you have already built, with no special case. The parentheses show the tree, so the header is also a small reminder of how the item was parsed.

Values of any type

An item can be any expression, so it can have any type the evaluator knows. An INT prints its digits, now possibly with a minus sign, since -a and a - 10 can go below zero. A VARCHAR prints as stored and a NULL as NULL. A comparison is a truth value, and prints true or false, or NULL when it is unknown: select c > 1 from t; on a row whose c is NULL prints NULL.

A literal is an item too. select 1, 'x', id from t; prints 1 and x on every row, under the headers 1 and 'x'.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, sections 40 to 43.

The checks run before any row is read: every column of the select list then of the WHERE (00402), then the operators of the select list then of the WHERE (00403), then the numbers of both (00404). rows_examined counts what the scan reads, exactly as before; Project adds nothing to it. In CSV mode the header line is the same headers separated by ,, each quoted like any field: select 'a,b' from t; has the header "'a,b'".

What you should see when done

$droid --db shop.db
Welcome to droid!
>create table items (id int, price int, qty int, name varchar(20));
Create table statement executed successfully
>insert into items (id, price, qty, name) values (1, 250, 4, 'lamp');
Insert statement executed successfully (1 rows)
>insert into items (id, price, qty, name) values (2, 40, 0, 'cable');
Insert statement executed successfully (1 rows)
>insert into items (id, price, name) values (3, 900, 'desk');
Insert statement executed successfully (1 rows)
>select name, price * qty as total, qty > 0 from items;
name | total | qty > 0
lamp | 1000 | true
cable | 0 | false
desk | NULL | NULL
(3 rows)
[EXEC] rows_examined=3
>select price / qty from items;
[ERROR:01501] Division by zero
>select name, price / qty as each from items where qty > 0;
name | each
lamp | 62
(1 rows)
[EXEC] rows_examined=3
>select name from items where total > 100;
[ERROR:00402] Column total does not exist
>explain select name, price - 50 from items where id >= 2;
[PLAN] select name, price - 50 from items where id >= 2
[PLAN]   └── Project (cols=[name, price - 50])
[PLAN]       └── IndexScan (table=items, range: id >= 2)
>.exit
exiting.. good bye!

The desk's quantity was left out, so it is NULL, and so is everything computed from it. Dividing by the cable's zero stops the second query, while the third never reaches that row, because the WHERE drops it before Project computes anything. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• Projecting before filtering. Computing the items for every row, then filtering, divides by zero on rows the WHERE would have dropped.
• Headers as typed. Echoing the item's text gives a+b*2 or a + b * 2; the header is the printed tree, a + (b * 2).
• Aliases visible to the WHERE. Resolving WHERE columns against the select list makes where dbl > 2 work; it must be 00402.
• A Project node for *. select * keeps the plan it had.
• Truth values printed as 1 and 0. A comparison prints true or false, and unknown prints NULL.
• Negative numbers printed unsigned. An INT column never goes below 0, but an item can. Print the signed value.
• Checking the WHERE first. An unknown column in the select list must be reported before one in the WHERE.

Language notes

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

• C and C++: keep the items as an array of expression pointers with an optional alias each; an output row is an array of the value structs from the evaluator. Print an int64_t with PRId64 from <inttypes.h>.
• Rust: Vec<(Expr, Option<String>)> for the items; implementing Display for your expression type gives you ast, the plan line and the header from one function.
• Zig: a slice of items, each an expression and an optional alias (?[]const u8); a format function on the expression type serves ast, explain and the header alike.