This is an automated email from the ASF dual-hosted git repository.
silun 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 270ba649cf [CALCITE-7382] The TopDownGeneralDecorrelator returns an
error result when a subquery contains a LIMIT 1
270ba649cf is described below
commit 270ba649cfa80228d3f4f6635a08ab06de504399
Author: Silun Dong <[email protected]>
AuthorDate: Sun Jan 18 17:07:53 2026 +0800
[CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when
a subquery contains a LIMIT 1
---
.../sql2rel/TopDownGeneralDecorrelator.java | 85 +++++++++++-----------
core/src/test/resources/sql/new-decorr.iq | 26 +++++++
2 files changed, 68 insertions(+), 43 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index 10b9a411ff..72401e3426 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -43,6 +43,7 @@
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexWindow;
+import org.apache.calcite.rex.RexWindowBounds;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.fun.SqlCountAggFunction;
@@ -598,50 +599,48 @@ public RelNode unnestInternal(Sort sort, boolean
allowEmptyOutputFromRewrite) {
RelCollation shiftCollation = sort.getCollation().apply(targetMapping);
builder.push(newInput);
- if (!sort.collation.getFieldCollations().isEmpty()
- && (sort.offset != null || sort.fetch != null)) {
- // the Sort with ORDER BY and LIMIT or OFFSET have to be changed during
rewriting because
- // now the limit has to be enforced per value of the outer bindings
instead of globally.
- // It can be rewritten using ROW_NUMBER() window function and filtering
on it,
- // see section 4.4 in paper Improving Unnesting of Complex Queries
- List<RexNode> partitionKeys = new ArrayList<>();
- for (CorDef corDef : corDefs) {
- int partitionKeyIndex =
requireNonNull(inputInfo.corDefOutputs.get(corDef));
- partitionKeys.add(builder.field(partitionKeyIndex));
- }
- RexNode rowNumber = builder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
- .over()
- .partitionBy(partitionKeys)
- .orderBy(builder.fields(shiftCollation))
- .toRex();
- List<RexNode> projectsWithRowNumber = new ArrayList<>(builder.fields());
- projectsWithRowNumber.add(rowNumber);
- builder.project(projectsWithRowNumber);
-
- List<RexNode> conditions = new ArrayList<>();
- if (sort.offset != null) {
- RexNode greaterThenLowerBound =
- builder.call(
- SqlStdOperatorTable.GREATER_THAN,
- builder.field(projectsWithRowNumber.size() - 1),
- sort.offset);
- conditions.add(greaterThenLowerBound);
- }
- if (sort.fetch != null) {
- RexNode upperBound = sort.offset == null
- ? sort.fetch
- : builder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
- RexNode lessThenOrEqualUpperBound =
- builder.call(
- SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
- builder.field(projectsWithRowNumber.size() - 1),
- upperBound);
- conditions.add(lessThenOrEqualUpperBound);
- }
- builder.filter(conditions);
- } else {
- builder.sortLimit(sort.offset, sort.fetch,
builder.fields(shiftCollation));
+ // the Sort have to be changed during rewriting because now the
order/limit/offset has to be
+ // enforced per value of the outer bindings instead of globally. It can be
rewritten using
+ // ROW_NUMBER() window function and filtering on it, see section 4.4 in
paper
+ // Improving Unnesting of Complex Queries
+ List<RexNode> partitionKeys = new ArrayList<>();
+ for (CorDef corDef : corDefs) {
+ int partitionKeyIndex =
requireNonNull(inputInfo.corDefOutputs.get(corDef));
+ partitionKeys.add(builder.field(partitionKeyIndex));
+ }
+ RexNode rowNumber = builder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
+ .over()
+ .partitionBy(partitionKeys)
+ .orderBy(builder.fields(shiftCollation))
+ .rowsFrom(RexWindowBounds.UNBOUNDED_PRECEDING)
+ .rowsTo(RexWindowBounds.CURRENT_ROW)
+ .toRex();
+ List<RexNode> projectsWithRowNumber = new ArrayList<>(builder.fields());
+ projectsWithRowNumber.add(rowNumber);
+ builder.project(projectsWithRowNumber);
+
+ List<RexNode> conditions = new ArrayList<>();
+ if (sort.offset != null) {
+ RexNode greaterThenLowerBound =
+ builder.call(
+ SqlStdOperatorTable.GREATER_THAN,
+ builder.field(projectsWithRowNumber.size() - 1),
+ sort.offset);
+ conditions.add(greaterThenLowerBound);
}
+ if (sort.fetch != null) {
+ RexNode upperBound = sort.offset == null
+ ? sort.fetch
+ : builder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
+ RexNode lessThenOrEqualUpperBound =
+ builder.call(
+ SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
+ builder.field(projectsWithRowNumber.size() - 1),
+ upperBound);
+ conditions.add(lessThenOrEqualUpperBound);
+ }
+ builder.filter(conditions);
+
RelNode newSort = builder.build();
UnnestedQuery unnestedQuery
= new UnnestedQuery(sort, newSort, inputInfo.corDefOutputs,
inputInfo.oldToNewOutputs);
diff --git a/core/src/test/resources/sql/new-decorr.iq
b/core/src/test/resources/sql/new-decorr.iq
index d329c160ed..4e804687da 100644
--- a/core/src/test/resources/sql/new-decorr.iq
+++ b/core/src/test/resources/sql/new-decorr.iq
@@ -98,4 +98,30 @@ EnumerableSort(sort0=[$0], dir0=[ASC])
!plan
!}
+# [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a
subquery contains a LIMIT 1
+# This case comes from sub-query.iq [CALCITE-6652]
+!use scott
+SELECT dname, (SELECT emp.comm FROM "scott".emp WHERE dept.deptno = emp.deptno
ORDER BY emp.comm LIMIT 1) FROM "scott".dept;
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | 0.00 |
++------------+--------+
+(4 rows)
+
+!ok
+
+SELECT dname, (SELECT empno FROM emp WHERE dept.deptno = emp.deptno LIMIT 1)
FROM dept WHERE deptno = 10;
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | 7782 |
++------------+--------+
+(1 row)
+
+!ok
+
# End new-decorr.iq
