rdbms-playground/docs/simple-mode-limitations.md

Simple-mode query limitations

Simple mode's DSL query surface is deliberately a subset of SQL. The DSL is a teaching on-ramp; advanced mode (raw SQL) is the full surface. This document is the running list of what a simple-mode query cannot express that advanced-mode SQL can.

It serves two audiences:

• Students — each entry is the seed of a short explanation of why the boundary exists and what to use instead (often: switch to advanced mode).
• Designers — the consolidated list feeds the future Q4 SQL-subset specification: the inverse view of what the supported subset deliberately leaves out.

The list grows as new simple-mode surface lands; each entry names the ADR that drew the boundary.

WHERE expressions (ADR-0026)

• Comparison operands are a column or a literal, not a nested expression. (a > b) = (c > d) — comparing two boolean sub-expressions — cannot be written. Parentheses group boolean sub-expressions, not comparison operands.
• A bare column is not a boolean. A predicate always has an operator: write Active = true, not Active.
• No arithmetic in expressions (Price * 1.1).
• No string concatenation.
• No scalar functions (upper(Name), length(x), …).
• No subqueries, and no EXISTS.

Query shape (ADR-0026)

• No ORDER BY. show data … limit <n> orders implicitly by the primary key; explicit ordering is not yet available.
• No LIMIT … OFFSET — limit takes a row count only.

Table creation (ADR-0029)

• A simple-mode table always has a primary key; an advanced-mode table need not. create table … with pk … is mandatory in simple mode (ADR-0029) — the bare with pk even defaults to id serial. Advanced-mode SQL follows standard SQL and permits a PK-less table: create table t (a int) declares no primary key. This is not a storage problem — every ordinary table (STRICT included) carries SQLite's implicit rowid, which keys it internally; only a WITHOUT ROWID table (which this app never creates) would lack one. So the simple-mode requirement is a pedagogical boundary (teach that tables should have a key), not an engine constraint. Consequences in a PK-less table, all handled: show data … limit falls back to rowid order (no stable user-facing key to order by); update / delete still target the affected rows by rowid; and there is no "PK column" to drop — dropping a declared PK column is refused in both modes (the shared do_drop_column guard: "cannot drop primary-key column …").
• create table declares only primary-key columns. create table T with pk … makes every listed column part of the primary key; there is no simple-mode syntax for a non-PK column in the same statement. Non-PK columns are added afterward with add column. Creating a table with a mix of PK and non-PK columns in one statement needs advanced-mode CREATE TABLE syntax.
• check (<expr>) constraints reuse the WHERE-expression grammar (ADR-0026), so the same limits apply: no scalar functions (length(x)), no arithmetic. A check is a boolean combination of column-vs-literal comparisons, LIKE, IN, BETWEEN, and IS NULL.