Repository navigation
Expression language does not say where NULLs sort in a window ORDER BY #498
Description
The window function syntax in core-spec/expression_language.md is
[ORDER BY order_expr [ASC|DESC], ...]
with no NULLS FIRST | NULLS LAST option and no statement of the default. The section says window functions "should act consistently with window functions in ANSI SQL", but engines disagree on where NULLs go. Defaults, checked 2026-10-02:
| Engine or parser | ASC | DESC | Source |
|---|---|---|---|
| ClickHouse 26.9.5.2 | NULLs last | NULLs last | executed; docs |
| DuckDB 1.5.6 | last | last | executed |
| PostgreSQL | last | first | docs: "null values sort as if larger than any non-null value" |
| Snowflake | last | first | docs: DEFAULT_NULL_ORDERING defaults to LAST, "NULL values are considered to be higher than any non-NULL values" |
| SQLGlot 30.21.0, default dialect (inherited by the Ossie dialect of #222) | first | last | NULL_ORDERING = "nulls_are_small" |
| SQLGlot 30.21.0, Snowflake and PostgreSQL dialects | last | first | NULL_ORDERING = "nulls_are_large" |
A transpiler that preserves semantics emits an explicit NULLS FIRST or NULLS LAST whenever the target engine's default differs from the source dialect's. So the same model expression yields different ClickHouse SQL depending on which parser read it. For cumulative_sales in examples/tpcds_semantic_model.yaml:
SUM(SUM(store_sales.ss_ext_sales_price)) OVER (ORDER BY date_dim.d_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)the #222 dialect produces ORDER BY date_dim.d_date NULLS FIRST ..., the Snowflake parser produces ORDER BY date_dim.d_date ... (ClickHouse default, NULLs last).
Measured on ClickHouse 26.9.5.2 with the metrics evaluated over a LEFT JOIN (join_use_nulls = 1), so that some store_sales rows have a NULL d_date:
| Metric | #222 dialect (NULLs first) | Snowflake parser (NULLs last) |
|---|---|---|
cumulative_sales, running total on the NULL-date row |
124,556,714.61 | 5,262,470,646.52 |
monthly_sales_change, January 1998 |
-75,399,822.72 (the previous row is the NULL one) | NULL (no previous row) |
Script, corpus and raw output: https://github.com/Dactopus/ossie-clickhouse/blob/main/research/2026-10-02-pr222-sqlglot-dialect/README.md
Both readings are consistent with the spec as written. Ways to close the gap:
- State a default. The two conventions in use are "NULLs are larger than any value" (PostgreSQL, Snowflake) and "NULLs last in both directions" (ClickHouse, DuckDB).
- Add
NULLS FIRST | NULLS LASTto the window ORDER BY grammar, so an author can say it. - Both.
In any case an implementation on an engine whose default differs has to emit the explicit clause, so a case with NULLs in the ordering column belongs in the compliance suite.
Raised at JB's request in https://lists.apache.org/thread/k3scr08d3c1zgz7x0vj8wz7szsvv2ozh
Activity
christianeu-db commentedon Oct 12, 2026
@ekut this is a great find - do you want to take a first pass at this item?
I definitely think the grammar should be extended (your option 2). I see at least 3 different behaviors in the table above so clearly we need some way of specifying the behavior for cross-vendor portability.
Then, a design question is probably whether we should allow for unspecified defaults (which could be wrong) or requiring this option to be set (which fails loudly). That seems worth raising with the broader list
ekut commentedon Oct 13, 2026
Thanks, Chris. #577 adds
NULLS FIRST | NULLS LASTto the window grammar. The default-or-required question is on the list: https://lists.apache.org/thread/z2jr7q4pllsk5nkzv7d18ro8844n5tgyfeat(core-spec): allow NULLS FIRST and NULLS LAST in window ORDER BY (#…