Tisya Bhatia created CALCITE-7597:
-------------------------------------
Summary: Support ORDER BY ALL
Key: CALCITE-7597
URL: https://issues.apache.org/jira/browse/CALCITE-7597
Project: Calcite
Issue Type: New Feature
Components: core
Reporter: Tisya Bhatia
ORDER BY ALL: when ORDER BY ALL appears with no order items, the query sorts by
every expression in the SELECT clause, in select-list order. An optional
trailing ASC/DESC and NULLS FIRST/LAST applies to all keys.
Example:
SELECT name, hire_date
FROM emp
ORDER BY ALL; -- equivalent to ORDER BY name, hire_date
SELECT name, hire_date
FROM emp
ORDER BY ALL DESC NULLS LAST; -- direction applies to every key
Motivation: keeps the ORDER BY in sync with the SELECT list automatically and
removes boilerplate when sorting by every projected column. It is an emerging
convergence across warehouse dialects (DuckDB, Databricks, Snowflake, Spark,
Trino), and a natural companion to GROUP BY ALL (CALCITE-7594).
Compatibility: purely additive. ORDER BY ALL does not parse today, so enabling
it cannot change the meaning of any existing query. Unlike GROUP BY, ALL has no
existing meaning after ORDER BY, so there is no set-quantifier collision.
Proposed behaviour:
- ALL must stand alone (no trailing order items).
- The parser emits a marker that the validator expands into the SELECT
expressions during validateOrderList, before the base validation runs, so the
converter/optimizer never see a marker.
- SELECT * with ORDER BY ALL is rejected with a clear error for now (the star
is not expanded at validation time); may be revisited later.
Scope:
- ORDER BY ALL only. Positional ORDER BY <ordinal> is already supported via
SqlConformance.isSortByOrdinal and is out of scope.
A working prototype (parser + validator + tests + reference docs) is ready. PR
will be up.
A discussion will be raised on [email protected].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
