Semantics and data rules

NULLs and logic

• filter, having, and join conditions keep only rows where the condition is TRUE; FALSE and NULL drop the row. Use is null rather than = null.
• and/or follow SQL three-valued logic (null and false is false, null or true is true).
• COUNT(*) counts rows; COUNT(expr) counts non-NULL values. SUM, AVG, MIN, and MAX ignore NULL inputs and return NULL for an empty aggregate input.
• Sorting NULLs follows DuckDB: they order as the largest values (last when ascending, first when descending).

Division and numeric types

• / returns a number (float) for numeric operands, matching DuckDB: 7 / 2 is 3.5 (and -7 / 2 is -3.5); % is integer remainder for integer operands.
• PostgreSQL, Redshift, and SQLite truncate integer division to 3; when porting SQL that relied on truncation, convert explicitly — there is no integer-division operator yet.

CSV loading and type inference

Before loading a CSV, the compiler scans the header and all rows itself, then tells DuckDB the inferred types. Inference is per column, not per cell:

• a column whose non-empty values are all integers becomes BIGINT; all numeric (including decimals) becomes DOUBLE;
• a leading-zero integer such as 02134 or 00501, a mixed/dirty value, or any non-numeric text keeps the whole column VARCHAR;
• ISO YYYY-MM-DD and timestamp columns infer as DATE/TIMESTAMP;
• empty/whitespace-only cells become NULL; literal null, none, and nan strings stay text;
• duplicate (case-insensitive) headers and rows with the wrong field count are rejected with an error naming the file.

Override inference when needed:

unsequel run query.unsequel --delimiter ';' --column-type orders.postal_code=text

Types accepted for --column-type: text, integer, number, boolean, date, time, timestamp, and DuckDB decimal(p,s). Parquet input does not accept overrides, and the delimiter option applies to every CSV loaded in that command.

Column naming and ambiguity

• Qualified names use the source label: the alias if given, otherwise the last segment of the table name.
• After a join, a bare name that exists on both sides is an error — qualify it. select * exposes a colliding right column as <label>_<column>.
• select *, name = expression is allowed, but the added name must not duplicate a live column.
• Projections replace the addressable scope: after select order_id, a later stage addresses order_id, not orders.order_id.
• A group by key can be a bare column or name = expression; qualified group keys are written as named expressions.

Sort order and pagination

• sort records an internal row ordinal. Filters, derives, windows, joins, projections, and pagination preserve it; the final query orders by that ordinal, then removes it from visible output.
• group by, distinct, set operators, and sql reset the ordering boundary. Add a final sort when output order matters.
• Pagination applies against the established order; without a sort, which rows take returns is engine-defined.
• ORDER BY ordinals are not part of the language; sort by a name or expression listed at that point.

Set operators and nested programs

• union deduplicates, union all keeps duplicates; intersect and except follow SQL. Both sides must have the same column count (checked unless a side is sql-opaque); names and order follow the left side.
• with definitions are program-scoped, may reference earlier definitions, and are resolved before schema tables in from/join.
• Subquery sources must be aliased; the analyzer computes the alias's column families, so type checks behave like a table.

The sql hatch

• One query per stage, starting with SELECT or WITH; ; outside strings/comments is rejected.
• The previous relation is __input__; named tables remain visible. Substitution never touches string literals or comments.
• Its output columns are opaque: later stages are not schema-checked, and join after a hatch is rejected.

Engine boundaries

The production compiler targets DuckDB only. Type errors, unsupported expressions, and runtime data problems surface as clean error: lines. Known boundaries relative to standard SQL and to the Python prototype:

• DuckDB is the execution engine, so function and type behavior is DuckDB's, not a portable ANSI subset.
• Floating-point division by zero can yield infinity in DuckDB while the Python memory engine returns NULL; JSON numbers are rendered differently (for example 192.0 vs 192).
• Ties in ORDER BY are engine-defined; add a unique tie-breaker key when deterministic output matters.
• LIKE is case-sensitive in DuckDB and in the Python engine; the two engines are intended to agree on the shared subset, with conformance probes in the repository.