Hi all, I'd like to propose support for ORDER BY ALL in Calcite - a shorthand that sorts by every expression in the SELECT clause, in select-list order.
Here is the Jira ticket: https://issues.apache.org/jira/browse/CALCITE-7597 Context: - Today, if I want to order by every expression in the SELECT list, I would have to repeat them in the ORDER BY. - Like GROUP BY ALL, an ORDER BY ALL shorthand has become convergence across warehouse dialects, and supporting it keeps Calcite-based dialects updated and aligned. Proposal: - Add ORDER BY ALL: when ALL appears with no order items, order by every SELECT expression in select-list order. An optional trailing ASC / DESC and NULLS FIRST / NULLS LAST applies to all keys. It's implemented as a parser marker that the validator rewrites into the concrete order expressions before normal order validation runs, so the converter / optimizer never see a marker. - Alternatives considered: I kept ALL (matches DuckDB, Spark, and others). The existing ALL has no meaning after ORDER BY, so there is no set-quantifier collision to preserve. The syntax is purely new. - Expected impact / tradeoff: purely additive. ORDER BY ALL does not parse today, so enabling it cannot change the meaning of any existing query. SELECT * with ORDER BY ALL is rejected with a clear error for now (the star isn't expanded at validation time) For example: SELECT name, hire_date FROM emp ORDER BY ALL; resolves to SELECT name, hire_date FROM emp ORDER BY name, hire_date; and a trailing direction applies to all keys, so ORDER BY ALL DESC resolves to ORDER BY name DESC, hire_date DESC. Open questions: - Since this feature is additive, I don't think a conformance flag is needed, but would we prefer one? - Should SELECT * be supported in this proposal, or is the explicit error enough for an initial cut? If this direction sounds reasonable, I have a prototype ready (parser + validator + tests + reference docs) and will put up the PR soon. A companion GROUP BY ALL proposal (CALCITE-7594) is in the same vein (already sent on dev@calcite - https://lists.apache.org/thread/ynrzm793l8swymshx1nohd5jhsxd326z). Best, Tisya Bhatia
