You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ossie.apache.org by "ekut (via GitHub)" <gi...@apache.org> on 2026/10/13 01:46:30 UTC
Re: [PR] Create the initial compliance suite [ossie]
ekut commented on code in PR #237:
URL: https://github.com/apache/ossie/pull/237#discussion_r4253920324
##########
compliance/ADAPTER_INTERFACE.md:
##########
@@ -0,0 +1,85 @@
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Adapter Interface Contract
+
+**Version**: 1.0
+
+---
+
+## Purpose
+
+This document defines the boundary between the **test harness** and an **OSI implementation adapter**. It is the authoritative reference for what adapters may and must not do.
+
+## CLI Contract
+
+```bash
+<adapter> sql --model <model.yaml> --query-file <query.json> --dialect <dialect>
Review Comment:
The adapter returns SQL, and the harness runs it in DuckDB. An engine whose SQL depends on its backend cannot take part that way. ClickHouse, for example, needs `join_use_nulls = 1` for a FULL JOIN to give NULL rather than 0 on the missing side. An engine with no SQL output, such as a service or a DAX engine, has nothing to hand over. Since the suite judges rows and error codes, would the harness accept rows from the engine, run on the dataset loaded into its own backend, as a second adapter mode? For the cases above I ran `gold.sql` in DuckDB and the executor on the same data in ClickHouse.
##########
compliance/foundation/tests/cross_grain/moderate/t-005a-single-step-sum/query.json:
##########
@@ -0,0 +1,12 @@
+{
+ "dataset": "customers",
Review Comment:
A measure here is an object, `{"name": ..., "metric": ...}`, and the query has a top-level `dataset`. #246 §5.1.1 and the query schema of #529 (b5418ee) take measures as strings and have no `dataset`. Which form will the harness pass to an adapter? My cases use the measure objects and leave out `dataset`, which #246 does not define.
##########
compliance/foundation/tests/cross_grain/moderate/t-005a-single-step-sum/model.yaml:
##########
@@ -0,0 +1,37 @@
+name: f_prelude_model
+datasets:
+ - name: customers
+ source: customers
+ primary_key: [id]
Review Comment:
`model.yaml` is a short form: `primary_key` on the dataset, expressions as strings, `dimension: {}`. An engine loads the Ossie schema, where an expression has `dialects`. Is the adapter expected to convert one to the other, or will the cases move to the Ossie schema?
##########
compliance/foundation/DATA_TESTS.md:
##########
@@ -0,0 +1,2258 @@
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# DATA_TESTS.md — Concrete Test Vectors for the Foundation Compliance Suite
+
+This document is the **concrete realization** of the Foundation Conformance
+Decisions catalogued in `foundational_semantics.md`. Each
+**T-NNN** entry below is a runnable test vector: a model, a dataset, a query,
+and the row set or error code an implementation MUST produce. The spec's
+**D-NNN** decisions say *what* an implementation must do; this file says *what
+inputs prove it*.
+
+Implementations are encouraged to translate each entry into a fixture under
+the published Foundation compliance suite — for `osi_python` this is
+[`compliance/foundation/tests/`](/tests/)
+(one folder per `T-NNN` containing `metadata.yaml + model.yaml +
+query.json + gold.sql`). Implementations on other engines run the
+same suite through the standard adapter contract documented in
+[`compliance/ADAPTER_INTERFACE.md`](../../compliance/ADAPTER_INTERFACE.md).
+The expected outputs are normative and implementation-independent.
+
+> **Bootstrap-slice note.** The compliance suite currently ported into
+> this repo only ships fixtures for the T-005 family
+> (`compliance/foundation/tests/cross_grain/moderate/`). Every
+> other `T-NNN` entry below is normative documentation for tests that
+> will land — with their own fixtures — in follow-up PRs. See
+> `compliance/foundation/README.md` for current status.
+
+---
+
+## 1. Conventions
+
+### 1.1 Test entry shape
+
+Every test has the same five fields:
+
+| Field | Meaning |
+|:---|:---|
+| **Anchors** | The spec section(s) and `D-NNN` decision(s) the test pins. |
+| **Fixture** | The named model fixture (§3) the test runs against. |
+| **Data** | The concrete rows. For brevity, fixture-shared data lives in §3 and is referenced by name. Test-local data is shown inline. |
+| **Query** | The semantic query — written in the Foundation surface used by tests (a thin YAML around §5's `Aggregation Query` / `Scalar Query` shapes). |
+| **Expected** | Either a row set (presented as a markdown table, order-insensitive unless noted) or `error: <code>` with the diagnostic content the engine MUST surface. |
+
+A test passes iff the engine's observable output (rows or typed error)
+matches the **Expected** field. Tests do **not** assert on SQL string,
+plan shape, CTE layout, or any other internal artefact (§11.1 of the
+semantics spec).
+
+### 1.2 Row-set comparison
+
+- **Order-insensitive** by default — sort both sides by all output columns before comparing.
+- **NULL-aware** — `NULL == NULL` for the purposes of the test (the SQL three-valued logic does not apply to assertions).
+- **Type-aware** — numeric values are compared by value, not by representation; `100`, `100.0`, and `1e2` are equal. String values must match exactly.
+
+### 1.3 Error comparison
+
+An error result is a triple `(code, anchor, must_contain)`:
+
+```yaml
+error:
+ code: E_AGGREGATE_IN_WHERE
+ anchor: §5.3 # the spec section the engine MUST cite in its diagnostic
+ must_contain: # substrings the diagnostic MUST include (case-insensitive)
+ - "aggregate"
+ - "Where"
+```
+
+An engine MAY include additional diagnostic content. The code and the
+`must_contain` substrings are the only assertions; wording is otherwise free.
+
+### 1.4 Determinism guarantee
+
+Every test that produces a row set MUST be deterministic: running it
+twice on the same fixture MUST produce byte-identical SQL and the same
+multiset of rows. This is also `D-014`'s test shape, applied uniformly.
+
+---
+
+### 1.5 NULL handling, empty groups, and `0` (D-033)
+
+The Foundation follows **standard SQL** for aggregates over empty
+input row sets (§6.11 / D-033):
+
+| Aggregate family | Empty-input result |
+|:---|:---|
+| `COUNT(*)`, `COUNT(x)`, `COUNT(DISTINCT x)` | `0` |
+| `SUM`, `AVG`, `MIN`, `MAX`, `MEDIAN`, percentiles, `STDDEV`, `VARIANCE` | `NULL` |
+
+Tests in this catalog use these results literally — but **only for
+dim values that actually appear in the result**. Per §1.6 (Plan A for
+single-measure shapes), a dim value with no matching fact row is
+dropped from the result entirely; there is no empty-aggregate cell
+because there is no row.
+
+The empty-aggregate rule applies to:
+
+- **Multi-measure stitch cells** where one branch has no contribution
+ for a dim value present via the other branch (T-011, T-045) —
+ `SUM` → `NULL`, `COUNT` → `0`.
+- **Dim values present via the `NULL`-key orphan bucket** when the
+ fact row has no matching dim row (T-047, T-053 etc.) — same rule
+ applies; the orphan-bucket row is part of the result, and its
+ cells follow standard SQL.
+
+Models that prefer `0` for a `SUM` cell MUST declare it explicitly:
+`COALESCE(SUM(amount), 0) AS revenue`. The Foundation does NOT
+auto-rewrite `SUM` to `COALESCE(SUM, 0)`.
+
+---
+
+### 1.6 Resolved — single-measure shapes follow Plan A (preserve facts)
+
+Earlier drafts of this catalog had an unresolved tension between two
+flagship tests:
+
+- **T-001** (`Dims: [customers.region]; Measures: [SUM(orders.amount)]`)
+ expected a `NORTH` row with `revenue = NULL` — implying the planner
+ preserves the dimension domain even when no fact rows match.
+- **T-006** (`Dims: [customers.region]; Measures: [COUNT(*)]; Where
+ orders.status = 'completed'`) expected NORTH to *not* appear —
+ implying the planner anchors on the fact and drops dim values with
+ no matching fact.
+
+Both could not be correct under one planner. The Architect resolved
+this in favour of **Plan A — preserve facts, drop unmatched dim values
+— for single-measure aggregation queries**, with the **multi-measure
+shape using FULL OUTER stitch (§6.6 row 3) to preserve both sides** as
+a deliberate contrast. The spec was updated to match (§6.6, §6.2 step 7,
+D-001 example), and existing tests were brought into alignment.
+
+#### Decision summary
+
+| Query shape | Default join | Which dim values appear in the result |
+|:---|:---|:---|
+| Single-measure aggregation: `Dims: [dim.X]; Measures: [SUM(fact.Y)]` | `fact LEFT JOIN dim` (§6.6 row 1) | Distinct `dim.X` reached by a surviving `fact` row, plus a `NULL`-key bucket for orphan facts. **A dim value with no matching fact does NOT appear.** |
+| Multi-measure aggregation: `Dims: [dim.X]; Measures: [SUM(factA.Y), SUM(factB.Y)]` | Each measure independently resolved per row 1, then `FULL OUTER` stitch on shared dims (§6.6 row 3) | Union of dim values reachable from *either* branch. A dim value appears if either fact pulls it in. |
+
+This rule is now consistent across every test in the catalog:
+
+- **T-001, T-005a-e, T-026, T-028, T-047, T-053**: single-measure or
+ single-fact-source queries — NORTH does not appear.
+- **T-006**: single-measure query with a fact-level WHERE — NORTH
+ does not appear.
+- **T-011, T-045**: multi-measure stitch — NORTH appears via the
+ branch that has data for customer 4.
+
+#### How a user gets the "preserve all dim values" behaviour
+
+If a user wants every dim value to appear (BI-tool convention), they
+make the query multi-measure by adding a measure sourced from the dim
+dataset itself:
+
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - SUM(orders.amount) AS revenue
+ - COUNT(customers.id) AS customer_count
+```
+
+This converts the shape into a multi-measure stitch — the `customers`
+branch contributes every distinct region (because `COUNT(customers.id)`
+is defined for every region in `customers`), and the FULL OUTER
+preserves NORTH with `revenue = NULL`, `customer_count = 1`.
+
+This is the BI-tool "all regions appear" behaviour, surfaced
+*deliberately* through the query shape rather than as a hidden
+planner default. The user opts in by asking for it.
+
+#### Why Plan A (not preserve-dims by default)
+
+- **Matches raw SQL.** `SELECT dim.X, SUM(fact.Y) FROM fact LEFT JOIN
+ dim ... GROUP BY dim.X` produces exactly the single-measure Plan A
+ result. The Foundation does not introduce surprises relative to
+ the SQL the user could write by hand.
+- **Matches Snowflake Semantic Views.** Snowflake's default plan
+ shape is also Plan A — porting Snowflake models to OSI is a no-op
+ for this dimension behaviour.
+- **Composes cleanly with multi-measure stitch.** §6.6 row 3 already
+ mandates FULL OUTER for multi-measure shapes; row 1 says LEFT for
+ single-measure shapes. The contrast is the mental model: "the
+ multi-measure stitch is the *only* place both sides are preserved
+ — and that's because there is no other correct shape for merging
+ two independently-aggregated facts."
+- **Surfaces orphan facts.** Plan A keeps the `NULL`-key bucket for
+ orphan fact rows (e.g. order 105 with `customer_id = 99`). This is
+ a data-quality signal — preserve-dims would lose it.
+
+#### Trade-off accepted
+
+A first-time BI user typing a single-measure query and *expecting* to
+see every region might be surprised when a region with no fact data
+is absent. The mitigation is the documented multi-measure pattern
+above. The Foundation prefers "predictable, matches raw SQL" over
+"BI-tool-magical".
+
+> **Note for test authors.** When writing a new test that groups by a
+> dimension over a single-fact source, write the expected rows
+> following Plan A: include only dim values reached by a fact row,
+> plus the `NULL`-key bucket for orphans. If you want every dim value
+> in the expected rows, the test must be multi-measure (with one of
+> the measures sourced from the dim dataset).
+
+---
+
+## 2. How to add a new test
+
+1. Pick the lowest-numbered free `T-NNN`.
+2. Link it to a `D-NNN` in `foundational_semantics.md` — if no `D-NNN` covers the behaviour, add one there first.
+3. Write the test against an existing fixture (§3) if possible; create a new fixture only when the existing ones can't express the shape.
+4. Cite the test from the relevant spec § so the doc and the test grow together.
+
+---
+
+## 3. Common fixtures
+
+### 3.1 Fixture **F-PRELUDE** — single-fact star with multi-fact extension
+
+Mirrors the *Prelude model* in `foundational_semantics.md` (§4).
+
+```yaml
+datasets:
+ - name: customers
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: region, type: string }
+ - { name: segment, type: string }
+
+ - name: orders
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: customer_id, type: integer }
+ - { name: amount, type: decimal(10,2) }
+ - { name: status, type: string }
+
+ - name: returns
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: customer_id, type: integer }
+ - { name: amount, type: decimal(10,2) }
+
+ - name: premium_customers
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+
+relationships:
+ - { name: orders_to_customer, from: orders, to: customers, from_columns: [customer_id], to_columns: [id] } # N:1
+ - { name: returns_to_customer, from: returns, to: customers, from_columns: [customer_id], to_columns: [id] } # N:1
+```
+
+**Data:**
+
+`customers`:
+
+| id | region | segment |
+|:--:|:-------|:--------|
+| 1 | EAST | retail |
+| 2 | EAST | retail |
+| 3 | WEST | wholesale |
+| 4 | NORTH | retail |
+
+`orders` (note: orphan order 105 with `customer_id = 99` not in `customers`):
+
+| id | customer_id | amount | status |
+|:---:|:-----------:|-------:|:-----------|
+| 101 | 1 | 100.00 | completed |
+| 102 | 1 | 50.00 | completed |
+| 103 | 2 | 200.00 | pending |
+| 104 | 3 | 75.00 | completed |
+| 105 | 99 | 30.00 | completed | ← orphan: customer 99 not in customers
+
+`returns`:
+
+| id | customer_id | amount |
+|:---:|:-----------:|-------:|
+| 201 | 1 | 10.00 |
+| 202 | 3 | 5.00 |
+| 203 | 4 | 15.00 | ← customer 4 has a return but no order
+
+`premium_customers`:
+
+| id |
+|:--:|
+| 1 |
+| 3 |
+
+### 3.2 Fixture **F-BRIDGE** — M:N through a bridge
+
+Mirrors *Mini-model M2* in Appendix A. This is the fixture for the
+flagship `T-015` bridge-deduplication test.
+
+```yaml
+datasets:
+ - name: actors
+ primary_key: [actor_id]
+ fields:
+ - { name: actor_id, type: integer }
+ - { name: name, type: string }
+ - { name: height, type: integer }
+
+ - name: movies
+ primary_key: [movie_id]
+ fields:
+ - { name: movie_id, type: integer }
+ - { name: title, type: string }
+ - { name: gross, type: decimal(10,2) }
+
+ - name: appearances
+ primary_key: [actor_id, movie_id]
+ role: bridge
+ fields:
+ - { name: actor_id, type: integer }
+ - { name: movie_id, type: integer }
+
+relationships:
+ - { name: app_to_actor, from: appearances, to: actors, from_columns: [actor_id], to_columns: [actor_id] } # N:1
+ - { name: app_to_movie, from: appearances, to: movies, from_columns: [movie_id], to_columns: [movie_id] } # N:1
+```
+
+**Data:**
+
+`actors`:
+
+| actor_id | name | height |
+|:--------:|:------|-------:|
+| 1 | Alice | 170 |
+| 2 | Bob | 170 |
+| 3 | Carol | 180 |
+
+`movies`:
+
+| movie_id | title | gross |
+|:--------:|:-------|-------:|
+| 10 | Action | 100.00 |
+| 11 | Drama | 200.00 |
+| 12 | Comedy | 50.00 |
+
+`appearances`:
+
+| actor_id | movie_id |
+|:--------:|:--------:|
+| 1 | 10 |
+| 1 | 11 |
+| 2 | 10 | ← M10 (Action) has two actors at height 170
+| 3 | 12 |
+
+### 3.3 Fixture **F-BRIDGE-NONE** — variant of F-BRIDGE without the bridge
+
+Same `actors` and `movies` data as F-BRIDGE, but no `appearances` dataset
+and an undeclared M:N edge between `actors` and `movies`. Used to test
+that the engine fails closed instead of guessing a join path.
+
+```yaml
+datasets:
+ - name: actors
+ primary_key: [actor_id]
+ fields: [ { name: actor_id }, { name: name }, { name: height } ]
+ - name: movies
+ primary_key: [movie_id]
+ fields: [ { name: movie_id }, { name: title }, { name: gross } ]
+
+# Note: no `relationships:` block. The model declares no edge; the
+# engine MUST NOT silently fabricate one.
+```
+
+### 3.4 Fixture **F-AMBIG** — two relationships between the same datasets
+
+Mirrors *Mini-model M3* in Appendix A. Used for `E_AMBIGUOUS_PATH`.
+
+```yaml
+datasets:
+ - name: orders
+ primary_key: [id]
+ fields:
+ - { name: id }
+ - { name: placed_by_id }
+ - { name: fulfilled_by_id }
+ - { name: amount }
+
+ - name: users
+ primary_key: [id]
+ fields: [ { name: id }, { name: region } ]
+
+relationships:
+ - { name: order_placed_by, from: orders, to: users, from_columns: [placed_by_id], to_columns: [id] }
+ - { name: order_fulfilled_by, from: orders, to: users, from_columns: [fulfilled_by_id], to_columns: [id] }
+```
+
+**Data:**
+
+`orders`:
+
+| id | placed_by_id | fulfilled_by_id | amount |
+|:---:|:------------:|:---------------:|-------:|
+| 301 | 1 | 2 | 100.00 |
+| 302 | 2 | 2 | 50.00 |
+
+`users`:
+
+| id | region |
+|:--:|:-------|
+| 1 | EAST |
+| 2 | WEST |
+
+### 3.5 Fixture **F-NOPATH** — two disconnected datasets
+
+Mirrors *Mini-model M1* in Appendix A. No relationships are declared.
+
+```yaml
+datasets:
+ - name: orders
+ primary_key: [id]
+ fields: [ { name: id }, { name: customer_id }, { name: amount } ]
+
+ - name: inventory_movements
+ primary_key: [movement_id]
+ fields: [ { name: movement_id }, { name: warehouse_id }, { name: quantity } ]
+```
+
+---
+
+## 4. Tests
+
+### 4.A Query shape
+
+#### T-001 — Aggregation-query cardinality is `DISTINCT(Dimensions)`
+
+**Anchors:** §5.1.1, §5.2 · D-001
+**Fixture:** F-PRELUDE
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - SUM(orders.amount) AS revenue
+```
+
+**Expected (single-measure ⇒ Plan A per §6.6 row 1 and §6.2 step 7):**
+
+| region | revenue |
+|:-------|--------:|
+| EAST | 350.00 |
+| WEST | 75.00 |
+| NULL | 30.00 | ← orphan order 105 (customer_id = 99 not in customers)
+
+Row count = `COUNT(DISTINCT region)` over the regions reached by an
+`orders` row, plus one for the orphan-customer bucket. NORTH (customer
+4's region) does **not** appear: customer 4 has no orders, and a
+single-measure aggregation query follows the fact's natural LEFT join
+shape (`orders LEFT JOIN customers`) — a dim value reachable only
+from the dim domain is *not* in the result.
+
+Engines that "carry" `customer_id` into the output grain because the
+measure is sourced from `orders` fail this test on row count (they
+would group by `customer_id`, not `region`, and produce four data
+rows + orphan). Engines that produce a `NORTH | NULL` row are
+applying the multi-measure stitch shape to a single-measure query —
+also wrong (see §1.6).
+
+If the user wants NORTH to appear with `revenue = NULL`, they must
+make the query multi-measure (e.g. `Measures: [SUM(orders.amount) AS
+revenue, COUNT(customers.id) AS customer_count]`) — the FULL OUTER
+stitch in §6.6 row 3 then preserves every region present in
+`customers`. See T-011 for the canonical multi-measure shape.
+
+#### T-002 — Mixing `Dimensions` and `Fields` is rejected
+
+**Anchors:** §5.1 · D-010
+**Fixture:** F-PRELUDE
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Fields: [customers.id]
+Measures: [SUM(orders.amount)]
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_MIXED_QUERY_SHAPE
+ anchor: §5.1
+ must_contain: ["Dimensions", "Fields", "scalar"]
+```
+
+#### T-003 — Bare metric reference inside `Fields` is rejected
+
+**Anchors:** §5.1.2 · D-011
+**Fixture:** F-PRELUDE. Assume `orders.total_revenue = SUM(amount)` is declared.
+
+**Query:**
+```yaml
+Fields: [orders.id, orders.total_revenue]
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_AGGREGATE_IN_SCALAR_QUERY
+ anchor: §5.1.2
+ must_contain: ["scalar", "total_revenue", "aggregate"]
+```
+
+A scalar query asks for row-level values; a metric is by definition an
+aggregate at query grain, which has no defined value at the row grain
+of `orders`. The engine MUST NOT silently aggregate, broadcast, or
+"window" the metric.
+
+---
+
+### 4.B Field & metric grain
+
+#### T-004 — Implicit home-grain aggregation in a field expression
+
+**Anchors:** §4.3 · D-003, D-015
+**Fixture:** F-PRELUDE. Add this field to the model:
+
+```yaml
+fields:
+ - name: customers.lifetime_value
+ expression: "SUM(orders.amount)"
+```
+
+**Query (scalar):**
+```yaml
+Fields: [customers.region, customers.id, customers.lifetime_value]
+```
+
+**Expected:**
+
+| region | id | lifetime_value |
+|:-------|:--:|---------------:|
+| EAST | 1 | 150.00 | ← SUM(100, 50)
+| EAST | 2 | 200.00 |
+| WEST | 3 | 75.00 |
+| NORTH | 4 | NULL | ← no orders (or 0, depending on engine's LEFT-aggregate convention — both are acceptable; test allows either)
+
+The inner `SUM` is evaluated at the **home grain of `customers`** (one
+sum per customer row), regardless of any grouping the consuming query
+applies. This is what makes `lifetime_value` reusable across queries.
+
+**Compilation-strategy equivalence (D-015):** an engine MAY compile the
+inner `SUM` as (a) a correlated subquery, (b) a `LATERAL` / `CROSS APPLY`,
+or (c) a pre-aggregated CTE joined back to `customers`. The test passes
+under any of the three.
+
+#### T-005 — Cross-grain aggregation: single-step and nested forms
+
+This is the **flagship test family for D-020** — the most-debated
+semantic in cross-vendor BI portability. The Foundation accepts **both**
+single-step and explicit-nested forms over a `1 : N` edge; they give
+different numerical answers for non-distributive aggregates (the
+single-step form gives the "all rows at once" answer, the nested form
+gives the "per-home-row first" answer). For distributive aggregates
+they give identical answers, so either form is acceptable for style.
+
+**Anchors:** §4.5 form (1), §6.1 Semantic 2 · D-020
+
+**Fixture:** F-PRELUDE.
+
+---
+
+##### T-005a — Single-step cross-grain `SUM` over `1 : N` is accepted (distributive)
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.total_order_amount
+ expression: "SUM(orders.amount)" # single-step; resolves at query grain
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.total_order_amount]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent — see T-001):**
+
+| region | total_order_amount |
+|:-------|-------------------:|
+| EAST | 350.00 | ← orders 101, 102, 103
+| WEST | 75.00 | ← order 104
+| NULL | 30.00 | ← orphan order 105
+
+No error. For distributive aggregates the single-step form is
+equivalent to the explicit-nested form `SUM(SUM(orders.amount))` — both
+return identical numbers (cross-validated by T-005d below).
+
+---
+
+##### T-005b — Single-step cross-grain `AVG` over `1 : N` is accepted (heavy-weighted)
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.avg_order_amount
+ expression: "AVG(orders.amount)" # single-step; standard SQL semantics
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.avg_order_amount]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):** the heavy-customer-weighted SQL average.
+
+| region | avg_order_amount |
+|:-------|-----------------:|
+| EAST | 116.67 | ← AVG(100, 50, 200) = 350/3 — three orders in EAST
+| WEST | 75.00 | ← AVG(75) — one order in WEST
+| NULL | 30.00 | ← orphan order 105
+
+No error. Note that EAST's answer (`116.67`) is **not** the unweighted
+average across customers — Customer 1 averages `75` over two orders,
+Customer 2 averages `200` over one order, and the unweighted answer
+would be `(75 + 200) / 2 = 137.5`. That second number is what the
+**nested** form produces; see T-005c.
+
+---
+
+##### T-005c — Explicit nested cross-grain `AVG` over `1 : N` is accepted (unweighted)
+
+The Snowflake-style explicit form is **still accepted**; it just gives a
+different (and meaningfully different) answer for non-distributive
+aggregates. The Foundation does not force this style — but neither does
+it reject it. Users porting from Snowflake Semantic Views can leave their
+nested expressions unchanged.
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.avg_of_per_customer_avg
+ expression: "AVG(AVG(orders.amount))" # inner: per-customer avg; outer: avg across customers
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.avg_of_per_customer_avg]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):** the unweighted average across customers in each region.
+
+| region | avg_of_per_customer_avg |
+|:-------|------------------------:|
+| EAST | 137.50 | ← AVG(Cust1=75, Cust2=200) = 275/2 — two customers in EAST
+| WEST | 75.00 | ← AVG(Cust3=75) — one customer in WEST
+| NULL | 30.00 | ← orphan order's customer is unknown; treated as one customer-of-one-order
+
+The user gets to pick: write `AVG(orders.amount)` for the
+heavy-weighted answer (T-005b) or `AVG(AVG(orders.amount))` for the
+unweighted answer (T-005c). Both are conformant. The two numbers differ
+for non-distributive aggregates — this difference is the central
+BI-portability decision in D-020.
+
+---
+
+##### T-005d — Single-step and nested `SUM` are numerically equivalent (distributive)
+
+Run both forms against identical data and assert the row sets are
+identical.
+
+**Metric A (single-step):**
+```yaml
+- name: customers.sum_single
+ expression: "SUM(orders.amount)"
+```
+
+**Metric B (nested):**
+```yaml
+- name: customers.sum_nested
+ expression: "SUM(SUM(orders.amount))"
+```
+
+**Query A:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.sum_single]
+```
+
+**Query B:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.sum_nested]
+```
+
+**Expected:** identical row sets (after column-name normalisation).
+Both forms are single-measure aggregation queries ⇒ Plan A ⇒ NORTH
+is absent and the orphan `NULL`-region row is present. This test is
+what makes "port your Snowflake nested form unchanged" portable in
+the distributive case — and is the canonical demonstration that
+nested-vs-single is purely a stylistic choice when the aggregate is
+distributive.
+
+---
+
+##### T-005e — `COUNT(DISTINCT)` single-step over `1 : N` is accepted
+
+**Anchors:** §4.5, §6.1 · D-020 case (d), D-022 caveat
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.distinct_order_statuses
+ expression: "COUNT(DISTINCT orders.status)"
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.distinct_order_statuses]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):**
+
+| region | distinct_order_statuses |
+|:-------|------------------------:|
+| EAST | 2 | ← `completed` (101, 102) + `pending` (103)
+| WEST | 1 | ← `completed` (104)
+| NULL | 1 | ← orphan 105 has `completed`
+
+No `E_UNSAFE_REAGGREGATION`. This pins D-020 (d) and the D-022 caveat
+explicitly: a holistic aggregate over a plain `1 : N` edge is one SQL
+aggregate over the joined rows — well-defined per D-020. The
+`E_UNSAFE_REAGGREGATION` error only fires when the plan would require
+*decomposing* a holistic aggregate across grains, which happens on M:N
+(D-022) or fan-out shapes the engine cannot pre-aggregate safely.
+
+#### T-006 — `COUNT(*)` over a dataset is well-defined at the home grain
+
+**Anchors:** §7.2 · D-016
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - COUNT(*) AS order_count
+Where:
+ - orders.status = 'completed'
+```
+
+**Expected:**
+
+| region | order_count |
+|:-------|------------:|
+| EAST | 2 | ← orders 101, 102 (102? wait 103 is pending). 101+102 completed. 103 pending excluded.
+| WEST | 1 | ← order 104
+| NULL | 1 | ← orphan order 105 (status=completed)
+
+`COUNT(*)` is the count of rows of the **measure-bearing dataset**
+that survive the predicates — here, `orders`. Engines that emit
+`COUNT(<orders.primary_key>)` as a transparent rewrite (e.g.
+Snowflake's `COUNT(*)`-rejection workaround) MUST produce the same
+numbers.
+
+---
+
+### 4.C Predicate routing
+
+#### T-007 — Aggregate in `Where` is rejected
+
+**Anchors:** §5.3 · D-005, D-012a
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [SUM(orders.amount)]
+Where:
+ - SUM(orders.amount) > 100 # aggregate inside Where
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_AGGREGATE_IN_WHERE
+ anchor: §5.3
+ must_contain: ["aggregate", "Where", "Having"] # diagnostic should hint at Having
+```
+
+The diagnostic MUST identify the offending aggregate by name and SHOULD
+suggest moving the predicate to `Having`.
+
+#### T-008 — Pure row-level predicate in `Having` is rejected
+
+**Anchors:** §5.3 · D-012b
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [SUM(orders.amount)]
+Having:
+ - customers.region = 'EAST' # row-level dimension predicate
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_NON_AGGREGATE_IN_HAVING
+ anchor: §5.3
+ must_contain: ["Having", "Where"]
+```
+
+Note this test is about *routing*, not behaviour. An engine that
+silently treats `Having` as `Where` produces the right numbers but
+breaks the contract, and so fails the test on the error code, not
+the rows.
+
+#### T-009 — Mixed-level boolean predicate is rejected
+
+**Anchors:** §5.3 · D-012c
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Having:
+ - customers.region = 'EAST' AND SUM(orders.amount) > 100
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_MIXED_PREDICATE_LEVEL
+ anchor: §5.3
+ must_contain: ["row-level", "aggregate", "split"]
+```
+
+The diagnostic SHOULD suggest splitting the predicate (row-level half
+→ `Where`, aggregate half → `Having`).
+
+---
+
+### 4.D Default join behaviour
+
+#### T-010 — `N:1` enrichment defaults to `LEFT`; orphans appear under `NULL`
+
+**Anchors:** §6.4 · D-004a
+**Fixture:** F-PRELUDE.
+
+Covered indirectly by T-001's expected rows — the `region = NULL` row
+exists *only because* the default join from `orders → customers` is
+`LEFT` and order 105's `customer_id = 99` does not match. An engine
+that defaults to `INNER` returns one fewer row and fails the test on
+row count. This is the single most common silent-correctness failure
+in BI tools (Snowflake's "first matching row" rule is the worst case;
+see the Snowflake divergence note in the spec).
+
+#### T-011 — Multi-fact composition defaults to `FULL OUTER` on shared dims
+
+**Anchors:** §6.4 · D-004b
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - SUM(orders.amount) AS revenue
+ - SUM(returns.amount) AS return_total
+```
+
+**Expected (multi-measure ⇒ FULL OUTER stitch per §6.6 row 3 / §6.2
+step 7; *both* sides preserved):**
+
+| region | revenue | return_total |
+|:-------|--------:|-------------:|
+| EAST | 350.00 | 10.00 | ← both facts have data
+| WEST | 75.00 | 5.00 | ← both facts have data
+| NORTH | NULL | 15.00 | ← only `returns` has data (customer 4); revenue is `SUM` over zero rows = `NULL` (§6.11 / D-033)
+| NULL | 30.00 | NULL | ← only `orders` has data (orphan 105); return_total is `SUM` over zero rows = `NULL`
+
+This is the **multi-measure** counterpart to T-001's single-measure
+Plan A: because there are two independently-aggregated facts
+(`orders` and `returns`), §6.6 row 3 mandates a `FULL OUTER` between
+the two pre-aggregated branches. The union of dim values appearing in
+*either* branch is the result. NORTH appears here (via the `returns`
+branch) because customer 4 has a return, even though customer 4 has
+no orders. Without `returns` as a second measure, this would be a
+single-measure query and Plan A would drop NORTH (see T-001). An
+engine that defaults to `INNER` between the two pre-aggregated
+branches loses NORTH and the orphan-bucket, failing on row count.
+
+**Companion `COUNT` test:** replacing either `SUM` with `COUNT(*)` or
+`COUNT(orders.id)` would produce `0` in the missing-side cell, not
+`NULL`, per standard SQL. See T-047 for the explicit
+`SUM`-returns-`NULL` / `COUNT`-returns-`0` contrast.
+
+#### T-012 — Scalar grand total uses `CROSS JOIN`
+
+**Anchors:** §6.4 · D-004c
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [] # no dimensions ⇒ scalar grain
+Measures:
+ - SUM(orders.amount) AS total_revenue
+ - SUM(returns.amount) AS total_returns
+```
+
+**Expected:** exactly one row.
+
+| total_revenue | total_returns |
+|--------------:|--------------:|
+| 455.00 | 30.00 |
+
+`455.00 = 100+50+200+75+30` (all orders including orphan);
+`30.00 = 10+5+15` (all returns). The two scalars sit on a single row
+joined via `CROSS JOIN` (`1 ⋈ 1`), not on separate rows.
+
+---
+
+### 4.E M:N resolution — the flagship section
+
+#### T-013 — Two unrelated facts with no shared dimension raise `E3013`
Review Comment:
T-013 expects E3013 for two measures of unrelated facts with no dimensions. Step 7 of #246 at cc0d070 (L785) and §6.6 row 4 (L889) answer the same query with one row of each fact's total, and JB read it so on dev@ [1]. Appendix A (L1283) supports E3013. Its rationale cites "Mini-model M1" in Appendix A and "Promise 4", which I could not find in any commit of #246. I asked which outcome holds in the #246 review [2]; until then, which one should the suite hold?
[1] https://lists.apache.org/thread/cwfkw2pdortoproz5k96l3ksgt2hg46x
[2] https://github.com/apache/ossie/pull/246#discussion_r4253917296
##########
compliance/foundation/DATA_TESTS.md:
##########
@@ -0,0 +1,2258 @@
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# DATA_TESTS.md — Concrete Test Vectors for the Foundation Compliance Suite
+
+This document is the **concrete realization** of the Foundation Conformance
+Decisions catalogued in `foundational_semantics.md`. Each
+**T-NNN** entry below is a runnable test vector: a model, a dataset, a query,
+and the row set or error code an implementation MUST produce. The spec's
+**D-NNN** decisions say *what* an implementation must do; this file says *what
+inputs prove it*.
+
+Implementations are encouraged to translate each entry into a fixture under
+the published Foundation compliance suite — for `osi_python` this is
+[`compliance/foundation/tests/`](/tests/)
+(one folder per `T-NNN` containing `metadata.yaml + model.yaml +
+query.json + gold.sql`). Implementations on other engines run the
+same suite through the standard adapter contract documented in
+[`compliance/ADAPTER_INTERFACE.md`](../../compliance/ADAPTER_INTERFACE.md).
+The expected outputs are normative and implementation-independent.
+
+> **Bootstrap-slice note.** The compliance suite currently ported into
+> this repo only ships fixtures for the T-005 family
+> (`compliance/foundation/tests/cross_grain/moderate/`). Every
+> other `T-NNN` entry below is normative documentation for tests that
+> will land — with their own fixtures — in follow-up PRs. See
+> `compliance/foundation/README.md` for current status.
+
+---
+
+## 1. Conventions
+
+### 1.1 Test entry shape
+
+Every test has the same five fields:
+
+| Field | Meaning |
+|:---|:---|
+| **Anchors** | The spec section(s) and `D-NNN` decision(s) the test pins. |
+| **Fixture** | The named model fixture (§3) the test runs against. |
+| **Data** | The concrete rows. For brevity, fixture-shared data lives in §3 and is referenced by name. Test-local data is shown inline. |
+| **Query** | The semantic query — written in the Foundation surface used by tests (a thin YAML around §5's `Aggregation Query` / `Scalar Query` shapes). |
+| **Expected** | Either a row set (presented as a markdown table, order-insensitive unless noted) or `error: <code>` with the diagnostic content the engine MUST surface. |
+
+A test passes iff the engine's observable output (rows or typed error)
+matches the **Expected** field. Tests do **not** assert on SQL string,
+plan shape, CTE layout, or any other internal artefact (§11.1 of the
+semantics spec).
+
+### 1.2 Row-set comparison
+
+- **Order-insensitive** by default — sort both sides by all output columns before comparing.
+- **NULL-aware** — `NULL == NULL` for the purposes of the test (the SQL three-valued logic does not apply to assertions).
+- **Type-aware** — numeric values are compared by value, not by representation; `100`, `100.0`, and `1e2` are equal. String values must match exactly.
+
+### 1.3 Error comparison
+
+An error result is a triple `(code, anchor, must_contain)`:
+
+```yaml
+error:
+ code: E_AGGREGATE_IN_WHERE
+ anchor: §5.3 # the spec section the engine MUST cite in its diagnostic
+ must_contain: # substrings the diagnostic MUST include (case-insensitive)
+ - "aggregate"
+ - "Where"
+```
+
+An engine MAY include additional diagnostic content. The code and the
+`must_contain` substrings are the only assertions; wording is otherwise free.
+
+### 1.4 Determinism guarantee
+
+Every test that produces a row set MUST be deterministic: running it
+twice on the same fixture MUST produce byte-identical SQL and the same
+multiset of rows. This is also `D-014`'s test shape, applied uniformly.
+
+---
+
+### 1.5 NULL handling, empty groups, and `0` (D-033)
+
+The Foundation follows **standard SQL** for aggregates over empty
+input row sets (§6.11 / D-033):
+
+| Aggregate family | Empty-input result |
+|:---|:---|
+| `COUNT(*)`, `COUNT(x)`, `COUNT(DISTINCT x)` | `0` |
+| `SUM`, `AVG`, `MIN`, `MAX`, `MEDIAN`, percentiles, `STDDEV`, `VARIANCE` | `NULL` |
+
+Tests in this catalog use these results literally — but **only for
+dim values that actually appear in the result**. Per §1.6 (Plan A for
+single-measure shapes), a dim value with no matching fact row is
+dropped from the result entirely; there is no empty-aggregate cell
+because there is no row.
+
+The empty-aggregate rule applies to:
+
+- **Multi-measure stitch cells** where one branch has no contribution
+ for a dim value present via the other branch (T-011, T-045) —
+ `SUM` → `NULL`, `COUNT` → `0`.
+- **Dim values present via the `NULL`-key orphan bucket** when the
+ fact row has no matching dim row (T-047, T-053 etc.) — same rule
+ applies; the orphan-bucket row is part of the result, and its
+ cells follow standard SQL.
+
+Models that prefer `0` for a `SUM` cell MUST declare it explicitly:
+`COALESCE(SUM(amount), 0) AS revenue`. The Foundation does NOT
+auto-rewrite `SUM` to `COALESCE(SUM, 0)`.
+
+---
+
+### 1.6 Resolved — single-measure shapes follow Plan A (preserve facts)
+
+Earlier drafts of this catalog had an unresolved tension between two
+flagship tests:
+
+- **T-001** (`Dims: [customers.region]; Measures: [SUM(orders.amount)]`)
+ expected a `NORTH` row with `revenue = NULL` — implying the planner
+ preserves the dimension domain even when no fact rows match.
+- **T-006** (`Dims: [customers.region]; Measures: [COUNT(*)]; Where
+ orders.status = 'completed'`) expected NORTH to *not* appear —
+ implying the planner anchors on the fact and drops dim values with
+ no matching fact.
+
+Both could not be correct under one planner. The Architect resolved
+this in favour of **Plan A — preserve facts, drop unmatched dim values
+— for single-measure aggregation queries**, with the **multi-measure
+shape using FULL OUTER stitch (§6.6 row 3) to preserve both sides** as
+a deliberate contrast. The spec was updated to match (§6.6, §6.2 step 7,
+D-001 example), and existing tests were brought into alignment.
+
+#### Decision summary
+
+| Query shape | Default join | Which dim values appear in the result |
+|:---|:---|:---|
+| Single-measure aggregation: `Dims: [dim.X]; Measures: [SUM(fact.Y)]` | `fact LEFT JOIN dim` (§6.6 row 1) | Distinct `dim.X` reached by a surviving `fact` row, plus a `NULL`-key bucket for orphan facts. **A dim value with no matching fact does NOT appear.** |
+| Multi-measure aggregation: `Dims: [dim.X]; Measures: [SUM(factA.Y), SUM(factB.Y)]` | Each measure independently resolved per row 1, then `FULL OUTER` stitch on shared dims (§6.6 row 3) | Union of dim values reachable from *either* branch. A dim value appears if either fact pulls it in. |
+
+This rule is now consistent across every test in the catalog:
+
+- **T-001, T-005a-e, T-026, T-028, T-047, T-053**: single-measure or
+ single-fact-source queries — NORTH does not appear.
+- **T-006**: single-measure query with a fact-level WHERE — NORTH
+ does not appear.
+- **T-011, T-045**: multi-measure stitch — NORTH appears via the
+ branch that has data for customer 4.
+
+#### How a user gets the "preserve all dim values" behaviour
+
+If a user wants every dim value to appear (BI-tool convention), they
+make the query multi-measure by adding a measure sourced from the dim
+dataset itself:
+
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - SUM(orders.amount) AS revenue
+ - COUNT(customers.id) AS customer_count
+```
+
+This converts the shape into a multi-measure stitch — the `customers`
+branch contributes every distinct region (because `COUNT(customers.id)`
+is defined for every region in `customers`), and the FULL OUTER
+preserves NORTH with `revenue = NULL`, `customer_count = 1`.
+
+This is the BI-tool "all regions appear" behaviour, surfaced
+*deliberately* through the query shape rather than as a hidden
+planner default. The user opts in by asking for it.
+
+#### Why Plan A (not preserve-dims by default)
+
+- **Matches raw SQL.** `SELECT dim.X, SUM(fact.Y) FROM fact LEFT JOIN
+ dim ... GROUP BY dim.X` produces exactly the single-measure Plan A
+ result. The Foundation does not introduce surprises relative to
+ the SQL the user could write by hand.
+- **Matches Snowflake Semantic Views.** Snowflake's default plan
+ shape is also Plan A — porting Snowflake models to OSI is a no-op
+ for this dimension behaviour.
+- **Composes cleanly with multi-measure stitch.** §6.6 row 3 already
+ mandates FULL OUTER for multi-measure shapes; row 1 says LEFT for
+ single-measure shapes. The contrast is the mental model: "the
+ multi-measure stitch is the *only* place both sides are preserved
+ — and that's because there is no other correct shape for merging
+ two independently-aggregated facts."
+- **Surfaces orphan facts.** Plan A keeps the `NULL`-key bucket for
+ orphan fact rows (e.g. order 105 with `customer_id = 99`). This is
+ a data-quality signal — preserve-dims would lose it.
+
+#### Trade-off accepted
+
+A first-time BI user typing a single-measure query and *expecting* to
+see every region might be surprised when a region with no fact data
+is absent. The mitigation is the documented multi-measure pattern
+above. The Foundation prefers "predictable, matches raw SQL" over
+"BI-tool-magical".
+
+> **Note for test authors.** When writing a new test that groups by a
+> dimension over a single-fact source, write the expected rows
+> following Plan A: include only dim values reached by a fact row,
+> plus the `NULL`-key bucket for orphans. If you want every dim value
+> in the expected rows, the test must be multi-measure (with one of
+> the measures sourced from the dim dataset).
+
+---
+
+## 2. How to add a new test
+
+1. Pick the lowest-numbered free `T-NNN`.
+2. Link it to a `D-NNN` in `foundational_semantics.md` — if no `D-NNN` covers the behaviour, add one there first.
+3. Write the test against an existing fixture (§3) if possible; create a new fixture only when the existing ones can't express the shape.
+4. Cite the test from the relevant spec § so the doc and the test grow together.
+
+---
+
+## 3. Common fixtures
+
+### 3.1 Fixture **F-PRELUDE** — single-fact star with multi-fact extension
+
+Mirrors the *Prelude model* in `foundational_semantics.md` (§4).
+
+```yaml
+datasets:
+ - name: customers
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: region, type: string }
+ - { name: segment, type: string }
+
+ - name: orders
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: customer_id, type: integer }
+ - { name: amount, type: decimal(10,2) }
+ - { name: status, type: string }
+
+ - name: returns
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+ - { name: customer_id, type: integer }
+ - { name: amount, type: decimal(10,2) }
+
+ - name: premium_customers
+ primary_key: [id]
+ fields:
+ - { name: id, type: integer }
+
+relationships:
+ - { name: orders_to_customer, from: orders, to: customers, from_columns: [customer_id], to_columns: [id] } # N:1
+ - { name: returns_to_customer, from: returns, to: customers, from_columns: [customer_id], to_columns: [id] } # N:1
+```
+
+**Data:**
+
+`customers`:
+
+| id | region | segment |
+|:--:|:-------|:--------|
+| 1 | EAST | retail |
+| 2 | EAST | retail |
+| 3 | WEST | wholesale |
+| 4 | NORTH | retail |
+
+`orders` (note: orphan order 105 with `customer_id = 99` not in `customers`):
+
+| id | customer_id | amount | status |
+|:---:|:-----------:|-------:|:-----------|
+| 101 | 1 | 100.00 | completed |
+| 102 | 1 | 50.00 | completed |
+| 103 | 2 | 200.00 | pending |
+| 104 | 3 | 75.00 | completed |
+| 105 | 99 | 30.00 | completed | ← orphan: customer 99 not in customers
+
+`returns`:
+
+| id | customer_id | amount |
+|:---:|:-----------:|-------:|
+| 201 | 1 | 10.00 |
+| 202 | 3 | 5.00 |
+| 203 | 4 | 15.00 | ← customer 4 has a return but no order
+
+`premium_customers`:
+
+| id |
+|:--:|
+| 1 |
+| 3 |
+
+### 3.2 Fixture **F-BRIDGE** — M:N through a bridge
+
+Mirrors *Mini-model M2* in Appendix A. This is the fixture for the
+flagship `T-015` bridge-deduplication test.
+
+```yaml
+datasets:
+ - name: actors
+ primary_key: [actor_id]
+ fields:
+ - { name: actor_id, type: integer }
+ - { name: name, type: string }
+ - { name: height, type: integer }
+
+ - name: movies
+ primary_key: [movie_id]
+ fields:
+ - { name: movie_id, type: integer }
+ - { name: title, type: string }
+ - { name: gross, type: decimal(10,2) }
+
+ - name: appearances
+ primary_key: [actor_id, movie_id]
+ role: bridge
+ fields:
+ - { name: actor_id, type: integer }
+ - { name: movie_id, type: integer }
+
+relationships:
+ - { name: app_to_actor, from: appearances, to: actors, from_columns: [actor_id], to_columns: [actor_id] } # N:1
+ - { name: app_to_movie, from: appearances, to: movies, from_columns: [movie_id], to_columns: [movie_id] } # N:1
+```
+
+**Data:**
+
+`actors`:
+
+| actor_id | name | height |
+|:--------:|:------|-------:|
+| 1 | Alice | 170 |
+| 2 | Bob | 170 |
+| 3 | Carol | 180 |
+
+`movies`:
+
+| movie_id | title | gross |
+|:--------:|:-------|-------:|
+| 10 | Action | 100.00 |
+| 11 | Drama | 200.00 |
+| 12 | Comedy | 50.00 |
+
+`appearances`:
+
+| actor_id | movie_id |
+|:--------:|:--------:|
+| 1 | 10 |
+| 1 | 11 |
+| 2 | 10 | ← M10 (Action) has two actors at height 170
+| 3 | 12 |
+
+### 3.3 Fixture **F-BRIDGE-NONE** — variant of F-BRIDGE without the bridge
+
+Same `actors` and `movies` data as F-BRIDGE, but no `appearances` dataset
+and an undeclared M:N edge between `actors` and `movies`. Used to test
+that the engine fails closed instead of guessing a join path.
+
+```yaml
+datasets:
+ - name: actors
+ primary_key: [actor_id]
+ fields: [ { name: actor_id }, { name: name }, { name: height } ]
+ - name: movies
+ primary_key: [movie_id]
+ fields: [ { name: movie_id }, { name: title }, { name: gross } ]
+
+# Note: no `relationships:` block. The model declares no edge; the
+# engine MUST NOT silently fabricate one.
+```
+
+### 3.4 Fixture **F-AMBIG** — two relationships between the same datasets
+
+Mirrors *Mini-model M3* in Appendix A. Used for `E_AMBIGUOUS_PATH`.
+
+```yaml
+datasets:
+ - name: orders
+ primary_key: [id]
+ fields:
+ - { name: id }
+ - { name: placed_by_id }
+ - { name: fulfilled_by_id }
+ - { name: amount }
+
+ - name: users
+ primary_key: [id]
+ fields: [ { name: id }, { name: region } ]
+
+relationships:
+ - { name: order_placed_by, from: orders, to: users, from_columns: [placed_by_id], to_columns: [id] }
+ - { name: order_fulfilled_by, from: orders, to: users, from_columns: [fulfilled_by_id], to_columns: [id] }
+```
+
+**Data:**
+
+`orders`:
+
+| id | placed_by_id | fulfilled_by_id | amount |
+|:---:|:------------:|:---------------:|-------:|
+| 301 | 1 | 2 | 100.00 |
+| 302 | 2 | 2 | 50.00 |
+
+`users`:
+
+| id | region |
+|:--:|:-------|
+| 1 | EAST |
+| 2 | WEST |
+
+### 3.5 Fixture **F-NOPATH** — two disconnected datasets
+
+Mirrors *Mini-model M1* in Appendix A. No relationships are declared.
+
+```yaml
+datasets:
+ - name: orders
+ primary_key: [id]
+ fields: [ { name: id }, { name: customer_id }, { name: amount } ]
+
+ - name: inventory_movements
+ primary_key: [movement_id]
+ fields: [ { name: movement_id }, { name: warehouse_id }, { name: quantity } ]
+```
+
+---
+
+## 4. Tests
+
+### 4.A Query shape
+
+#### T-001 — Aggregation-query cardinality is `DISTINCT(Dimensions)`
+
+**Anchors:** §5.1.1, §5.2 · D-001
+**Fixture:** F-PRELUDE
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - SUM(orders.amount) AS revenue
+```
+
+**Expected (single-measure ⇒ Plan A per §6.6 row 1 and §6.2 step 7):**
+
+| region | revenue |
+|:-------|--------:|
+| EAST | 350.00 |
+| WEST | 75.00 |
+| NULL | 30.00 | ← orphan order 105 (customer_id = 99 not in customers)
+
+Row count = `COUNT(DISTINCT region)` over the regions reached by an
+`orders` row, plus one for the orphan-customer bucket. NORTH (customer
+4's region) does **not** appear: customer 4 has no orders, and a
+single-measure aggregation query follows the fact's natural LEFT join
+shape (`orders LEFT JOIN customers`) — a dim value reachable only
+from the dim domain is *not* in the result.
+
+Engines that "carry" `customer_id` into the output grain because the
+measure is sourced from `orders` fail this test on row count (they
+would group by `customer_id`, not `region`, and produce four data
+rows + orphan). Engines that produce a `NORTH | NULL` row are
+applying the multi-measure stitch shape to a single-measure query —
+also wrong (see §1.6).
+
+If the user wants NORTH to appear with `revenue = NULL`, they must
+make the query multi-measure (e.g. `Measures: [SUM(orders.amount) AS
+revenue, COUNT(customers.id) AS customer_count]`) — the FULL OUTER
+stitch in §6.6 row 3 then preserves every region present in
+`customers`. See T-011 for the canonical multi-measure shape.
+
+#### T-002 — Mixing `Dimensions` and `Fields` is rejected
+
+**Anchors:** §5.1 · D-010
+**Fixture:** F-PRELUDE
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Fields: [customers.id]
+Measures: [SUM(orders.amount)]
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_MIXED_QUERY_SHAPE
+ anchor: §5.1
+ must_contain: ["Dimensions", "Fields", "scalar"]
+```
+
+#### T-003 — Bare metric reference inside `Fields` is rejected
+
+**Anchors:** §5.1.2 · D-011
+**Fixture:** F-PRELUDE. Assume `orders.total_revenue = SUM(amount)` is declared.
+
+**Query:**
+```yaml
+Fields: [orders.id, orders.total_revenue]
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_AGGREGATE_IN_SCALAR_QUERY
+ anchor: §5.1.2
+ must_contain: ["scalar", "total_revenue", "aggregate"]
+```
+
+A scalar query asks for row-level values; a metric is by definition an
+aggregate at query grain, which has no defined value at the row grain
+of `orders`. The engine MUST NOT silently aggregate, broadcast, or
+"window" the metric.
+
+---
+
+### 4.B Field & metric grain
+
+#### T-004 — Implicit home-grain aggregation in a field expression
+
+**Anchors:** §4.3 · D-003, D-015
+**Fixture:** F-PRELUDE. Add this field to the model:
+
+```yaml
+fields:
+ - name: customers.lifetime_value
+ expression: "SUM(orders.amount)"
+```
+
+**Query (scalar):**
+```yaml
+Fields: [customers.region, customers.id, customers.lifetime_value]
+```
+
+**Expected:**
+
+| region | id | lifetime_value |
+|:-------|:--:|---------------:|
+| EAST | 1 | 150.00 | ← SUM(100, 50)
+| EAST | 2 | 200.00 |
+| WEST | 3 | 75.00 |
+| NORTH | 4 | NULL | ← no orders (or 0, depending on engine's LEFT-aggregate convention — both are acceptable; test allows either)
+
+The inner `SUM` is evaluated at the **home grain of `customers`** (one
+sum per customer row), regardless of any grouping the consuming query
+applies. This is what makes `lifetime_value` reusable across queries.
+
+**Compilation-strategy equivalence (D-015):** an engine MAY compile the
+inner `SUM` as (a) a correlated subquery, (b) a `LATERAL` / `CROSS APPLY`,
+or (c) a pre-aggregated CTE joined back to `customers`. The test passes
+under any of the three.
+
+#### T-005 — Cross-grain aggregation: single-step and nested forms
+
+This is the **flagship test family for D-020** — the most-debated
+semantic in cross-vendor BI portability. The Foundation accepts **both**
+single-step and explicit-nested forms over a `1 : N` edge; they give
+different numerical answers for non-distributive aggregates (the
+single-step form gives the "all rows at once" answer, the nested form
+gives the "per-home-row first" answer). For distributive aggregates
+they give identical answers, so either form is acceptable for style.
+
+**Anchors:** §4.5 form (1), §6.1 Semantic 2 · D-020
+
+**Fixture:** F-PRELUDE.
+
+---
+
+##### T-005a — Single-step cross-grain `SUM` over `1 : N` is accepted (distributive)
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.total_order_amount
+ expression: "SUM(orders.amount)" # single-step; resolves at query grain
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.total_order_amount]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent — see T-001):**
+
+| region | total_order_amount |
+|:-------|-------------------:|
+| EAST | 350.00 | ← orders 101, 102, 103
+| WEST | 75.00 | ← order 104
+| NULL | 30.00 | ← orphan order 105
+
+No error. For distributive aggregates the single-step form is
+equivalent to the explicit-nested form `SUM(SUM(orders.amount))` — both
+return identical numbers (cross-validated by T-005d below).
+
+---
+
+##### T-005b — Single-step cross-grain `AVG` over `1 : N` is accepted (heavy-weighted)
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.avg_order_amount
+ expression: "AVG(orders.amount)" # single-step; standard SQL semantics
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.avg_order_amount]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):** the heavy-customer-weighted SQL average.
+
+| region | avg_order_amount |
+|:-------|-----------------:|
+| EAST | 116.67 | ← AVG(100, 50, 200) = 350/3 — three orders in EAST
+| WEST | 75.00 | ← AVG(75) — one order in WEST
+| NULL | 30.00 | ← orphan order 105
+
+No error. Note that EAST's answer (`116.67`) is **not** the unweighted
+average across customers — Customer 1 averages `75` over two orders,
+Customer 2 averages `200` over one order, and the unweighted answer
+would be `(75 + 200) / 2 = 137.5`. That second number is what the
+**nested** form produces; see T-005c.
+
+---
+
+##### T-005c — Explicit nested cross-grain `AVG` over `1 : N` is accepted (unweighted)
+
+The Snowflake-style explicit form is **still accepted**; it just gives a
+different (and meaningfully different) answer for non-distributive
+aggregates. The Foundation does not force this style — but neither does
+it reject it. Users porting from Snowflake Semantic Views can leave their
+nested expressions unchanged.
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.avg_of_per_customer_avg
+ expression: "AVG(AVG(orders.amount))" # inner: per-customer avg; outer: avg across customers
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.avg_of_per_customer_avg]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):** the unweighted average across customers in each region.
+
+| region | avg_of_per_customer_avg |
+|:-------|------------------------:|
+| EAST | 137.50 | ← AVG(Cust1=75, Cust2=200) = 275/2 — two customers in EAST
+| WEST | 75.00 | ← AVG(Cust3=75) — one customer in WEST
+| NULL | 30.00 | ← orphan order's customer is unknown; treated as one customer-of-one-order
+
+The user gets to pick: write `AVG(orders.amount)` for the
+heavy-weighted answer (T-005b) or `AVG(AVG(orders.amount))` for the
+unweighted answer (T-005c). Both are conformant. The two numbers differ
+for non-distributive aggregates — this difference is the central
+BI-portability decision in D-020.
+
+---
+
+##### T-005d — Single-step and nested `SUM` are numerically equivalent (distributive)
+
+Run both forms against identical data and assert the row sets are
+identical.
+
+**Metric A (single-step):**
+```yaml
+- name: customers.sum_single
+ expression: "SUM(orders.amount)"
+```
+
+**Metric B (nested):**
+```yaml
+- name: customers.sum_nested
+ expression: "SUM(SUM(orders.amount))"
+```
+
+**Query A:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.sum_single]
+```
+
+**Query B:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.sum_nested]
+```
+
+**Expected:** identical row sets (after column-name normalisation).
+Both forms are single-measure aggregation queries ⇒ Plan A ⇒ NORTH
+is absent and the orphan `NULL`-region row is present. This test is
+what makes "port your Snowflake nested form unchanged" portable in
+the distributive case — and is the canonical demonstration that
+nested-vs-single is purely a stylistic choice when the aggregate is
+distributive.
+
+---
+
+##### T-005e — `COUNT(DISTINCT)` single-step over `1 : N` is accepted
+
+**Anchors:** §4.5, §6.1 · D-020 case (d), D-022 caveat
+
+**Metric declaration:**
+```yaml
+metrics:
+ - name: customers.distinct_order_statuses
+ expression: "COUNT(DISTINCT orders.status)"
+```
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [customers.distinct_order_statuses]
+```
+
+**Expected (single-measure ⇒ Plan A; NORTH absent):**
+
+| region | distinct_order_statuses |
+|:-------|------------------------:|
+| EAST | 2 | ← `completed` (101, 102) + `pending` (103)
+| WEST | 1 | ← `completed` (104)
+| NULL | 1 | ← orphan 105 has `completed`
+
+No `E_UNSAFE_REAGGREGATION`. This pins D-020 (d) and the D-022 caveat
+explicitly: a holistic aggregate over a plain `1 : N` edge is one SQL
+aggregate over the joined rows — well-defined per D-020. The
+`E_UNSAFE_REAGGREGATION` error only fires when the plan would require
+*decomposing* a holistic aggregate across grains, which happens on M:N
+(D-022) or fan-out shapes the engine cannot pre-aggregate safely.
+
+#### T-006 — `COUNT(*)` over a dataset is well-defined at the home grain
+
+**Anchors:** §7.2 · D-016
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures:
+ - COUNT(*) AS order_count
+Where:
+ - orders.status = 'completed'
+```
+
+**Expected:**
+
+| region | order_count |
+|:-------|------------:|
+| EAST | 2 | ← orders 101, 102 (102? wait 103 is pending). 101+102 completed. 103 pending excluded.
+| WEST | 1 | ← order 104
+| NULL | 1 | ← orphan order 105 (status=completed)
+
+`COUNT(*)` is the count of rows of the **measure-bearing dataset**
+that survive the predicates — here, `orders`. Engines that emit
+`COUNT(<orders.primary_key>)` as a transparent rewrite (e.g.
+Snowflake's `COUNT(*)`-rejection workaround) MUST produce the same
+numbers.
+
+---
+
+### 4.C Predicate routing
+
+#### T-007 — Aggregate in `Where` is rejected
+
+**Anchors:** §5.3 · D-005, D-012a
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [SUM(orders.amount)]
+Where:
+ - SUM(orders.amount) > 100 # aggregate inside Where
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_AGGREGATE_IN_WHERE
+ anchor: §5.3
+ must_contain: ["aggregate", "Where", "Having"] # diagnostic should hint at Having
+```
+
+The diagnostic MUST identify the offending aggregate by name and SHOULD
+suggest moving the predicate to `Having`.
+
+#### T-008 — Pure row-level predicate in `Having` is rejected
+
+**Anchors:** §5.3 · D-012b
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Dimensions: [customers.region]
+Measures: [SUM(orders.amount)]
+Having:
+ - customers.region = 'EAST' # row-level dimension predicate
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_NON_AGGREGATE_IN_HAVING
+ anchor: §5.3
+ must_contain: ["Having", "Where"]
+```
+
+Note this test is about *routing*, not behaviour. An engine that
+silently treats `Having` as `Where` produces the right numbers but
+breaks the contract, and so fails the test on the error code, not
+the rows.
+
+#### T-009 — Mixed-level boolean predicate is rejected
+
+**Anchors:** §5.3 · D-012c
+**Fixture:** F-PRELUDE.
+
+**Query:**
+```yaml
+Having:
+ - customers.region = 'EAST' AND SUM(orders.amount) > 100
+```
+
+**Expected:**
+```yaml
+error:
+ code: E_MIXED_PREDICATE_LEVEL
+ anchor: §5.3
+ must_contain: ["row-level", "aggregate", "split"]
+```
+
+The diagnostic SHOULD suggest splitting the predicate (row-level half
+→ `Where`, aggregate half → `Having`).
+
+---
+
+### 4.D Default join behaviour
+
+#### T-010 — `N:1` enrichment defaults to `LEFT`; orphans appear under `NULL`
+
+**Anchors:** §6.4 · D-004a
+**Fixture:** F-PRELUDE.
+
+Covered indirectly by T-001's expected rows — the `region = NULL` row
+exists *only because* the default join from `orders → customers` is
+`LEFT` and order 105's `customer_id = 99` does not match. An engine
+that defaults to `INNER` returns one fewer row and fails the test on
+row count. This is the single most common silent-correctness failure
+in BI tools (Snowflake's "first matching row" rule is the worst case;
+see the Snowflake divergence note in the spec).
+
+#### T-011 — Multi-fact composition defaults to `FULL OUTER` on shared dims
Review Comment:
On F-PRELUDE only `orders` has a NULL group (the orphan order 105), so a FULL OUTER JOIN by plain `=` passes T-011. #246 asks for NULL-safe equality in this merge (L784, L888); it only matters when both facts have a NULL group. One of my cases puts a NULL group in both facts, where only the NULL-safe merge gives a single NULL row. Would the suite take such a case next to T-011?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@ossie.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org