This is an automated email from the ASF dual-hosted git repository.
xiedeyantu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new ea98b94f39 [CALCITE-7597] Support ORDER BY ALL
ea98b94f39 is described below
commit ea98b94f396456ebe89b29fc56d8011b39a1fbd2
Author: Tisya Bhatia <[email protected]>
AuthorDate: Mon Jun 22 10:03:01 2026 -0500
[CALCITE-7597] Support ORDER BY ALL
---
core/src/main/codegen/templates/Parser.jj | 31 ++++++++++--
.../apache/calcite/runtime/CalciteResource.java | 3 ++
.../main/java/org/apache/calcite/sql/SqlKind.java | 4 ++
.../calcite/sql/fun/SqlInternalOperators.java | 12 +++++
.../calcite/sql/validate/SqlValidatorImpl.java | 57 ++++++++++++++++++++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../apache/calcite/sql/test/SqlAdvisorTest.java | 12 +++--
.../org/apache/calcite/test/SqlValidatorTest.java | 24 +++++++++
core/src/test/resources/sql/sort.iq | 14 ++++++
site/_docs/reference.md | 8 ++-
.../apache/calcite/sql/parser/SqlParserTest.java | 30 ++++++++++++
11 files changed, 188 insertions(+), 8 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 077481c262..5519dd566d 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -3093,6 +3093,7 @@ SqlNodeList OrderBy(boolean accept) :
{
final List<SqlNode> list = new ArrayList<SqlNode>();
final Span s;
+ SqlNode all;
}
{
<ORDER> {
@@ -3104,10 +3105,32 @@ SqlNodeList OrderBy(boolean accept) :
throw SqlUtil.newContextException(s.pos(),
RESOURCE.illegalOrderBy());
}
}
- <BY> OrderItemList(list)
- {
- return new SqlNodeList(list, s.addAll(list).pos());
- }
+ <BY>
+ (
+ <ALL> { all = SqlInternalOperators.ORDER_BY_ALL.createCall(getPos()); }
+ (
+ <ASC>
+ | <DESC> { all = SqlStdOperatorTable.DESC.createCall(getPos(), all);
}
+ )?
+ (
+ LOOKAHEAD(2)
+ <NULLS> <FIRST> {
+ all = SqlStdOperatorTable.NULLS_FIRST.createCall(getPos(),
all);
+ }
+ |
+ <NULLS> <LAST> {
+ all = SqlStdOperatorTable.NULLS_LAST.createCall(getPos(), all);
+ }
+ )?
+ {
+ list.add(all);
+ return new SqlNodeList(list, s.addAll(list).pos());
+ }
+ |
+ OrderItemList(list) {
+ return new SqlNodeList(list, s.addAll(list).pos());
+ }
+ )
}
<#if parser.includeSelectBy!false>
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 171a8da68a..ac2ed85019 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -777,6 +777,9 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("Streaming ORDER BY must start with monotonic expression")
ExInst<SqlValidatorException> streamMustOrderByMonotonic();
+ @BaseMessage("ORDER BY ALL requires an explicit SELECT list; ''*'' is not
supported")
+ ExInst<SqlValidatorException> orderByAllRequiresExplicitSelectList();
+
@BaseMessage("Set operator cannot combine streaming and non-streaming
inputs")
ExInst<SqlValidatorException> streamSetOpInconsistentInputs();
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 680c833c17..a70c71df76 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -195,6 +195,9 @@ public enum SqlKind {
*/
ORDER_BY,
+ /** The ALL keyword of the ORDER BY clause. */
+ ORDER_BY_ALL,
+
/** WITH clause. */
WITH,
@@ -1482,6 +1485,7 @@ public enum SqlKind {
TIMESTAMP_ADD, TIMESTAMP_DIFF, TIMESTAMP_SUB,
EXTRACT, INTERVAL,
LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY,
+ ORDER_BY_ALL,
NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE,
VALUES, WITH, WITH_ITEM, ITEM, SKIP_TO_FIRST, SKIP_TO_LAST,
JSON_VALUE_EXPRESSION, UNNEST),
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index 8757e1e177..d1429f501b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -208,6 +208,18 @@ private SqlInternalOperators() {
public static final SqlInternalOperator GROUP_BY_ALL =
new SqlRollupOperator("GROUP BY ALL", SqlKind.GROUP_BY_ALL);
+ /** {@code ORDER BY ALL}, a placeholder expanded during validation into
+ * a standard {@code ORDER BY}. */
+ public static final SqlInternalOperator ORDER_BY_ALL =
+ // High precedence so the placeholder is never wrapped in parentheses
when it
+ // appears alone or inside DESC / NULLS FIRST | LAST in an
always-parentheses writer
+ new SqlInternalOperator("ORDER BY ALL", SqlKind.ORDER_BY_ALL, 100) {
+ @Override public void unparse(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ writer.keyword("ALL");
+ }
+ };
+
/** Fetch operator is ONLY used for its precedence during unparsing. */
public static final SqlOperator FETCH =
SqlBasicOperator.create("FETCH")
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index a41be8c67b..37f6712e90 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -5209,6 +5209,7 @@ protected void validateOrderList(SqlSelect select) {
// ORDER BY is validated in a scope where aliases in the SELECT clause
// are visible. For example, "SELECT empno AS x FROM emp ORDER BY x"
// is valid.
+ rewriteOrderByAll(select);
SqlNodeList orderList = select.getOrderList();
if (orderList == null) {
return;
@@ -5236,6 +5237,62 @@ protected void validateOrderList(SqlSelect select) {
}
}
+ protected void rewriteOrderByAll(SqlSelect select) {
+ final SqlNodeList orderList = select.getOrderList();
+ if (orderList == null || orderList.size() != 1) {
+ return;
+ }
+
+ SqlNode node = orderList.get(0);
+ boolean desc = false;
+ SqlKind nulls = null;
+
+ while (node instanceof SqlCall) {
+ final SqlKind kind = node.getKind();
+ if (kind == SqlKind.NULLS_FIRST || kind == SqlKind.NULLS_LAST) {
+ nulls = kind;
+ node = ((SqlCall) node).operand(0);
+ } else if (kind == SqlKind.DESCENDING) {
+ desc = true;
+ node = ((SqlCall) node).operand(0);
+ } else {
+ break;
+ }
+ }
+
+ if (node.getKind() != SqlKind.ORDER_BY_ALL) {
+ return;
+ }
+ final SqlParserPos pos = orderList.getParserPosition();
+ final List<SqlNode> keys = new ArrayList<>();
+
+ for (SqlNode selectItem : select.getSelectList()) {
+ final SqlNode expr = SqlUtil.stripAs(selectItem);
+ if (expr instanceof SqlIdentifier && ((SqlIdentifier) expr).isStar()) {
+ throw newValidationError(expr,
+ RESOURCE.orderByAllRequiresExplicitSelectList());
+ }
+ keys.add(applyOrderByAllDirection(expr, desc, nulls, pos));
+ }
+ select.setOrderBy(new SqlNodeList(keys, pos));
+ }
+
+ /** Wraps a single ORDER BY ALL key with the optional descending direction
+ * and null-ordering that apply to every expanded key. */
+ private static SqlNode applyOrderByAllDirection(SqlNode key, boolean desc,
+ @Nullable SqlKind nulls, SqlParserPos pos) {
+ SqlNode result = key;
+ if (desc) {
+ result = SqlStdOperatorTable.DESC.createCall(pos, result);
+ }
+ if (nulls == SqlKind.NULLS_FIRST) {
+ result = SqlStdOperatorTable.NULLS_FIRST.createCall(pos, result);
+ } else if (nulls == SqlKind.NULLS_LAST) {
+ result = SqlStdOperatorTable.NULLS_LAST.createCall(pos, result);
+ }
+ return result;
+ }
+
/**
* Validates an item in the GROUP BY clause of a SELECT statement.
*
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 4096537b7f..0799d8d6cc 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -256,6 +256,7 @@ CannotConvertToStream=Cannot convert table ''{0}'' to stream
CannotConvertToRelation=Cannot convert stream ''{0}'' to relation
StreamMustGroupByMonotonic=Streaming aggregation requires at least one
monotonic expression in GROUP BY clause
StreamMustOrderByMonotonic=Streaming ORDER BY must start with monotonic
expression
+OrderByAllRequiresExplicitSelectList=ORDER BY ALL requires an explicit SELECT
list; ''*'' is not supported
StreamSetOpInconsistentInputs=Set operator cannot combine streaming and
non-streaming inputs
CannotStreamValues=Cannot stream VALUES
CyclicDefinition=Cannot resolve ''{0}''; it references view ''{1}'', whose
definition is cyclic
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 94cbc6fb9e..fad2778a38 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -71,6 +71,10 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
Collections.singletonList(
"KEYWORD(*)");
+ private static final List<String> ORDER_BY_ALL_KEYWORD =
+ Collections.singletonList(
+ "KEYWORD(ALL)");
+
protected static final List<String> FROM_KEYWORDS =
Arrays.asList(
"KEYWORD(()",
@@ -744,10 +748,12 @@ protected List<String> getJoinKeywords() {
String sql;
sql = "select emp.empno from sales.emp where empno=1 order by ^dummy";
- f.withSql(sql).assertHint(EXPR_KEYWORDS, EMP_COLUMNS, EMP_TABLE);
+ f.withSql(sql).assertHint(EXPR_KEYWORDS, ORDER_BY_ALL_KEYWORD, EMP_COLUMNS,
+ EMP_TABLE);
sql = "select emp.empno from sales.emp where empno=1 order by ^";
- f.withSql(sql).assertComplete(EXPR_KEYWORDS, EMP_COLUMNS, EMP_TABLE);
+ f.withSql(sql).assertComplete(EXPR_KEYWORDS, ORDER_BY_ALL_KEYWORD,
+ EMP_COLUMNS, EMP_TABLE);
sql =
"select emp.empno\n"
@@ -755,7 +761,7 @@ protected List<String> getJoinKeywords() {
+ " mpno,name,ob,gr,iredate,al,omm,eptno,lacker)\n"
+ "where e.mpno=1 order by ^";
f.withSql(sql)
- .assertComplete(EXPR_KEYWORDS,
+ .assertComplete(EXPR_KEYWORDS, ORDER_BY_ALL_KEYWORD,
Arrays.asList("COLUMN(MPNO)",
"COLUMN(NAME)",
"COLUMN(OB)",
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index c4e8993839..f85c6c75bf 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7324,6 +7324,30 @@ public boolean isBangEqualAllowed() {
.ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7597";>[CALCITE-7597]
+ * Support ORDER BY ALL</a>. */
+ @Test void testOrderByAll() {
+ // expands to all SELECT items, validates fine
+ sql("select deptno, sal from emp order by all").ok();
+ // direction applies to every expanded key
+ sql("select deptno, sal from emp order by all desc").ok();
+ // SELECT * can't be expanded here
+ sql("select ^*^ from emp order by all")
+ .fails("(?s).*ORDER BY ALL requires an explicit SELECT list.*");
+ // Aliases that shadow other column names must not confuse expansion
+ sql("select empno as deptno, deptno as empno from emp order by all").ok();
+ // verify "x" still resolves and the two features coexist
+ sql("select sal as x, x + 1 as y from emp order by all")
+ .withValidatorIdentifierExpansion(true)
+ .withConformance(SqlConformanceEnum.BABEL)
+ .ok();
+ sql("select sal as x, x + 1 as y from emp order by all desc")
+ .withValidatorIdentifierExpansion(true)
+ .withConformance(SqlConformanceEnum.BABEL)
+ .ok();
+ }
+
@Test void testOrder() {
final SqlConformance conformance = fixture().conformance();
sql("select empno as x from emp order by empno").ok();
diff --git a/core/src/test/resources/sql/sort.iq
b/core/src/test/resources/sql/sort.iq
index b7df52bff2..f9149a5482 100644
--- a/core/src/test/resources/sql/sort.iq
+++ b/core/src/test/resources/sql/sort.iq
@@ -549,4 +549,18 @@ order by au."books"[1]."title";
!ok
+# [CALCITE-7597] ORDER BY ALL sorts by every SELECT expression, in list order
+select x, y from (values (2, 'b'), (1, 'a'), (1, 'c')) as t(x, y)
+order by all;
++---+---+
+| X | Y |
++---+---+
+| 1 | a |
+| 1 | c |
+| 2 | b |
++---+---+
+(3 rows)
+
+!ok
+
# End sort.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b6ecb6b52b..6250a6d639 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -191,7 +191,7 @@ ## Grammar
| query MINUS [ ALL | DISTINCT ] query
| query INTERSECT [ ALL | DISTINCT ] query
}
- [ ORDER BY orderItem [, orderItem ]* ]
+ [ ORDER BY { ALL [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] | orderItem
[, orderItem]* } ]
[ LIMIT [ start, ] { count | ALL } ]
[ OFFSET start { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
@@ -421,6 +421,12 @@ ## Grammar
In *orderItem*, if *expression* is a positive integer *n*, it denotes
the <em>n</em>th item in the SELECT clause.
+`ORDER BY ALL` sorts by every expression in the SELECT clause,
+in the order that they appear in the list; for example:
+"SELECT x, y FROM t ORDER BY ALL" is equivalent to
+"SELECT x, y FROM t ORDER BY x, y"
+An optional trailing ASC / DESC and NULLS FIRST / NULLS LAST applies to all
keys.
+
In *query*, *count* and *start* may each be either an unsigned integer literal
or a dynamic parameter whose value is an integer.
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 0fa8c43fd9..591278ebfa 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -3947,6 +3947,36 @@ void checkPeriodPredicate(Checker checker) {
+ "ORDER BY `EMPNO`, `GENDER` DESC, `DEPTNO`, `EMPNO`, `NAME`
DESC");
}
+ @Test void testOrderByAll() {
+ final String sql = "select x, y from t\n"
+ + "order by all";
+ final String expected = "SELECT `X`, `Y`\n"
+ + "FROM `T`\n"
+ + "ORDER BY ALL";
+ sql(sql).ok(expected);
+
+ final String sql1 = "select x, y from t\n"
+ + "order by all desc";
+ final String expected1 = "SELECT `X`, `Y`\n"
+ + "FROM `T`\n"
+ + "ORDER BY ALL DESC";
+ sql(sql1).ok(expected1);
+
+ final String sql2 = "select x, y from t\n"
+ + "order by all desc nulls last";
+ final String expected2 = "SELECT `X`, `Y`\n"
+ + "FROM `T`\n"
+ + "ORDER BY ALL DESC NULLS LAST";
+ sql(sql2).ok(expected2);
+
+ final String sql3 = "select x, y from t\n"
+ + "order by all nulls first";
+ final String expected3 = "SELECT `X`, `Y`\n"
+ + "FROM `T`\n"
+ + "ORDER BY ALL NULLS FIRST";
+ sql(sql3).ok(expected3);
+ }
+
@Test void testOrderNullsFirst() {
final String sql = "select * from emp\n"
+ "order by gender desc nulls last,\n"
