Aliases and the Binder

Give a FROM table an alias, accept qualified column names such as u.name and u.*, and resolve every name in a SELECT to one entry of the FROM before planning, so that explain logical prints each column with the name of the entry it belongs to.

Not run yet · 6 tests

Why this stage exists

The next stage joins two tables, and the first thing a join needs is a way to say which id you mean. users and orders both have one. So before writing a single join, droid learns to name things: a table in the FROM can get a short alias, and a column can be qualified by it, u.id.

That sounds like parsing, and the parsing is the easy part. The real change is a step between parsing and planning that has existed quietly since the first SELECT: looking up every name and deciding what it means. Real engines call it the binder (or the analyser). Until now it had one table to look in. From here it has several, and later, with subqueries, it has nested scopes. This stage builds it while there is still only one table, so that nothing else changes at the same time.

Entries and names

Each table in a FROM is an entry, and an entry has a name: its alias if it has one, otherwise its table's name.

In from users u, the entry reads table users and is called u. A qualified name u.c looks for the entry called u, then for column c in its table.

Once a table has an alias, the alias is its only name: select users.id from users u is an error, 01701, as in the SQL standard. This is what will let the same table appear twice in one query later, users a join users b: two entries, one table, two names.

A bare name such as name is looked up in every entry's table. With one entry that is what has always happened.

Binding

Binding resolves each reference once, and stores the answer: entry and column. Everything after it works from that answer, never from the spelling. Two things follow.

Headers come from the column. select u.name from users u prints the header name, as select name from users always has. Only an expression keeps its typed form, u.id * 2.

Spellings do not matter. With from emp e, group by e.dept and a select list that says dept name the same column, so the select list may show it. The GROUP BY rule of the group-by stage compared expressions by how they print; now a column compares by what it is bound to.

explain logical is where you can see the binder's work. It prints every column with its entry's name in front, even the ones typed bare:

explain logical select name, count(*) from users u where id > 1 group by name

Project (cols=[u.name, COUNT(*)])

Aggregate (keys=[u.name], aggs=[COUNT(*)])

Filter (u.id > 1)

Get (table=users, alias=u)

Every reference is bound to entry u. Without an alias, the entry's name is the table's: users.name.

The physical plan of explain still prints expressions as typed, and a scan of an aliased entry adds alias=u. A bound written u.id = 1 is a key bound like id = 1.

This changes one earlier output: explain logical used to print columns as typed. The format case that checked it is retired in this stage.

The algorithm in words

Binding a SELECT

Resolve every name once, before planning.

1.Check the FROM's table, then record its entry: the table and the entry's name (the alias if there is one, else the table's name).
2.For each column reference, in the order the checks have always run: if it is qualified, find the entry with that name (01701 if none), then the column in that entry's table (00402 if none).
3.If it is bare, find the entry whose table has that column (00402 if none).
4.Expand q.* into the columns of entry q, in table order (01701 if there is no entry q).
5.Store the result on the reference: which entry and which column. Everything after binding (headers, GROUP BY matching, plans) works from that, not from how the name was typed.

Output contract for this stage

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

Input	Output
`select u.name from users u;`	header `name`
`select u., u.id 2 from users u ...;`	header `id \| name \| dept \| u.id * 2`
`select users.id from users u;`	`[ERROR:01701] ...`
`select u.nope from users u;`	`[ERROR:00402] ...`
`explain logical select name from users where dept = 'eng';`	`Project (cols=[users.name])`, `Filter (users.dept = 'eng')`, `Get (table=users)`
`explain select u.name from users u where u.id = 2;`	`Project (cols=[u.name])`, `IndexScan (table=users, alias=u, key=2)`
`tokenize u.name`	`[IDENTIFIER - u]`, `[SYMBOL - .]`, `[IDENTIFIER - name]`
`ast select name from users u;`	`Statement: SELECT`, `Table: users AS u`, `Columns: [name]`

What you should see when done

shell

$droid --db names.db
Welcome to droid!
>create table users (id int primary key, name varchar(20));
Create table statement executed successfully
>insert into users (id, name) values (1, 'ada');
Insert statement executed successfully (1 rows)
>select u.id, u.name from users as u where u.id = 1;
id | name
1 | ada
(1 rows)
[EXEC] rows_examined=1
>select users.name from users u;
[ERROR:01701] no FROM entry is called users
>.exit
exiting.. good bye!

Common bugs in this stage

Keeping the table's name usable after an alias. The alias replaces it.
Headers printed as u.name. The header is the column's name.
Comparing GROUP BY keys as text. e.dept and dept are one column once bound.
Reading u . name differently from u.name. They are the same three tokens.
Taking a keyword for an alias. In from users where ..., where starts the WHERE.

Language notes

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

C: give each column reference two fields filled in by the binder, an entry index and a column index, and keep the typed text for printing.
C++: a bound reference can be a small struct {entry, column} stored in the expression node next to its typed name.
Rust: resolve into a new type, so that a plan can only be built from an expression that has been bound.
Zig: a tagged union for references, unbound and bound, makes the planner refuse the unbound kind.

Loading starter code…

No run yet

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