SELECT with WHERE: Projection and Selection

After this lesson your engine answers SELECT ... FROM table [WHERE ...]: it prints a header line, then the rows that pass the filter, showing only the requested columns. Two independent ideas are at work, and keeping them apart makes the code short.

Projection: which columns

The select list is * or names, mixed freely. Resolve each name to a column position once, before looking at any row:

SELECT b, a, b FROM t        columns resolved to positions   [1, 0, 1]
SELECT b, * FROM t           * expands in place              [1, 0, 1]   (t has columns a, b)

The header repeats the names as resolved (b|a|b), and every row is printed by picking those positions, so a column may appear twice or in any order. An unknown name is ERR unknown column: <name>, and finding it before the first row is printed means an error never follows half a result.

Selection: which rows

A WHERE clause is tested against each row independently. In this lesson it is one or more comparisons joined by AND:

comparison := operand op operand        operand := column name | literal
op         := =  !=  <>  <  >  <=  >=

Either side can be a column or a literal, so a < b, a < 5 and 5 > a are all valid. A row is kept if every comparison is true.

Comparing values

An unknown comparison never keeps a row. That has two surprising consequences: a = NULL matches nothing, not even rows where a is NULL, and a != 1 does not match rows where a is NULL. (Lesson 7 returns to this when OR and NOT arrive.) The way to ask for NULLs is IS NULL, also in lesson 7.

The order of the checks

Parse the whole statement first, then check names in the order they appear: the table, then the select list, then the WHERE clause. The WHERE clause is checked even when the table has no rows, so a typo is reported rather than hidden by an empty table.

SELECT zz FROM t WHERE yy = 1     ->  ERR unknown column: zz   (select list before WHERE)
SELECT a FROM ghost               ->  ERR unknown table: ghost
SELECT FROM t                     ->  ERR syntax error

Scan, then print

With names resolved, the executor is a loop: for each stored row, evaluate the WHERE; if it is true, print the projected columns joined by |. Every row is visited, however few match. Lesson 6 makes that cost visible. If nothing matches, the header is still printed: an empty result is not an error.

Common mistakes

• Comparing printed text. 10 > 9 must be numeric.
• Letting NULL match. WHERE a = NULL returns the header only.
• Resolving names per row. Resolve once, before the loop; it is faster and gives errors up front.
• Dropping the header when nothing matches.

Your exercise

The starter has the lexer, the catalog, INSERT, a token cursor, and the SELECT driver. You complete the pieces it calls: col_index (name to position), the typed comparison cond_eval, and the AND-chain parse, bind and evaluate functions. The mistake to avoid: treating NULL as an ordinary value.