This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 eaadc32db8 [CALCITE-5406] Support the SELECT DISTINCT ON statement for
PostgreSQL dialect
eaadc32db8 is described below
commit eaadc32db821186e4a887b804099c2dbdd96878d
Author: Yu Xu <[email protected]>
AuthorDate: Mon May 11 16:54:46 2026 +0800
[CALCITE-5406] Support the SELECT DISTINCT ON statement for PostgreSQL
dialect
---
babel/src/main/codegen/config.fmpp | 1 +
.../java/org/apache/calcite/test/BabelTest.java | 36 ++++++
babel/src/test/resources/sql/select.iq | 86 ++++++++++++++
core/src/main/codegen/config.fmpp | 5 +-
core/src/main/codegen/default_config.fmpp | 2 +
core/src/main/codegen/templates/Parser.jj | 31 ++++-
.../apache/calcite/runtime/CalciteResource.java | 9 ++
.../java/org/apache/calcite/sql/SqlSelect.java | 54 +++++++--
.../org/apache/calcite/sql/SqlSelectOperator.java | 13 ++-
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 10 ++
.../calcite/sql/validate/SqlConformanceEnum.java | 10 ++
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 53 ++++++++-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 98 +++++++++++++++-
.../calcite/runtime/CalciteResource.properties | 3 +
core/src/test/codegen/config.fmpp | 1 +
.../apache/calcite/test/SqlToRelConverterTest.java | 31 +++++
.../org/apache/calcite/test/SqlValidatorTest.java | 125 ++++++++++++++++++++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 56 +++++++++
server/src/main/codegen/config.fmpp | 1 +
site/_docs/reference.md | 21 +++-
.../apache/calcite/sql/parser/SqlParserTest.java | 98 ++++++++++++++++
23 files changed, 733 insertions(+), 19 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp
b/babel/src/main/codegen/config.fmpp
index 30c2ce7d65..a4e0e9d1ce 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -618,6 +618,7 @@ data: {
includeParsingStringLiteralAsArrayLiteral: true
includeIntervalWithoutQualifier: true
includeSelectBy: true
+ includeDistinctOn: true
}
}
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
index 1deda157fa..6103d61ed4 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
@@ -594,6 +594,41 @@ private void checkSqlResult(String funLibrary, String
query, String result) {
.returns(result);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement</a>. */
+ @Test void testDistinctOn() {
+ final SqlValidatorFixture v = Fixtures.forValidator()
+ .withParserConfig(c -> c.withParserFactory(SqlBabelParserImpl.FACTORY))
+ .withConformance(SqlConformanceEnum.BABEL);
+
+ // Basic DISTINCT ON
+ v.withSql("select distinct on (deptno) empno, ename from emp order by
deptno, empno")
+ .ok();
+
+ // DISTINCT ON with multiple columns
+ v.withSql("select distinct on (deptno, job) empno, ename from emp order by
deptno, job, empno")
+ .ok();
+
+ // DISTINCT ON with expression
+ v.withSql("select distinct on (deptno) empno, sal * 12 as annual_sal "
+ + "from emp order by deptno, sal desc").ok();
+
+ // DISTINCT ON without ORDER BY should fail
+ v.withSql("^select distinct on (deptno) empno from emp^")
+ .fails("SELECT DISTINCT ON requires an ORDER BY clause");
+
+ // DISTINCT ON with ORDER BY mismatch should fail
+ v.withSql("select distinct on (deptno) empno from emp order by ^empno^")
+ .fails("SELECT DISTINCT ON expressions must match ORDER BY
expressions");
+
+ // DISTINCT and DISTINCT ON are mutually exclusive
+ v.withSql("SELECT DISTINCT ^DISTINCT^ ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, empno")
+ .fails("(?s)Incorrect syntax near the keyword 'DISTINCT' at line 1,
column 17.*");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7337";>[CALCITE-7337]
* Add age function (enabled in PostgreSQL library)</a>. */
@@ -637,4 +672,5 @@ private void checkSqlResult(String funLibrary, String
query, String result) {
.query("SELECT AGE(timestamp '2023-12-25') FROM (VALUES (1)) t")
.runs();
}
+
}
diff --git a/babel/src/test/resources/sql/select.iq
b/babel/src/test/resources/sql/select.iq
index f94905c16e..60af913f14 100755
--- a/babel/src/test/resources/sql/select.iq
+++ b/babel/src/test/resources/sql/select.iq
@@ -332,4 +332,90 @@ from emp e join dept d on e.deptno = d.deptno;
SELECT * REPLACE list contains unknown column(s): DEPTNO
!error
+# [CALCITE-5406] Support the SELECT DISTINCT ON statement for PostgreSQL
dialect
+# Note: All results in this section have been verified against PostgreSQL and
are identical
+
+# Test basic DISTINCT ON
+SELECT DISTINCT ON (deptno) empno, ename, deptno
+FROM emp
+ORDER BY deptno, empno;
++-------+-------+--------+
+| EMPNO | ENAME | DEPTNO |
++-------+-------+--------+
+| 7782 | CLARK | 10 |
+| 7369 | SMITH | 20 |
+| 7499 | ALLEN | 30 |
++-------+-------+--------+
+(3 rows)
+
+!ok
+
+# Test DISTINCT ON with descending order
+SELECT DISTINCT ON (deptno) empno, ename, sal, deptno
+FROM emp
+ORDER BY deptno, sal DESC, empno;
++-------+-------+---------+--------+
+| EMPNO | ENAME | SAL | DEPTNO |
++-------+-------+---------+--------+
+| 7839 | KING | 5000.00 | 10 |
+| 7788 | SCOTT | 3000.00 | 20 |
+| 7698 | BLAKE | 2850.00 | 30 |
++-------+-------+---------+--------+
+(3 rows)
+
+!ok
+
+# Test DISTINCT ON with multiple columns
+SELECT DISTINCT ON (deptno, job) empno, ename, deptno, job
+FROM emp
+ORDER BY deptno, job, empno;
++-------+--------+--------+-----------+
+| EMPNO | ENAME | DEPTNO | JOB |
++-------+--------+--------+-----------+
+| 7934 | MILLER | 10 | CLERK |
+| 7782 | CLARK | 10 | MANAGER |
+| 7839 | KING | 10 | PRESIDENT |
+| 7788 | SCOTT | 20 | ANALYST |
+| 7369 | SMITH | 20 | CLERK |
+| 7566 | JONES | 20 | MANAGER |
+| 7900 | JAMES | 30 | CLERK |
+| 7698 | BLAKE | 30 | MANAGER |
+| 7499 | ALLEN | 30 | SALESMAN |
++-------+--------+--------+-----------+
+(9 rows)
+
+!ok
+
+# Test DISTINCT ON with window function
+SELECT DISTINCT ON (deptno) empno, ename, ROW_NUMBER() OVER (ORDER BY sal) as
rn
+FROM emp
+ORDER BY deptno, empno;
++-------+-------+----+
+| EMPNO | ENAME | RN |
++-------+-------+----+
+| 7782 | CLARK | 9 |
+| 7369 | SMITH | 1 |
+| 7499 | ALLEN | 8 |
++-------+-------+----+
+(3 rows)
+
+!ok
+
+# Test DISTINCT and DISTINCT ON are mutually exclusive
+SELECT DISTINCT DISTINCT ON (deptno) empno, ename, deptno
+FROM emp
+ORDER BY deptno, empno;
+Incorrect syntax near the keyword 'DISTINCT'
+!error
+
+# Test DISTINCT ON unparse
+SELECT DISTINCT ON (deptno) empno, ename, deptno
+FROM emp
+ORDER BY deptno, empno;
+
+SELECT DISTINCT ON ("DEPTNO") "EMP"."EMPNO", "EMP"."ENAME", "EMP"."DEPTNO"
+FROM "scott"."EMP" AS "EMP"
+ORDER BY "DEPTNO", "EMPNO"
+!explain-validated-on all
+
# End select.iq
diff --git a/core/src/main/codegen/config.fmpp
b/core/src/main/codegen/config.fmpp
index 73d981bf39..d8286c4ce1 100644
--- a/core/src/main/codegen/config.fmpp
+++ b/core/src/main/codegen/config.fmpp
@@ -40,8 +40,9 @@ data: {
# FMPP will use the declaration from default_config.fmpp.
parser: {
# Generated parser implementation package and class name.
- package: "org.apache.calcite.sql.parser.impl",
- class: "SqlParserImpl",
+ package: "org.apache.calcite.sql.parser.impl"
+ class: "SqlParserImpl"
+ includeDistinctOn: true
# List of files in @includes directory that have parser method
# implementations for parsing custom SQL statements, literals or types
diff --git a/core/src/main/codegen/default_config.fmpp
b/core/src/main/codegen/default_config.fmpp
index a2547273cb..9cb0542337 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -460,4 +460,6 @@ parser: {
includeAdditionalDeclarations: false
includeParsingStringLiteralAsArrayLiteral: false
includeIntervalWithoutQualifier: false
+ includeStarExclude: false
+ includeDistinctOn: false
}
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 4bbf49840c..3b2209538e 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -1023,6 +1023,24 @@ List<SqlNode> FunctionParameterList(ExprContext
exprContext) :
}
}
+void AllOrDistinctOrDistinctOn(List<SqlLiteral> keywords, List<SqlNode>
distinctOnList) :
+{
+ final Span s;
+ final SqlNodeList distinctOn;
+}
+{
+ <ALL> { keywords.add(SqlSelectKeyword.ALL.symbol(getPos())); }
+|
+ <DISTINCT> { s = span(); }
+ (
+ <ON> <LPAREN>
+ distinctOn = ExpressionCommaList(s, ExprContext.ACCEPT_SUB_QUERY)
+ <RPAREN>
+ { distinctOnList.addAll(distinctOn.getList()); }
+ )?
+ { keywords.add(SqlSelectKeyword.DISTINCT.symbol(s.end(this))); }
+}
+
SqlLiteral AllOrDistinct() :
{
}
@@ -1389,6 +1407,7 @@ SqlSelect SqlSelect() :
final SqlNode qualify;
final SqlNodeList by;
final List<SqlNode> hints = new ArrayList<SqlNode>();
+ final List<SqlNode> distinctOnList = new ArrayList<SqlNode>();
final Span s;
}
{
@@ -1400,9 +1419,15 @@ SqlSelect SqlSelect() :
keywords.add(SqlSelectKeyword.STREAM.symbol(getPos()));
}
)?
+<#if parser.includeDistinctOn!default.parser.includeDistinctOn>
+ (
+ AllOrDistinctOrDistinctOn(keywords, distinctOnList)
+ )?
+<#else>
(
keyword = AllOrDistinct() { keywords.add(keyword); }
)?
+</#if>
{
keywordList = new SqlNodeList(keywords, s.addAll(keywords).pos());
}
@@ -1431,10 +1456,14 @@ SqlSelect SqlSelect() :
}
)
{
+ final SqlNodeList distinctOn = distinctOnList.isEmpty()
+ ? null
+ : new SqlNodeList(distinctOnList, Span.of(distinctOnList).pos());
final SqlSelect select = new SqlSelect(s.end(this), keywordList,
new SqlNodeList(selectList, Span.of(selectList).pos()),
fromClause, where, groupBy, having, windowDecls, qualify,
- null, null, null, new SqlNodeList(hints, getPos()));
+ null, null, null, new SqlNodeList(hints, getPos()),
+ distinctOn);
SqlByRewriter.rewrite(select, by);
return select;
}
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 13745bf0df..171a8da68a 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -508,6 +508,15 @@ ExInst<SqlValidatorException>
intervalFieldExceedsPrecision(Number a0,
@BaseMessage("QUALIFY expression ''{0}'' must contain a window function")
ExInst<SqlValidatorException>
qualifyExpressionMustContainWindowFunction(String a0);
+ @BaseMessage("SELECT DISTINCT ON is not supported under the current SQL
conformance level")
+ ExInst<SqlValidatorException> distinctOnNotAllowed();
+
+ @BaseMessage("SELECT DISTINCT ON requires an ORDER BY clause")
+ ExInst<SqlValidatorException> distinctOnRequiresOrderBy();
+
+ @BaseMessage("SELECT DISTINCT ON expressions must match ORDER BY
expressions")
+ ExInst<SqlValidatorException> distinctOnOrderByMismatch();
+
@BaseMessage("ROW/RANGE not allowed with RANK, DENSE_RANK, ROW_NUMBER,
PERCENTILE_CONT/DISC or LAG/LEAD functions")
ExInst<SqlValidatorException> rankWithFrame();
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
index 0faa1d024f..ad542c31a0 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
@@ -43,6 +43,7 @@ public class SqlSelect extends SqlCall {
public static final int WHERE_OPERAND = 3;
public static final int HAVING_OPERAND = 5;
public static final int QUALIFY_OPERAND = 7;
+ public static final int DISTINCT_ON_OPERAND = 12;
SqlNodeList keywordList;
SqlNodeList selectList;
@@ -56,6 +57,7 @@ public class SqlSelect extends SqlCall {
@Nullable SqlNode offset;
@Nullable SqlNode fetch;
@Nullable SqlNodeList hints;
+ @Nullable SqlNodeList distinctOn;
boolean hasByClause;
//~ Constructors -----------------------------------------------------------
@@ -72,7 +74,8 @@ public SqlSelect(SqlParserPos pos,
@Nullable SqlNodeList orderBy,
@Nullable SqlNode offset,
@Nullable SqlNode fetch,
- @Nullable SqlNodeList hints) {
+ @Nullable SqlNodeList hints,
+ @Nullable SqlNodeList distinctOn) {
super(pos);
this.keywordList = requireNonNull(keywordList != null
? keywordList : new SqlNodeList(pos));
@@ -88,10 +91,29 @@ public SqlSelect(SqlParserPos pos,
this.offset = offset;
this.fetch = fetch;
this.hints = hints;
+ this.distinctOn = distinctOn;
this.hasByClause = false;
}
- /** deprecated, without {@code qualify}. */
+ /** Constructor without {@code distinctOn}; distinctOn defaults to null. */
+ public SqlSelect(SqlParserPos pos,
+ @Nullable SqlNodeList keywordList,
+ SqlNodeList selectList,
+ @Nullable SqlNode from,
+ @Nullable SqlNode where,
+ @Nullable SqlNodeList groupBy,
+ @Nullable SqlNode having,
+ @Nullable SqlNodeList windowDecls,
+ @Nullable SqlNode qualify,
+ @Nullable SqlNodeList orderBy,
+ @Nullable SqlNode offset,
+ @Nullable SqlNode fetch,
+ @Nullable SqlNodeList hints) {
+ this(pos, keywordList, selectList, from, where, groupBy, having,
+ windowDecls, qualify, orderBy, offset, fetch, hints, null);
+ }
+
+ /** deprecated, without {@code qualify} and {@code distinctOn}. */
@Deprecated // to be removed before 2.0
public SqlSelect(SqlParserPos pos,
@Nullable SqlNodeList keywordList,
@@ -106,7 +128,7 @@ public SqlSelect(SqlParserPos pos,
@Nullable SqlNode fetch,
@Nullable SqlNodeList hints) {
this(pos, keywordList, selectList, from, where, groupBy, having,
- windowDecls, null, orderBy, offset, fetch, hints);
+ windowDecls, null, orderBy, offset, fetch, hints, null);
}
//~ Methods ----------------------------------------------------------------
@@ -122,7 +144,8 @@ public SqlSelect(SqlParserPos pos,
@SuppressWarnings("nullness")
@Override public List<SqlNode> getOperandList() {
return ImmutableNullableList.of(keywordList, selectList, from, where,
- groupBy, having, windowDecls, qualify, orderBy, offset, fetch, hints);
+ groupBy, having, windowDecls, qualify, orderBy, offset, fetch, hints,
+ distinctOn);
}
@Override public void setOperand(int i, @Nullable SqlNode operand) {
@@ -160,12 +183,23 @@ public SqlSelect(SqlParserPos pos,
case 10:
fetch = operand;
break;
+ case 11:
+ hints = (SqlNodeList) operand;
+ break;
+ case 12:
+ distinctOn = (SqlNodeList) operand;
+ break;
default:
throw new AssertionError(i);
}
}
public final boolean isDistinct() {
+ // DISTINCT ON is mutually exclusive with DISTINCT, so when DISTINCT ON is
present,
+ // we return false to indicate that standard DISTINCT processing is not
needed
+ if (isDistinctOn()) {
+ return false;
+ }
return getModifierNode(SqlSelectKeyword.DISTINCT) != null;
}
@@ -273,10 +307,6 @@ public void setFetch(@Nullable SqlNode fetch) {
this.fetch = fetch;
}
- public void setHints(@Nullable SqlNodeList hints) {
- this.hints = hints;
- }
-
@Pure
public @Nullable SqlNodeList getHints() {
return this.hints;
@@ -327,4 +357,12 @@ public boolean hasWhere() {
public boolean isKeywordPresent(SqlSelectKeyword targetKeyWord) {
return getModifierNode(targetKeyWord) != null;
}
+
+ public boolean isDistinctOn() {
+ return distinctOn != null && !distinctOn.isEmpty();
+ }
+
+ public @Nullable SqlNodeList getDistinctOn() {
+ return distinctOn;
+ }
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
index fea1c2235f..1c1a8edfe8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
@@ -78,7 +78,8 @@ private SqlSelectOperator() {
(SqlNodeList) operands[8],
operands[9],
operands[10],
- (SqlNodeList) operands[11]);
+ (SqlNodeList) operands[11],
+ operands.length > 12 ? (SqlNodeList) operands[12] : null);
}
/**
@@ -114,7 +115,8 @@ public SqlSelect createCall(
orderBy,
offset,
fetch,
- hints);
+ hints,
+ null);
}
@Override public <R> void acceptCall(
@@ -150,6 +152,13 @@ public SqlSelect createCall(
final SqlNode keyword = select.keywordList.get(i);
keyword.unparse(writer, 0, 0);
}
+ if (select.isDistinctOn()) {
+ writer.keyword("ON");
+ final SqlWriter.Frame frame =
+ writer.startList("(", ")");
+ castNonNull(select.distinctOn).unparse(writer, 0, 0);
+ writer.endList(frame);
+ }
writer.topN(select.fetch, select.offset);
final SqlNodeList selectClause = select.selectList;
writer.list(SqlWriter.FrameTypeEnum.SELECT_LIST, SqlWriter.COMMA,
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index b19d8c071d..82f06a48d1 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -172,4 +172,8 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
@Override public boolean supportsUnsignedTypes() {
return SqlConformanceEnum.DEFAULT.supportsUnsignedTypes();
}
+
+ @Override public boolean isDistinctOnAllowed() {
+ return SqlConformanceEnum.DEFAULT.isDistinctOnAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index fcd3b46641..32b4a03b90 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -681,4 +681,14 @@ default boolean isColonFieldAccessAllowed() {
* True when the unsigned versions of integer types are supported.
*/
boolean supportsUnsignedTypes();
+
+ /**
+ * Whether {@code SELECT DISTINCT ON} is allowed.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BABEL},
+ * {@link SqlConformanceEnum#LENIENT};
+ * false otherwise.
+ */
+ boolean isDistinctOnAllowed();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index f0f258171b..4475c4ce80 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -532,4 +532,14 @@ public enum SqlConformanceEnum implements SqlConformance {
return false;
}
}
+
+ @Override public boolean isDistinctOnAllowed() {
+ switch (this) {
+ case BABEL:
+ case LENIENT:
+ return true;
+ default:
+ return false;
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index 25f8d7e037..0d415d8aec 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -177,4 +177,8 @@ protected SqlDelegatingConformance(SqlConformance delegate)
{
@Override public boolean supportsUnsignedTypes() {
return delegate.supportsUnsignedTypes();
}
+
+ @Override public boolean isDistinctOnAllowed() {
+ return delegate.isDistinctOnAllowed();
+ }
}
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 1e2b298808..a41be8c67b 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
@@ -3267,8 +3267,10 @@ private void registerQuery(
if (orderList != null) {
// If the query is 'SELECT DISTINCT', restrict the columns
// available to the ORDER BY clause.
+ // DISTINCT ON is an exception: ORDER BY may reference columns
+ // not in the SELECT list.
final SqlValidatorScope selectScope3 =
- select.isDistinct()
+ (select.isDistinct() && !select.isDistinctOn())
? new AggregatingSelectScope(selectScope, select, true)
: selectScope2;
OrderByScope orderScope =
@@ -4463,6 +4465,7 @@ protected void validateSelect(
// dialects you can refer to columns of the select list, e.g.
// "SELECT empno AS x FROM emp ORDER BY x"
validateOrderList(select);
+ validateDistinctOnClause(select);
if (shouldCheckForRollUp(from)) {
checkRollUpInSelectList(select);
@@ -4997,6 +5000,54 @@ protected void validateQualifyClause(SqlSelect select) {
}
}
+ protected void validateDistinctOnClause(SqlSelect select) {
+ SqlNodeList distinctOn = select.getDistinctOn();
+ if (distinctOn == null || distinctOn.isEmpty()) {
+ return;
+ }
+
+ if (!config.conformance().isDistinctOnAllowed()) {
+ throw newValidationError(select, RESOURCE.distinctOnNotAllowed());
+ }
+
+ SqlNodeList orderList = select.getOrderList();
+ if (orderList == null || orderList.isEmpty()) {
+ throw newValidationError(select, RESOURCE.distinctOnRequiresOrderBy());
+ }
+
+ if (orderList.size() < distinctOn.size()) {
+ throw newValidationError(orderList.get(orderList.size() - 1),
+ RESOURCE.distinctOnOrderByMismatch());
+ }
+
+ final SqlValidatorScope orderScope = getOrderScope(select);
+ for (int i = 0; i < distinctOn.size(); i++) {
+ SqlNode distinctOnExpr = expand(distinctOn.get(i), orderScope);
+ SqlNode orderItem = orderList.get(i);
+ SqlNode orderExpr = stripOrderByModifiers(orderItem);
+ orderExpr = expand(orderExpr, orderScope);
+ if (!SqlNode.equalDeep(distinctOnExpr, orderExpr, Litmus.IGNORE)) {
+ throw newValidationError(orderItem,
RESOURCE.distinctOnOrderByMismatch());
+ }
+ }
+ }
+
+ /** Strips ASC, DESC, NULLS FIRST, NULLS LAST from an ORDER BY item. */
+ private static SqlNode stripOrderByModifiers(SqlNode orderExpr) {
+ while (orderExpr instanceof SqlCall) {
+ SqlCall call = (SqlCall) orderExpr;
+ SqlKind kind = call.getKind();
+ if (kind == SqlKind.DESCENDING
+ || kind == SqlKind.NULLS_FIRST
+ || kind == SqlKind.NULLS_LAST) {
+ orderExpr = call.operand(0);
+ } else {
+ break;
+ }
+ }
+ return orderExpr;
+ }
+
protected void validateMustFilterRequirements(SqlSelect select,
SelectNamespace ns) {
ns.filterRequirement = FilterRequirement.EMPTY;
if (select.getFrom() != null) {
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 53d54dd6d2..1551d1630d 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -720,6 +720,9 @@ private static RelCollation requiredCollation(RelNode r) {
if (r instanceof Project) {
return requiredCollation(((Project) r).getInput());
}
+ if (r instanceof Filter) {
+ return requiredCollation(((Filter) r).getInput());
+ }
if (r instanceof Delta) {
return requiredCollation(((Delta) r).getInput());
}
@@ -816,9 +819,13 @@ protected void convertSelectImpl(
distinctify(bb, true);
}
- convertOrder(
- select, bb, collation, orderExprList, select.getOffset(),
- select.getFetch());
+ if (select.isDistinctOn()) {
+ convertOrder(select, bb, collation, orderExprList, null, null);
+ } else {
+ convertOrder(
+ select, bb, collation, orderExprList, select.getOffset(),
+ select.getFetch());
+ }
if (select.hasHints()) {
final List<RelHint> hints = SqlUtil.getRelHint(hintStrategies,
select.getHints());
@@ -839,6 +846,20 @@ protected void convertSelectImpl(
} else {
bb.setRoot(bb.root(), true);
}
+
+ if (select.isDistinctOn()) {
+ convertDistinctOn(bb, select, collationList);
+ final @Nullable RexNode offsetExpr = select.getOffset() == null
+ ? null : convertExpression(select.getOffset());
+ final @Nullable RexNode fetchExpr = select.getFetch() == null
+ ? null : convertExpression(select.getFetch());
+ if (offsetExpr != null || fetchExpr != null) {
+ bb.setRoot(
+ LogicalSort.create(bb.root(), RelCollations.EMPTY,
+ offsetExpr, fetchExpr),
+ false);
+ }
+ }
}
/**
@@ -1021,6 +1042,77 @@ private void distinctify(
rel.getRowType().getFieldNames(), ImmutableSet.of()), false);
}
+ /**
+ * Converts a SELECT DISTINCT ON clause into a relational expression
+ * using ROW_NUMBER() window function.
+ */
+ private void convertDistinctOn(Blackboard bb, SqlSelect select,
+ List<RelFieldCollation> collationList) {
+ if (bb.root == null) {
+ throw new IllegalArgumentException("rel must not be null");
+ }
+ final SqlNodeList distinctOn = requireNonNull(select.getDistinctOn(),
"distinctOn");
+
+ relBuilder.push(bb.root());
+ final RelDataType inputRowType = bb.root().getRowType();
+
+ // Build PARTITION BY expressions from DISTINCT ON.
+ // DISTINCT ON expressions are a prefix of ORDER BY,
+ // so we can use the field indices from collationList.
+ final List<RexNode> partitionKeys = new ArrayList<>();
+ for (int i = 0; i < distinctOn.size(); i++) {
+ RelFieldCollation fieldCollation = collationList.get(i);
+ partitionKeys.add(
+ rexBuilder.makeInputRef(inputRowType,
fieldCollation.getFieldIndex()));
+ }
+
+ // Build ORDER BY expressions for the window function
+ final List<RexFieldCollation> orderKeys = new ArrayList<>();
+ for (RelFieldCollation fieldCollation : collationList) {
+ RexNode ref = rexBuilder.makeInputRef(inputRowType,
fieldCollation.getFieldIndex());
+ final Set<SqlKind> kinds = new HashSet<>();
+ if (fieldCollation.getDirection() ==
RelFieldCollation.Direction.DESCENDING) {
+ kinds.add(SqlKind.DESCENDING);
+ }
+ switch (fieldCollation.nullDirection) {
+ case FIRST:
+ kinds.add(SqlKind.NULLS_FIRST);
+ break;
+ case LAST:
+ kinds.add(SqlKind.NULLS_LAST);
+ break;
+ default:
+ break;
+ }
+ orderKeys.add(new RexFieldCollation(ref, kinds));
+ }
+
+ final RelDataType bigintType =
+ typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RexNode rowNumber =
+ rexBuilder.makeOver(bigintType, SqlStdOperatorTable.ROW_NUMBER,
ImmutableList.of(),
+ partitionKeys, ImmutableList.copyOf(orderKeys),
+ RexWindowBounds.UNBOUNDED_PRECEDING, RexWindowBounds.CURRENT_ROW,
+ RexWindowExclusion.EXCLUDE_NO_OTHER, true, true, false, false, false);
+
+ // Add ROW_NUMBER as the last column
+ final List<RexNode> fields = new ArrayList<>(relBuilder.fields());
+ fields.add(rowNumber);
+ relBuilder.project(fields);
+
+ // Filter rn = 1
+ relBuilder.filter(
+ relBuilder.equals(
+ Util.last(relBuilder.fields()),
+ relBuilder.literal(BigDecimal.ONE)));
+
+ // Remove the ROW_NUMBER column
+ relBuilder.project(
+ Util.skipLast(relBuilder.fields()));
+
+ bb.setRoot(relBuilder.build(), false);
+ }
+
/**
* Converts a query's ORDER BY clause, if any.
*
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 325b69d872..4096537b7f 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -171,6 +171,9 @@ FollowingBeforePrecedingError=Upper frame boundary cannot
be PRECEDING when lowe
WindowNameMustBeSimple=Window name must be a simple identifier
DuplicateWindowName=Duplicate window names not allowed
EmptyWindowSpec=Empty window specification not allowed
+DistinctOnNotAllowed=SELECT DISTINCT ON is not supported under the current SQL
conformance level
+DistinctOnRequiresOrderBy=SELECT DISTINCT ON requires an ORDER BY clause
+DistinctOnOrderByMismatch=SELECT DISTINCT ON expressions must match ORDER BY
expressions
DupWindowSpec=Duplicate window specification not allowed in the same window
clause
QualifyExpressionMustContainWindowFunction=QUALIFY expression ''{0}'' must
contain a window function
RankWithFrame=ROW/RANGE not allowed with RANK, DENSE_RANK, ROW_NUMBER,
PERCENTILE_CONT/DISC or LAG/LEAD functions
diff --git a/core/src/test/codegen/config.fmpp
b/core/src/test/codegen/config.fmpp
index 99ecb93da9..3f98500191 100644
--- a/core/src/test/codegen/config.fmpp
+++ b/core/src/test/codegen/config.fmpp
@@ -69,6 +69,7 @@ data: {
implementationFiles: [
"parserImpls.ftl"
]
+ includeDistinctOn: true
}
}
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 218f018fb6..c9f409f8c3 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -6239,4 +6239,35 @@ void checkUserDefinedOrderByOver(NullCollation
nullCollation) {
assertThat(plan, not(containsString("FLOOR(FLOOR")));
assertThat(plan, containsString("FLOOR($4, FLAG(WEEK))"));
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnSimple() {
+ final String sql = "SELECT DISTINCT ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, empno";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnMultiple() {
+ final String sql = "SELECT DISTINCT ON (deptno, job) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, job, hiredate DESC";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnWithLimit() {
+ final String sql = "SELECT DISTINCT ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, empno\n"
+ + "LIMIT 5";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
}
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 4b4d2302de..c4e8993839 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6817,7 +6817,130 @@ void testReturnsCorrectRowTypeOnCombinedJoin() {
f.withSql(qualifyOnMultipleWindowFunctions).ok();
}
- /** Negative tests for the {@code QUALIFY} clause. */
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnNotAllowed() {
+ // DISTINCT ON is not allowed under default SQL conformance
+ sql("^SELECT DISTINCT ON (deptno) empno FROM emp^ ORDER BY deptno")
+ .fails("SELECT DISTINCT ON is not supported under the current SQL
conformance level");
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnPositive() {
+ final SqlValidatorFixture f =
+ fixture().withConformance(SqlConformanceEnum.LENIENT);
+
+ f.withSql("SELECT DISTINCT ON (deptno) empno, ename FROM emp ORDER BY
deptno, empno")
+ .ok();
+
+ f.withSql("SELECT DISTINCT ON (deptno, job) empno FROM emp ORDER BY
deptno, job, hiredate")
+ .ok();
+
+ f.withSql("SELECT DISTINCT ON (deptno) empno FROM emp ORDER BY deptno
DESC")
+ .ok();
+
+ f.withSql("SELECT DISTINCT ON (deptno) empno FROM emp ORDER BY deptno
NULLS FIRST")
+ .ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnNegative() {
+ final SqlValidatorFixture f =
+ fixture().withConformance(SqlConformanceEnum.LENIENT);
+
+ // DISTINCT ON requires ORDER BY
+ f.withSql("^SELECT DISTINCT ON (deptno) empno FROM emp^")
+ .fails("SELECT DISTINCT ON requires an ORDER BY clause");
+
+ // ORDER BY must contain all DISTINCT ON expressions as prefix
+ f.withSql("SELECT DISTINCT ON (deptno, job) empno FROM emp ORDER BY
^deptno^")
+ .fails("SELECT DISTINCT ON expressions must match ORDER BY
expressions");
+
+ // ORDER BY prefix must match exactly
+ f.withSql("SELECT DISTINCT ON (deptno, job) empno FROM emp ORDER BY ^job^,
deptno")
+ .fails("SELECT DISTINCT ON expressions must match ORDER BY
expressions");
+
+ // DISTINCT ON with extra ORDER BY is ok
+ f.withSql("SELECT DISTINCT ON (deptno) empno FROM emp ORDER BY deptno,
job")
+ .ok();
+
+ // Duplicate expressions in DISTINCT ON are allowed but must be matched in
ORDER BY
+ f.withSql("SELECT DISTINCT ON (deptno, deptno) deptno, empno FROM emp
ORDER BY deptno, deptno")
+ .ok();
+
+ // DISTINCT ON with expression
+ f.withSql("SELECT DISTINCT ON (empno % 2) empno, ename FROM emp ORDER BY
empno % 2")
+ .ok();
+
+ // Empty DISTINCT ON is not allowed (parse error)
+ f.withSql("SELECT DISTINCT ON ((^)^) deptno FROM emp")
+ .fails("(?s)Encountered \"\\)\" at .*");
+
+ // DISTINCT ON can reference alias (like ORDER BY)
+ f.withSql("SELECT DISTINCT ON (x) empno AS x, deptno FROM emp ORDER BY x")
+ .ok();
+
+ // DISTINCT ON with alias-column name clash: alias in SELECT takes
precedence
+ f.withSql("SELECT DISTINCT ON (deptno) empno AS deptno, deptno AS d FROM
emp ORDER BY deptno")
+ .ok();
+
+ // DISTINCT ON with qualified column reference
+ f.withSql("SELECT DISTINCT ON (e.deptno) e.deptno AS x, e.empno "
+ + "FROM emp AS e ORDER BY e.deptno")
+ .ok();
+
+ // Integer literal in both DISTINCT ON and ORDER BY matches at validator
+ // (ordinal resolution happens later in SqlToRelConverter)
+ f.withSql("SELECT DISTINCT ON (2) empno, ename FROM emp ORDER BY 2")
+ .ok();
+
+ // Expressions in DISTINCT ON (not ordinals)
+ f.withSql("SELECT DISTINCT ON (empno % 2, CHAR_LENGTH(ename)) empno, ename
"
+ + "FROM emp ORDER BY empno % 2, CHAR_LENGTH(ename)")
+ .ok();
+
+ // DISTINCT ON referencing non-projected column requires ORDER BY
+ f.withSql("^SELECT DISTINCT ON (deptno) empno, ename FROM emp^")
+ .fails("SELECT DISTINCT ON requires an ORDER BY clause");
+
+ // DISTINCT ON with aggregate query
+ f.withSql("SELECT DISTINCT ON (deptno) deptno, SUM(sal) "
+ + "FROM emp GROUP BY deptno ORDER BY deptno")
+ .ok();
+
+ // DISTINCT ON with aggregate expression
+ f.withSql("SELECT DISTINCT ON (SUM(sal)) deptno, SUM(sal) "
+ + "FROM emp GROUP BY deptno ORDER BY SUM(sal)")
+ .ok();
+
+ // DISTINCT ON with aggregate query and alias
+ f.withSql("SELECT DISTINCT ON (sum_sal) deptno, SUM(sal) AS sum_sal "
+ + "FROM emp GROUP BY deptno ORDER BY sum_sal")
+ .ok();
+
+ // DISTINCT ON with USING join (requires qualified reference due to
Calcite limitation)
+ f.withSql("SELECT DISTINCT ON (emp.deptno) * "
+ + "FROM emp JOIN dept USING (deptno) ORDER BY emp.deptno")
+ .ok();
+
+ // DISTINCT ON with NATURAL join (requires qualified reference due to
Calcite limitation)
+ f.withSql("SELECT DISTINCT ON (emp.deptno) * FROM emp NATURAL JOIN dept
ORDER BY emp.deptno")
+ .ok();
+
+ // DISTINCT ON with unqualified column reference - should work if column
is unambiguous
+ f.withSql("SELECT DISTINCT ON (deptno) empno, deptno FROM emp ORDER BY
deptno, empno")
+ .ok();
+
+ // DISTINCT ON with unqualified column in ORDER BY - should match
+ f.withSql("SELECT DISTINCT ON (deptno) empno, deptno FROM emp ORDER BY
deptno")
+ .ok();
+ }
+
@Test void testQualifyNegative() {
final SqlValidatorFixture f =
fixture().withConformance(SqlConformanceEnum.LENIENT);
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 6900b06ff8..1d05fd1b2f 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1991,6 +1991,62 @@ LogicalTableModify(table=[[CATALOG, SALES, EMP]],
operation=[DELETE], flattened=
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnMultiple">
+ <Resource name="sql">
+ <![CDATA[SELECT DISTINCT ON (deptno, job) empno, ename
+FROM emp
+ORDER BY deptno, job, hiredate DESC]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], JOB=[$3], HIREDATE=[$4])
+ LogicalFilter(condition=[=($5, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], JOB=[$3],
HIREDATE=[$4], $f5=[ROW_NUMBER() OVER (PARTITION BY $2, $3 ORDER BY $2 NULLS
LAST, $3 NULLS LAST, $4 DESC NULLS FIRST)])
+ LogicalSort(sort0=[$2], sort1=[$3], sort2=[$4], dir0=[ASC],
dir1=[ASC], dir2=[DESC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], JOB=[$2],
HIREDATE=[$4])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnSimple">
+ <Resource name="sql">
+ <![CDATA[SELECT DISTINCT ON (deptno) empno, ename
+FROM emp
+ORDER BY deptno, empno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[=($3, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], $f3=[ROW_NUMBER()
OVER (PARTITION BY $2 ORDER BY $2 NULLS LAST, $0 NULLS LAST)])
+ LogicalSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnWithLimit">
+ <Resource name="sql">
+ <![CDATA[SELECT DISTINCT ON (deptno) empno, ename
+FROM emp
+ORDER BY deptno, empno
+LIMIT 5]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalSort(fetch=[5])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[=($3, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], $f3=[ROW_NUMBER()
OVER (PARTITION BY $2 ORDER BY $2 NULLS LAST, $0 NULLS LAST)])
+ LogicalSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/server/src/main/codegen/config.fmpp
b/server/src/main/codegen/config.fmpp
index e5c3eaf4d2..ba64f0b71a 100644
--- a/server/src/main/codegen/config.fmpp
+++ b/server/src/main/codegen/config.fmpp
@@ -99,6 +99,7 @@ data: {
implementationFiles: [
"parserImpls.ftl"
]
+ includeDistinctOn: true
}
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 80337bbfb1..b6ecb6b52b 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -205,7 +205,7 @@ ## Grammar
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
select:
- SELECT [ hintComment ] [ STREAM ] [ ALL | DISTINCT ]
+ SELECT [ hintComment ] [ STREAM ] [ ALL | DISTINCT [ ON '(' expression
[, expression ]* ')' ] ]
{ starWithExclude | projectItem [, projectItem ]* }
[ BY expression [, expression ]* ]
FROM tableExpression
@@ -214,6 +214,8 @@ ## Grammar
[ HAVING booleanExpression ]
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
[ QUALIFY booleanExpression ]
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ LIMIT expression [ OFFSET expression ] ]
The optional, non-standard `BY` clause groups and orders the query by
the specified expressions, and automatically adds them to the SELECT list
@@ -221,6 +223,13 @@ ## Grammar
with an explicit `GROUP BY` or `ORDER BY` clause in the same query.
`SELECT ... BY` is recognized only when the Babel parser is enabled. It sets
the generated parser configuration flag `includeSelectBy` to `true`.
+The optional `DISTINCT ON` clause is a PostgreSQL extension that allows you to
+eliminate duplicate rows based on specified expressions, keeping the first row
+in each group as determined by the `ORDER BY` clause. This is recognized only
+when the Babel parser is enabled. It sets the generated parser configuration
flag
+`includeDistinctOn` to `true`. When using `DISTINCT ON`, the expressions in the
+`DISTINCT ON` clause must match the beginning of the `ORDER BY` clause.
+
For example:
{% highlight sql %}
@@ -236,6 +245,16 @@ ## Grammar
ORDER BY deptno
{% endhighlight %}
+Example of `DISTINCT ON`:
+
+{% highlight sql %}
+SELECT DISTINCT ON (deptno) empno, ename, deptno
+FROM emp
+ORDER BY deptno, empno
+{% endhighlight %}
+
+This query keeps only the first employee (ordered by empno) in each department.
+
selectWithoutFrom:
SELECT [ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
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 5c24c0a806..0adc5be12a 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
@@ -6230,6 +6230,104 @@ private static Matcher<SqlNode> isCharLiteral(String s)
{
sql(sql).fails("(?s).*Encountered \"QUALIFY\" at .*");
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOn() {
+ final String sql = "SELECT DISTINCT ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, empno";
+
+ final String expected = "SELECT DISTINCT ON (`DEPTNO`) `EMPNO`, `ENAME`\n"
+ + "FROM `EMP`\n"
+ + "ORDER BY `DEPTNO`, `EMPNO`";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnMultiple() {
+ final String sql = "SELECT DISTINCT ON (deptno, job) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, job, hiredate DESC";
+
+ final String expected = "SELECT DISTINCT ON (`DEPTNO`, `JOB`) `EMPNO`,
`ENAME`\n"
+ + "FROM `EMP`\n"
+ + "ORDER BY `DEPTNO`, `JOB`, `HIREDATE` DESC";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnWithEverything() {
+ final String sql = "SELECT DISTINCT ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "WHERE deptno > 3\n"
+ + "GROUP BY deptno, empno, ename\n"
+ + "HAVING COUNT(*) > 1\n"
+ + "ORDER BY deptno, empno\n"
+ + "LIMIT 5\n";
+
+ final String expected = "SELECT DISTINCT ON (`DEPTNO`) `EMPNO`, `ENAME`\n"
+ + "FROM `EMP`\n"
+ + "WHERE (`DEPTNO` > 3)\n"
+ + "GROUP BY `DEPTNO`, `EMPNO`, `ENAME`\n"
+ + "HAVING (COUNT(*) > 1)\n"
+ + "ORDER BY `DEPTNO`, `EMPNO`\n"
+ + "FETCH NEXT 5 ROWS ONLY";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnWithOffset() {
+ final String sql = "SELECT DISTINCT ON (deptno) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno, empno\n"
+ + "OFFSET 10 ROWS\n"
+ + "FETCH NEXT 5 ROWS ONLY";
+
+ final String expected = "SELECT DISTINCT ON (`DEPTNO`) `EMPNO`, `ENAME`\n"
+ + "FROM `EMP`\n"
+ + "ORDER BY `DEPTNO`, `EMPNO`\n"
+ + "OFFSET 10 ROWS\n"
+ + "FETCH NEXT 5 ROWS ONLY";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnWithExpression() {
+ final String sql = "SELECT DISTINCT ON (deptno + 1) empno, ename\n"
+ + "FROM emp\n"
+ + "ORDER BY deptno + 1, empno";
+
+ final String expected = "SELECT DISTINCT ON ((`DEPTNO` + 1)) `EMPNO`,
`ENAME`\n"
+ + "FROM `EMP`\n"
+ + "ORDER BY (`DEPTNO` + 1), `EMPNO`";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5406";>[CALCITE-5406]
+ * Support the SELECT DISTINCT ON statement for PostgreSQL dialect</a>. */
+ @Test void testDistinctOnWithJoin() {
+ final String sql = "SELECT DISTINCT ON (e.deptno) e.empno, d.dname\n"
+ + "FROM emp AS e JOIN dept AS d ON e.deptno = d.deptno\n"
+ + "ORDER BY e.deptno, e.empno";
+
+ final String expected = "SELECT DISTINCT ON (`E`.`DEPTNO`) "
+ + "`E`.`EMPNO`, `D`.`DNAME`\n"
+ + "FROM `EMP` AS `E`\n"
+ + "INNER JOIN `DEPT` AS `D` ON (`E`.`DEPTNO` = `D`.`DEPTNO`)\n"
+ + "ORDER BY `E`.`DEPTNO`, `E`.`EMPNO`";
+ sql(sql).ok(expected);
+ }
+
@Test void testNullTreatment() {
sql("select lead(x) respect nulls over (w) from t")
.ok("SELECT (LEAD(`X`) RESPECT NULLS OVER (`W`))\n"
