Skip to content

Version 0.9.0

Latest

Choose a tag to compare

@jgarzik jgarzik released this 12 Oct 09:31
39ee49a

sqawk 0.9.0

This release is a correctness and data-safety overhaul. Query results now follow SQLite 3.45 semantics, verified by differential testing of hundreds of thousands of random queries against SQLite. --write never alters a row you didn't change. Several behaviours change from 0.8.2; read Breaking changes before upgrading.

Install: cargo install --locked sqawk (Rust 1.88 or newer).

Highlights

  • Correct answers. 0.8.2 gave silently wrong results for many queries. Examples:

    • WHERE was ignored on outer joins.
    • NOT IN was wrong when NULLs were involved.
    • Uncorrelated subqueries were evaluated by a separate interpreter that treated unknown operators as true.
    • ORDER BY and LIMIT were ignored on UNION.
    • SUM over no rows returned 0 instead of NULL.

    SELECT is now compiled by one pipeline (FROM planner, WHERE, aggregation, windows, output stage), with one expression compiler and SQL three-valued logic everywhere.

  • Safe writeback. Rows a statement does not change are written back byte for byte:

    • leading zeros (007), number spellings (1.50), quoting, BOM, CRLF, # lines, blank lines, ragged rows and invalid UTF-8 are all preserved;
    • a no-op UPDATE ... --write leaves the file identical;
    • a read-only target aborts the whole writeback before any file is written.
  • New SQL:

    • common table expressions (WITH, WITH RECURSIVE)
    • transactions (BEGIN / COMMIT / ROLLBACK)
    • JOIN ... USING, NATURAL JOIN, and chains of outer joins
    • aggregates inside expressions (ROUND(AVG(x),1), SUM(CASE ...)), GROUP BY expressions and positions, ORDER BY aliases, FILTER, NULLS FIRST/LAST
    • window frames, NTILE/FIRST_VALUE/LAST_VALUE/NTH_VALUE/PERCENT_RANK/CUME_DIST
    • hex literals, bitwise operators, and many SQLite functions
  • Speed. Hash joins replace nested loops on equality joins; a 200k×1k join dropped from about 29 s to 0.06 s. Correlated subqueries use hash lookups, and window frames, wide tables and lazily computed CTEs now run in linear time.

  • No unsafe code. The crate has #![forbid(unsafe_code)]; Miri reports no undefined behaviour.

Breaking changes

Files and output

  • Header line: .tsv/.tab files are tab-separated; every other file, files with no extension and stdin are comma-separated unless -F is given. The first line is always the header unless --no-header or --tabledef is given. The old guess, which decided whether a non-.csv first line was data, is gone.
  • # lines are data unless --comments is given. With it they are skipped and written back in place.
  • Type inference: a field loads as a number only if its text round-trips exactly. 007, 1.50, 1e3, 5 and true stay text, but they still compare and do arithmetic as numbers.
  • Output:
    • NULL prints as an empty field, not NULL.
    • Output is RFC 4180 CSV with proper quoting.
    • Reals print like SQLite's CAST(x AS TEXT): 15 significant digits, .0 on whole values (AVG → 70000.0), exponent form for very large or very small values (1.5e+300).
  • Result column names follow SQLite: a column reference is named by its bare column name (u.name → name), SELECT * over a join gives bare names, and an expression is named by its SQL text (salary*2, COUNT(*)).
  • Errors are one line on stderr, sqawk: <message>, with exit status 1 (usage errors 2). The SQL echo and the "Caused by" chain are gone.
  • -v writes debugging output to stderr; its format is not stable.

SQL semantics (now SQLite's)

  • Arithmetic: integer arithmetic is exact 64-bit and overflow becomes a real; 5/2 is 2, and dividing by zero gives NULL. ROUND with negative digits treats them as 0.
  • Strings and comparison:
    • LIKE is ASCII case-insensitive.
    • || with NULL gives NULL.
    • LENGTH, SUBSTR and INSTR count characters.
  • Parsing:
    • Operator precedence is SQLite's.
    • Compound SELECTs are evaluated left to right.
    • An integer ORDER BY or GROUP BY term is a column position only when it fits in 32 bits.
  • Numbers vs number-looking text: one rule everywhere. Text that reads as a number compares as that number in =, <, IN, joins, ORDER BY and MIN/MAX; DISTINCT and GROUP BY still keep '1' and 1 apart.
  • Unknown names: an unknown table qualifier (zz.k) or schema (foo.t) is an error; main.t works.
  • Unsupported syntax is rejected with an error instead of being silently ignored. Examples: RETURNING, ON CONFLICT, UPDATE FROM, DELETE USING, unknown CREATE TABLE options, STORED AS PARQUET.
  • CREATE TABLE t (...) LOCATION 'path' on an existing file now reads that file under the declared columns. In 0.8.2 it ignored the file's rows and overwrote the file on --write.
  • Some deliberate differences from SQLite remain; all are listed in doc/sql_reference.md under "Differences from SQLite".

Command line and REPL

  • -i cannot be combined with -s or -f.
  • REPL with piped input:
    • stdout carries only results;
    • an error makes the exit status 1;
    • .exit N sets the exit status;
    • an unterminated last statement still runs.
  • History is kept in $XDG_STATE_HOME/sqawk/history or ~/.sqawk_history, and only when input is a terminal. 0.8.2 wrote ./.sqawk_history in the current directory.
  • Table specs: a name=path prefix counts only when the name is a plain identifier. Loading the same file twice is an error.

Packaging

  • tsq is no longer installed by cargo install sqawk; it is a development tool in the source tree (cargo run -p tsq).
  • The crate ships only the sqawk binary; the unintended library target is gone.

Added

  • Command-line flags:
    • -f FILE / -f -, which read SQL from a file or stdin, in order with -s
    • --output-delimiter and --no-output-header
    • --field-separator (long form of -F), --no-header and --comments
  • SQL:
    • CTEs (WITH, WITH RECURSIVE, [NOT] MATERIALIZED), BEGIN/COMMIT/ROLLBACK, USING/NATURAL joins, multiple and chained outer joins, expressions and aggregates in join select lists
    • window frames (ROWS, and RANGE with UNBOUNDED/CURRENT ROW), named windows, windows over joins and grouped results
    • FILTER on aggregates, NULLS FIRST/LAST, IS [NOT] DISTINCT FROM, generic x IS y
    • CREATE TABLE ... LOCATION over existing files with WITH (HEADER=..., COMMENTS=..., DELIMITER=...); declared column types are kept and shown in .schema
  • Functions: IFNULL, IIF, INSTR, TYPEOF, LPAD/RPAD, GROUP_CONCAT/STRING_AGG, TOTAL, SIGN, multi-argument MIN/MAX, HEX, CHAR, UNICODE, PRINTF/FORMAT (a port of SQLite's printf), QUOTE, CONCAT_WS, TIMEDIFF, and the full SQLite date/time modifier set including localtime/utc (via chrono).
  • REPL: tab completion for keywords, functions, tables and columns.
  • tsq (development tool):
    • gen, diff (random SQL against a bundled SQLite 3.45 oracle, with shrinking) and corpus
    • wb (writeback invariants, including kill-during-write atomicity)
    • cli (CLI and REPL surface fuzzing) and stress (time and memory baselines)

Fixed

  • Wrong answers: every defect found by three review rounds and by differential testing against SQLite. That is hundreds of red-list tests, all passing.
  • Data loss:
    • writeback rewrote untouched cells (01234 → 1234, doubled quotes, NULL text, dropped # rows);
    • CREATE TABLE ... LOCATION overwrote existing files;
    • REPL .cd then .save wrote to the wrong file.
  • Crashes: stack overflow on long expressions (SQLite's depth limits now apply), panics on huge printf widths, date modifiers and SUBSTR lengths, and a panic on a closed stdout or stderr.
  • Performance:
    • quadratic time in correlated EXISTS, IN-subquery filters, window frames running to UNBOUNDED FOLLOWING, wide tables (a 1000-column SELECT * went from 12.8 s to 0.08 s), and CTEs computed when never read;
    • LIKE no longer compiles a regex for every row.
  • Line numbers: load-error line numbers in CRLF files.
  • Windows: error messages no longer show the \\?\ path prefix.

Removed

  • The REPL's regex dependency, the hand-written mmap CSV parser and its unsafe code, the header-guessing heuristic, and the Python test tools (replaced by tsq).