Schema Validation

Why this stage exists

The parser answers one question: is this statement well formed? insert into products (id, colour) values (1, 'red'); is. It has the right keywords in the right places, a column list and a value list of the same length. It still means nothing, because products has no column called colour. Whether a statement makes sense depends on what the database holds, and the parser does not know that. The catalog does.

Until the previous stage there was one table, and its three columns were part of the program. Checking a statement meant comparing a name with id, name or email and a string's length with 27. Now tables come from CREATE TABLE. products has a VARCHAR(40) where users has a VARCHAR(1000), orders is keyed by a column called oid, and each INT column has the same range as the key. Every one of those limits is in the catalog. Some of these checks were already there, scattered through the executor, written against users; this stage puts all of them in one place and has them read the catalog.

Real databases have the same step. PostgreSQL calls it parse analysis, and SQLite name resolution. Textbooks call it binding: each name in the statement is bound to the table or column the catalog says it is, and each value is checked against that column's type. What comes out is a statement the planner can trust. The planner never has to wonder whether a column exists or whether a value fits.

What changes: a pass between the parser and the planner

A line now goes through four steps before anything is read: the lexer, the parser, validation, the planner. Each can stop the line, and the first one that stops it prints the only error. Validation reads the parsed statement and the catalog, and never a page of the table, so however large the table is, a statement is rejected before any row is read.

explain is a planner, so it sits after validation too: a statement that would fail prints its code and no [PLAN] line. The checks are only the ones that can be decided from the statement and the catalog. An INSERT of a key the table already has is only found by reading the tree, so it is still the executor's 00701, and explain still prints the plan for it.

What changes: one order for every check

A statement can break several rules at once. insert into products (id, price, colour) values (1, 'x', 2); gives a string to an INT column and names a column that does not exist. It prints one error, and it must be the same one on every implementation, so the order of the checks is part of the contract.

The rule is to read the statement from left to right and check each piece fully before moving on. For an INSERT:

So (id, price, colour) with (1, 'x', 2) is 00403: price is checked, name and value, before colour is looked at. Swap the two, (id, colour, price) with (1, 2, 'x'), and it is 00402. The key comes after the columns, because an INSERT that leaves it out may still have broken something earlier. The size comes last, because it depends on every value.

UPDATE reads its assignments the same way: each one's column, then that it is not assigned twice, then that it is not the key (00801, whatever the key is called), then its value's type, range and length. Then comes its WHERE. SELECT checks the table, its select list, then its WHERE, and DELETE checks the table, then its WHERE. A WHERE checks its column, the value's type and, for an INT, its range.

A WHERE does not check a string's length. It stores nothing, so a string longer than its column only compares: where title = '<41 bytes>' matches no row, and where title < '<41 bytes>' matches every row that sorts before it.

PostgreSQL does the same checks in the same place, and more: it resolves functions, casts values between types, and expands * into the column list before planning. droid's types are too few to need casts, so a value of the wrong type is an error, not a conversion.

Output contract for this stage

The full definition is on the output contract page, sections 10, 12, 14 and 19. This stage adds no code: it applies the codes that users already had to every table, in one order.

A statement that fails prints its one line, and an optional HINT: line, and nothing else. It changes nothing, and explain prints no plan for it.

What you should see when done

On a new file:

Welcome to droid!
>create table products (id int, title varchar(40), price int);
Create table statement executed successfully
>insert into products (id, colour) values (1, 'red');
[ERROR:00402] Unknown column: colour
>insert into products (id, price, colour) values (1, 'x', 2);
[ERROR:00403] Type mismatch: price is INT
>insert into products (id, title) values (1, 'abcdefghijabcdefghijabcdefghijabcdefghijk');
[ERROR:00405] String too long: title is VARCHAR(40)
>insert into products (title) values ('lamp');
[ERROR:00406] Missing key column: id
>insert into products (id, price) values (1, 4294967296);
[ERROR:00404] Integer out of range: price
>select * from products;
id | title | price
(0 rows)
[EXEC] rows_examined=0
>insert into products (id, title, price) values (1, 'lamp', 30);
Insert statement executed successfully (1 rows)
>update products set price = 40, id = 2;
[ERROR:00801] Cannot update the key column: id
>explain select * from products where price = 'x';
[ERROR:00403] Type mismatch: price is INT
>select * from products where title = 'abcdefghijabcdefghijabcdefghijabcdefghijk';
id | title | price
(0 rows)
[EXEC] rows_examined=1
>select * from products;
id | title | price
1 | lamp | 30
(1 rows)
[EXEC] rows_examined=1
>.exit
exiting.. good bye!

Things to notice. Five rejected INSERTs left the table empty. The UPDATE did not set price to 40 before it reached id: the whole statement failed. explain printed an error and no plan. The 41-byte string that could not be stored could still be compared in a WHERE.

Common bugs in this stage

• Checking while doing. An executor that checks each value as it writes it has already written the first when the second fails. Check the whole statement, then run it.
• Checking all the names, then all the values. The order is per column: (id, price, colour) with (1, 'x', 2) is 00403, not 00402.
• The key checked first. 00406 comes after every column has been checked; an INSERT without the key and with a bad value reports the bad value.
• Limits from the program. A VARCHAR's limit is its own n: 40 for title, 5 for a VARCHAR(5), not 1000 or 27. An INT that is not the key has the key's range.
• Characters, not bytes. é is two bytes. A VARCHAR(40) holds 20 of them, not 40.
• Looking for id. The key is the first column. update orders set oid = 5 is 00801, and update orders set id = 5 is 00402 when orders has no id.
• Validating the WHERE like an INSERT. A string in a WHERE is compared, not stored, so its length is never an error.
• explain that plans first. A plan printed for a statement that cannot run tells the user it could.

Language notes

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

• C and C++: validation needs the parsed statement and a table's schema, and nothing else: give it no pager and no tree, and a check that reads a row cannot creep in. Return the first code found and let the caller print it.
• Rust: a validation step that takes the parsed statement and the catalog and returns Result<(), Error> makes the order of checks the order of ?s. A validated statement can be a type of its own, so the planner cannot be given one that skipped the step.
• Zig: an error union returned from the validation step does the same; the first try that fails is the one reported.