Current Status: Proposed Final
Working Group
| Lead(s) | Participants |
|---|---|
| Will Pugh, Snowflake Khushboo Bhatia, Snowflake | LLyod Tabb, Malloy Dianne Wood, Atscale Lior Ebel, Salesforce Quigley Malcolm, dbt Labs Kurt, Relational AI Justin Talbot, Databricks Pavel Tiunov, Cube Damian Waldron, Thoughtspot Oliver Laslett, Lightdash Martin Traverso, Starburst JB Onofré, The ASF Raul Beiroa, Denodo |
There are two layers in Ossie that need an expression language:
- Ontology layer. This layer maps onto the ontology layer which sits above the logical layer. It maps more closely to modelling languages like OWL, (Py)Rel from RelationalAI, and Legend from Goldman Sachs
- Logical layer. This layer maps directly to the databases and physical layer. It maps closely to traditional BI semantic models.
This proposal is only targeted at the Logical Layer. It would be nice if the Ontological layer could reuse the same expression language, but that will be treated as a separate proposal.
This document defines the SQL expression language subset that Ossie-compliant implementations MUST support. The goal is to provide a portable expression language that works across all Ossie implementations while allowing vendors to expose richer database-specific functionality through dialect extensions. In particular, it is meant for expressions at the logical layer. This means metrics, fields, filters, etc In the future, expressions such as arbitrary join expressions should also use this expression language.
We expect there will be extensions to this language to cover concepts such as sub-queries, grain calculations, etc. However, these will each have their own proposal.
This expression language is scoped to declaring a model — authoring the logical layer's metrics, fields, and filters. It does not define a query interface. The two are distinct:
- Declaration uses expressions to define what a model is. This document governs declaration.
- Querying evaluates a question against a declared model and returns rows. Query interfaces will be defined in separate documents.
Consequently, the expressions defined here only declare a model: adding a dialect here does not mean Ossie supports querying with that dialect.
- Portability: Core functions work identically across all implementations
- Familiarity: Based on widely-adopted SQL syntax and semantics
- Analytical Focus: Prioritizes functions commonly used in BI and analytics
- Extensibility: Vendor dialects can extend beyond the core
- OSSIE_SQL_2026 is registered as a dialect in the Ossie spec; it refers to this language specification.
- Make OSSIE_SQL_2026 the default dialect if one is not chosen.
The core language is based on ANSI SQL:2003 Core (ISO/IEC 9075-2:2003), selected for its:
- Wide adoption across major databases (Snowflake, Databricks, PostgreSQL, BigQuery)
- Well-defined semantics
- Support for modern analytical features (window functions, CTEs)
The language also requires optional SQL:2003 feature T612, the aggregate <filter clause> (FILTER (WHERE ...)), which is not part of Core. See Conditional Aggregations.
The identifiers will match standard SQL identifiers:
Field: <SQL Identifier>
FieldExpr: Field | Field ‘.’ Field
The Ossie spec currently contains three namespaces, which determine the visibility and uniqueness of each value. Where and how a field (or metric) is defined will determine the namespace for it, which in turn determines the ways it can be addressed by other fields.
All identifiers MUST be valid names and follow ANSI SQL naming, with the size limitation of 128 characters for identifiers. Many databases support longer identifiers, however, this number is safe for a broad number of vendors.
Regular identifiers (unquoted) should be case insensitive. For example, an identifier id is regular, so it would match with Id or iD. Comparing quoted and non-quoted identifiers is DB specific, so for best portability it is best to use simple identifiers.
Function names should also be case insensitive. For example, SUM, sum, and Sum name the same function.
The quote character for the Ossie dialect will follow ANSI SQL and support the double quote character (“). This means that if an expression is in a field expression or as an identifier in the YAML, this will be the expected quoting. However, there are some databases that use other escape characters. Working with these have the option of either creating expressions using their dialect or having the Ossie document written in the Ossie dialect, but then queried in the local dialect.
| You type this in SQL | Equivalent to | Will it match a column created as id? |
|---|---|---|
| id | ID | Yes (Standard behavior) |
| Id | ID | Yes (Standard behavior) |
| "ID" | ID | Yes (Force-matched to normalized case) |
| "id" | id | No (Quotes cause an exact match to lowercase) |
Sometimes, we may refer to a normalized identifier. This is a form the identifiers can be put in, so they can be matched easily and matches can be made with case-sensitive, exact matching. For normalized identifiers:
- Regular identifiers are upper cased
- Quoted identifiers have their quotes stripped and any escaped characters are unescaped
Namespaces define how an identifier is looked up in an expression. Identifiers
will be able to be multi-part and the parts will be separated by the '.' characters, E.g. dataset.field This matches SQL conventions.
What an identifier refers to depends on where the expression appears:
- In a field expression, identifiers refer to columns of the dataset's
source. A field expression cannot refer to other fields. - In a dataset-scoped metric expression, unqualified identifiers refer to fields declared in that dataset, even when a source column has the same name. Identifiers qualified with
source, as insource.column, refer to columns of the dataset'ssource. Thesourcequalifier is case insensitive. If the dataset declares a field namedsource, the name refers to that field, and the dataset's metrics cannot refer to source columns. - In a model-scoped metric expression, identifiers must be qualified. An identifier written
dataset.fieldrefers to a field declared in that dataset, not to a source column. InsideAGG, an identifier writtendataset.metricrefers to a dataset-scoped metric of that dataset; thisAGG(dataset.metric)form is the only way to reference a metric.
See Name Resolution in the core specification.
Ossie expressions support the following SQL constructs within any expression:
| Construct | Notes |
|---|---|
| Column and Metric references | Varies based on whether in Ontology or Semantic models. For semantic models, see Name Resolution. See also namespacing in Ossie Discussion Point: Core Analytic Abstractions And future Ontology documentation |
| Arithmetic operators | +, -, *, / (true division, see Numeric Semantics), % (modulo) |
| Comparison operators | =, <>, !=, <, >, <=, >= |
| Logical operators | AND, OR, NOT |
BETWEEN |
x BETWEEN a AND b |
IN / NOT IN |
x IN (a, b, c) This only supports lists of values, not subqueries. |
LIKE / ILIKE |
Pattern matching |
IS NULL / IS NOT NULL |
Null checks |
CASE WHEN |
Conditional logic |
| Aggregate functions | Core functions used for aggregations |
| Window functions | Core supported window functions |
| Scalar functions | See function categories below |
| Parentheses | Expression grouping |
| Construct | Reason |
|---|---|
SELECT / FROM / JOIN |
Handled by semantic layer |
GROUP BY |
Controlled by grain specification |
WHERE |
Use filter property instead |
| Subqueries | Use field references instead, or EXISTS_IN() for filtering based on a subquery. |
| CTEs | Use field references instead |
UNION / INTERSECT / EXCEPT |
Not applicable to expressions |
| DDL statements | Out of scope |
| DML statements | Out of scope |
AGG(...) outside a model-scoped metric |
Reserved for a future version. See Measure Evaluation: AGG. |
Standard SQL operator precedence applies (highest to lowest):
- Parentheses
() - Unary operators:
+,- - Multiplication/Division:
*,/,% - Addition/Subtraction:
+,- - Comparison:
=,<>,<,>,<=,>=,LIKE,IN,BETWEEN,IS NULL - Logical NOT:
NOT - Logical AND:
AND - Logical OR:
OR
| Function | Syntax | Description | Decomposability |
|---|---|---|---|
SUM |
SUM(expr) |
Sum of values | Distributive |
COUNT |
COUNT(expr) |
Count of non-null values | Distributive |
COUNT(*) |
COUNT(*) |
Count of all rows | Distributive |
COUNT(DISTINCT expr) |
COUNT(DISTINCT expr) |
Count of distinct values | Holistic |
AVG |
AVG(expr) |
Arithmetic mean | Algebraic |
MIN |
MIN(expr) |
Minimum value | Distributive |
MAX |
MAX(expr) |
Maximum value | Distributive |
| Function | Syntax | Description | Decomposability |
|---|---|---|---|
STDDEV |
STDDEV(expr) |
Sample standard deviation | Algebraic |
STDDEV_POP |
STDDEV_POP(expr) |
Population standard deviation | Algebraic |
STDDEV_SAMP |
STDDEV_SAMP(expr) |
Sample standard deviation (alias for STDDEV) | Algebraic |
VARIANCE |
VARIANCE(expr) |
Sample variance | Algebraic |
VAR_POP |
VAR_POP(expr) |
Population variance | Algebraic |
VAR_SAMP |
VAR_SAMP(expr) |
Sample variance (alias for VARIANCE) | Algebraic |
| Function | Syntax | Description | Decomposability |
|---|---|---|---|
MEDIAN |
MEDIAN(expr) |
Median value (50th percentile) | Holistic |
PERCENTILE_CONT |
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr) |
Continuous percentile (interpolated) | Holistic |
PERCENTILE_DISC |
PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY expr) |
Discrete percentile (actual value) | Holistic |
Where p is a value between 0 and 1 (e.g., 0.5 for median, 0.75 for 75th percentile).
Approximate functions trade exact accuracy for significantly better performance on large datasets. They use probabilistic algorithms (sketches) that are efficiently mergeable, making them well-suited for distributed computation.
| Function | Syntax | Description | Typical Error |
|---|---|---|---|
APPROX_COUNT_DISTINCT |
APPROX_COUNT_DISTINCT(expr) |
Approximate distinct count using HyperLogLog or something similar. Actual method is up to providers. | ~2% |
APPROX_PERCENTILE |
APPROX_PERCENTILE(expr, p) |
Approximate percentile using t-digest or similar | ~1% |
-- Approximate distinct count (much faster than COUNT(DISTINCT) on large data)
APPROX_COUNT_DISTINCT(customer_id)
-- Approximate median
APPROX_PERCENTILE(amount, 0.5)
-- Approximate 95th percentile
APPROX_PERCENTILE(response_time, 0.95)Database Support:
| Function | Snowflake | BigQuery | Databricks | PostgreSQL |
|---|---|---|---|---|
APPROX_COUNT_DISTINCT |
✅ | ✅ | ✅ | ❌ (extension) |
APPROX_PERCENTILE |
✅ | ✅ APPROX_QUANTILES |
✅ | ❌ |
Note: BigQuery uses APPROX_QUANTILES(expr, num_buckets) which returns an array. To get a specific percentile: APPROX_QUANTILES(amount, 100)[OFFSET(50)] for median.
SUM / COUNT aggregation functions support DISTINCT.
All aggregations except AGG must support filtered aggregation:
-- DISTINCT modifier
SUM(DISTINCT amount)
COUNT(DISTINCT customer_id)
-- Filtered aggregation via CASE
SUM(CASE WHEN status = 'completed' THEN amount END)
COUNT(CASE WHEN status = 'completed' THEN 1 END)
-- Filtered aggregation via FILTER (WHERE ...)
SUM(amount) FILTER (WHERE status = 'completed')
COUNT(*) FILTER (WHERE status = 'completed')Every aggregate function except AGG must support a postfix FILTER (WHERE <predicate>) modifier:
<aggregate_function>(<args>) FILTER (WHERE <predicate>)
In this version, AGG takes no FILTER clause (see Measure Evaluation: AGG).
The clause has the semantics of the SQL:2003 <filter clause> (optional feature T612): the aggregate considers only the rows for which <predicate> succeeds. FILTER is applied to the aggregate's input, not as a query WHERE, so it never removes output groups. A converter or engine MUST NOT rewrite an intrinsic FILTER predicate into a query-level WHERE clause when doing so would change group membership.
FILTER (WHERE ...) is a modifier on an aggregate expression. It is not the standalone WHERE clause listed under Not Supported in Expressions. The <predicate> must reference only fields of the same dataset as the aggregate's arguments.
An engine MAY evaluate FILTER (WHERE ...) by rewriting it to an equivalent expression, but the result MUST be identical to the result that the <filter clause> semantics define.
Note (non-normative): For an aggregate function that ignores NULL inputs, FILTER (WHERE ...) is equivalent to a CASE expression with no ELSE:
SUM(amount) FILTER (WHERE status = 'completed') --> SUM(CASE WHEN status = 'completed' THEN amount END)
COUNT(*) FILTER (WHERE status = 'completed') --> COUNT(CASE WHEN status = 'completed' THEN 1 END)
COUNT(DISTINCT customer_id) FILTER (WHERE status = 'completed') --> COUNT(DISTINCT CASE WHEN status = 'completed' THEN customer_id END)A non-matching row contributes NULL, which these functions ignore. An ELSE 0 would count as a real row and would corrupt AVG, MIN, MAX, and COUNT. The identity does not hold for an aggregate function that does not ignore NULL inputs, such as ARRAY_AGG.
Ossie SQL reserves the function name AGG for evaluating a measure: a column that holds an aggregate calculation instead of a value for each row. The Relational Query Interface defines measures (Section 4) and AGG (Section 6), including its valid arguments. In OSSIE_SQL_2026 expressions, an implementation MUST NOT give AGG any other meaning. Expressions in other dialects are not affected. Because function names should be case insensitive, the reservation covers every capitalization, such as agg and Agg.
A model-scoped metric may use AGG to evaluate a dataset-scoped metric, as in AGG(orders.total_revenue). The argument is a single dataset.metric reference, and the call takes no FILTER (WHERE ...) clause (see Metric Scoping). OSSIE_SQL_2026 expressions MUST NOT call AGG anywhere else until a future version defines it.
Compatibility. Earlier versions of this specification did not reserve AGG, so an older OSSIE_SQL_2026 expression might call a different function by that name. To update it, rename that function or write the expression in a vendor dialect. Reserving AGG as a function name does not prevent a field, metric, or column from being named agg.
For multi-stage aggregation (see Ossie Analytical Context Extension):
| Category | Functions |
|---|---|
| Distributive | SUM, COUNT, MIN, MAX |
| Algebraic | AVG, STDDEV, VARIANCE |
| Holistic | MEDIAN, PERCENTILE, COUNT DISTINCT |
| Sketch-based | APPROX_COUNT_DISTINCT, APPROX_PERCENTILE |
| Function | Syntax | Returns | Description |
|---|---|---|---|
CURRENT_DATE |
CURRENT_DATE or CURRENT_DATE() |
DATE | Current date |
CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP or CURRENT_TIMESTAMP() |
TIMESTAMP | Current timestamp |
CURRENT_TIME |
CURRENT_TIME or CURRENT_TIME() |
TIME | Current time |
| Function | Syntax | Description |
|---|---|---|
YEAR |
YEAR(date_expr) |
Extract year (integer) |
QUARTER |
QUARTER(date_expr) |
Extract quarter (1-4) |
MONTH |
MONTH(date_expr) |
Extract month (1-12) |
DAY |
DAY(date_expr) |
Extract day of month (1-31) |
DAYOFYEAR |
DAYOFYEAR(date_expr) |
Day of year (1-366) |
HOUR |
HOUR(timestamp_expr) |
Extract hour (0-23) |
MINUTE |
MINUTE(timestamp_expr) |
Extract minute (0-59) |
SECOND |
SECOND(timestamp_expr) |
Extract second (0-59) |
-- EXTRACT function (SQL standard)
EXTRACT(YEAR FROM date_expr)
EXTRACT(MONTH FROM date_expr)
EXTRACT(DAY FROM date_expr)
-- DATE_PART function (common alternative)
DATE_PART('year', date_expr)
DATE_PART('month', date_expr)
DATE_PART('day', date_expr)Supported date parts for EXTRACT and DATE_PART:
YEAR,QUARTER,MONTH,WEEK,DAYDAYOFWEEK,DAYOFYEARHOUR,MINUTE,SECOND,MILLISECOND
| Function | Syntax | Description |
|---|---|---|
DATE_TRUNC |
DATE_TRUNC(part, date_expr) |
Truncate to specified precision |
Supported parts: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'
-- Examples
DATE_TRUNC('month', order_date) -- First day of month
DATE_TRUNC('quarter', order_date) -- First day of quarter
DATE_TRUNC('week', order_date) -- First day of week (Monday)| Function | Syntax | Description |
|---|---|---|
DATEADD |
DATEADD(part, amount, date_expr) |
Add interval to date |
DATEDIFF |
DATEDIFF(part, start_date, end_date) |
Difference between dates |
-- Add/subtract intervals
DATEADD(day, 7, order_date) -- Add 7 days
DATEADD(month, -1, order_date) -- Subtract 1 month
DATEADD(year, 1, order_date) -- Add 1 year
-- Calculate differences
DATEDIFF(day, start_date, end_date) -- Days between dates
DATEDIFF(month, start_date, end_date) -- Months between dates
DATEDIFF(year, start_date, end_date) -- Years between datesConstruct DATE, TIME, and TIMESTAMP values using ANSI typed literals or CAST.
ISO-8601 strings (YYYY-MM-DD, YYYY-MM-DD HH:MI:SS, HH:MI:SS) require no format
model and behave identically across engines, making them the portable default.
| Form | Syntax | Description |
|---|---|---|
| Typed literal | DATE '2024-01-15' |
Construct a DATE |
| Typed literal | TIMESTAMP_NTZ '2024-01-15 10:30:00' |
Construct a wall-clock timestamp (no time zone) |
| Typed literal | TIME '10:30:00' |
Construct a TIME |
| Cast | CAST('2024-01-15' AS DATE) |
Parse ISO string to DATE |
| Cast | CAST('2024-01-15 10:30:00' AS TIMESTAMP_NTZ) |
Parse ISO string to timestamp |
| Cast | CAST('10:30:00' AS TIME) |
Parse ISO string to TIME |
TO_DATE |
TO_DATE(string) |
Parse ISO string to DATE |
TO_TIMESTAMP |
TO_TIMESTAMP(string) |
Parse ISO string to timestamp |
Parsing with an explicit format string relies on a datetime format model whose token
vocabulary differs across engines (Oracle/TO_CHAR-style, strftime %-codes, and
Java/LDML patterns are all in use).
For portability, we are looking to restrict the format argument to
the portable core format tokens defined in Date Formatting below, and prefer the
single-argument, ISO-8601 forms above where possible.
Since, this differs so widely across databases, consider this experimental for now.
| Function | Syntax | Description |
|---|---|---|
TO_DATE |
TO_DATE(string, format) |
Parse string to date |
TO_TIMESTAMP |
TO_TIMESTAMP(string, format) |
Parse string to timestamp |
| Function | Syntax | Description |
|---|---|---|
TO_CHAR |
TO_CHAR(date_expr, format) |
Format date as string |
Ossie defines a portable core of format tokens: the tokens that can be expressed in every major engine's datetime format model.
This feature is experimental. Implementations choosing to support these should support the following tokens for the
format argument of TO_CHAR. The strftime and Java/LDML columns below are informative,
provided to aid translation.
| Token | Meaning | strftime (C / Python / BigQuery) |
Java/LDML (Spark, .NET) |
|---|---|---|---|
YYYY |
4-digit year | %Y |
yyyy |
YY |
2-digit year | %y |
yy |
MM |
Month (01-12) | %m |
MM |
MON |
Abbreviated month name | %b |
MMM |
MONTH |
Full month name | %B |
MMMM |
DD |
Day of month (01-31) | %d |
dd |
DY |
Abbreviated day name | %a |
EEE |
DAY |
Full day name | %A |
EEEE |
HH24 |
Hour (00-23) | %H |
HH |
HH12 (HH) |
Hour (01-12) | %I |
hh |
MI |
Minute (00-59) | %M |
mm |
SS |
Second (00-59) | %S |
ss |
AM / PM |
Meridiem indicator | %p |
a |
Locale-dependent output. The name tokens (MON, MONTH, DY, DAY, AM/PM)
render text whose language is governed by engine/session locale settings; the spelling
is not guaranteed identical across engines.
Fractional seconds are available everywhere but the token and precision differ
(Oracle FF1–FF9, strftime %f, Java S…SSSSSS); treat sub-second formatting as
a dialect extension.
| Function | Syntax | Description |
|---|---|---|
CONCAT |
CONCAT(str1, str2, ...) |
Concatenate strings |
|| |
str1 || str2 |
Concatenation operator |
LENGTH |
LENGTH(str) |
String length in characters |
LOWER |
LOWER(str) |
Convert to lowercase |
UPPER |
UPPER(str) |
Convert to uppercase |
TRIM |
TRIM(str) |
Remove leading/trailing whitespace |
LTRIM |
LTRIM(str) |
Remove leading whitespace |
RTRIM |
RTRIM(str) |
Remove trailing whitespace |
LEFT |
LEFT(str, n) |
First n characters |
RIGHT |
RIGHT(str, n) |
Last n characters |
SUBSTRING |
SUBSTRING(str, start, length) |
Extract substring |
REPLACE |
REPLACE(str, from, to) |
Replace occurrences |
SPLIT_PART |
SPLIT_PART(str, delimiter, part) |
Extract part by delimiter |
| Function | Syntax | Description |
|---|---|---|
POSITION |
POSITION(substr IN str) |
Position of substring (1-based) |
CHARINDEX |
CHARINDEX(substr, str) |
Alias for POSITION |
CONTAINS |
CONTAINS(str, substr) |
Returns TRUE if contains |
STARTSWITH |
STARTSWITH(str, prefix) |
Returns TRUE if starts with |
ENDSWITH |
ENDSWITH(str, suffix) |
Returns TRUE if ends with |
| Pattern | Syntax | Description |
|---|---|---|
LIKE |
str LIKE pattern |
Case-sensitive pattern match |
ILIKE |
str ILIKE pattern |
Case-insensitive pattern match |
REGEXP_LIKE |
REGEXP_LIKE(str, pattern) |
Regular expression match |
Pattern wildcards for LIKE:
%- Match any sequence of characters_- Match any single character
| Function | Syntax | Description |
|---|---|---|
REGEXP_EXTRACT |
REGEXP_EXTRACT(str, pattern) |
Extract first match |
REGEXP_REPLACE |
REGEXP_REPLACE(str, pattern, replacement) |
Replace matches |
REGEXP_COUNT |
REGEXP_COUNT(str, pattern) |
Count matches |
| Function | Syntax | Description |
|---|---|---|
ABS |
ABS(x) |
Absolute value |
ROUND |
ROUND(x, d) |
Round to d decimal places; a value exactly halfway rounds away from zero (see Numeric Semantics) |
FLOOR |
FLOOR(x) |
Round down to integer |
CEIL / CEILING |
CEIL(x) |
Round up to integer |
TRUNC / TRUNCATE |
TRUNC(x, d) |
Truncate to d decimal places |
MOD |
MOD(x, y) |
Modulo (remainder) |
SIGN |
SIGN(x) |
Sign (-1, 0, or 1) |
Two results depend on the engine unless the language fixes them, so the same metric could return different numbers on different engines:
ROUND(x, d)breaks ties half away from zero. A value exactly halfway between two results rounds to the one farther from zero:ROUND(2.5, 0)is3,ROUND(-2.5, 0)is-3,ROUND(0.125, 2)is0.13. The rule is exact forDECIMAL/NUMERICvalues. ForFLOAT/DOUBLEit applies to the value as stored (2.675is not exactly halfway in binary), and engines differ near such values, so binary floating-point ties are best effort./is true division. The quotient keeps its fractional part whatever the operand types:5 / 2is2.5, not2. Integer division is written explicitly, for exampleTRUNC(x / y, 0)(toward zero) orFLOOR(x / y).
An engine whose default differs evaluates an equivalent expression instead, and because the rules are fixed a converter can emit it:
- Integer division (two integer operands):
CAST(x AS DECIMAL(p, s)) / y, which is exact where the engine keepsDECIMALarithmetic inDECIMAL, orCAST(x AS DOUBLE) / y, which loses precision for integers above 2^53. - Half-to-even or missing
ROUND(for example, PostgreSQL hasround(double precision)with half-to-even ties but noround(double precision, integer)): withy = ABS(x) * f, where for a literaldthe factorfis10to the powerdwritten as an exact literal (100ford = 2),SIGN(x) * (FLOOR(y) + CASE WHEN y - FLOOR(y) >= 0.5 THEN 1 ELSE 0 END) / f. It takes the fraction first, so it does not round when it adds0.5(asFLOOR(y + 0.5)does for large doubles and for values just below a tie). It is exact whereDECIMALarithmetic staysDECIMAL; where the engine computes it inDOUBLE, theFLOAT/DOUBLEcaveat above applies. - Engine functions can also differ in their arguments (for example SQLite's
TRUNCtakes one argument, and itsROUNDignores a negatived); a converter targeting such an engine rewrites those calls too.
| Function | Syntax | Description |
|---|---|---|
POWER |
POWER(x, y) |
x raised to power y |
SQRT |
SQRT(x) |
Square root |
EXP |
EXP(x) |
e raised to power x |
LN |
LN(x) |
Natural logarithm |
LOG |
LOG(base, x) |
Logarithm with specified base |
LOG10 |
LOG10(x) |
Base-10 logarithm |
| Function | Syntax | Description |
|---|---|---|
SIN |
SIN(x) |
Sine (x in radians) |
COS |
COS(x) |
Cosine |
TAN |
TAN(x) |
Tangent |
ASIN |
ASIN(x) |
Arc sine |
ACOS |
ACOS(x) |
Arc cosine |
ATAN |
ATAN(x) |
Arc tangent |
ATAN2 |
ATAN2(y, x) |
Arc tangent of y/x |
RADIANS |
RADIANS(degrees) |
Convert degrees to radians |
DEGREES |
DEGREES(radians) |
Convert radians to degrees |
PI |
PI() |
Value of π |
| Function | Syntax | Description |
|---|---|---|
GREATEST |
GREATEST(x, y, ...) |
Maximum of arguments |
LEAST |
LEAST(x, y, ...) |
Minimum of arguments |
-- Searched CASE
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
-- Simple CASE
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END| Function | Syntax | Description |
|---|---|---|
IF |
IF(condition, true_result, false_result) |
Ternary conditional |
IFF |
IFF(condition, true_result, false_result) |
Alias for IF |
NULLIF |
NULLIF(expr1, expr2) |
Returns NULL if equal |
COALESCE |
COALESCE(expr1, expr2, ...) |
First non-null value |
IFNULL |
IFNULL(expr, default) |
Alias for COALESCE with 2 args |
NVL |
NVL(expr, default) |
Alias for COALESCE with 2 args |
NVL2 |
NVL2(expr, not_null_result, null_result) |
Different results for null/not-null |
ZeroIfNull |
ZEROIFNULL(expr) |
Returns 0 if null |
NullIfZero |
NULLIFZERO(expr) |
Returns NULL if zero |
| Function | Syntax | Description |
|---|---|---|
BOOLEAN |
TRUE, FALSE |
Boolean literals |
NOT |
NOT expr |
Logical negation |
AND |
expr1 AND expr2 |
Logical AND |
OR |
expr1 OR expr2 |
Logical OR |
Window functions operate over a window frame defined by OVER(). This should act consistently with window functions in ANSI SQL.
When, adding the query interface, window functions will be subject to where they are allowed.
function_name(args) OVER (
[PARTITION BY partition_expr, ...]
[ORDER BY order_expr [ASC|DESC] [NULLS {FIRST|LAST}], ...]
[frame_clause]
)Each ORDER BY item has three independent parts:
order_expr: the value rows are sorted by.ASCorDESC: the sort direction of non-NULL values.NULLS FIRSTorNULLS LAST: whether NULL values oforder_exprsort before or after its non-NULL values, regardless of the sort direction.
-- Rank non-NULL revenues from high to low, rows with a NULL revenue last
RANK() OVER (ORDER BY revenue DESC NULLS LAST)Without NULLS FIRST or NULLS LAST, NULL sorts as the largest value: last for ASC, first for DESC. This applies to OSSIE_SQL_2026 expressions; other dialects keep their own semantics. Implementations MUST produce this order whatever the native default of the engine, or report the error that Window ORDER BY NULL Placement requires. An implementation that converts a native expression into OSSIE_SQL_2026 MUST add NULLS FIRST or NULLS LAST to each item whose native placement differs from this default.
-- revenue over three rows: 10, NULL, 5; each comment lists the values numbered 1, 2, 3
ROW_NUMBER() OVER (ORDER BY revenue) -- 5, 10, NULL
ROW_NUMBER() OVER (ORDER BY revenue DESC) -- NULL, 10, 5Frame clause options:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWROWS BETWEEN n PRECEDING AND n FOLLOWINGRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| Function | Syntax | Description |
|---|---|---|
ROW_NUMBER |
ROW_NUMBER() OVER (...) |
Sequential row number |
RANK |
RANK() OVER (...) |
Rank with gaps for ties |
DENSE_RANK |
DENSE_RANK() OVER (...) |
Rank without gaps |
NTILE |
NTILE(n) OVER (...) |
Divide into n buckets |
PERCENT_RANK |
PERCENT_RANK() OVER (...) |
Relative rank (0-1) |
CUME_DIST |
CUME_DIST() OVER (...) |
Cumulative distribution |
| Function | Syntax | Description |
|---|---|---|
LAG |
LAG(expr, offset, default) OVER (...) |
Value from previous row |
LEAD |
LEAD(expr, offset, default) OVER (...) |
Value from next row |
FIRST_VALUE |
FIRST_VALUE(expr) OVER (...) |
First value in window |
LAST_VALUE |
LAST_VALUE(expr) OVER (...) |
Last value in window |
NTH_VALUE |
NTH_VALUE(expr, n) OVER (...) |
Nth value in window |
All standard aggregation functions can be used as window functions:
-- Running total
SUM(amount) OVER (ORDER BY order_date)
-- Running average
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
-- Partition totals
SUM(amount) OVER (PARTITION BY region)
-- Percent of total
amount / SUM(amount) OVER () * 100CAST(expression AS target_type)Supported target types:
VARCHAR/STRING- Character stringINTEGER/INT/BIGINT- IntegerDECIMAL/NUMERIC- Fixed-point decimalFLOAT/DOUBLE- Floating-pointBOOLEAN- BooleanDATE- DateTIMESTAMP- TimestampTIME- Time
TRY_CAST(expression AS target_type) -- Returns NULL on failure-- Standard comparison (returns NULL if either side is NULL)
a = b
-- Null-safe comparison (treats NULLs as equal)
a IS NOT DISTINCT FROM b -- TRUE if both are NULL
a IS DISTINCT FROM b -- TRUE if one is NULL and other isn'tOssie implementations MAY support additional functions through dialect-specific extensions. When using dialect extensions, the expression must specify the dialect.
The Ossie dialect should always be supported. Other dialects MAY be ignored. There is no guarantee that all different dialects for an expression will act the same, so implementations should be consistent with their dialect handling. This means that if an Ossie model has an expression written in two dialects, the implementation should deterministically choose which dialect to use.
The dialect field is a free-form string; the core specification does not enumerate a closed set. For a non-normative catalog of dialects well-known tools use, see extensions/dialects.md.
Put the portable expression in sql and keep vendor-specific variants in
dialects:
expression:
sql: DATE_TRUNC('month', order_date)
dialects:
- dialect: SNOWFLAKE
expression: DATE_TRUNC('month', order_date)
- dialect: BIGQUERY
expression: DATE_TRUNC(order_date, MONTH)| Function | ANSI_SQL | Snowflake | BigQuery | Databricks | PostgreSQL |
|---|---|---|---|---|---|
| Date truncation | DATE_TRUNC('month', d) |
DATE_TRUNC('month', d) |
DATE_TRUNC(d, MONTH) |
DATE_TRUNC('month', d) |
DATE_TRUNC('month', d) |
| Date add | DATEADD(day, 7, d) |
DATEADD(day, 7, d) |
DATE_ADD(d, INTERVAL 7 DAY) |
DATE_ADD(d, 7) |
d + INTERVAL '7 days' |
| String concat | CONCAT(a, b) |
CONCAT(a, b) |
CONCAT(a, b) |
CONCAT(a, b) |
a || b |
| Null coalesce | COALESCE(a, b) |
COALESCE(a, b) or NVL(a, b) |
COALESCE(a, b) or IFNULL(a, b) |
COALESCE(a, b) |
COALESCE(a, b) |
| Current timestamp | CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP() |
CURRENT_TIMESTAMP() |
CURRENT_TIMESTAMP() |
CURRENT_TIMESTAMP |
| Substring | SUBSTRING(s, start, len) |
SUBSTR(s, start, len) |
SUBSTR(s, start, len) |
SUBSTRING(s, start, len) |
SUBSTRING(s, start, len) |
Vendors may expose their own feature through extensions, however the default for Ossie should be to pass unknown values through.:
This section maps Ossie standard functions to their equivalents in popular BI tools.
| Ossie Standard | Tableau | Looker Studio | DAX | Sigma |
|---|---|---|---|---|
SUM(x) |
SUM(x) |
SUM(X) |
SUM(x) |
Sum(x) |
COUNT(x) |
COUNT(x) |
COUNT(X) |
COUNT(x) |
Count(x) |
COUNT(DISTINCT x) |
COUNTD(x) |
COUNT_DISTINCT(X) |
DISTINCTCOUNT(x) |
CountDistinct(x) |
AVG(x) |
AVG(x) |
AVG(X) |
AVERAGE(x) |
Avg(x) |
MIN(x) |
MIN(x) |
MIN(X) |
MIN(x) |
Min(x) |
MAX(x) |
MAX(x) |
MAX(X) |
MAX(x) |
Max(x) |
STDDEV(x) |
STDEV(x) |
STDDEV(X) |
STDEV.S(x) |
StdDev(x) |
STDDEV_POP(x) |
STDEVP(x) |
STDDEV(X) |
STDEV.P(x) |
N/A |
VARIANCE(x) |
VAR(x) |
VARIANCE(X) |
VAR.S(x) |
Variance(x) |
MEDIAN(x) |
MEDIAN(x) |
MEDIAN(X) |
MEDIAN(x) |
Median(x) |
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY x) |
PERCENTILE(x, 0.75) |
PERCENTILE(X, 75) |
PERCENTILE.INC(x, 0.75) |
Percentile(x, 0.75) |
Two of the DAX mappings are not the same-named function, because DAX treats BLANK differently from SQL's NULL:
COUNT(x)maps toCOUNTA, notCOUNT. Both count non-blank values, but DAXCOUNTdocumentsTRUE/FALSEcolumns as unsupported, whereas SQLCOUNT(x)counts non-NULL values of any type.COUNTAcounts non-blank values of any type.COUNT(DISTINCT x)maps toDISTINCTCOUNTNOBLANK, notDISTINCTCOUNT. SQL excludes NULL from the distinct set, but DAXDISTINCTCOUNTcounts BLANK as a distinct value, so it is off by one on any nullable column.
Two further differences have no faithful DAX equivalent and should be expected rather than worked around:
- Over an empty set the SQL count functions return
0, while the DAX ones return BLANK. This is intentional in DAX, since it lets a visual hide the empty row. STDEV.S,STDEV.P,VAR.SandVAR.Praise an error when fewer than two non-blank rows remain, where SQL yields NULL (sample) or0(population).
| Ossie Standard | Tableau | Looker Studio | DAX | Sigma |
|---|---|---|---|---|
YEAR(d) |
YEAR(d) |
YEAR(Date) |
YEAR(d) |
Year(d) |
MONTH(d) |
MONTH(d) |
MONTH(Date) |
MONTH(d) |
Month(d) |
DAY(d) |
DAY(d) |
DAY(Date) |
DAY(d) |
Day(d) |
DATE_TRUNC('month', d) |
DATETRUNC('month', d) |
TODATE(d, "YYYYMM01", "YYYYMMDD") |
DATE(YEAR(d), MONTH(d), 1) |
N/A — no direct equivalent; Sigma's UI-driven date bucketing is not addressable as a formula argument |
DATEADD(day, n, d) |
DATEADD('day', n, d) |
DATE_ADD(d, n) (days only) |
DATE(d) + n or DATEADD(d, n, DAY) |
DateAdd(d, n, "day") |
DATEDIFF(day, d1, d2) |
DATEDIFF('day', d1, d2) |
DATE_DIFF(d1, d2) |
DATEDIFF(d1, d2, DAY) |
DateDiff(d1, d2, "day") |
CURRENT_DATE |
TODAY() |
TODAY() |
TODAY() |
Today() |
DAX also has a DATEADD function, but it is not the equivalent of the scalar SQL
DATEADD: it is a time-intelligence function that returns a table of shifted dates
for the current filter context, and it requires a marked date table with a contiguous
date range. For a row-level shift, add a number of days to the date directly, or use
EDATE to shift by whole months.
| Ossie Standard | Tableau | Looker Studio | DAX | Sigma |
|---|---|---|---|---|
CONCAT(a, b) |
a + b |
CONCAT(X, Y) |
CONCATENATE(a, b) or a & b |
Concat(a, b) or a & b |
LENGTH(s) |
LEN(s) |
LENGTH(X) |
LEN(s) |
Length(s) |
LOWER(s) |
LOWER(s) |
LOWER(X) |
LOWER(s) |
Lower(s) |
UPPER(s) |
UPPER(s) |
UPPER(X) |
UPPER(s) |
Upper(s) |
TRIM(s) |
TRIM(s) |
TRIM(X) |
TRIM(s) |
Trim(s) |
LEFT(s, n) |
LEFT(s, n) |
LEFT_TEXT(X, n) |
LEFT(s, n) |
Left(s, n) |
RIGHT(s, n) |
RIGHT(s, n) |
RIGHT_TEXT(X, n) |
RIGHT(s, n) |
Right(s, n) |
SUBSTRING(s, start, len) |
MID(s, start, len) |
SUBSTR(X, start, len) |
MID(s, start, len) |
Mid(s, start, len) |
REPLACE(s, from, to) |
REPLACE(s, from, to) |
REPLACE(X, Y, Z) |
SUBSTITUTE(s, from, to) |
Replace(s, from, to) |
CONTAINS(s, sub) |
CONTAINS(s, sub) |
CONTAINS_TEXT(X, text) |
CONTAINSSTRING(s, sub) |
Contains(s, sub) |
| Ossie Standard | Tableau | Looker Studio | DAX | Sigma |
|---|---|---|---|---|
CASE WHEN... |
CASE WHEN... or IF... |
CASE WHEN... |
SWITCH(TRUE(), ...) |
If(c1, v1, c2, v2, ..., else) (variadic; else is optional and defaults to Null) |
IF(cond, t, f) |
IF cond THEN t ELSE f END |
N/A (use CASE) | IF(cond, t, f) |
If(cond, t, f) |
COALESCE(a, b) |
IFNULL(a, b) or ZN(a) |
COALESCE(...) |
COALESCE(a, b) |
IfNull(a, b) (2-argument only) |
NULLIF(a, b) |
IF a = b THEN NULL ELSE a END |
N/A | IF(a = b, BLANK(), a) |
NullIf(a, b) |
| Ossie Standard | Tableau | Looker Studio | DAX | Sigma |
|---|---|---|---|---|
ROW_NUMBER() OVER(...) |
INDEX() |
N/A | RANKX(...) with DENSE |
RowNumber() — but partition/order come from UI table-calculation configuration, not formula arguments, so it has no portable expression form |
RANK() OVER(...) |
RANK(expr) |
N/A | RANKX(...) |
Rank() — same UI-configuration caveat as RowNumber() |
SUM(...) OVER(PARTITION BY...) |
{FIXED [...]: SUM(...)} |
N/A (blending only) | Context-dependent | RunningSum(...)/RunningAvg(...) — same UI-configuration caveat |
LAG(x, 1) OVER(ORDER BY...) |
LOOKUP(x, -1) |
N/A | CALCULATE(x, PREVIOUSDAY(...)) |
Lag(x, 1) — same UI-configuration caveat |
RUNNING_SUM(...) |
RUNNING_SUM(SUM(...)) |
N/A | CALCULATE(SUM(...), FILTER(...)) |
RunningSum(...) — same UI-configuration caveat |
Sigma's table-calculation functions (RowNumber, Rank, RunningSum, RunningAvg, Lag, Lead, etc.) resolve their partition/order context from workbook UI configuration (which pivot/table the calculation is attached to) rather than from arguments passed in the formula text itself. Because that context isn't recoverable from the formula string alone, the Sigma converter treats these as untranslatable to ANSI SQL and carries the original Sigma formula through in the SIGMA dialect only — see its LIMITATIONS.md.
When an ORDER BY item omits the clause, the Ossie placement is NULLS LAST for ASC and NULLS FIRST for DESC. An implementation produces any placement, given or default, as follows:
- On an engine with
NULLS FIRSTandNULLS LAST, it emits the clause. It MAY omit a default placement that the native default already gives. This holds only if the session cannot change that default, or the implementation sets it. Snowflake (DEFAULT_NULL_ORDERING) and DuckDB (default_null_order) let a session change it. - On an engine without them, it puts a sort key
CASE WHEN order_expr IS NULL THEN 1 ELSE 0 ENDimmediately before eachORDER BYitem, built from that item'sorder_expr. The key is ascending to put NULLs last and descending to put them first. For example,ORDER BY d_year, d_moy DESCbecomesORDER BY CASE WHEN d_year IS NULL THEN 1 ELSE 0 END, d_year, CASE WHEN d_moy IS NULL THEN 1 ELSE 0 END DESC, d_moy DESC. Where the native placement already matches, it MAY omit the key, on the same condition as in the bullet above. - If an item needs the key and the engine cannot add it, the implementation MUST report an error instead of emitting SQL that places NULLs differently. For example, MySQL requires exactly one
ORDER BYexpression in aRANGEframe with an offset.
Native defaults:
| Engine | NULLS FIRST, NULLS LAST |
Native ASC |
Native DESC |
|---|---|---|---|
| Oracle, PostgreSQL, Redshift, Snowflake | yes | NULLS LAST | NULLS FIRST |
| BigQuery, Databricks | yes | NULLS FIRST | NULLS LAST |
| ClickHouse, DuckDB, Trino | yes | NULLS LAST | NULLS LAST |
| MySQL, SQL Server | no | NULLS FIRST | NULLS LAST |
Implementations MUST support all functions marked as REQUIRED in this specification. These represent the minimum portable expression language.
Implementations SHOULD support functions marked as RECOMMENDED. These are common analytical functions that may not be available in all databases.
Implementations MAY support additional functions through dialect extensions. These should be documented as dialect-specific.
A machine-readable ANTLR 4 grammar for this language is available in grammar/. It is a
tooling aid; where it and this document differ, this document is authoritative.
| Version | Date | Changes |
|---|---|---|
| 0.2.0.dev | 2026-07-15 | Initial draft |
| 0.2.0.dev | 2026-10-29 | State that function names are case insensitive. Reserve AGG for measure evaluation; expressions that call another function named AGG must rename it or move to a vendor dialect |
| 0.2.0.dev | 2026-11-06 | Allow model-scoped metrics to evaluate a dataset-scoped metric with AGG(dataset.metric) |
