This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 1be8d0b37a [CALCITE-7900] Query with DISTINCT ON crashes
1be8d0b37a is described below
commit 1be8d0b37a0e1139f4dc348a59d1816e85e02798
Author: Mihai Budiu <[email protected]>
AuthorDate: Thu Oct 29 11:40:52 2026 -0700
[CALCITE-7900] Query with DISTINCT ON crashes
Signed-off-by: Mihai Budiu <[email protected]>
---
babel/src/test/resources/sql/select.iq | 54 +++++++++++
.../apache/calcite/sql2rel/SqlToRelConverter.java | 35 +++----
.../apache/calcite/test/SqlToRelConverterTest.java | 49 ++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 101 +++++++++++++++++----
4 files changed, 202 insertions(+), 37 deletions(-)
diff --git a/babel/src/test/resources/sql/select.iq
b/babel/src/test/resources/sql/select.iq
index 73e9c5a541..5512dc515e 100755
--- a/babel/src/test/resources/sql/select.iq
+++ b/babel/src/test/resources/sql/select.iq
@@ -473,4 +473,58 @@ FROM "scott"."EMP" AS "EMP"
ORDER BY "DEPTNO", "EMPNO"
!explain-validated-on all
+# [CALCITE-7900] Query with DISTINCT ON crashes
+# Note: All results in this section have been verified against PostgreSQL and
are identical
+
+# DISTINCT ON in a sub-query
+SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename, deptno
+ FROM emp
+ ORDER BY deptno, empno) AS t
+ORDER BY deptno;
++-------+-------+--------+
+| EMPNO | ENAME | DEPTNO |
++-------+-------+--------+
+| 7782 | CLARK | 10 |
+| 7369 | SMITH | 20 |
+| 7499 | ALLEN | 30 |
++-------+-------+--------+
+(3 rows)
+
+!ok
+
+# DISTINCT ON in a sub-query, ORDER BY columns not in the SELECT list
+SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename
+ FROM emp
+ ORDER BY deptno, sal DESC, empno) AS t
+ORDER BY empno;
++-------+-------+
+| EMPNO | ENAME |
++-------+-------+
+| 7698 | BLAKE |
+| 7788 | SCOTT |
+| 7839 | KING |
++-------+-------+
+(3 rows)
+
+!ok
+
+# DISTINCT ON in a sub-query with LIMIT
+SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename, deptno
+ FROM emp
+ ORDER BY deptno DESC, sal, empno
+ LIMIT 2) AS t
+ORDER BY empno;
++-------+-------+--------+
+| EMPNO | ENAME | DEPTNO |
++-------+-------+--------+
+| 7369 | SMITH | 20 |
+| 7900 | JAMES | 30 |
++-------+-------+--------+
+(2 rows)
+
+!ok
+
# End select.iq
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 7ff2a824a9..49953ced1f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -831,13 +831,13 @@ protected void convertSelectImpl(
}
if (select.isDistinctOn()) {
- convertOrder(select, bb, collation, orderExprList, null, null);
- } else {
- convertOrder(
- select, bb, collation, orderExprList, select.getOffset(),
- select.getFetch());
+ convertDistinctOn(bb, select, collationList);
}
+ convertOrder(
+ select, bb, collation, orderExprList, select.getOffset(),
+ select.getFetch());
+
if (select.hasHints()) {
final List<RelHint> hints = SqlUtil.getRelHint(hintStrategies,
select.getHints());
// Attach the hints to the first Hintable node we found from the root
node.
@@ -857,20 +857,6 @@ 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);
- }
- }
}
/**
@@ -1154,6 +1140,8 @@ protected void convertOrder(
|| (offset instanceof SqlLiteral
&& Objects.equals(((SqlLiteral) offset).bigDecimalValue(),
BigDecimal.ZERO)))
&& fetch == null) {
+ // DISTINCT ON may have added ORDER BY expressions; remove them
+ removeOrderExprs(bb, orderExprList);
return;
}
}
@@ -1165,6 +1153,12 @@ protected void convertOrder(
fetch == null ? null : convertExpression(fetch)),
false);
+ removeOrderExprs(bb, orderExprList);
+ }
+
+ /** Removes the expressions that were added to the project list only to
+ * sort the result. */
+ private void removeOrderExprs(Blackboard bb, List<SqlNode> orderExprList) {
// If extra expressions were added to the project list for sorting,
// add another project to remove them. But make the collation empty,
because
// we can't represent the real collation.
@@ -4304,7 +4298,8 @@ protected void gatherOrderExprs(
return;
}
- if (removeSortInSubQuery(bb.top)) {
+ // DISTINCT ON needs the ORDER BY even if the sort is removed
+ if (removeSortInSubQuery(bb.top) && !select.isDistinctOn()) {
SqlNode offset = select.getOffset();
if ((offset == null
|| (offset instanceof SqlLiteral
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 74da0946d0..06f991c803 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -6973,4 +6973,53 @@ void checkUserDefinedOrderByOver(NullCollation
nullCollation) {
+ "LIMIT 5";
sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7900";>[CALCITE-7900]
+ * Query with DISTINCT ON crashes</a>. The sub-query drops its sort, but
+ * the window that implements DISTINCT ON keeps the ORDER BY keys. */
+ @Test void testDistinctOnInSubQuery() {
+ final String sql = "SELECT * FROM (\n"
+ + " SELECT DISTINCT ON (deptno) empno, ename, deptno\n"
+ + " FROM emp\n"
+ + " ORDER BY deptno, empno) AS t\n"
+ + "ORDER BY deptno";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7900";>[CALCITE-7900]
+ * Query with DISTINCT ON crashes</a>. The ORDER BY keys are not in the
+ * SELECT list, so the sub-query must remove them after DISTINCT ON. */
+ @Test void testDistinctOnInSubQueryOrderByNotInSelect() {
+ final String sql = "SELECT * FROM (\n"
+ + " SELECT DISTINCT ON (deptno) empno, ename\n"
+ + " FROM emp\n"
+ + " ORDER BY deptno, sal DESC) AS t";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** As {@link #testDistinctOnInSubQueryOrderByNotInSelect()}, but the
+ * sub-query keeps its sort. */
+ @Test void testDistinctOnInSubQueryKeepSort() {
+ final String sql = "SELECT * FROM (\n"
+ + " SELECT DISTINCT ON (deptno) empno, ename\n"
+ + " FROM emp\n"
+ + " ORDER BY deptno, sal DESC) AS t";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT)
+ .withConfig(c -> c.withRemoveSortInSubQuery(false)).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7900";>[CALCITE-7900]
+ * Query with DISTINCT ON crashes</a>. The Sort that applies LIMIT must keep
+ * the ORDER BY collation; otherwise it can keep any 2 rows. */
+ @Test void testDistinctOnInSubQueryWithLimit() {
+ final String sql = "SELECT * FROM (\n"
+ + " SELECT DISTINCT ON (deptno) empno, ename\n"
+ + " FROM emp\n"
+ + " ORDER BY deptno DESC, sal\n"
+ + " LIMIT 2) AS t";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
}
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 b793a776ba..22aeace682 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2101,6 +2101,77 @@ 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="testDistinctOnInSubQuery">
+ <Resource name="sql">
+ <![CDATA[SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename, deptno
+ FROM emp
+ ORDER BY deptno, empno) AS t
+ORDER BY deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(sort0=[$2], dir0=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[=($3, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], $f3=[ROW_NUMBER()
OVER (PARTITION BY $7 ORDER BY $7 NULLS LAST, $0 NULLS LAST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnInSubQueryKeepSort">
+ <Resource name="sql">
+ <![CDATA[SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename
+ FROM emp
+ ORDER BY deptno, sal DESC) AS t]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalSort(sort0=[$2], sort1=[$3], dir0=[ASC], dir1=[DESC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], SAL=[$3])
+ LogicalFilter(condition=[=($4, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], SAL=[$5],
$f4=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY $7 NULLS LAST, $5 DESC NULLS
FIRST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnInSubQueryOrderByNotInSelect">
+ <Resource name="sql">
+ <![CDATA[SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename
+ FROM emp
+ ORDER BY deptno, sal DESC) AS t]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalFilter(condition=[=($4, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], SAL=[$5],
$f4=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY $7 NULLS LAST, $5 DESC NULLS
FIRST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctOnInSubQueryWithLimit">
+ <Resource name="sql">
+ <![CDATA[SELECT * FROM (
+ SELECT DISTINCT ON (deptno) empno, ename
+ FROM emp
+ ORDER BY deptno DESC, sal
+ LIMIT 2) AS t]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalSort(sort0=[$2], sort1=[$3], dir0=[DESC], dir1=[ASC], fetch=[2])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], SAL=[$3])
+ LogicalFilter(condition=[=($4, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], SAL=[$5],
$f4=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY $7 DESC NULLS FIRST, $5 NULLS
LAST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2113,12 +2184,11 @@ ORDER BY deptno, job, hiredate DESC]]>
<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]])
+ LogicalSort(sort0=[$2], sort1=[$3], sort2=[$4], dir0=[ASC], dir1=[ASC],
dir2=[DESC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], JOB=[$3],
HIREDATE=[$4])
+ LogicalFilter(condition=[=($5, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], JOB=[$2],
HIREDATE=[$4], $f5=[ROW_NUMBER() OVER (PARTITION BY $7, $2 ORDER BY $7 NULLS
LAST, $2 NULLS LAST, $4 DESC NULLS FIRST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2131,12 +2201,11 @@ ORDER BY deptno, empno]]>
<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]])
+ LogicalSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[=($3, 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], $f3=[ROW_NUMBER()
OVER (PARTITION BY $7 ORDER BY $7 NULLS LAST, $0 NULLS LAST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2150,13 +2219,11 @@ LIMIT 5]]>
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1])
- LogicalSort(fetch=[5])
+ LogicalSort(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC], 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]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7], $f3=[ROW_NUMBER()
OVER (PARTITION BY $7 ORDER BY $7 NULLS LAST, $0 NULLS LAST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
