Query semantics

A query is a JSON AST, not SQL. What it means is fixed by a set of rules, and each rule by conformance cases: one JSON file per case in crates/opengrid-conformance/cases/, each holding a query against the shared orders dataset and the exact expected answer.

{
  "id": "s8-decimal-is-exact",
  "rule": "S8",
  "query": {
    "source": "orders",
    "select": ["id", "amount"],
    "filter": { "field": "amount", "op": "eq", "value": "999999999.99" }
  },
  "expected": { "columns": ["id", "amount"], "rows": [[7, "999999999.99"]] },
  "ordered": false
}

Who answers them

The same case files run against

• the local engine, natively (crates/opengrid-engine/tests/conformance_engine.rs),
• the WASM build in headless Chrome (crates/opengrid-wasm/tests/conformance_in_browser.rs): one test per case file, listed when the crate is built, so a new case runs there too,
• the hybrid path, with a source that refuses what it does not declare (crates/opengrid-engine/tests/conformance_hybrid.rs),
• PostgreSQL (crates/opengrid-datasource-postgres/tests/conformance_postgres.rs) — this one skips itself without a database; point it at one with OPENGRID_TEST_PG.

The rules, by their cases

Rule	What the cases pin
S1	Comparisons with NULL: eq and ne exclude NULL rows, NOT of an unknown stays unknown
S2	in lists, including the empty list, which matches nothing
S3	NULL ordering, ascending and descending
S4	Binary collation
S5	contains and starts_with are case-sensitive
S5a	icontains and istarts_with fold both sides with Unicode’s lowercase mapping (no locale, final sigma applied): Müller is found by müller, Σ at the end of a word becomes ς, ß stays (STRASSE is not Straße), and İ becomes i and a combining dot, so a Turkish İstanbul is not found by istanbul. The engine, SQLite (og_fold, registered by the connector) and PostgreSQL (lower under ICU’s und-x-icu collation, so the database needs ICU) agree on fold-cases/. Accent-insensitive matching and locale collation of sorting are not part of it (#391)
S6	Multi-column sort and paging with offset
S7	NaN: not NULL, sorts after numbers, -0.0 equals 0.0
S8	Decimals are exact
S9	Dates have no time zone; timestamps keep microseconds
S10	NULL forms its own group, and the empty string is a different one
S11	Aggregates ignore NULL; count(*) versus count(field); empty sets
S12	Aggregate result types: sum of int64 stays int64, of decimal widens to 38 digits
S13	Unicode normalisation: NFC is not NFD
S14	The empty string is a value, not NULL
S15	Derived date-part columns (year, month) filter, sort and group like stored ones
L1	has: a list holds an element, compared exactly and case-sensitively; a NULL list is unknown and drops, so not has drops it too
L2	has_all: every given element is in the list, in any order and any number of times; no elements is every list but a NULL one
L3	is_empty is true for the empty list only; a NULL list is not empty, and not is_empty keeps it
L4	A list comes back as it went in: order, repeats, empty elements and text with commas or quotes kept; NULL and the empty list stay two things
L5	count of a list field skips NULL; a list is found as NULL with is_null
L6	has and has_all combine with and, or, not like any filter

A list column has no order: it is not sorted, grouped or compared as a whole, and has no min or max — the query is refused with ListNotOrdered — and eq, in and the text operators do not apply to it. Its elements are never NULL, and the empty list is not NULL. The cases are in list-cases/ over lists.csv; the engine, SQLite and PostgreSQL answer them alike.

The file names say the rest — s7-nan-sorts-after-numbers.json, s9-timestamp-microsecond-precision.json — and each file is short enough to read.

Calculated measures in a pivot

A pivot’s measure is an aggregate; with calc it is shown as a calculation over the pivot’s cells instead: { "field": "qty", "fn": "sum", "as": "share", "calc": "share_of_row" }. The calculations are part of the pivot’s contract — the engine, SQLite and PostgreSQL compute them, not the element that shows them — and three rules fix what they mean. Their cases are in pivot-cases/, against the same orders dataset, each with the whole expected pivot.

Rule	What the cases pin
P10	A share is defined for sum and count; a share of avg, min or max is refused. A running total takes all five, where the aggregate’s result is a number. A calculated measure is a measure like any other: its own alias, its own column, beside its aggregate when the pivot names both. A sort by it orders by its aggregate
P11	share_of_row, share_of_column, share_of_total: the cell divided by its row’s total, its column’s total or the grand total — a float64 fraction, NULL where the cell is NULL or the total is NULL or zero. Subtotal rows and the total row follow the same rule
P12	running_total: the sum of the row’s cells — the aggregate’s values, whichever aggregate — up to and including the column, in the columns’ order, starting again in every row; subtotal and total rows the same, over their own cells. NULL adds nothing; before the row’s first value the cell stays NULL. The type is the aggregate’s own

Which total. A row’s total is the sum of its cells across every generated column — with two column dimensions, across all of them. A column’s total is that column’s cell in the grand total row; the grand total is the grand total row’s total. There is no share of the parent group: a row two levels down is still divided by the column’s cell in the grand total row. In the grand total row a share of the column is therefore 1 and a share of the total is the column’s part of everything; in a subtotal row each is the subtotal’s own part. Without a column dimension a row is its own total — a share of the row is 1, and a share of the column equals the share of the total.

Why a share is for sum and count only. A share divides by a total, and the total is added up from the cells: only a sum or a count of the parts is the sum or the count of the whole — which keeps every share what the raw rows give (a subtotal is asked for, never rolled up; the average of averages is not an average). A share of avg, min or max is an error that names the measure, before anything runs.

A running total takes every aggregate whose result is a number: it claims no total of the raw rows, it adds up what the cells show. Over avg it is the sum of the averages so far, over min or max the sum of the smallest or largest values so far; a subtotal or total row adds up its own cells, so its running total is not the sum of the rows above it. min or max of a text, a date, a timestamp or a boolean is no number, and its running total is refused, naming the measure.

What a share is. A fraction, not a percentage: 0.25, as float64, whatever the measure’s type; percent signs and digits are the page’s format. A row’s shares of its row total add up to 1, to within floating point — the suite compares a float within a relative 1e-9. A share is plain division, so negative values give shares below 0 or above 1 (50.00 of a total of -12295.67 is -0.0041); only a total of exactly zero, or NULL, gives NULL. An empty cell follows from what it holds: a sum of nothing is NULL and so is its share, a count of nothing is 0 and its share is 0.0. NaN is a value, not NULL: a sum of float64 that is NaN makes the shares of it NaN.

What a running total is. It runs along the generated columns as they are ordered — values ascending, NULL last — across both column dimensions without starting again at the outer one, and anew in every row. A cell no row falls into exists, as in every pivot: for a sum it is NULL and the running total carries on unchanged past it (or stays NULL, before the first value); for a count it is 0, which is a value from the first column on; for avg, min and max it is NULL, as for a sum. The type is the aggregate’s own result type (S12): an integer sum stays int64 and a decimal sum its 38-digit decimal, exact to the last digit; avg is float64; min and max keep their column’s type, a decimal its precision. A running total that outgrows its type — 64 bits, or the digits of its decimal — is an error (limit_exceeded), never a wrapped or widened number.

Order and limits. A calculation changes cells, not rows or columns: a calculated measure counts towards max_columns and max_cells as the one column per column value it is. sort … by a calculated measure orders the rows by its aggregate over the whole row, as for any measure — the order of a pivot never depends on what a measure is shown as.

How much is tested

Counted from the repository when this site was built:

Suite	Files
Query conformance cases (crates/opengrid-conformance/cases/)	54
Pivot conformance cases (crates/opengrid-conformance/pivot-cases/)	25
Playwright end-to-end specs (tests/e2e/)	106
of them running axe-core over the result	64

The end-to-end specs drive the elements in a real browser; the ones that load @axe-core/playwright check the result for accessibility in every state they reach — see Accessibility.