Repository navigation
View expander #502
Description
An Ossie query expression, running against an Ossie model, translates to a query (or queries) against the underlying data store.
Suppose a model M has views (data sets) V1, V2 and V3, and a particular query Q accesses views V1 and V2. The semantics of the model determine a valid translation to query Q2 on the underlying data store. The conversion from Q(M) → Q2 is known as view expansion.
Now suppose that M and its views V1, V2, V3 are, like Q, expressed in Ossie SQL, and that Ossie SQL is a superset of Postgres SQL. The expanded query Q2 is in the same language as Q, albeit referencing a different set of database objects and no longer using extension constructs such as measures. Q2 can be executed on Postgres.
It is useful for the expression language to have a reference implementation where, for any valid query, we can see the expanded query and execute it to see the result. It is also useful to have a corpus of queries that are valid (per the specification) and see their output.
This feature proposes a view expander that takes a SQL query, possibly with CTEs involving measures, and returns an equivalent SQL query that does not involve measures. (A later version of this feature might have a catalog of view definitions, but for now let's require that the views are specified inline as CTEs.)
For example, given
with enhancedEmps as (
select *,
measure(avg(sal)) as avg_sal
from scott.emps)
select deptno,
measure(avg_sal)
from enhancedEmps
group by deptno;the view expander would return
select deptno,
avg(sal) as avg_sal
from scott.emps
group by deptnoWe would initially deliver this via the OST test runner (see #501). Users would write SQL query tests (to expand and execute a query, and see the results) or tests that use the expand(query) function (to expand a query and print its expanded SQL).
Activity
christianeu-db commentedon Oct 6, 2026
+1 to a mechanism that expands a layer 2 or layer 3 query to runnable SQL and/or results. Will built an early version of layer 3 -> SQL in #237.
My main open question is the input format. I propose the tests accept Ossie models that use OSSIE_SQL_2026 in their expressions, with dataset sources supplied as hardcoded
VALUESclauses. Once #343 lands, a dataset with per-dataset fields and metrics maps to this query (using your proposed function syntax):
sql SELECT <field1Expression> AS <field1Name>, ..., <fieldNExpression> AS <fieldNName>, MEASURE(<metric1Expression>) AS <metric1Name>, ..., MEASURE(<metricNExpression>) AS <metricNName> FROM <datasetSourceQueryOrTable> Ossie models in OSSIE_SQL_2026 as test inputs give us a few things things:
julianhyde commentedon Oct 6, 2026
I see your point that some tests will use OSSIE_SQL_2026, but I think we will also need tests that use a more mature dialect of SQL.
A standard is not a language, at least where SQL is concerned. The SQL standard is huge, it can't cover everything, and no vendor implements everything; for example, there major differences between vendors in how to subtract two dates (various
DATEDIFFfunctions, Postgres'agefunction, and the standard's own '(d2 - d1) DAY' syntax).For testing proposed semantics of measures, we need a language. We to be able to write and execute whole, complex queries (with correlated subqueries, window functions, measures in scalar subqueries in measures, etc.) I proposed Postgres SQL because the language covers standard SQL, engine is mature and implements all the corner cases. For understanding measures semantics, the tests would not need to use obscure functions or data types.
Also, the view expander needs queries, views, and expanded queries to be in the same language (dare I say endomorphism?) and while the queries might be simple (e.g. single table) the expanded queries will be complex (with joins and/or scalar subqueries).
For embedding expressions in Ossie docs, you need a standard. The standard can be a narrow subset of standard SQL. ("If your queries consist of only X, Y, and Z, any compliant Ossie implementation will handle them.")
(I didn't check whether you defined OSSIE_SQL_2026 as "Standard SQL plus X, Y, Z" or "A, B, C, ... X, Y, Z". Hopefully the former. But still, the reference implementation of OSSIE_SQL_2026 will be narrow at first, and I don't want to gate the view expander on that reference implementation.)
I propose that a test script states which dialect it is intended for:
If and when OSSIE_SQL_2026 (and its reference implementation) has broadened to include joins, subqueries, etc. we can convert the corpus of tests to use it exclusively. I don't think that conversion will be difficult.
Julian
christianeu-db commentedon Oct 6, 2026
Fair point about Postgres maturity. Ossie models can also use ANSI_SQL expressions, so we could use Postgres in the examples. The relational layer in #354 proposes that OSSIE_SQL_2026 additionally support joins, subqueries, etc.
I’m happy to start with Postgres for most of the initial tests. My main concern is that the test setups use Ossie models as inputs. Under the proposed relational semantics, each Ossie dataset defines a view with metrics that can then be queried.
We can also include tests using SQL-native syntax to create metrics, but I’d like the suite to stay grounded in Ossie models. Otherwise, I worry the file format could drift toward being purely a metadata-exchange format rather than a foundation for consistent query semantics.
julianhyde commentedon Oct 7, 2026
The file format (case #501) needs to be a good container for whatever we want to test. As you say, a very common case will be tests that execute ossie-sql queries against an ossie model defined in an external file. I would propose something like this:
I have a particular interest in queries that join two data sets. That probably goes beyond where the spec is currently, which is fine. I just need the file format to be able to express those kinds of tests.
christianeu-db commentedon Oct 7, 2026
Julian,
loadModellooks like a great addition.The ability to query . as a table lines up well with the in-flight layer 2 semantics. As your example illustrates, this would give a way to write tests that join datasets, both from the same model and even across models. The latter is particularly exciting since generally composition across semantic models has traditionally been a hard problem.
Since
scottis also an object accessible to SQL, I can see howloadModelalso scales to writing tests using the layer 3 semantics (spec).Overall, fully on board with adding a compliance script file type (#501) and tests that take layer 2/3 queries output the equivalent SQL
test(compliance): add relational-semantics query compliance tests