Semantics contract¶
For LOD concepts see Understand LOD; for declarations, weighting and supported combinations see the LOD authoring reference. This page defines the shared query semantics.
The Apache Ossie (formerly OSI) core spec deliberately leaves execution semantics implicit: a metric is a name plus a raw SQL string, a relationship is a column mapping, and a field can declare a logical type and a time-dimension role. This document is the normative contract for how dosi-engine fills those gaps. It is versioned with the engine; behavioral changes here are breaking changes.
Rules are labeled S-<area>-<n> for reference from issues and tests.
Fields and metrics accept the optional Ossie core datatype: String, Integer,
Decimal, Float, Boolean, Date, Time, DateTime, DateTimeTz, or Opaque
(case-sensitive). It describes the expression's result, not necessarily the
source column. Omit it when unknown; Opaque means a known type outside the
portable vocabulary. Decimal leaves precision and scale unspecified;
DateTime has no timezone, while DateTimeTz identifies an instant with
offset/timezone context. The declaration does not insert casts, change metric
classification, or set output display formats. Both engine modes accept it.
1. Expressions¶
- S-EXPR-1 Dosi selects
sqlfirst, followed by anOSSIE_SQL_2026entry, thenANSI_SQL, then the first SQL dialect supported in the current mode.MDX/TABLEAU/MAQLentries are ignored; an expression with only non-SQL entries is a compile error for metrics and a warning for fields. - S-EXPR-2 Expression fragments are parsed as a single scalar expression. Anything else — multiple projections, trailing aliases, statements — is a parse error (this is also the injection barrier: user text never reaches SQL by string concatenation anywhere in the engine).
2. Metric inference¶
- S-METRIC-1 A metric expression must contain at least one aggregate
call. Supported aggregates:
SUM,COUNT,COUNT(DISTINCT x),AVG,MIN,MAX. Any other aggregate function isunsupported_aggregate. - S-METRIC-2 Classification, applied to the parenthesis-stripped root:
- a single aggregate call → aggregate metric;
agg / agg(both sides bare aggregates) → ratio; the division is wrappedCAST(numerator AS DOUBLE) / denominatorto avoid integer division on truncating engines;- anything else → expression; each aggregate subtree becomes a
measure, the surrounding arithmetic is preserved verbatim (including
NULLIF,CASE, …). NoteSUM(a)/NULLIF(SUM(b),0)is an expression, not a ratio, and is not double-cast — the authored SQL wins. - S-METRIC-3 Rejected in metric expressions, as structured errors:
window functions (
window_in_metric), subqueries (subquery_in_metric), nested aggregates (nested_aggregate), column references outside any aggregate (bare_column_in_metric), aggregates over no column (SUM(1)→bare_column_in_metric), multi-columnCOUNT(DISTINCT a, b), and references to other metrics — metric references live exclusively in the DATUSderiveextension key (docs/datus-extensions.md#d-derive), never in expression SQL; theexpressionof a derived metric stays a self-contained flattened form.
3. Measures (synthesized)¶
- S-MEASURE-1 Each distinct aggregate call becomes a measure named
{dataset}_{stem}_{suffix}: stem = the aggregate argument rendered and sanitized (lowercase; letters and digits of any script stay, every other run →_, so'新品'and'老品'keep apart); suffix =sum,count,count_distinct,average,min,max;COUNT(*)→{dataset}_rows_count. - S-MEASURE-2 Measures dedupe by signature (dataset + aggregate kind +
distinct + structural argument key), within and across metrics. Keys ignore
source positions, ordinary comments and redundant parentheses, retain
literal precision, identifier quoting and native dialect semantics, and
normalize verified standard function representations. SQL rendering is
separate from identity; opaque expressions return
expression_comparison_unsupportedrather than a comparison based on diagnostic text. Two different aggregates that still sanitize to the same name (a+against a-) are never merged: each takes the name{dataset}_{stem}_{suffix}_{tag},tag= 8 hex digits of its signature, everywhere it occurs. An explicit name (D-MEASURE) is never retagged; a clash with one is stillmeasure_name_collision.
4. Column → dataset attribution¶
- S-ATTR-1
dataset.columnresolves exactly; the qualifier must be a dataset name and the column one of its declared fields. Metric and filter columns reference fields (which may themselves be expressions), not raw physical columns. - S-ATTR-2 A bare column resolves iff exactly one dataset declares a
field with that name; zero →
unknown_column, several →ambiguous_column(with candidates). - S-ATTR-3 All columns inside one aggregate must belong to one dataset
(
cross_dataset_aggregateotherwise). - S-ATTR-3′ The one exception: in
CASE WHEN <guard> THEN <arg> END(oneWHEN, noELSE— the shape a D-DERIVE filter writes), the aggregate's dataset isarg's, andguardmay read one other dataset that it reaches by a unique many→one path — a dimension's flag guarding a fact's measure (SUM(CASE WHEN customers.premier_customer THEN orders.margin END)). The guard column stays qualified in the compiled measure, is joined (or, when it reads a level of detail, broadcast) beside each fact row, and never changes the grain. The shape may nest — a filter over a metric that is guarded already folds toCASE WHEN <p> THEN CASE WHEN <g> THEN <arg> END END, which is<p> AND <g>— and every level's guard counts toward the same one dataset. A guard the arg's dataset does not reach, two guard datasets, anELSE, or any other mixing is stillcross_dataset_aggregate; a level-of-detail base with such a guard isnot_implemented. - S-ATTR-4
COUNT(*)attributes to the unique dataset of the metric's other measures, or to the model's only dataset; otherwisecount_star_needs_dataset.
5. Joins¶
- S-JOIN-1 Relationships are the only join source. Edges are followed
strictly many→one (
from→to), so walking a path never multiplies the origin's rows. Joins are on the AND-ed equality of the relationship's column pairs, and areLEFT JOINby default (orphan many-side rows survive into a NULL dimension bucket). A relationship may declareINNERvia the Datusjoin_typeextension (drop orphans — attribution semantics; docs/datus-extensions.md#d-join). - S-JOIN-2 A target dataset is reachable iff exactly one simple path
exists (edge-level: parallel relationships are distinct paths). Zero paths
→
no_join_path; several →ambiguous_join_pathwith each candidate path spelled out. Maximum path length: 6 hops. - S-JOIN-3 Self-relationships and cyclic walks are never followed.
- S-JOIN-4 A relationship declared
cardinality: many_to_many(Datus D-CONFORM, docs/datus-extensions.md#d-conform) is not a join edge: the graph never follows it in either direction, and naming it in a relationship path isinvalid_relationship_path. Its column pairs declare one dimension spelled on two datasets — the transitive closure of every such pair is a conformed class — and are consumed by S-FAN-7 instead.
6. Fan-out protection & branch assignment¶
The correctness core. Terms: a measure's home is the dataset its columns live on; the required set of a query is every dataset referenced by group-by dims and filters.
- S-FAN-1 Candidate evaluation bases for a measure: datasets that reach
(S-JOIN-2) both the measure's home and the entire required set.
Duplicate-sensitive aggregates (
SUM,AVG,COUNT) are additionally restricted to their home — evaluating them over a fanned-out join would double-count.COUNT DISTINCT,MIN,MAXmay also evaluate from any dataset that reaches their home. - S-FAN-2 A measure evaluates at its home whenever the home is a
candidate. The dataset an aggregate names is its row set, so
COUNT(DISTINCT dim.k)counts everydimentity in the group whether it stands alone or shares a ratio with a fact measure — one definition, one number, regardless of what else is in the query. Only a measure whose home cannot reach the required set moves: groupingSUM(fact.x) / COUNT(DISTINCT dim.k)by a dimension that onlyfactreaches evaluates the count over the fact join — on the entities the fact observes — because nothing else can serve that grouping. A measure evaluated away from home is projected as<measure>__at_<base>, so two placements of one measure in a query never share a column. To count observed entities under every grouping, aggregate the fact's foreign key instead:COUNT(DISTINCT fact.dim_fk)lives on the fact and never moves. A time range ormetric_timeadds the fact's time column to the required set, so a time-scoped ratio's dimension count follows the fact too. - S-FAN-3 Measures on different bases aggregate in separate branches,
each at its base's grain. Branches all produce the requested dim columns and merge
FULL OUTER JOINon them; the k-th branch joins onCOALESCE(m0.key … m(k-1).key) = mk.key(see S-FAN-5), and final keys project asCOALESCEacross branches. Zero group-by keys →CROSS JOIN. Where a dialect cannot express that join — MySQL/TiDB have no FULL JOIN, ClickHouse cannot use aCOALESCEas a join key, and BigQuery requires an equality of fields from both sides of a FULL JOIN — the merge is emitted as akeysCTE (UNIONof every branch's key tuples) that each branchLEFT JOINs back on=. The two shapes yield the same rows except where a group key is NULL (S-FAN-5). - S-FAN-4 A duplicate-sensitive measure whose home has no candidate base
is a
fan_out_riskerror, with a retry hint, when some dataset reaches both its home and the group-bys: there the measure could only be evaluated across a fanning join, and a duplicate-insensitive aggregate would evaluate. When no dataset reaches both, no relationship connects them and no aggregate would help, so the error isno_join_path, whatever the aggregate. The engine never silently emits a double-counting query. - S-FAN-5 Every join Dosi adds on group keys compares them with
=, so a NULL key matches nothing (SQLNULL = NULLis unknown). Across branches, underFULL OUTER JOINeach branch's NULL group stays a row of its own, with the other branches' measures NULL. In thekeysform (MySQL, TiDB, ClickHouse, BigQuery) the NULL key is one row that meets no branch, so all its measures read NULL. A row whose key is NULL likewise meets no level-of-detail value (LOD reference). Dosi offers no null-safe merge. - S-FAN-6 A bare count metric (its value is exactly one COUNT / COUNT
DISTINCT measure) reads 0, not NULL, for a group present only in other
branches — a count over no rows is 0. This applies only when the whole metric
is the count; a count embedded in a ratio or expression keeps NULL so it
propagates (and a count denominator never becomes a literal
0divisor). A metric's Datusfill_nulls_withextension overrides this default, applies to any metric kind, and is part of the metric's value wherever another metric reads it (docs/datus-extensions.md#d-fill). - S-FAN-7 Conformed resolution is per branch. A group-by dimension, WHERE
column or time-range column whose dataset a branch's base does not reach
along many→one edges resolves to the member of its conformed class (S-JOIN-4)
that lives on the base — or the one member the base reaches — and projects
under the query's output name, so the branches still merge on one column
(S-FAN-3).
metric_timeresolves the same way when the facts' primary time dimensions form one class: each branch filters and groups its own date. A grain truncates the resolved column, under that column's own D-GRAIN floor. Only paired columns resolve this way; an unpaired column keeps the ordinaryunconformed_dimension/no_join_path. A model with no conformed class plans exactly as before.
7. Dimensions, grains, time¶
- S-TIME-1 Group-by items are
dataset.fieldor a bare unique field name; output columns are named{field}, or{field}__{grain}when a query-time grain is applied. Duplicate output names are an error. - S-TIME-2 Grains (
day,week,month,quarter,year) apply only to fields whose effectiveis_timeis true, lowering toDATE_TRUNC(per-dialect idioms where the dialect lacks it: MySQL/TiDB useDATE()/STR_TO_DATE(DATE_FORMAT(...))/YEARWEEKrewrites, ISO weeks starting Monday). An explicitdimension.is_timetakes precedence; otherwiseDate,Time,DateTime, andDateTimeTzdefault to true, including whendimensionis absent. Other or omitted datatypes default to false. Useis_time: falseto exclude audit timestamps. This role rule applies in both modes. AcceptingTimemetadata does not add time-of-day ranges or sub-day grains: current time queries require date-bearing values. - S-TIME-3 A time range is half-open
[start, end)over ISO dates, compiled asfield >= DATE start AND field < DATE endbefore aggregation, against: the explicitly named time dimension, else the single time item in the group-by (metric_timecounts as one), else — with no time item at all — each metric's primary time dimension (S-TIME-5). Only a group-by holding several time items still needs an explicit name (time_range_needs_dimension). - S-TIME-4 Ossie core carries no granularity metadata; native grain is
whatever the field expression yields, unless the field declares one via
the Datus
time_granularityextension (docs/datus-extensions.md#d-grain) — then requesting a strictly finer grain is agrain_too_fineerror. Coredatatypeidentifies the logical type but not a string or integer date encoding ('20260101',202601). These require the Datustimeextension (docs/datus-extensions.md#d-format) to describe their layout;String/Integeralone cannot select a parser. The encoding also supplies the native grain whentime_granularityis absent. A time field withdatatype: Datehas native day grain without a Datus declaration. Window metrics (period-over-period / rolling / cumulative) are the Datus D-WINDOW extension consuming the S-TIME-5 axis — see window-extension.md; a time spine remains proposed upstream. - S-TIME-5 Every metric may have a primary (aggregation) time
dimension, resolved as: the metric-level Datus
time_dimension(docs/datus-extensions.md#d-time), else the unique primary time among the metric's datasets — a dataset's being its explicittime_dimensionextension, else its singleis_timefield (this last inference reads no extension and applies in basic mode too). The reserved query namemetric_timegroups/filters by it: in a multi-branch plan each branch substitutes its own primary time column under the shared output name (metric_time/metric_time__{grain}) and branches merge on that name, so unrelated facts align on their respective business time axes. A metric with no resolvable primary time isno_primary_time_dimensionwhen it is grouped bymetric_time; under a time range alone it is read whole and the result says so (S-FILTER-2). A metric whose datasets keep primary times of their own — a compose over two facts — groups bymetric_timethe way its members do side by side: each branch on its own dataset's axis, aligned on the shared name. A window over one needs a single axis, which pairing the facts' dates (D-CONFORM) provides. Two metrics on one base dataset with different primary times — a compose on a conformed month beside a member on its own fact's month — each get their own branch over that base, filtered and grouped on their own axis, and merge on the group keys like any two branches: every metric reads exactly what it reads alone, at the cost of one more scan of that base per axis. A model field literally namedmetric_timeis shadowed by the reserved name — qualify it asdataset.metric_time.
8. Filters¶
- S-FILTER-1
--where(where_sql) is scalar boolean SQL. It is split into its top-levelANDconjuncts, and each conjunct runs in the position that can evaluate it — decided by which columns exist there, never by guessing intent: - a conjunct that names a selected metric filters the result rows:
after every metric, window level and compose is computed, before
order_by/limit— so "the top 10 of the groups above X" means what it says. Undertime_rangesthe name is{metric}__{tag}; - in a window query, a conjunct that names only group-by fields also filters the result, so the rows kept keep the ranks, partition counts and weights the whole population gave them;
- every other conjunct — one naming a field the query does not group by, or a time field grouped at a grain — filters the input rows before aggregation, in every branch. Columns resolve per S-ATTR rules; filter datasets join into each branch like dims. When a branch carries a fixed level-of-detail rollup (D-LOD), such a conjunct is a dimension filter: it applies after the rollup is computed and never scopes it.
Outside window queries a condition on group-by fields stays on the input:
filtering a grouping key before or after aggregating gives the same rows
and the same values, and the input position prunes the scan. A reference
resolves as a field first, so a metric sharing a field's name does not move
an existing condition.
- S-FILTER-2 context_filter is row-level SQL that always filters the
input — the population a window ranks, counts and weighs. The same
predicate answers a different question in each place:
where_sql: "market = 'North'" shows North's rank among all markets,
context_filter: "market = 'North'" ranks North against itself. The time
range (time_range / time_ranges) sits in this position too; a window's
lookback scan widens it and trims back to the requested bounds after the
window, so it is a calculation window, not a display selection. Both are
the context a fixed level-of-detail rollup is computed over, so neither may
read a level-of-detail value (unsupported_filter).
The context layer reaches every part of a query — each branch that aggregates metrics, and each fixed rollup a metric, dimension or filter reads — and each part reads it on its own source:
- the time range is the query's period. Each part applies it to its own
dataset's primary time (S-TIME-5): a rollup over another dataset reads the
period on that dataset's date, not on the host's. A part whose dataset has
no time field (a snapshot, a mapping) is read whole, with the warning
time_axis_missing; one with several undeclared time fields is refused (time_axis_ambiguous— declare itstime_dimension). A range pinned to one column (time_range.dimension) lands on that column wherever a part can read it — its own dataset, one it reaches many→one, or a conformed pair. A part that cannot read it reads the period on its own time field when the pinned column is its dataset's primary time (the column then names the period, not one table's date), and skips it otherwise; - each
context_filterconjunct applies to every part that can read all of its columns, the same three ways. A part that cannot skips that conjunct, with the warningcontext_skipped; a conjunct no part can read is refused (context_unreadable).
where_sql is not part of the context layer: it filters the branches' rows
and never a fixed rollup (S-FILTER-1). This is Tableau's order of
operations: the period and context_filter act as context filters, which a
FIXED level of detail is computed under, and where_sql as a dimension
filter, which it is not. So a fixed value that should not follow the period
— a customer's first order over all history — is read by putting the
query's dates in where_sql, on the main dataset's own date, instead of in
time_range: they pick the rows the metrics count and leave every rollup
unscoped. A time field declared with a storage format
(D-FORMAT) cannot be compared in
where_sql; compare a field that reads the stored column as it is, in its
stored spelling ('20240101'), when the model has one.
- S-FILTER-3 Refused: subqueries, window functions and bind parameters
(unsupported_filter); inline aggregates (aggregate_in_where — declare
the metric and name it instead); a metric the query does not select
(result_filter_metric_not_selected); one conjunct that names both a
metric and an ungrouped field (result_filter_unprojected — it has no
position, so group by the field or split the conjunct).
- S-FILTER-4 For most metrics the three positions give the same numbers.
They differ only where a value depends on rows outside its own group, in
the metric itself or in anything it is computed from (compose members,
window bases, filter bases, the fixed fields its expression reads):
| Part | How the positions act on it |
|---|---|
| a fixed metric or field | time_range and context_filter narrow it; where_sql does not, so a condition on the fixed value itself goes in where_sql |
an include or exclude grain |
every position narrows it |
| a period offset (previous month, year over year) | dates go in time_range, which widens the scan for the lookback; in where_sql they cut the lookback and the compared value comes back empty |
| a frame that resets every period (month to date) | the same as a period offset |
| a time-ordered frame without a reset, or a time-ordered first or nth value | the positions give the same numbers; the first periods read only what is in range |
| a value over the population: rank, bucket, a value-ordered frame, a whole-partition count | where_sql on a grouped field keeps the value among all rows; context_filter computes it within the subset |
| - S-FILTER-5 A condition is never dropped silently. Every compile and | |
execute result carries warnings, each with a code, a message and the |
|
part it concerns — a branch named by the metrics it computes, a rollup by |
|
its fixed field: context_skipped (a part cannot read a context_filter |
|
conjunct, or a range pinned to a column it cannot read), time_axis_missing |
|
| (a part's dataset has no time field, so the period does not scope it) and | |
empty_time_window (a fixed rollup's period and a context_filter |
|
| condition on the same column leave no day). Read them on a dry run before | |
| reading the numbers. |
explain shows each position: ResultFilter[…] above the projection root,
Filter on the scan. A result filter lowers to one more SELECT over the whole
query (WITH …, result_rows AS (…) SELECT … FROM result_rows WHERE …),
because the outermost window level computes in its own projection, where a
WHERE would run first.
9. Datasets¶
- S-DATA-1 A
sourcecontaining whitespace is an inline query (compiled as a derived table); otherwise it is a table reference split on.into up to catalog.schema.table. Identifier quoting is not yet supported. - S-DATA-2 Within a branch, each dataset appears at most once and is aliased by its dataset name (no self-joins in v1).
10. Field references & explicit paths¶
Both query planes resolve a field reference the same way, in one place.
- S-VIA-1 A reference is
field,dataset.field, orrelationship[.relationship…].field. The first segment is matched against dataset names first, so a model whose relationship shares a dataset's name keeps resolving to the dataset — adding this spelling cannot change what an existing reference means. - S-VIA-2 A relationship-prefixed reference names its own join path
edge by edge. The edges must chain (each leaves the dataset the previous
one arrived at), must not revisit a dataset, and are capped at the graph's
6-hop bound. Otherwise
unknown_relationship/invalid_relationship_path. - S-VIA-3 In a metric query the branch base is chosen after resolution,
so a named path pins it: the base must be the path's first edge's
fromdataset. Two named paths that disagree on their origin are an error, as is a path whose origin cannot host the query's measures. - S-VIA-4 Filters accept all three spellings, at any path length. A reference of three or more parts is not a SQL column — it parses as struct-field access — so the planner resolves those separately from ordinary columns. Every reference in a filter is resolved against the model or the filter is refused; none reaches the generated SQL unvalidated.
- S-VIA-5 A query reaches each dataset by exactly one path. Lowering
aliases tables by dataset name (S-DATA-2), so two paths to one dataset
would read the same alias twice; that is
not_implemented, never silently-equal columns.
11. Detail queries (projection plane)¶
- S-SELECT-1
fromfixes the result grain: one output row per row of that dataset. Every other dataset is reached along many→one edges only (S-JOIN-1), so no join in the path can multiply the root's rows. A dataset reachable only against that direction isdetail_fanout, whosesuggested_retrynames the root that answers the question at the finer grain. - S-SELECT-2 The required set is every dataset named by a projection, a filter column, or the time range. All of them are joined; only projections become output columns.
- S-SELECT-3 An output column is the reference with the
fromprefix stripped and each remaining.replaced by__, plus__{grain}when a grain is applied. Root fields stay bare; everything else carries its path. Two fields yielding one name isduplicate_output_name. - S-SELECT-4 A detail query projects row-level fields only. A metric
name in the projection is
metric_in_detail_query; an aggregate inwhereisaggregate_in_where(S-FILTER-2). - S-SELECT-5 With no
dimension, a time range routes to the root dataset's primary time dimension (D-TIME), else to the single time field among the projections, elsetime_range_needs_dimension. - S-SELECT-6 Order keys name projected output columns
(
unknown_order_key), and sort NULLs as S-ORDER-2 says.limitdefaults to 100 and is clamped to 10000 by every surface.
12. Errors¶
- S-ERR-1 Every compile/query error carries a stable snake_case
code, a human message, the names involved,candidateswhere a bad reference has alternatives, andsuggested_retrywhere a rewrite would succeed.--format jsonemits the full structure. Error text is not a stable API; codes are.
13. Ordering¶
- S-ORDER-1 A metric query's order key names a metric or a group-by
item, by its output name or by the spelling the query used; a detail
query's names a projected column (S-SELECT-6). Anything else is
unknown_order_key.desc: truesorts descending. - S-ORDER-2 NULLs sort last, ascending and descending alike, on every
warehouse.
nulls: firston an order key puts them first instead; on the CLI,--order "-revenue nulls first". Warehouses disagree on their own default: the PostgreSQL family, Oracle, Redshift and Snowflake put NULLs first when sorting descending, and the MySQL family, StarRocks, Doris, BigQuery and Databricks put them first ascending. So the SQL states a placement only where the target's default differs from the one asked for. MySQL and TiDB have noNULLS FIRST | LASTclause, so they sort onCASE WHEN key IS NULL THEN 1 ELSE 0 ENDahead of the key itself. They sort a SELECT over the finished rows (WITH …, result_rows AS (…)), because inside an expression they read a name as a table's column before the output column of that name. Alimittherefore keeps the same rows on every warehouse, even when the key is NULL for some of them. A window's ordering is declared in the model instead, and keeps the warehouse default unless it setsorder.nulls.