Correctness & Drift Tests

OBSL ships a two-tier integration suite for the compiler. Tier 1 — correctness answers "is this query producing the right answer?" Tier 2 — drift answers "is this query producing the same answer it did yesterday?" Each tier catches the bugs the other can't, and they are designed to run together so the cheap regression check (drift) only locks in values that have been independently ratified (correctness).

The full design rationale lives in design/PLAN_correctness_and_drift_tests.md (an internal-only working note — design/ is gitignored). This page is the operator's manual.

TL;DR

Goal	Command
Fast PR check	uv run pytest tests/integration/drift/
Full correctness ratification	uv run pytest tests/integration/correctness/
Both at once	uv run pytest tests/integration/correctness/ tests/integration/drift/
Re-snap after intentional SQL change	UPDATE_SNAPSHOTS=1 uv run pytest tests/integration/drift/
Cross-vendor exec (seven engines; see below)	uv run pytest -m docker tests/integration/drift/vendor_exec/

The first four run in well under a second against the bundled DuckDB seed (examples/orionbelt_1_commerce.duckdb). The vendor-exec sweep takes tens of seconds for the container engines, and a few minutes when cloud credentials are present and Snowflake, BigQuery and Databricks join in.

When does it run automatically?

Tier	CI	Why
Tier 1 — correctness	✅ every push + PR to main	Plain pytest, fast (~1 s)
Tier 2 — drift (DuckDB exec + 8-dialect compile-only + metadata gate)	✅ every push + PR	Plain pytest, fast (~1.5 s)
Phase A — vendor execution sweep	❌ skipped in CI	Marked pytest.mark.docker; needs containers, and cloud credentials for three of the seven engines

CI runs both tiers in a single pytest invocation, so a green workflow implies every drift snapshot is anchored to a green Tier 1 check by construction. The vendor-exec sweep is opt-in — run locally, or wire a separate workflow job that boots service containers if you want it on CI.

Worth knowing what that opt-in costs: a green PR says the SQL compiles and matches its snapshot, not that any engine will run it. See Limitations.

Why two tiers

A snapshot test (golden test, recorded test, approval test) only ever asserts "same as last time". If the snapshot was captured while the query was already wrong, it locks the wrong answer in. Every future run reports green and the bug is invisible until production catches it.

OBSL avoids this by capturing snapshots only after a separate, independent verification that the value is correct. A snapshot is the cache of a correctness proof, never the proof itself.

Tier 1 — correctness (independent verification)
  │
  │   for each query, compute the answer two independent ways
  │   and assert numerical equality
  │
  ▼
Tier 2 — drift (snapshot regression)
  │
  │   capture the SQL + sorted rows of every ratified query
  │   diff future runs against the captured value

Tier 1 — correctness checks

Every correctness test computes a query result two ways: once via the OBSL compiler and once via an independent method that does not exercise the same compiler code path. The methods used in the v0 corpus:

Method	What it catches	Test file
Aggregation invariance — ungrouped == sum of grouped	grain-leak, dim joining wrong fact	test_aggregation_invariance.py
Hierarchical rollup — coarse grain == sum of fine grain	time-grain composition bugs	test_hierarchical_rollup.py
Hand-authored reference SQL — SQL written by reading the schema directly	join-path, role-playing dim, fan-trap	test_hand_sql_reference.py
Pandas baseline — same algorithm rebuilt in pandas	window-frame, cumulative, rolling, period-over-period	test_pandas_baseline.py
Metric algebra — recompute the formula from base measures	expression-evaluation drift, NULLIF, decimal cast	test_metric_algebra.py
CFL split — combined cross-fact == per-fact queries combined	CFL leg corruption, NULL-padding mistakes	test_cfl_split.py
Filter additivity — IN (a,b) == =a + =b	WHERE-clause routing in CFL legs	test_filter_additivity.py

All tests live under tests/integration/correctness/. The bundled DuckDB seed and the matching OBML model (examples/orionbelt_1_commerce.yaml) are the source of truth for every correctness ratification.

Adding a new correctness test

1. Pick at least two methods from the table above. Document the choice in the test docstring so future readers can audit the cross-check.
2. Use the run_query fixture (tests/integration/correctness/conftest.py) — it compiles a QueryObject through the full pipeline, executes against the seed, and returns rows as list[dict] with Decimal values preserved.
3. Use assert_decimal_equal (or pytest.approx for ratios/averages only) to compare. Sums must use exact equality per plan §10.1.

Tier 2 — drift snapshots

Once a query passes its Tier 1 check, the harness captures:

• the compiled SQL string, plus
• the canonical-sorted result rows,

into a YAML file under tests/integration/drift/duckdb/<query_id>.yaml. A pointer to the ratifying Tier 1 test is recorded as last_verified_by:; the metadata gate (tests/integration/drift/test_snapshot_metadata.py) verifies every pointer resolves to a real, collectable pytest test.

Per-dialect SQL strings (no execution, just compile-and-store) live under tests/integration/drift/compile_only/<dialect>/<query_id>.sql. These catch dialect-specific emit drift — DECIMAL → NUMBER for Snowflake, BOOLEAN representation differences, LISTAGG vs STRING_AGG — without needing a live database for any vendor other than DuckDB.

When drift fails

The failure message looks like:

FAILED tests/integration/drift/test_drift_duckdb.py::test_drift_duckdb_exec[02_total_sales_by_country]

Snapshot drift detected (tests/integration/drift/duckdb/02_total_sales_by_country.yaml).
Last verified by tier-1: tests/integration/correctness/test_hand_sql_reference.py::test_obsl_matches_hand_sql[total_sales_by_country].
Diverged fields: sql.
If this change is intentional, re-snap with:
  UPDATE_SNAPSHOTS=1 uv run pytest tests/integration/drift/duckdb/02_total_sales_by_country.yaml

The last verified by line is the trust signal: as long as that Tier 1 test still passes in the same workflow, the underlying correctness still holds and you are looking at a downstream change in SQL emission. If the Tier 1 test also fails, do not re-snap — fix the compiler.

Re-snapping safely

# 1. confirm Tier 1 still passes for the affected query
uv run pytest tests/integration/correctness/

# 2. re-snap drift artefacts (only the failing ones, ideally)
UPDATE_SNAPSHOTS=1 uv run pytest \
    tests/integration/drift/test_drift_duckdb.py::test_drift_duckdb_exec[02_total_sales_by_country]

# 3. verify the new snapshot is green
uv run pytest tests/integration/drift/

# 4. inspect the diff before committing
git diff tests/integration/drift/

CI runs Tier 1 + Tier 2 in a single pytest invocation, so a green workflow implies every snapshot is anchored to a green Tier 1 check by construction.

Corpus structure

The v0 corpus is split across two locations under tests/integration/correctness/ to keep query files faithful to the OBML schema:

correctness/
├── corpus.yaml              ← test-rig manifest (id, description,
│                              lastVerifiedBy, optional handSql block)
├── queries/                 ← pure-OBML query files
│   ├── 01_total_sales.yaml
│   ├── 02_total_sales_by_country.yaml
│   └── ...                  ← each file is exactly the body you would
│                              POST to /v1/query/sql
└── reference_sql/           ← hand-written SQL ratifiers (§3.3)
    └── *.sql

Each entry in corpus.yaml references a query file under queries/<id>.yaml by ID convention. The manifest carries everything that doesn't belong in the OBML schema (the ratification pointer, sort keys for hand-SQL row comparison, etc.).

Adding a new corpus query

1. Drop a pure-OBML YAML file under tests/integration/correctness/queries/<id>.yaml. It is exactly the payload a user would POST to /v1/query/sql — you can copy/paste between the two.
2. Add a Tier 1 test in tests/integration/correctness/ using one or two of the methods listed above (or rely on an existing one if the query is already covered by, e.g., aggregation invariance).
3. Append a manifest entry to corpus.yaml:

   - id: <id>                            # matches the queries/ filename
     description: <one-line summary>
     lastVerifiedBy: tests/integration/correctness/...::<test_node_id>
     handSql:                            # optional, only for §3.3 cases
       refFile: <name>.sql               # under reference_sql/
       sortKeys: [<dim cols>]
   

4. Generate the snapshot:

   UPDATE_SNAPSHOTS=1 uv run pytest \
       tests/integration/drift/test_drift_duckdb.py \
       tests/integration/drift/test_drift_compile_only.py \
       -k <id>
   

5. Re-run without UPDATE_SNAPSHOTS to confirm the new snapshot is green, and commit the query YAML, the manifest entry, and the generated drift artefacts in the same change.

Vendor-execution sweep (Phase A)

Compile-only snapshots cover all 8 registered dialects without needing a database. The vendor-execution sweep adds a stronger check: every corpus query is executed against a real engine and the row set is compared cell-by-cell to the DuckDB golden under drift/duckdb/<id>.yaml.

Seven engines run today. Four come up as testcontainers and need nothing but Docker; three are cloud warehouses reached with credentials from the environment, and skip cleanly when those are absent.

engine	how	needs
DuckDB	in-process	nothing
PostgreSQL 16, MySQL 8, ClickHouse	testcontainers	Docker
Snowflake, BigQuery, Databricks	live connection	credentials in the environment

Dremio is the one dialect with no fixture here. It has its own opt-in stack under tests/integration/dremio/, driven by tests/integration/dremio/run.sh.

tests/integration/drift/vendor_exec/
├── _seed.py                    ← extracts the bundled commerce DuckDB once,
│                                 loads it into each vendor (DOUBLE →
│                                 DECIMAL(18,2) for exact arithmetic)
├── _catalog_values.py          ← expected values for the portable function
│                                 catalog, shared by the function sweep
├── conftest.py                 ← per-vendor fixtures, all yielding the same
│                                 VendorTarget(name, dialect, execute) shape
├── test_vendor_exec.py         ← the corpus, cell-by-cell against the golden
├── test_vendor_measure_sweep.py← every measure and metric of the commerce
│                                 model, asserting it executes at all
├── test_function_exec.py       ← every portable catalog entry, against its
│                                 documented value
├── test_exact_avg_exec.py      ← the exact-AVG rewrite, on values the corpus
│                                 seed deliberately does not contain
└── test_overflow_cast_exec.py  ← a measure that outgrows its type: no engine
                                  may answer with a plausible wrong number

The last two exist because the corpus cannot reach them. Its values sit far below any type's limit, so an aggregate that only misbehaves past a 64-bit boundary needs rows the seed does not carry — and adding them would change the DuckDB golden for every vendor.

Run it:

# Full sweep. Cloud engines skip unless their credentials are present:
uv run pytest -m docker tests/integration/drift/vendor_exec/

# One vendor:
uv run pytest -m docker tests/integration/drift/vendor_exec/test_vendor_exec.py::test_postgres_vendor_exec

# One corpus query, one vendor:
uv run pytest -m docker tests/integration/drift/vendor_exec/ -k "test_postgres_vendor_exec[07_"

Cross-engine result normalisation: tz-naive datetime, midnight-→-date collapse, and numeric values rounded to 12 significant digits via float() to absorb cross-engine division drift on ratios while preserving money sums up to ~$1 T at 2-dp precision.

Limitations

• The container suites do not run in CI. Every check that gates a PR is compile-only, so a query that compiles to valid SQL an engine then refuses stays green until someone runs Docker locally. That is not hypothetical: four ClickHouse CFL cases failed for a month behind green checks, and no compile-only test could have caught them, because the SQL was well formed and only the engine rejected it.
• Dremio has no fixture in this sweep, so its coverage is the separate opt-in stack rather than the corpus.
• The half-boundary cases on rolling AVG / division-precision ratios are intrinsic to each engine's float / decimal arithmetic and are marked xfail in the vendor-exec sweep, not "fixed" by tightening the comparison.