Support Analytics Console: A Small SQL Engine

Support Analytics Console: a small SQL engine

Support and ops staff slice ticket data in the Support Analytics Console, an internal tool that runs a small SQL-like language over in-memory tables (tickets, customers, agents, ...). You have inherited its engine. Last week it shipped wrong numbers into a weekly review (incident SUP-3318), and the roadmap behind the incident is long: reports need aggregates, then joins, and then someone will point the console at a real month of tickets.

Everything the console does is specified below. Where this document says "natural order" it means the order rows were loaded in.

Codebase

How your code is run

run(operations: list[list]) -> list executes the operations in order against a fresh engine and returns one result per operation. Errors are returned as "error: <message>" strings, never raised, and one failing operation does not stop the batch.

• columns is a list of [name, type] pairs with type "int" or "text". Cell values are an int, a string or null. Table and column names match [A-Za-z_][A-Za-z0-9_]*, are case-sensitive, must be unique within their scope and must not be a keyword (case-insensitive). Anything else is invalid table definition, as is a row of the wrong width or a value of the wrong type (true/false are not ints).
• generate builds one of customers, agents or tickets (see datagen.py) with 0 <= n <= 50000 rows from a seed. The generated data is a pure function of its arguments.
• An unknown operation name returns "error: unknown operation"; wrong argument count or types return "error: invalid arguments".
• Every table is read-only after loading.

Worked example used throughout this README: the console is loaded with

tickets(id int, customer_id int, agent_id int, priority int, status text, minutes int)
  1  10   7     1  open     NULL      4  12   8     3  solved   45
  2  10   NULL  3  open     NULL      5  13   NULL  1  pending  20
  3  11   7     2  closed   30        6  99   8     2  closed   75
customers(id int, name text, region text, tier text)
  10 Acme emea pro        12 Cobalt emea enterprise
  11 Birch amer NULL      13 Dune apac free

The language

SELECT ( * | item {, item} )
FROM table_ref
[ WHERE condition ]
[ ORDER BY column [ASC | DESC] {, ...} ]
[ LIMIT n ] [ ; ]

item      := column [AS alias]
table_ref := table [[AS] alias]
column    := name | qualifier.name

Lexical rules. Keywords are case-insensitive; identifiers are case-sensitive. Strings are single-quoted with '' for a quote. Integer literals are digits, optionally preceded by - inside a condition (-5); NULL is a literal. Operators: = != <> < <= > >= (<> means !=). Text compares by code point. The whole text is tokenized before parsing, so a lexical error (unterminated string, unexpected character '<c>') is reported before any syntax error that appears earlier in the text.

Conditions (WHERE), loosest binding first: OR, AND, NOT, then one predicate. AND and OR are left-associative; parentheses group. A predicate is operand cmp operand, operand IS [NOT] NULL or operand [NOT] IN (literal, ...). An operand is a column or a literal. NOT a = 1 means NOT (a = 1).

NULL and three-valued logic. A condition evaluates to *true*, *false* or *unknown*. Comparing with NULL (including NULL = NULL) is unknown. x IN (...) is unknown when x is NULL; otherwise true if x equals a non-NULL list item, else unknown if the list contains NULL, else false. NOT IN is NOT (IN). IS [NOT] NULL is never unknown. AND: false if either side is false, else unknown if either is unknown, else true. OR: true if either is true, else unknown if either is unknown, else false. NOT unknown is unknown. WHERE keeps a row only if its condition is true, so a row is never kept just because the condition is not false.

ORDER BY. Keys apply left to right, ASC by default. NULL sorts before every value: first when ascending, last when descending. Rows that tie on every key keep their earlier relative order. A bare name in ORDER BY that equals a select-list alias refers to that select item (the alias wins over a column of the same name); otherwise it is a column, which need not be selected. Without ORDER BY, rows come out in natural order. LIMIT n keeps the first n rows after ordering (LIMIT 0 is empty).

Output. SELECT * yields the table's columns in declaration order under their own names. A select item's column header is its alias if it has one, otherwise its text as written without spaces (id, t.id).

Names and types. A column may be qualified with the table's label: its alias if it has one, otherwise its name (once a table has an alias its own name is no longer a valid qualifier). Comparing an int with text, in any predicate, is a planning error; a NULL literal compares with anything. In the message each operand is shown in canonical form: a column as written (t.id), a text literal in single quotes ('a'), an integer as digits, an aggregate as in Part 2. For IN, the left side is the operand and the right side is the first list item whose type differs. Aggregate types (Part 2): COUNT, SUM, AVG are int; MIN/MAX have their argument's type.

Errors. Messages are built in errors.py. Check order for a query: tokenizing, then parsing (first problem left to right), then planning: the table(s) in FROM order (unknown table), then names and types in clause order: select list, WHERE, ORDER BY, each left to right (unknown column '<as written>', type mismatch: <left> is <type> but <right> is <type>). Unsupported constructs from later Parts currently answer error: not supported.

Part 1 - Stop the wrong numbers (incident SUP-3318)

Goal: make single-table queries follow the rules above. Four reports from the weekly review:

1. "WHERE status = 'open' OR priority = 1 AND agent_id IS NULL returns tickets that are not open and have an agent."
2. "WHERE NOT status = 'closed' AND priority > 2 is returning the wrong tickets."
3. "Tickets with no agent never show up for agent_id != 7, which is right, but they show up for NOT agent_id = 7." Plus: NOT IN against a list that contains a NULL still returns rows.
4. "Sorting by minutes puts the unfinished tickets at the wrong end, and after one of those queries the *next* query without any ORDER BY comes back in a different order."

On the worked-example data, SELECT id FROM tickets WHERE status = 'open' OR priority = 1 AND agent_id IS NULL returns {"columns": ["id"], "rows": [[1], [2], [5]]} and SELECT id FROM tickets WHERE NOT agent_id = 7 returns rows [[4], [6]].

tests/test_engine.py has failing examples. Parts 2-4 are not required for Part 1.

Part 2 - Aggregates (reporting request SUP-3340)

Goal: add GROUP BY, aggregates and HAVING.

item     := column [AS alias] | aggregate [AS alias]
aggregate := COUNT(*) | COUNT(column) | SUM(column) | MIN(column) | MAX(column) | AVG(column)
SELECT ... FROM ... [WHERE ...] [GROUP BY column {, column}] [HAVING condition] [ORDER BY ...] [LIMIT n]

Function names are case-insensitive (count(*) is fine; any other name is unknown function '<as written>'); only COUNT accepts * (only COUNT accepts *). An aggregate may also appear in HAVING (as an operand) and ORDER BY.

Rules

1. A query is *grouped* if it has GROUP BY or HAVING or uses an aggregate anywhere. WHERE runs first, on input rows, and may not contain aggregates (aggregate not allowed in WHERE). Rows are then grouped by the GROUP BY columns; with no GROUP BY the whole filtered input is one group, even when it is empty (with GROUP BY and no input rows there are no groups). NULL group keys form one group. Groups are produced in order of first appearance; ORDER BY then applies to the groups.
2. In a grouped query every select item, HAVING operand and ORDER BY key that is a column must be one of the GROUP BY columns (the same column however it is spelled: status, t.status), otherwise column '<as written>' must appear in GROUP BY. SELECT * is not allowed: select * is not allowed in an aggregate query. HAVING and ORDER BY may use aggregates that are not selected. Select aliases are usable in ORDER BY only, not in HAVING.
3. COUNT(*) counts rows in the group. COUNT(col) counts rows where col is not NULL. SUM, MIN, MAX, AVG ignore NULL inputs; with no non-NULL input they are NULL (COUNT is 0). SUM and AVG need an int column (SUM requires an integer column); MIN/MAX work on either type (text by code point).
4. AVG is the exact mean rounded to 2 decimal places, halves away from zero (1/8 -> 0.13, -1/8 -> -0.13, 3/8 -> 0.38), returned as a number such as 52.5 or 20.0. Do not round with floats: round() rounds halves to even and float means are not exact.
5. Header of an aggregate without alias: the function name in upper case and its argument as written, no spaces: COUNT(*), SUM(t.minutes). HAVING keeps a group only if its condition is true. Aggregates that appear more than once are one aggregate.
6. Error order extends the Part 1 order: after names and types in clause order, GROUP BY columns are resolved after WHERE and before HAVING, and the grouping errors in rule 2 come only after every name has resolved (select list first, then HAVING, then ORDER BY).

Example

SELECT status, COUNT(*) AS n, COUNT(minutes), SUM(minutes), AVG(minutes)
FROM tickets GROUP BY status HAVING COUNT(*) >= 1 ORDER BY n DESC, status

returns columns ["status", "n", "COUNT(minutes)", "SUM(minutes)", "AVG(minutes)"] and rows [["closed", 2, 2, 105, 52.5], ["open", 2, 0, null, null], ["pending", 1, 1, 20, 20.0], ["solved", 1, 1, 45, 45.0]].

Part 3 - Joins (reporting request SUP-3377)

Goal: add INNER JOIN and LEFT JOIN (and plain JOIN, which is INNER) with any number of joins.

FROM table_ref { [INNER | LEFT] JOIN table_ref ON condition }

Rules

1. Joins are applied left to right. For each row so far, in order, every row of the new table, in its natural order, for which the ON condition is true forms a combined row. An INNER join drops rows with no match; a LEFT join keeps such a row once, with all of the new table's columns NULL.
2. ON may be any condition over the tables joined so far plus the new table (a column of a table that appears later is unknown column). Aggregates are not allowed in ON (aggregate not allowed in ON).
3. WHERE runs after every join. So a LEFT JOIN with a filter in ON keeps the left row (null-extended) when the filter fails, while the same filter in WHERE drops it. NULL = NULL is not a match.
4. Each table reference has a label (alias, else table name); labels must be unique (duplicate table alias '<label>'), so a table can be joined to itself under two aliases. A qualified column uses a label; a bare column must exist in exactly one joined table (unknown column / ambiguous column '<name>'). Qualifier problems are unknown column '<qualifier>.<name>'.
5. SELECT * lists every table's columns in join order. With a single table headers are bare column names as before; with joins every header is label.column.
6. Error order: all tables first (for each reference, unknown table then duplicate table alias), then names and types in clause order SELECT, each ON in join order, WHERE, GROUP BY, HAVING, ORDER BY.
7. Everything from Parts 1 and 2 keeps working on joined rows (NULL logic, grouping, COUNT(col) on null-extended rows, ORDER BY, LIMIT).

> The Part 1 executor keeps one {column: value} dict per row, keyed by bare column name. That cannot

> represent two tables that both have an id. Expect to change how rows are represented and how names are

> resolved, not just add a loop.

Example

SELECT t.id, c.name FROM tickets t LEFT JOIN customers c ON t.customer_id = c.id AND c.region = 'emea'

returns headers ["t.id", "c.name"] and rows [[1, "Acme"], [2, "Acme"], [3, null], [4, "Cobalt"], [5, null], [6, null]]. The same query with AND c.region = 'emea' moved into WHERE returns only [[1, "Acme"], [2, "Acme"], [4, "Cobalt"]].

Part 4 - Run it on a real month of tickets (stretch)

Goal: the console is now pointed at generated tables of several thousand tickets joined to a couple of thousand customers and a few dozen agents. A join that compares every pair of rows will not finish.

Rules

1. Results, including row order and headers, must be identical to what Part 3's rules define; this Part changes how fast queries run, not what they return.
2. Join conditions that are conjunctions (AND) containing equalities between a column of the rows so far and a column of the joined table are the common case at scale. The rest of ON may be anything and must still behave exactly as in Part 3.
3. Hidden tests run several queries per run on tables generated with generate (including three-way joins, LEFT joins where many tickets match no customer, and NULL join keys) and the whole suite has a few seconds.
4. Don't change what a query means to make it fast: where a condition is evaluated (ON vs WHERE) and how NULLs compare are part of the rules.

Notes

• catalog.py, tokenizer.py, datagen.py, formatter.py, engine.py and api.py are known-good.
• You may use the AI assistant freely; you are expected to verify what it produces.
• Later Parts must not break earlier ones; hidden tests re-check earlier behaviour.
• Parts 1-3 is a strong result. Part 4 is a stretch.