Query.Structured: a columnar, indexed in-memory query engine — Design

← All docsdocs/QUERY_DESIGN.md on GitHub

core/compiler/lib_src/query.obs, built into core/lib/query.obl. Pure Objeck; no native module. This builds on the correctness fixes in #1291 (numeric aggregates, CSV typing, a stable order by, parser precedence, deletes) and assumes it has merged.

Why

Query.Structured exists to make structures that are not tables queryable as tables: a filesystem through FromFilesystem and Finder, a CSV file through FromCsv, and in intent other hierarchical or relational data (section 6). Research code needs the same thing: load a CSV or two, or a tree of result files, then filter, join, group, aggregate and hand a matrix to System.ML. Nothing in the standard library does that well. Query.Structured is the closest, and on 2026-10-22 it was timed at 100,000 rows × 3 columns (Windows x64, -opt s3, after the

#1291 fixes), against the same data in plain arrays:

OperationPlain arraysQuery.Structured
Sum a column0.001 s0.051 s
where value > 0.5 (≈50k rows)0.003 s0.558 s
where grp = 'g7' (2k rows)0.115 s0.114 s
order by value—3.16 s
Insert 100k rows—0.98 s
10,000 Get(pk)—0.032 s

The costs are structural:

  1. Every query copies its result. A where builds a new Table: a new primary key, a new map entry and a new Compare[] for every matching row, each cell set through a by-name lookup. That is about 10 µs a row and most of the 0.56 s.
  2. Every cell is an object. Numbers are boxed IntRef/FloatRef in a per-row Compare[]; each row is an object on a doubly linked list and a node in a tree map. Sum chases pointers and type-tests 100,000 times instead of looping over a Float[], and the collector traces several objects per row.
  3. Queries are interpreted every time. The statement is re-parsed on each call, each row walks the expression tree through Compare dispatch, and with no indexes every equality filter scans the table.

It is also only a single table: no joins, no GROUP BY, no LIMIT, no descending order.

Goals

Not goals

Decision: a pure-Objeck engine, not one backed by SQLite

Running SQLite underneath the Query.Structured front end was considered and rejected (2026-10-23). It would have brought joins, grouping, B-tree indexes, transactions, persistence and full SQL at once, and removed most of the phases below. It was not chosen, for four reasons:

Choosing a pure-Objeck engine does not mean giving up persistence. Section 9 adds a native file format with fast save and load, partial reads, appends, crash-safe saves and a change log, which covers a research program's own data. SQLite remains the right tool where several processes write the same data, or where transactions must span tables, as a separate API.SQLite binding (see "Not goals"). It can later be attached as a source like any other.

Design

1. Storage: one typed column per field

Table
  @columns : Column[]            # in declaration order
  @names   : Hash<String, IntRef> # name -> column index, resolved once per query, never per row
  @size    : Int                 # rows ever appended
  @deleted : Bitmap / Nil        # tombstones, Nil until the first delete
  @indexes : Index[] per column  # see section 5

Column is abstract, with one subclass per storage type:

ColumnStorageHolds
IntColumnInt[] + capacityInt, Bool, Char, Byte
FloatColumnFloat[]Float
StringColumnString[]String
DateColumnInt[] of epoch secondsDate
ObjectColumnCompare[]anything else that implements Compare

Every column has a null bitmap (Int[], 64 rows a word) allocated only when the first Nil is stored. Arrays grow by doubling; Compact() trims them.

Typing. A column is declared (Table->New("t", ["id" : ColumnType->INT, ...]), or the AddColumn(name, type) form) or inferred from its first non-Nil value. Storing an Int into a FloatColumn widens it; storing a Float into an IntColumn promotes the column to FloatColumn once (a single array conversion); anything else incompatible promotes to ObjectColumn. Promotion is recorded so a test can assert on it. FromCsv types each column by scanning it with the #1291 rules (a column of numbers is IntColumn or FloatColumn, otherwise StringColumn), not cell by cell.

Why this is fast in Objeck specifically. A loop over Float[] is the code the JIT compiles best, with no allocation in the loop. Large arrays are allocated in the old generation, so a million-row column costs the collector one object, not a million. Boxed values appear only at the boundary (Row->Get returning Compare) and never inside scans.

2. Results are views

A query returns a View: the source table, the projected columns, and an Int[] of row numbers (or Nil for "all rows, in order"). Nothing is copied. Chaining (Where then OrderBy then Limit) only ever replaces the row-number array.

View
  @table   : Table
  @columns : Int[]   # projected column indexes
  @rows    : Int[]   # selected row numbers, Nil = every live row

View->ToTable() materializes when the caller wants an independent copy; Count(), iteration, aggregates, ToMatrix, ToCsv and ToJson read through the view directly. A view taken before a mutation of its table is invalidated (a version counter on the table; the view refuses with an error rather than returning stale or out-of-range rows).

3. Compiled predicates

A where clause is parsed once into a tree of typed nodes that are resolved against the table before any row is read:

Predicate      Test(row : Int) ~ Bool
  FloatCompare   column array + operator + constant   ->  values[row] > 0.5
  IntCompare, StringCompare, DateCompare, Like, IsNull
  And, Or, Not

Column names, types and constants are resolved at compile time, so evaluating a row is an array read and a comparison: no name lookup, no boxing, no Compare dispatch. A comparison between a numeric column and a numeric constant of the other kind converts the constant once. Comparisons involving Nil follow one simple rule, kept from #1291: every comparison with Nil is false, and IS NULL / IS NOT NULL test for it (no three-valued logic).

Evaluation is column-at-a-time where possible: an And of simple comparisons filters the row-number array one comparison at a time, starting with the most selective index-backed term (section 5), so later terms see fewer rows.

Prepared statements. Table->Prepare(sql) returns a Statement with ? placeholders; Bind(i, value) then Execute() reuses the compiled tree. Table->Query(sql) is Prepare plus Execute with a small per-table cache of recent statements, so a query in a loop is parsed once.

Method-chaining API. The same nodes are reachable without SQL, so the compiler checks column expressions and the language server can complete them:

rs := table->Where(Col("value")->Gt(0.5)->And(Col("grp")->Eq("g7")))
           ->OrderBy("value", Order->DESC)
           ->Limit(100)
           ->Select(["id", "value"]);

4. The SQL subset

SELECT [DISTINCT] (* | expr [AS name], ...)
FROM table [[INNER | LEFT] JOIN table ON condition ...]
[WHERE predicate]
[GROUP BY col, ... [HAVING predicate]]
[ORDER BY col [ASC | DESC], ...]
[LIMIT n [OFFSET m]]

INSERT INTO table [(cols)] VALUES (...), ...
UPDATE table SET col = value, ... [WHERE predicate]
DELETE FROM table [WHERE predicate]
CREATE TABLE name (col type, ...)
CREATE INDEX ON table (col) [USING HASH | SORTED | BTREE]

Expressions in the select list. expr is a column, a number, or arithmetic over them: + - * / %, with *, / and % binding tighter, unary minus and parentheses (select id, score * 100 as pct, (hi - lo) / 2 from runs). Values follow the aggregates' rule of reading numbers only:

Expressions are computed after WHERE, over the rows that pass, before DISTINCT, ORDER BY and LIMIT. WHERE, ON, ORDER BY, GROUP BY, HAVING and a grouped query's select list take expressions too ("Expressions in every clause" below). Every statement that fails returns Nil with GetError set; a program that calls a method on that Nil gets the language's ordinary NilError, which a ?-> region recovers from.

Aggregates: COUNT(*), COUNT(col), SUM, AVG, MIN, MAX, plus MEDIAN and STDDEV because research code wants them; numeric aggregates skip non-numeric and Nil cells, as #1291 made them. Tables are named in a Catalog (catalog->Add(table)), which Query resolves FROM and JOIN against; a single table's own Query keeps working without one.

Joins are hash joins on equality: build a hash of the smaller side's key column once, probe it with the other, emit pairs of row numbers. A join result is a JoinView holding two row-number arrays, not copied rows.

Group by hashes the group columns to group ids in one pass, then runs each aggregate over an Int[] of group ids in a second pass: two linear scans, no per-group sub-tables.

Order by sorts the row-number array with a stable merge sort over typed comparators (one per key column, resolved once), so it never compares boxed values. LIMIT n with ORDER BY uses a bounded heap instead of a full sort.

5. Indexes

Indexes are optional, per column, and always correct: every mutation updates them, or marks them stale to be rebuilt on the next query that uses them (the choice is per index kind, below).

KindStructureServesMaintenance
Hashvalue -> row numbers=, IN, joins, DISTINCT, GROUP BYupdated on every insert, update and delete
SortedInt[] of row numbers ordered by the column<, <=, >, >=, BETWEEN, ORDER BY, MIN/MAXrebuilt lazily after mutations (an O(n log n) sort)
B+ tree (phase 8)in-memory B+ tree, leaves are Int[] blocks of row numbers in key orderthe sorted index's queries, with O(log n) insertsupdated on every mutation

The sorted index is the right default for research data, which is mostly loaded once and queried many times: a binary search finds a range's start and end, and the range is already a row-number array in order, so it feeds a view with no further sort.

The B+ tree is in scope as the sorted index's upgrade, behind the same Index interface, for tables that take frequent inserts mixed with range queries, where re-sorting after every batch stops being cheap. In memory its advantage over the existing balanced Collection.Map is that leaves are contiguous Int[] blocks (fan-out around 64), so a range scan walks arrays rather than one tree node per entry, and the collector sees one object per block. It is built only when a benchmark shows a workload that needs it.

The planner is rule-based: for an And of terms, use the index of the most selective index-backed term (equality on a hash index, then a range on a sorted or B+ tree index), then filter the remaining terms over that candidate set. Without a usable index, scan.

6. Sources: making other structures queryable

This is the library's original purpose, and Finder and FromFilesystem are its first two instances: take a structure that is not a table, such as a directory tree, a document or a data file, and present it as tables the engine can query. Today they are two unrelated pieces. FromFilesystem lists one directory, without recursing, into six columns. Finder walks a tree matching a regular expression against full paths, but returns a Vector<String> instead of a table, so its results cannot be queried. use Data.XML is imported with no code behind it. The redesign makes sources one concept.

The interface. A Source declares its tables and columns, then fills them:

interface Source {
  method : virtual : public : GetTables() ~ TableSpec[];          # names, columns, types
  method : virtual : public : Load(catalog : Catalog, hint : ScanHint) ~ Bool;
}

ScanHint carries what the query already knows, so a source can push work down instead of materializing everything. Examples: the columns it needs (a filesystem source skips the owner lookup no query asked for), a filter it can apply cheaply (skip descending into directories a path LIKE 'src/%' excludes, or test the file name before calling stat), and a row limit. Pushdown is optional: a source that ignores the hint is still correct, because the engine applies the full predicate afterwards. Programs implement Source for their own structures; the library ships the common ones.

Hierarchies become tables with parent keys. A tree maps to rows with id, parent_id, depth and path columns (an adjacency list plus a materialized path). Once phase 4 adds joins, parent and child questions become self-joins, and subtree questions become path LIKE 'a/b/%'. Nested repeated data goes into a child table keyed by its parent's id, so one document can become several related tables that join back together.

SourceTables and columns
FilesystemSource(root, recursive, max_depth)files: id, parent_id, depth, name, path, ext, is_dir, size, modified, created, owner, is_readonly, is_hidden
CsvSource(path[, delimiter])one table, columns typed per section 1
JsonSource(text or path)an array of objects becomes one table, with nested objects flattened to dotted column names (address.city); nested arrays become child tables with parent_id
XmlSource(text or path, row_element)each row_element becomes a row, its attributes and simple child elements become columns, and repeated child elements become child tables
MatrixSource(m, names)one FloatColumn table (the FromMatrix bridge below)

Query a file by naming it. CSV is the most common case, so a file path in FROM or JOIN is a table. Nothing has to be loaded or registered first:

rs := Catalog->Run("select name, avg(score) from 'results/run_*.csv' group by name");
rs := Catalog->Run("select r.id, r.score, m.label from 'runs.csv' r join 'meta.csv' m on r.id = m.id");

Catalog->Run(sql) runs a statement against an empty catalog, so file tables are all it can see. catalog->Query(sql) does the same over a catalog the program has filled, so in-memory tables and files can be mixed in one statement.

More sources. Beyond the four above, the sources below are chosen for research use, ranked by value against the cost of building them on libraries the language already has.

Tier 1: in query.obl, built on libraries it already links or that are in lang.

SourceWhat becomes a tableBuilt on
TextSource(path, pattern[, record_start])Each line, or each multi-line record beginning at record_start, matched by a regular expression; named capture groups become columns, and lines that do not match are counted and optionally kept in a _unmatched table. Training logs, server logs, instrument output.Query.RegEx capture groups (#1290)
JsonLinesSource(path)One JSON object per line, mapped like JsonSource, with a record that fails to parse reported by line number. ML datasets, evaluation outputs, OpenAI batch files. It reads line by line, so it is also the first source that could stream.Data.JSON
Compression.gz, wrapping any file source above, transparently.lang's gzip and zlib
HttpJsonSource(url, records_path[, paging])The array at records_path in a JSON response; paging follows a next-page link or an offset parameter, up to a row limit. Public datasets and REST services.Web.HTTP, Data.JSON
ObjectSource(objects, columns)A Vector<T> of the program's own objects: one row per object, one column per named field, read through System.Introspection. Where reflection cannot reach a value, a column can be given as a function from the object to a value instead.System.Introspection

Tier 2: separate add-on libraries, because they need native modules the core should not load. A program that only queries CSV files must not need ODBC, ONNX Runtime or OpenCV installed, so each of these links query plus the library it adapts.

LibrarySourceWhat becomes a table
query_ml.oblMlResultSourceSystem.ML results: cross-validation folds and per-fold metrics, threshold sweeps, and predictions with their scores and true labels. Comparing runs becomes a GROUP BY and ORDER BY.
query_odbc.oblOdbcSource(connection, sql)The result of a query against a real database. With a scan hint, simple where terms are pushed into the SQL sent, so the database filters before anything is copied.
query_vision.oblVisionSource(folder, detector)Detections across a folder of images, one row each: image, label, score and box, from any API.Onnx detector. For example, the images where a person was found with a score above 0.9, grouped by camera.
query.obl (no native code)ArchiveSource(path)Zip and tar contents as a tree, like FilesystemSource, and any member readable as a source, such as a CSV inside a zip. Tar and stored zip members need only lang; deflated zip members use Byte->UncompressDeflate (raw deflate, RFC 1951) from lang. Built in phase 7; see "As built".
query.oblXlsxSource(path[, sheet])Each worksheet as a table, with typed cells and the first row as the header. An .xlsx file is a zip of XML parts, so this builds on ArchiveSource and XmlSource. Built in phase 7; see "As built".

Further sources fit the same interface without changing the engine, for example RSS through Web.RSS.

Source tests. Every source has its own regression test, programs/regression/query_source_<name>.obs, and every test builds its input files at run time in a temporary directory and deletes them afterwards. No dataset is committed, so there are no fixture files to drift or to fall foul of ignore rules, and every edge case is visible in the test itself. Each source's test covers:

Sources that reach outside the process are tested without the outside world:

Finder keeps its API and gains a table. Finder->New(regex)->FindFiles(path) keeps its signature and results, but becomes a query over FilesystemSource: select path from files where is_dir = false and path MATCHES regex. MATCHES is a new SQL operator backed by Query.RegEx, which gained character classes and capture groups in #1290. Finder->FindTable(path) returns the result as a View, so a file search can be filtered, sorted, grouped by extension or joined like any other table. Table->FromFilesystem(name, path) keeps its six columns and one-directory behaviour, and becomes FilesystemSource with recursive false.

7. Bridges

As built (phase 4c): ToCsv([delimiter]), WriteCsv(stream[, delimiter]), ToJson(), WriteJson(stream) and ToMatrix(names[, fill]) on both View and Table; Table->FromCsvText(name, text), Table->FromJson(name, text[, names]) and Table->FromMatrix(name, matrix, names). Writers stream a line or an object at a time and check every write. Numbers are exact (a Float as its shortest round-trip text, via Float->ToShortString). CSV dates are #MM/dd/yyyy hh:mm:ss#, the form CsvSource reads back as dates; JSON dates are ISO 8601 with Z for GMT. What a format cannot carry is documented rather than invented: CSV has no Bool (it reads back as the text true/false) and no GMT flag; JSON has no NaN or infinity (written as null) and no member order (so FromJson takes the column order as an optional argument). ToMatrix reads INT, FLOAT and BOOL cells (and numbers in OBJECT columns); an empty or non-numeric cell is an error naming the column and row unless a fill value is given. Describe() already existed.

8. Making it fast

Columns, views and compiled predicates remove the structural costs. These go further, in the order they pay off:

9. Persistence: a native table file

Research programs need to keep what they computed: a cleaned dataset, a feature table, results across runs. CSV and JSON stay for interchange, but they are slow to parse and lose types. The engine gets its own file format, .otab, which stores exactly what the column storage holds.

As built: OTAB_FORMAT.md is the byte-exact specification of versions 1 and 2 (2 adds index definitions; see "Indexes in .otab files" below), and it differs from the plan below in four ways. The schema sits in the footer, not the header, so opening a file is one read at its end. The codecs are zlib at level 1 (FAST) and level 9 (SMALL); lang's Brotli entry point turned out to be raw deflate. Each block carries its own dictionary of just the texts it uses. Delta and run-length encodings are not in version 1.

Layout.

header      magic "OTAB", format version, schema (names, types, dictionaries)
row group   for each column: type, encoding, null bitmap, data block, min, max, CRC32
row group   ...
footer      offset of every row group and column block, row count, footer CRC32
trailer     footer offset, magic

Each is used only where it shrinks the block, and the format version allows more later.

Saving safely. table->Save(path) writes a temporary file beside the target, flushes it, then renames it over the target, so a crash mid-save leaves the old file intact. One prerequisite: a rename over an existing file must replace it atomically on every platform. POSIX rename does; on Windows it needs MoveFileEx with MOVEFILE_REPLACE_EXISTING, so the VM's rename must be checked and fixed there first if it does not.

Appending. table->Append(path) writes only the new rows as additional row groups and a new footer, without rewriting the existing groups. Loading a dataset that grows by a run a day costs one row group a day.

A change log between saves. A table opened with Table->Open(path) records every insert, update and delete in a log file beside it (runs.otab.log), one length-prefixed and checksummed record per change, flushed per statement. Re-opening replays the log over the last save, so a crash loses at most the statement in progress. Checkpoint() folds the log into a new save and empties it. Durability is per statement; there are no multi-statement transactions.

As built (phase 3b): TableStore->New(path) opens a table for writing and reports why it could not (IsOpen, GetError, GetReplayed); Table->Open(path) is the short form that answers Nil. A durable table's Checkpoint() and Close() act on its store, and Save of its own file is a checkpoint. Each log record stores its length twice (once inverted), so damage to a length is told apart from a crash's torn tail instead of dropping every later record; a durable table refuses an OBJECT value the log cannot record. The byte layout of the log and the lock is in OTAB_FORMAT.md.

One writer, many readers. A process that opens a table for writing takes a lock file. Others can open it read-only and see the state of the last save or checkpoint. A second writer is refused with an error naming the lock's holder, and a stale lock left by a crash is detected and cleared. Exclusive creation of the lock file needs an exclusive-create primitive (O_EXCL on POSIX, CREATE_NEW on Windows), which the file API does not offer today, so it is added as a new VM trap first (appended after the existing ones, never by widening one).

Serialization. Tables, views and catalogs serialize in two ways, for two jobs:

This form follows the classes' field layout, so it suits data passed between parts of one program, or between processes running the same build, and not files kept across library versions.

Both round trips are tested: every column type, Nil cells, dictionary-encoded columns, deleted rows, views and catalogs, and, for the generic form, a table nested inside another object.

Indexes are rebuilt on load, since hash and sorted indexes rebuild in one pass. The file keeps their definitions (format 2, "Indexes in .otab files" below), not their entries.

10. Network: reading from and serving to other machines

11. Compatibility

Almost nothing outside the library uses it today (programs/tests/prgm193.obs calls FromFilesystem and Query; two programs use Finder), so the cost of keeping old programs working is small, and they must keep working:

Testing

Phases

Each phase lands as its own pull request, with its tests, a rebuilt query.obl, and the editor index regenerated for new classes.

  1. Columnar storage.
    • Storage: Column types, null bitmaps, typing and promotion, dictionary-encoded strings, and row groups with zone maps.
    • Table API: Row as a cursor, O(1) Get(pk), tombstone deletes with Compact(), the compatibility API on top, Describe().
    • Generic serialization: tables survive a Serializer/Deserializer round trip.
    • Sources: the Source interface with CsvSource, FilesystemSource (flat) and ObjectSource, and FromCsv, FromFilesystem and Finder reimplemented over them.

Tests: storage unit tests, query_serialize_generic, query_source_csv, query_source_filesystem, query_source_objects, and the #1291 tests unchanged. Benchmark: insert, scan and aggregates.

  1. Views and compiled predicates. View, copy-on-write result tables, the typed predicate nodes with batch evaluation over row groups, zone-map skipping, prepared statements and the statement cache, and the method-chaining API. The differential test starts here. Also the single-file text sources, which need no joins: TextSource, JsonLinesSource, and transparent .gz. Tests: query_source_text, query_source_jsonl, query_source_gzip.
  2. Persistence.
    • Prerequisites, each its own small VM pull request first: rename that replaces atomically on Windows, and an exclusive-create trap.
    • The .otab format: Save, Load, ToBytes, FromBytes, stream variants, per-block compression and CRC32s, and partial reads by column and row group.
    • Writing: Append, Open with the change log, Checkpoint, and the writer lock.

Tests:

Benchmark: save and load at one million rows.

  1. The relational layer.
    • SQL: Catalog, hash joins (on dictionary codes where both sides share a dictionary), GROUP BY/HAVING, multi-key ORDER BY ASC/DESC, LIMIT/OFFSET, and INSERT/UPDATE/DELETE/CREATE TABLE.
    • Bridges: ToMatrix, FromMatrix, CSV and JSON.
    • Hierarchical sources, which need joins to be useful: JsonSource and XmlSource with child tables, the recursive FilesystemSource with parent keys, MATCHES, Finder->FindTable, and scan-hint pushdown.
    • Files named in SQL: FROM and JOIN over CSV, TSV, JSON Lines, JSON, gzipped files and .otab, globs over several files, and the per-path cache.

Tests: query_source_json, query_source_xml, query_source_tree (the recursive filesystem with parent keys), and query_source_files_in_sql (globs, _file, the cache).

  1. Network.
    • Reading: URLs in every file source, the download cache with ETag and Last-Modified revalidation, range-request reads of .otab, and HttpJsonSource.
    • Serving: TableServer with its read-only query endpoint and limits, and RemoteSource with filter pushdown.

Tests, all against an in-process Web.HTTP.Server:

  1. Hash and sorted indexes and the rule-based planner.
  2. Tier 2 sources, one pull request each, in this order:
    1. query_ml.obl with MlResultSource
    2. ArchiveSource (tar first, then zip; raw deflate is Byte->UncompressDeflate)
    3. XlsxSource
    4. query_odbc.obl with OdbcSource and its pushdown
    5. query_vision.obl with VisionSource

Each comes with its own query_source_<name> test. Each add-on library also needs a line in core/compiler/build_libs.sh and in the other hard-coded library lists, all of which tools/cicd/check_doc_lists.py keeps in step.

  1. Conditional upgrades, each only when a benchmark shows the need, and behind interfaces that already exist, so nothing above changes:
    • a B+ tree index, for frequent inserts mixed with range queries that the sorted index serves badly;
    • parallel scans across row groups, with a multithreaded GC stress test;
    • index entries stored in .otab, if rebuilding them on load proves slow (their definitions already are: format 2).

As built

What the implementation does where it differs from, or settles something left open in, the design above. Each phase adds its notes here.

Phase 1 (#1298).

Phase 2.

Phase 4.

Phase 5 (reading).

Phase 5 (serving).

Phase 6 (indexes and the planner).

Phase 7 (query_ml.obl).

Phase 7 (query_vision.obl).

Phase 7 (ArchiveSource).

Phase 7 (XlsxSource).

Phase 7 (query_odbc.obl).

Toolchain defects found while building these phases were fixed at the source rather than worked around: gzip on small inputs (#1296), atomic rename on Windows (#1297), ten compiler defects (#1299), HTTP status codes and a TLS/collector deadlock (#1300), XmlElement attribute order, no way to bound how far deflate or gzip data inflates (phase 7: two new traps), and XmlElement text that could only be read trimmed (phase 7: GetDecodedText). Also in phase 7: the label merging at -opt s2/s3 could give a merged label the id of another label in a linked library method, so adding classes to query.obl made a query loop forever.

Concurrent readers

The contract. One thread writes a table, or several threads read it, never both at once: writes are not locked, and a program that mixes them must order them itself (as TableServer does with its readers-writer lock). Within that contract, any number of threads may query one table at the same time, including the first queries after a write, and every answer is the answer a scan gives on one thread. Table->GetError reports the calling thread's own last error ("Errors per thread" below), and a Catalog may be shared too ("One catalog, many threads").

What a read writes. Three things a query may rebuild on a table it does not otherwise change, and each was a defect under concurrent readers:

StateMade stale byDefect before
A sorted index (and a hash index after a whole-column rewrite)a cell write; Compact, promotion and the other whole-column rewritestwo threads rebuilt it at once, each publishing its own copy; harmless only while the fields happen to be seen in program order
A row group's zone mapa cell write that replaces a valuethe bounds were reset to "no values" and marked fresh before being recomputed, so a reader arriving meanwhile skipped the group and lost its rows (wrong answers, reproduced)
A table's plan cache (Table->Query, Execute, Prepare)a statement not seen beforeevery thread that missed inserted into one Map at once, corrupting its tree: a crash or a query that never returns (reproduced)

The fix: one rebuild lock, taken only on the slow path. A ThreadMutex held in a class variable (RebuildLock), one per process. It cannot be a field of Table, Column or the index: the generic Serializer would copy its native handle (section 9 keeps every field serializable). It is created by the first Table constructor, before any table exists to be shared, so threads never race to create it.

The test. query_index_concurrent writes to a shared table, then starts four threads that meet at a barrier before each query (ranges, equality, ORDER BY and Min/Max through the indexes, ranges that scan with zone maps, and SQL through the plan cache) and compares every answer with one an unindexed twin gave on one thread. Before the fix it failed in every mode: short answers from skipped groups, a Nil dereference, or a hang in the plan cache's Map.

Errors per thread

The gap. A table kept one @error for every thread querying it, and so did a catalog: GetError answered whichever thread failed last, and a thread whose query succeeded could read another thread's failure.

The fix: each thread reads its own. GetError keeps its signature and its meaning on one thread; with several, it answers the calling thread's last error on that object. Alternatives weighed:

So the runtime gained the least it could, two functions on System.Concurrency.Thread (new traps THREAD_CURRENT_ID and THREAD_IS_ALIVE, appended after the others): Thread->GetCurrentId() gives the calling thread a number, counted up from 1 the first time it asks and never given to another thread, even after this one ends (an operating system's thread ids are reused); Thread->IsAlive(id) says whether that thread still runs (the VM keeps the numbers of running threads in a set a thread_local's destructor leaves).

A table and a catalog hold a ThreadErrors: a Map from thread number to message, Nil while no thread has an error pending. A failed call sets the calling thread's entry, the next call that succeeds removes it, and the entries of threads that have ended are dropped whenever the map changes, so a thread that ends with an error unread leaves nothing behind for long. The common case costs one field read and no lock: a call that succeeds on an object with no pending errors sees the map is Nil and returns. That is safe without the lock because only a thread adds its own entry and the map goes back to Nil only when empty, so a thread that sees Nil has no entry. The lock, held only while the map is read or changed, is a class variable for the reason RebuildLock is one: the map is serializable, a lock is not.

A table or catalog carried through the generic Serializer carries its pending errors, as it carried its one error before; the numbers are this process's, so a copy read in another process may answer a stale error on a thread that has the same number until that thread's next call.

Statement, View, Query, OtabFile, TableStore and the sources keep one error each: each is one thread's object (a statement holds its bound values; a view, a file and a source are made by the call that uses them), and "One catalog, many threads" says so where it matters.

The test. query_thread_errors: four threads fail at once on one table and one catalog, each for its own reason (an unknown column or table named after the thread, a syntax error at a position of its own) while one of them succeeds, then read GetError after all have finished: each must see its own reason, the one that succeeded none, and the main thread, which never failed, none, over 40 rounds of new threads. Before the fix it failed in every mode: 640 of its 884 checks, or, in runs where the threads' statements corrupted the catalog's unlocked plan cache, a crash in Map->Insert or a hang.

One catalog, many threads

The gap. A catalog was one thread's: its table map, its plan cache and its file cache were Maps written without a lock, and a file named in SQL was read by every thread that missed the cache. Two threads querying through one catalog could corrupt a map (a crash in Map->Insert, a Nil dereference, or a query that never returns, all reproduced) and read the same file twice.

The contract.

CallWith other threads querying the catalog
Query, Prepare, Explain, Get, Has, GetNames, GetFileReads, GetError, and files, archives and workbooks named in SQLsafe; any number at once
Add, AddNew, Remove, Load, CREATE TABLEsafe: a query sees the table map before or after the change; a query already running keeps the tables it found
INSERT, UPDATE, DELETE, CREATE INDEX, DROP INDEX (through Execute or a statement)they write a table, so the table's contract applies: no other thread may use that table meanwhile. Writing a table no other thread uses (one a thread created for itself) is safe
SetFileAccess, SetArchiveLimitsapply to queries that start after them; a query running meanwhile may read either value. Set them before sharing the catalog
a Statement from Preparebelongs to the thread that binds it; each thread prepares its own (a plan cache hit, so cheap)

Locks, all on slow paths or held for a map operation only (CatalogLocks, class variables made by the first table or catalog, before there is one to share):

The locks cannot deadlock: nothing waits for a file lock while holding another lock, a thread holding a file lock may take only the catalog lock or an error lock, and those two take no other lock while held (an error lock asks the VM whether threads are alive, under the VM's own lock).

Two defects found on the way and fixed: an INSERT with no column list filled the column list of the cached statement with the table's columns on its first run, so after AddColumn, or with another table under that name, the same statement kept inserting into the old columns (now each run takes the table's columns as they are; query_sql_relational covers it); and the CREATE TABLE race above.

TableServer answered one request at a time under its mutex until it, too, was made to answer at once ("A server that answers at once" below).

The test. query_catalog_concurrent: six threads share one catalog over 30 rounds. Each round rewrites a CSV and a zip a little longer (so every thread misses the file cache for them at once), computes every answer alone with an oracle catalog, then the threads meet at a barrier before each statement: files in SQL (a CSV, a glob over three, members of a zip, a worksheet), joins of files with each other and with the catalog's tables, GROUP BY and HAVING, a statement new to the plan cache every round, statements that fail, a failure of each thread's own whose reason it must read back, prepared statements bound per thread, and a table each thread adds (Add) and creates and fills (CREATE TABLE, INSERT) while the others query. Every answer must equal the oracle's, and the shared catalog must read files exactly as often as the oracle did alone (once per changed file per round). Before the fix it crashed in Map->Insert or on a Nil, or hung, in every mode; it passes at -opt s3 and s0, with and without the JIT, and with OBJECK_JIT_THRESHOLD=1. query_net_read adds the URL half: four threads sharing a catalog query a URL with no validator (so every query downloads it) and one that answers 304, over eight rounds that each change the first; every answer must be right and the changed file read once per round. Before the fix a thread's query failed with "cannot write its copy" in the first round of every run.

One cache folder, many processes

The gap. The download cache lives in one folder under the system's temporary folder, so every process on a machine shares it, and "One catalog, many threads" made it safe only within one process: the URL's file lock kept threads apart, and nothing kept processes apart. A URL had one copy and one note (its ETag, Last-Modified and stamp), each replaced by a rename, so two processes could:

The design: immutable versions and one small note, published by one rename. Each URL has a folder of its own (named by the URL's hash) holding:

A fetch reads the note, revalidates with its validators, and on a 304 returns the version the note named. On a 200 it reads the note again: if it names the same content (the same stamp: another process published it meanwhile, or a server without validators sent it again), it returns that version and writes nothing; otherwise it writes a new version, publishes it, and returns it. A fetch always returns the version it decided on, whether or not its note is the one left in place, so two processes that download at once each get a whole, correct answer, and the last note wins, which costs at most one more download if it names the older version.

The note is the only file ever replaced, and readers hold it open only for one small read. Where that collides on Windows, the writer tries the rename again for up to about a second, and a reader that cannot open the note while it is being replaced tries again for a few milliseconds; a note that still cannot be read counts as none (the file is downloaded again), and a note that cannot be published leaves the old one, which names a whole version too. Neither is an error.

Collecting old versions without a lock. A reader learns a version's name from the note or by writing the version itself, and opens it at once. So a version the note no longer names can be in use only by a reader that read the note while it named that version, or that wrote it. The note's age bounds the first: while the note is older than the grace period (five minutes), every version it does not name was superseded at least that long ago. The version's own age bounds the second. A fetch therefore deletes, in its URL's folder, every file other than the note, the version the note names and the version it is returning that is older than the grace period, but only while the note is older than that too; it reads the note before its age, so a note replaced in between looks new and nothing is deleted. A file another process has open cannot be deleted on Windows, and a later fetch tries again. Versions a process wrote but never published, and notes left by a process that stopped mid-write, go the same way. No lock file is needed: nothing is ever read, changed and written back, and each step is one atomic rename or the creation of a new name.

The cache of earlier versions of the library (a copy HASH_name and a .meta beside it in the cache folder itself) is not read: the first fetch of a URL downloads it again into its own folder, and the old files stay until the temporary folder is cleaned.

Within a process, the catalog keeps its URL file lock only for single-flight reading: a fetch returns a version and its stamp, and threads that miss the catalog's file cache for that stamp together read the version once. It no longer re-reads the note under the lock to find the stamp of a copy that might have changed, because a version cannot change.

The test. query_url_cache_processes starts an in-process server whose file changes every 15 ms (two URLs: one with an ETag, one with no validator, so every fetch of it downloads), and in each of ten rounds starts six processes of its own program that point their cache at one shared folder and query the file 24 times each: through a catalog they keep, a new catalog each time, and a CsvSource. Every version of the file has a different number of rows, each naming the version, so an answer shows whether it read one whole version, and each query first asks the server which version it has, so an answer older than that is caught. After each round the server stops changing and the parent, sharing the folder, must read the current version. Before the fix it failed in every run on Windows, in every mode (133 to 184 of the 1,440 queries failed with "cannot write its copy"), and in most runs on Linux, where nothing fails but answers are wrong: two to five per run read a version older than the server had when the query began (a 304 for a note paired with an older copy), and some read a copy cut short. After it, none fail on either, at -opt s3 and s0, with and without the JIT, and with OBJECK_JIT_THRESHOLD=1.

Archives and workbooks in SQL

The gap. Phase 7's ArchiveSource and XlsxSource could only be loaded into a catalog by code; a query could not name results.zip or budget.xlsx the way it names runs.csv.

The syntax: a member after a dot, like a table after its schema. A quoted path is already a table in FROM and JOIN; a container's member follows it as a second quoted string:

select * from 'results.zip'                                  -- the archive's listing
select * from 'results.zip'.'2026/runs.csv'                  -- a member, by its extension
select * from csv('results.zip'.'notes.txt', delimiter => ';')    -- a member read a given way
select * from xml('results.zip'.'feed.xml', row => 'item')
select _member, count(*) from 'results.zip'.'2026/*.csv' group by _member
select * from 'runs/*.tar.gz'.'metrics.csv'                  -- one member of each archive; _file
select * from 'budget.xlsx'                                  -- the first worksheet
select * from 'budget.xlsx'.'Q1 2026'                        -- a worksheet by name
select _sheet, sum(amount) from 'budget.xlsx'.'Q*' group by _sheet
select * from xlsx('budget.xlsx'.'Report', header => false, first_row => 3)

Alternatives weighed: a separator inside the path ('results.zip#runs.csv', 'results.zip!/ runs.csv') would make a legal file name mean something else and need escaping; a function argument (zip('results.zip', member => 'runs.csv')) reads well but repeats the format the extension already gives. The two-string form reuses the dotted reference SQL already has (schema.table), keeps each name a plain literal, and composes with the existing function forms, whose options then apply to the member. The lexer gained . as a symbol where it is neither inside a name (t.col stays one word) nor the start of a number; it was an error everywhere else before, so no statement that parsed changes meaning.

What each form reads.

Safety and access, unchanged. Everything goes through ArchiveSource and XlsxSource, so their checks apply: nothing is extracted, a member climbing out with .., an absolute path, duplicate or overlapping zip members, a zip bomb, a CRC32 mismatch and the decompression limits are all refused. Catalog->SetFileAccess(false) refuses archives and workbooks like any file (so TableServer never reads one), and the new Catalog->SetArchiveLimits(member, total) sets the limits for archives and workbooks named in its queries (256 MB and 1 GB by default, as the sources' own); a refusal names SetArchiveLimits rather than the source's SetLimits. A URL archive is fetched through the download cache like any URL file.

Caching. As any file named in SQL: per catalog, keyed by path, member, format and options, and re-read when the archive's size or modification time changes.

Errors name the archive and member or sheet: a missing member or sheet, a member in no table format, an XML member without its row element, a pattern that matches nothing, a function form on an archive with no member, a member named on a plain file, and an unquoted or empty member.

Tests. query_archives_in_sql: a zip (deflated and stored members, a .txt read by csv(), XML by xml()), a .tar.gz and a .tgz (and a glob over both), a workbook (first sheet, a named sheet, a sheet pattern, header => false, first_row), joins across all three, the catalog cache, and every refusal above, each archive and workbook written by the test. Before the change every query fails at the lexer (unexpected character '.').

A server that answers at once

The gap. TableServer answered one request at a time: every request took its mutex, from parsing the statement to writing the answer, because a catalog could not be queried by two threads at once. Once it could ("One catalog, many threads"), the mutex served only to keep a request from reading a table while it was written, and the server could not even do that: the program that publishes the tables writes them from its own threads, which never took the server's mutex, so a request could read a table half changed while every other request waited in line.

What can write. Nothing over the wire: the server answers only SELECT (a statement that would change data, INSERT, UPDATE, DELETE, CREATE and DROP INDEX, CREATE TABLE, is refused with 403, before it runs) and has file access off. Every request only reads. The writer is the program, through the Table objects it published.

The design: a readers-writer lock, and an API for the program's writes.

A callback form (Write(change)) was weighed and left out: the pair is the smallest API that lets a program hold the write across several statements, and Objeck has no exceptions that could skip the EndWrite.

The test. query_server_concurrent: eight client threads meet at a barrier before each of nine requests (grouped and joined SELECTs, an ordered limit, DISTINCT, tables as JSON and .otab, and bad SQL), in CSV, JSON and .otab, over six rounds, and every answer must equal the one a catalog of the same tables gives on one thread. Then the program changes a published table sixty times between BeginWrite and EndWrite, cell by cell (every a is its row's id plus the changes so far and every b minus that, so a read during a change sees a sum or a range that is off), while four clients read it and another sends statements that would write, each of which must be refused with 403. On master the test does not compile (there is no BeginWrite); with the program's writes left unguarded, which is all master offered, 18 to 136 reads per run saw a change half done, in every mode. It passes at -opt s3 and s0, with and without the JIT, and with OBJECK_JIT_THRESHOLD=1.

Throughput. One SELECT (a GROUP BY over 20,000 rows) sent as fast as each client thread can, for six seconds, on this machine (Windows, 24 logical processors):

clientsbefore (mutex)after
137.8 req/s34.2 req/s
449.1 req/s86.7 req/s
848.2 req/s112.4 req/s

Indexes in .otab files

The gap. Phase 6 left indexes out of the file, so a table lost them on every Save and Load, every ToBytes and FromBytes, and every Open of a durable table; a program had to remember which indexes it made and create them again.

What is stored: the definition, not the entries. Each index's name, column and kind go in the footer. The entries are rebuilt from the column by the first query that uses the index, as a stale index is today. Storing the sorted order too was considered and left out:

The format: version 2, written only when needed. OTAB_FORMAT.md's rules say a version 1 footer must be consumed exactly and that new content comes with a new version, which a version 1 reader refuses. So the definitions are a new last part of the footer in version 2, and the writer picks the lowest version that holds the table: version 1 for a table without indexes, byte for byte what it wrote before, and version 2 only for a table with indexes. Libraries before this change read every file they read before, and refuse an indexed table's file by its header ("an .otab file of format 2; this library reads format 1") rather than misread it. This library reads both. A version 2 index list is validated like the rest of the footer (a known kind, a data column, no repeated name or column, a count below the column count) and refused with the index it found wrong.

Each path:

Tests. query_persist_indexes: every index kind on every indexable type through Save/Load, bytes, a compressed image and a partial read, each query through a restored index equal to a scan and naming the index in its plan; a table without indexes written byte for byte as an image made by master's library (kept in the test as hex) and that image read back; Append to version 1 and version 2 files; a durable table's indexes through Open, replay, CreateIndex, DropIndex and SQL; and footers whose index lists are wrong in each way above, each with a correct checksum so only the defect is refused. query_net_range reads the definitions over HTTP range requests; query_net_serve checks that served tables are version 1. tools/otab/otab_dump.py reads version 2.

Expressions in every clause

The gap. #1367 put arithmetic in the select list and nowhere else. where price * qty > 100, where a + b = c, group by x % 10, having sum(a) / count(*) > 2, order by hi - lo, join u on t.n + 1 = u.n and select max(x) - min(x) are each refused, so a research program computes a column in a first query and filters, groups or joins it in a second. These are the questions research code asks most, and the release v2026.10.2 includes the answer.

What is added, and what is not. One expression grammar, the select list's, in every clause: WHERE, JOIN ... ON, GROUP BY, HAVING, ORDER BY, and inside and around aggregates. No scalar functions (abs, round, coalesce, length, upper, lower): nothing below needs one, each is a separate decision about types and empty cells, and the grammar leaves room for them later (a name followed by (, as the aggregates are written). No string concatenation, no date arithmetic, no CASE, no expressions in UPDATE ... SET or INSERT ... VALUES: values there stay literals and placeholders. The method-chaining API (Col, Predicate) is unchanged; no public class or method is added.

Grammar

condition  := or
or         := and (OR and)*
and        := not (AND not)*
not        := NOT not | '(' condition ')' | test
test       := expr cmp expr
            | expr [NOT] BETWEEN expr AND expr
            | expr [NOT] IN '(' expr (',' expr)* ')'
            | expr [NOT] (LIKE | MATCHES) value
            | expr IS [NOT] NULL
cmp        := '=' | '<>' | '!=' | '<' | '<=' | '>' | '>='
expr       := term (('+' | '-') term)*
term       := unary (('*' | '/' | '%') unary)*
unary      := '-' unary | primary
primary    := number | name | '?' | '(' expr ')' | aggregate
            | text | date | NULL | TRUE | FALSE           -- in a condition only
aggregate  := COUNT '(' '*' ')' | func '(' expr ')'      -- func: COUNT SUM AVG MIN MAX MEDIAN STDDEV
value      := text | '?' | word                          -- a LIKE or MATCHES pattern, as now

Values

An expression produces, for each row, one of: a whole number (Int), a number (Float), text, a date, a Bool, another object (a cell of an OBJECT column that is none of these), or an empty value. Columns give their cells by their storage type; an OBJECT column's cell is classified by its class (IntRef, FloatRef, String, Date, BoolRef; any other class is an object). Literals: a number as written (a whole number is an Int, anything with a . or an exponent a Float, and so is a whole number of more than 18 digits, as a CSV cell is typed), text, a date, TRUE/FALSE as 1 and 0, NULL as empty. A placeholder has its bound value's kind.

Arithmetic reads numbers only (#1367's rule, kept): an operand that is empty, text, a date, a Bool or an object makes the result empty.

OperationIntsWith a FloatEmpty result when
a + b, a - b, a * bIntFloatan Int result does not fit in 64 bits
a / bFloat (7 / 2 is 3.5)Floatb is zero (0, 0.0 or -0.0)
a % bInt, the sign of a (truncated); a % -1 is 0Float: a - trunc(a / b) * bb is zero
-aIntFloata is the smallest Int

Comparisons, and why indexes and zone maps keep working

A test compares the two sides row by row with one table of rules, which is the rule a column has always been compared with a constant (section 3, "Phase 2" and "Phase 6" in "As built"):

  1. If either side is empty, every test except IS NULL is false (<> included).
  2. Two Ints compare exactly (so 2^53 + 1 > 2^53).
  3. Two numbers otherwise compare as Floats; if either is NaN they cannot be ordered.
  4. Two texts compare as text; two dates in time order.
  5. Two Bools compare false before true; a Bool read from a BOOL column also compares with the whole numbers 0 and 1 (which is what TRUE and FALSE are), as those numbers.
  6. Two other objects of the same class (cells of OBJECT columns and placeholders' values that are none of the kinds above) compare by that class's Compare, called on the left one, as Cell->Compare does.
  7. Anything else cannot be ordered.

Rule 5's asymmetry is the engine's today, kept on purpose: flag = true matches a BOOL column, while a BoolRef in an OBJECT column compared with the number 1 does not (Cell->Compare). With it, every pair of a column type and a constant compares exactly as the existing Filter compares them, which is what makes reduction (below) safe.

Values that cannot be ordered satisfy only <>. So NaN satisfies only <>, as it does today, and sorts after every number. NOT is the complement of a condition's rows, empty cells included (there is no three-valued logic, as before). x IN (a, b) is x = a OR x = b; x BETWEEN a AND b is x >= a AND x <= b. LIKE and MATCHES take part only when the operand is text (a number never matches, NOT LIKE included). IS NULL is true where the operand is empty, which for an expression includes an operand that is not a number and a zero divisor; NaN is not empty.

Reduction. At the start of each run, with placeholders bound and constants folded, each test is reduced where it can be:

There is no other rewriting: x + 0 > 5 is not x > 5 (on a text column the first is empty), and -x < -5 is not x > 5. Because a reduced test is the node the engine already had, and the expression evaluator implements the same table of rules, reduction can only change speed, never an answer. In an AND, the planner reads the best index-backed term (section 5) and the expression terms are then tested on its candidates; an OR with an expression term scans, as an OR with any unindexed term does. A view's GetPlan() and Explain show what was chosen: a reduced term exactly as today (where runs: SORTED index runs_score on score (>)), and expression terms as part of a scan (where runs: scan).

Where each kind of expression is evaluated

Aggregates keep their rules ("Phase 4"): numbers only, empty when a group has none; COUNT is INT; SUM, MIN and MAX stay INT over whole numbers, which for an expression argument means every result of it is an Int (its computed column is INT); AVG, MEDIAN and STDDEV are FLOAT. (SUM of Ints is computed in Int arithmetic and wraps on overflow, as it does today; making it empty instead, like expression arithmetic, is left for a separate change.)

Batch evaluation and the performance contract

Expressions are bound once per run (names to columns, placeholders to values, constants folded) into a tree of evaluator nodes, separate from the parsed statement. Each node evaluates a batch of up to 1,024 row numbers at once into scratch arrays it allocated when bound: a kind per row plus an Int[], a Float[], a String[] and a Compare[] for the values. A node is one loop over its children's arrays; a column node reads the column's own arrays (an encoded text column through its dictionary, a date as its stored milliseconds). An expression term compares two batches into the selection vector, chunk by chunk over the candidate rows, so its cost is a few array loops per 1,024 rows and no object is allocated per row: text and objects are references to the stored cells, numbers are never boxed. Computed columns are filled with FillNumbers from typed arrays rather than cell by cell. The only allocations in a query remain its result arrays and one set of scratch arrays per bound expression.

The bar: the select-list expression price * qty over 1,000,000 rows must not be slower than

#1367's row-at-a-time evaluator (which boxes every operand and result), and `where price * qty >

100` must be within a small factor of the same loop written over plain arrays. Both are measured, with the numbers recorded here, at stage 1; no timing gate is added to the suite.

Thread safety

A parsed statement and its expression trees are built by the parser and never changed after: they are shared by every thread that finds the plan in a table's or catalog's cache. Name resolution makes new trees instead of renaming in place; binding makes evaluator nodes and scratch arrays that belong to one run. So a run writes nothing a cached plan holds, and the existing contracts ("Concurrent readers", "One catalog, many threads") cover expressions with no new lock. (#1367's select-list evaluator numbered its column references inside the parsed tree at parse time; that state moves into the per-run binding.)

Prepared statements

A placeholder may stand anywhere an operand can, in any clause. Values are read at the start of each run, so constant folding and reduction use the bound values (where price > ? * 2 reads through a sorted index on price), and a statement bound again re-plans. An unbound placeholder is refused as now (no value bound for placeholder N). A bound value in arithmetic that is not a number gives empty results, by the rule above.

Errors

Every refusal returns Nil with GetError set, never a silent empty result:

StatementError
select zz + 1 from tunknown column 'zz' in 'zz + 1' (#1367)
where zz + 1 > 0unknown column 'zz'
where sum(x) > 1syntax error at position N: expected a column name (aggregates belong in HAVING, not WHERE), found 'sum' (as now)
group by sum(x)GROUP BY cannot use an aggregate: 'sum(x)'
group by 1, group by 2 * 3GROUP BY needs a column or an expression over columns, not '1'
select sum(count(*))an aggregate cannot contain another aggregate: 'sum(count(*))'
on sum(t.x) = u.yaggregates are not allowed in ON: 'sum(t.x)'
select k, n + 1 from t group by kcolumn 'n' must be in GROUP BY or inside an aggregate
where a < b < csyntax error at position N: expected ... (the parser's usual form)
order by zz + 1unknown column 'zz' in ORDER BY 'zz + 1'

Messages that exist today keep their text. Each stage replaces the refusals it lifts (#1367's an expression in the select list of a grouped or aggregate query is not supported, and the syntax errors that expressions in WHERE, ON, GROUP BY and HAVING meet today), and its test shows them before the change.

Stages

Each stage is its own pull request, merged before the next starts, with query.obl and every library that links it rebuilt (query_ml, query_odbc, query_server and query_vision change only by class ids when classes are added to query.obl, which a decompressed comparison checks).

  1. The expression core, WHERE and ORDER BY. The typed expression tree and its batch evaluator, used by the select list (replacing #1367's evaluator), WHERE (with reduction, UPDATE and DELETE included) and ORDER BY.
  2. Grouping. GROUP BY expressions, aggregates over expressions, expressions over aggregates in the select list, HAVING and ORDER BY.
  3. Joins. ON conditions: key equalities over expressions, other terms, and the every-pair path.

Tests

Every new or touched test runs at -opt s3 and -opt s0, each under obr, obr --jit=off and OBJECK_JIT_THRESHOLD=1, on Windows and on Linux.

As built

Stage 1 (expression core, WHERE, ORDER BY).

| Query | Before (master) | Stage 1 | |---|---|---| | select price * qty | 585 ms (#1367, row at a time) | 146 ms | | where price * qty > 100 (773k rows) | refused | 29 ms | | where price * qty > 1500 (28k rows) | refused | 23 ms | | the same filter over plain arrays | 9 ms | 9 ms | | where price > 50, a scanned Filter, for scale | 29 ms | 28 ms |

So an expression term costs what a scanned column test does, about 3× a hand-written loop. Without #1397 the expression terms took 530-650 ms (interpreted). Once they were compiled, the differential test found the x64 JIT raising "Divide by zero" for a NaN divisor (its guard took an unordered compare as equal), fixed in the JIT by #1399.

Stage 2 (grouping).

Stage 3 (joins).

Open questions