This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 1e92bbe799 [CALCITE-6942] Support decorrelated for sub-queries with
LIMIT 1 and OFFSET
1e92bbe799 is described below
commit 1e92bbe799252dd47b1bd7133211ad4761e53a06
Author: iwanttobepowerful <[email protected]>
AuthorDate: Tue Dec 16 19:15:11 2025 +0800
[CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 127 +++---
.../calcite/sql2rel/RelDecorrelatorTest.java | 199 ++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 22 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 13 +-
core/src/test/resources/sql/sub-query.iq | 440 ++++++++++++++++++---
site/_docs/history.md | 3 +
6 files changed, 687 insertions(+), 117 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index c5986208d2..ad6d12ca98 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -74,6 +74,7 @@
import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitorImpl;
+import org.apache.calcite.rex.RexWindowBounds;
import org.apache.calcite.runtime.PairList;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlExplainFormat;
@@ -575,16 +576,33 @@ protected RexNode removeCorrelationExpr(
}
if (isCorVarDefined && (rel.fetch != null || rel.offset != null)) {
- if (rel.fetch != null
- && rel.offset == null
- && RexLiteral.intValue(rel.fetch) == 1) {
- return decorrelateFetchOneSort(rel, frame);
- }
- // Can not decorrelate if the sort has per-correlate-key attributes like
- // offset or fetch limit, because these attributes scope would change to
- // global after decorrelation. They should take effect within the scope
- // of the correlation key actually.
- return null;
+ if (rel.offset == null && rel.fetch instanceof RexLiteral) {
+ final RexLiteral fetchLiteral = (RexLiteral) requireNonNull(rel.fetch,
"fetch");
+ final BigDecimal fetch = fetchLiteral.getValueAs(BigDecimal.class);
+ assert fetch != null;
+ if (fetch.equals(BigDecimal.ZERO)) {
+ return null;
+ }
+ }
+
+ //
+ // Rewrite logic:
+ //
+ // For correlated Sort with LIMIT/OFFSET:
+ // Special case: if OFFSET is null and FETCH = 1,
+ // we may rewrite as an Aggregate using MIN/MAX.
+ Frame aggFrame = decorrelateSortAsAggregate(rel, frame);
+ if (aggFrame != null) {
+ return aggFrame;
+ }
+
+ // General case: rewrite as
+ // Project(original_fields..., corVars..., rn)
+ // where rn = ROW_NUMBER() OVER (PARTITION BY corVars ORDER BY
sortExprs
+ // ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
+ // Filter(rn > offset, rn <= offset + fetch)
+ // This preserves per-corVar LIMIT/OFFSET semantics.
+ return decorrelateSortWithRowNumber(rel, frame);
}
final RelNode newInput = frame.r;
@@ -1036,30 +1054,7 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
return null;
}
- protected @Nullable Frame decorrelateFetchOneSort(Sort sort, final Frame
frame) {
- Frame aggFrame = decorrelateSortAsAggregate(sort, frame);
- if (aggFrame != null) {
- return aggFrame;
- }
- //
- // Rewrite logic:
- //
- // If sorted without offset and fetch = 1 (enforced by the caller),
rewrite the sort to be
- // Aggregate(group=(corVar.. , field..))
- // project(first_value(field) over (partition by corVar order by (sort
collation)))
- // input
- //
- // 1. For the original sorted input, apply the FIRST_VALUE window function
to produce
- // the result of sorting with LIMIT 1, and the same as the decorrelate
of aggregate,
- // add correlated variables in partition list to maintain semantic
consistency.
- // 2. To ensure that there is at most one row of output for
- // any combination of correlated variables, distinct for correlated
variables.
- // 3. Since we have partitioned by all correlated variables
- // in the sorted output field window, so for any combination of
correlated variables,
- // all other field values are unique. So the following two are
equivalent:
- // - group by corVar1, covVar2, field1, field2
- // - any_value(fields1), any_value(fields2) group by corVar1, covVar2
- // Here we use the first.
+ protected @Nullable Frame decorrelateSortWithRowNumber(Sort sort, final
Frame frame) {
final Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
@@ -1091,29 +1086,63 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
for (RelDataTypeField field : sort.getRowType().getFieldList()) {
final int newIdx =
requireNonNull(frame.oldToNewOutputs.get(field.getIndex()));
-
- RelBuilder.AggCall aggCall =
- relBuilder.aggregateCall(SqlStdOperatorTable.FIRST_VALUE,
- RexInputRef.of(newIdx, fieldList));
-
- // Convert each field from the sorted output to a window function that
partitions by
- // correlated variables, orders by the collation, and return the
first_value.
- RexNode winCall = aggCall.over()
- .orderBy(sortExprs)
- .partitionBy(corVarProjects.leftList())
- .toRex();
mapOldToNewOutputs.put(newProjExprs.size(), newProjExprs.size());
- newProjExprs.add(winCall, field.getName());
+ newProjExprs.add(RexInputRef.of(newIdx, fieldList), field.getName());
}
newProjExprs.addAll(corVarProjects);
- RelNode result = relBuilder.push(frame.r)
- .project(newProjExprs.leftList(), newProjExprs.rightList())
- .distinct().build();
+ relBuilder.push(frame.r);
+
+ RexNode rowNumberCall =
relBuilder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
+ .over()
+ .partitionBy(corVarProjects.leftList())
+ .orderBy(sortExprs)
+ .let(c -> c.rowsBetween(RexWindowBounds.UNBOUNDED_PRECEDING,
RexWindowBounds.CURRENT_ROW))
+ .toRex();
+ newProjExprs.add(rowNumberCall, "rn"); // Add the row number column
+ relBuilder.project(newProjExprs.leftList(), newProjExprs.rightList());
+
+ List<RexNode> conditions = new ArrayList<>();
+ if (sort.offset != null) {
+ RexNode greaterThenLowerBound =
+ relBuilder.call(
+ SqlStdOperatorTable.GREATER_THAN,
+ relBuilder.field(newProjExprs.size() - 1),
+ sort.offset);
+ conditions.add(greaterThenLowerBound);
+ }
+ if (sort.fetch != null) {
+ RexNode upperBound = sort.offset == null
+ ? sort.fetch
+ : relBuilder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
+ RexNode lessThenOrEqualUpperBound =
+ relBuilder.call(
+ SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
+ relBuilder.field(newProjExprs.size() - 1),
+ upperBound);
+ conditions.add(lessThenOrEqualUpperBound);
+ }
+
+ RelNode result;
+ if (!conditions.isEmpty()) {
+ result = relBuilder.filter(conditions).build();
+ } else {
+ result = relBuilder.build();
+ }
return register(sort, result, mapOldToNewOutputs, corDefOutputs);
}
protected @Nullable Frame decorrelateSortAsAggregate(Sort sort, final Frame
frame) {
+ if (sort.offset != null || sort.fetch == null) {
+ return null;
+ }
+
+ final BigDecimal fetch = ((RexLiteral)
sort.fetch).getValueAs(BigDecimal.class);
+ assert fetch != null;
+ if (!fetch.equals(BigDecimal.ONE)) {
+ return null;
+ }
+
final Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
if (sort.getCollation().getFieldCollations().size() == 1
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
index 80a3dda646..83bf5780cc 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -999,4 +999,203 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalTableScan(table=[[scott, DEPT]])\n";
assertThat(decorrelatedNoRules, hasTree(planDecorrelatedNoRules));
}
+
+ @Test void testDecorrelateCorrelatedOrderByLimitToRowNumber() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = ""
+ + "SELECT dname FROM dept WHERE 2000 > (\n"
+ + "SELECT emp.sal FROM emp where dept.deptno = emp.deptno\n"
+ + "ORDER BY year(hiredate), emp.sal limit 1)";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalFilter(condition=[>(2000.00, CAST($3):DECIMAL(12,
2))])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(SAL=[$0])\n"
+ + " LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC],
dir1=[ASC], fetch=[1])\n"
+ + " LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR),
$4)])\n"
+ + " LogicalFilter(condition=[=($cor0.DEPTNO, $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalJoin(condition=[=($0, $4)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalFilter(condition=[>(2000.00, CAST($0):DECIMAL(12,
2))])\n"
+ + " LogicalProject(SAL=[$0], DEPTNO=[$2])\n"
+ + " LogicalFilter(condition=[<=($3, 1)])\n"
+ + " LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR),
$4)], DEPTNO=[$7], rn=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 NULLS LAST)])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ @Test void testDecorrelateCorrelatedOrderByLimitToRowNumber2() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = ""
+ + "SELECT *\n"
+ + "FROM dept d\n"
+ + "WHERE d.deptno IN (\n"
+ + " SELECT e.deptno\n"
+ + " FROM emp e\n"
+ + " WHERE d.deptno = e.deptno\n"
+ + " LIMIT 10\n"
+ + " OFFSET 2\n"
+ + ")\n"
+ + "LIMIT 2\n"
+ + "OFFSET 1";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalSort(offset=[1], fetch=[2])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalFilter(condition=[=($0, $3)])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalSort(offset=[2], fetch=[10])\n"
+ + " LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[=($cor0.DEPTNO, $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalSort(offset=[1], fetch=[2])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalJoin(condition=[=($0, $4)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalFilter(condition=[=($1, $0)])\n"
+ + " LogicalAggregate(group=[{0, 1}])\n"
+ + " LogicalProject(DEPTNO=[$0], DEPTNO1=[$1])\n"
+ + " LogicalFilter(condition=[AND(>($2, 2), <=($2, +(2,
10)))])\n"
+ + " LogicalProject(DEPTNO=[$7], DEPTNO1=[$7],
rn=[ROW_NUMBER() OVER (PARTITION BY $7)])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ @Test void testDecorrelateCorrelatedOrderByLimitToRowNumber3() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = ""
+ + "SELECT deptno FROM dept WHERE 1000.00 >\n"
+ + "(SELECT sal FROM emp WHERE dept.deptno = emp.deptno\n"
+ + "order by emp.sal limit 1 offset 10)";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalFilter(condition=[>(1000.00, $3)])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalSort(sort0=[$0], dir0=[ASC], offset=[10],
fetch=[1])\n"
+ + " LogicalProject(SAL=[$5])\n"
+ + " LogicalFilter(condition=[=($cor0.DEPTNO, $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], SAL=[$3],
DEPTNO0=[$4], rn=[CAST($5):BIGINT])\n"
+ + " LogicalJoin(condition=[=($0, $4)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalFilter(condition=[>(1000.00, $0)])\n"
+ + " LogicalFilter(condition=[AND(>($2, 10), <=($2, +(10,
1)))])\n"
+ + " LogicalProject(SAL=[$5], DEPTNO=[$7], rn=[ROW_NUMBER()
OVER (PARTITION BY $7 ORDER BY $5 NULLS LAST)])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 2eafe44bd8..2b2deaa226 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2680,12 +2680,12 @@ LogicalProject(NAME=[$1])
<Resource name="planAfter">
<![CDATA[
LogicalProject(NAME=[$1])
- LogicalProject(DEPTNO=[$0], NAME=[$1], SAL=[CAST($2):INTEGER],
DEPTNO0=[CAST($3):INTEGER])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], SAL=[CAST($2):INTEGER],
DEPTNO0=[CAST($3):INTEGER], rn=[CAST($4):BIGINT])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[>(10, $0)])
- LogicalAggregate(group=[{0, 1}])
- LogicalProject(SAL=[FIRST_VALUE($5) OVER (PARTITION BY $7 ORDER BY
$5 DESC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)], DEPTNO=[$7])
+ LogicalFilter(condition=[<=($2, 1)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], rn=[ROW_NUMBER() OVER
(PARTITION BY $7 ORDER BY $5 DESC NULLS FIRST)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -2729,8 +2729,8 @@ LogicalProject(NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[>(10, $0)])
LogicalProject(SAL=[$0], DEPTNO=[$2])
- LogicalAggregate(group=[{0, 1, 2}])
- LogicalProject(SAL=[FIRST_VALUE($5) OVER (PARTITION BY $7 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 DESC NULLS FIRST RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)], EXPR$1=[FIRST_VALUE(EXTRACT(FLAG(YEAR),
$4)) OVER (PARTITION BY $7 ORDER BY EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 DESC
NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)],
DEPTNO=[$7])
+ LogicalFilter(condition=[<=($3, 1)])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)],
DEPTNO=[$7], rn=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 DESC NULLS FIRST)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -2843,8 +2843,8 @@ LogicalProject(NAME=[$1], EXPR$1=[$2])
LogicalJoin(condition=[=($0, $3)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(SAL=[$0], DEPTNO=[$2])
- LogicalAggregate(group=[{0, 1, 2}])
- LogicalProject(SAL=[FIRST_VALUE($5) OVER (PARTITION BY $7 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 NULLS LAST RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)], EXPR$1=[FIRST_VALUE(EXTRACT(FLAG(YEAR),
$4)) OVER (PARTITION BY $7 ORDER BY EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5
NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)],
DEPTNO=[$7])
+ LogicalFilter(condition=[<=($3, 1)])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)],
DEPTNO=[$7], rn=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 NULLS LAST)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -2884,8 +2884,8 @@ LogicalProject(NAME=[$1], EXPR$1=[$4])
LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$0],
NAME0=[CAST($1):VARCHAR(20) NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(SAL=[$0], DEPTNO=[$2], ENAME=[$3])
- LogicalAggregate(group=[{0, 1, 2, 3}])
- LogicalProject(SAL=[FIRST_VALUE($5) OVER (PARTITION BY $7, $1 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 NULLS LAST RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)], EXPR$1=[FIRST_VALUE(EXTRACT(FLAG(YEAR),
$4)) OVER (PARTITION BY $7, $1 ORDER BY EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5
NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)],
DEPTNO=[$7], ENAME=[$1])
+ LogicalFilter(condition=[<=($4, 1)])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)],
DEPTNO=[$7], ENAME=[$1], rn=[ROW_NUMBER() OVER (PARTITION BY $7, $1 ORDER BY
EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 NULLS LAST)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -8855,8 +8855,8 @@ LogicalProject(A=[$0], TS=[$1], X=[$2], X0=[$4])
LogicalJoin(condition=[AND(=($1, $5), =($2, $6))], joinType=[left])
LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20)
NOT NULL], EXPR$10=[$1])
LogicalValues(tuples=[[{ 'a', 1 }]])
- LogicalAggregate(group=[{0, 1, 2}])
- LogicalProject(X=[FIRST_VALUE($2) OVER (PARTITION BY $3, $4)],
EXPR$1=[$3], EXPR$00=[$4])
+ LogicalFilter(condition=[<=($3, 1)])
+ LogicalProject(X=[$2], EXPR$1=[$3], EXPR$00=[$4], rn=[ROW_NUMBER()
OVER (PARTITION BY $3, $4)])
LogicalJoin(condition=[AND(=($0, $4), <=($1, $3))],
joinType=[inner])
LogicalProject(A=[$1], TS=[$0], X=[$3])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
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 7cdde32314..cbb228b0ed 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -931,14 +931,13 @@ FROM
<Resource name="plan">
<![CDATA[
LogicalProject(DEPTNO=[$0], ENAME=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<=($3, 3)])
+ LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7], rn=[ROW_NUMBER() OVER
(PARTITION BY $7 ORDER BY $5 DESC NULLS FIRST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2196,8 +2195,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO=[$1], $f0=[true])
- LogicalAggregate(group=[{0, 1}])
- LogicalProject(EXPR$0=[FIRST_VALUE(1) OVER (PARTITION BY $0)],
DEPTNO=[$0])
+ LogicalFilter(condition=[<=($2, 1)])
+ LogicalProject(EXPR$0=[1], DEPTNO=[$0], rn=[ROW_NUMBER() OVER
(PARTITION BY $0)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 47f8f6497d..f64feb0a79 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -654,11 +654,10 @@ SELECT dname FROM "scott".dept WHERE 2000 > (SELECT
emp.sal FROM "scott".emp whe
EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t3])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2000.00:DECIMAL(12, 2)],
expr#4=[CAST($t1):DECIMAL(12, 2)], expr#5=[>($t3, $t4)], SAL=[$t1],
DEPTNO=[$t0], $condition=[$t5])
- EnumerableAggregate(group=[{1, 3, 4}])
- EnumerableWindow(window#0=[window(partition {1} order by [2, 0] range
between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [FIRST_VALUE($0),
FIRST_VALUE($2)])])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[IS NOT NULL($t7)], SAL=[$t5],
DEPTNO=[$t7], $2=[$t9], $condition=[$t10])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[<=($t3, $t4)],
expr#6=[2000.00:DECIMAL(12, 2)], expr#7=[CAST($t0):DECIMAL(12, 2)],
expr#8=[>($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..1=[{exprs}],
$condition=[$t9])
+ EnumerableWindow(window#0=[window(partition {1} order by [2, 0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[IS NOT NULL($t7)], SAL=[$t5],
DEPTNO=[$t7], $2=[$t9], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -678,11 +677,10 @@ SELECT dname FROM "scott".dept WHERE 2000 > (SELECT
emp.sal FROM "scott".emp whe
EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t3])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2000.00:DECIMAL(12, 2)],
expr#4=[CAST($t1):DECIMAL(12, 2)], expr#5=[>($t3, $t4)], SAL=[$t1],
DEPTNO=[$t0], $condition=[$t5])
- EnumerableAggregate(group=[{1, 3, 4}])
- EnumerableWindow(window#0=[window(partition {1} order by [2, 0] range
between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [FIRST_VALUE($0),
FIRST_VALUE($2)])])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[CAST($t3):INTEGER], expr#11=[8000],
expr#12=[>($t10, $t11)], expr#13=[IS NOT NULL($t7)], expr#14=[AND($t12, $t13)],
SAL=[$t5], DEPTNO=[$t7], $2=[$t9], $condition=[$t14])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[<=($t3, $t4)],
expr#6=[2000.00:DECIMAL(12, 2)], expr#7=[CAST($t0):DECIMAL(12, 2)],
expr#8=[>($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..1=[{exprs}],
$condition=[$t9])
+ EnumerableWindow(window#0=[window(partition {1} order by [2, 0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[CAST($t3):INTEGER], expr#11=[8000],
expr#12=[>($t10, $t11)], expr#13=[IS NOT NULL($t7)], expr#14=[AND($t12, $t13)],
SAL=[$t5], DEPTNO=[$t7], $2=[$t9], $condition=[$t14])
+ EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -722,16 +720,14 @@ EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1],
EXPR$1=[$t3])
# subquery contains null
SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY emp.comm desc limit 1) FROM "scott".dept;
-EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t2])
- EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
+EnumerableCalc(expr#0..4=[{inputs}], DNAME=[$t1], EXPR$1=[$t2])
+ EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], w0$o0=[$t1], DEPTNO=[$t0])
- EnumerableAggregate(group=[{7, 8}])
- EnumerableWindow(window#0=[window(partition {7} order by [6 DESC]
range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[FIRST_VALUE($6)])])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[1], expr#10=[<=($t8, $t9)],
COMM=[$t6], DEPTNO=[$t7], $2=[$t8], $condition=[$t10])
+ EnumerableWindow(window#0=[window(partition {7} order by [6 DESC] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+------------+--------+
| DNAME | EXPR$1 |
@@ -799,15 +795,13 @@ EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1],
EXPR$1=[$t3])
SELECT dname, (SELECT emp.sal FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY year(hiredate), emp.sal limit 1) FROM "scott".dept;
EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t2])
- EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
+ EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1], DEPTNO=[$t0])
- EnumerableAggregate(group=[{1, 3, 4}])
- EnumerableWindow(window#0=[window(partition {1} order by [2, 0]
range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[FIRST_VALUE($0), FIRST_VALUE($2)])])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[IS NOT NULL($t7)], SAL=[$t5],
DEPTNO=[$t7], $2=[$t9], $condition=[$t10])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[<=($t3, $t4)],
proj#0..1=[{exprs}], $condition=[$t5])
+ EnumerableWindow(window#0=[window(partition {1} order by [2, 0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[IS NOT NULL($t7)], SAL=[$t5],
DEPTNO=[$t7], $2=[$t9], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+------------+---------+
| DNAME | EXPR$1 |
@@ -826,15 +820,13 @@ EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1],
EXPR$1=[$t2])
SELECT dname, (SELECT emp.sal FROM "scott".emp where dept.deptno = emp.deptno
and mgr > 8000 ORDER BY year(hiredate), emp.sal limit 1) FROM "scott".dept;
EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t2])
- EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
+ EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1], DEPTNO=[$t0])
- EnumerableAggregate(group=[{1, 3, 4}])
- EnumerableWindow(window#0=[window(partition {1} order by [2, 0]
range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[FIRST_VALUE($0), FIRST_VALUE($2)])])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[CAST($t3):INTEGER], expr#11=[8000],
expr#12=[>($t10, $t11)], expr#13=[IS NOT NULL($t7)], expr#14=[AND($t12, $t13)],
SAL=[$t5], DEPTNO=[$t7], $2=[$t9], $condition=[$t14])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[<=($t3, $t4)],
proj#0..1=[{exprs}], $condition=[$t5])
+ EnumerableWindow(window#0=[window(partition {1} order by [2, 0] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[FLAG(YEAR)],
expr#9=[EXTRACT($t8, $t4)], expr#10=[CAST($t3):INTEGER], expr#11=[8000],
expr#12=[>($t10, $t11)], expr#13=[IS NOT NULL($t7)], expr#14=[AND($t12, $t13)],
SAL=[$t5], DEPTNO=[$t7], $2=[$t9], $condition=[$t14])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+------------+--------+
| DNAME | EXPR$1 |
@@ -2238,14 +2230,14 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[RAND()],
expr#6=[CAST($t5):INTEGER NOT NULL], expr#7=[2], expr#8=[MOD($t6, $t7)],
expr#9=[3], expr#10=[=($t8, $t9)], expr#11=[OR($t10, $t3)], SAL=[$t1],
$condition=[$t11])
+EnumerableCalc(expr#0..5=[{inputs}], expr#6=[RAND()],
expr#7=[CAST($t6):INTEGER NOT NULL], expr#8=[2], expr#9=[MOD($t7, $t8)],
expr#10=[3], expr#11=[=($t9, $t10)], expr#12=[OR($t11, $t3)], SAL=[$t1],
$condition=[$t12])
EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], cs=[$t1], DEPTNO=[$t0])
- EnumerableWindow(window#0=[window(partition {0} aggs
[FIRST_VALUE($1)])], constants=[[false]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[false], expr#3=[1],
expr#4=[<=($t1, $t3)], cs=[$t2], DEPTNO=[$t0], rn=[$t1], $condition=[$t4])
+ EnumerableWindow(window#0=[window(partition {0} rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])], constants=[[false]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2328,14 +2320,14 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
+EnumerableCalc(expr#0..5=[{inputs}], expr#6=[NOT($t3)], expr#7=[IS NOT
NULL($t3)], expr#8=[OR($t6, $t7)], expr#9=[IS NOT TRUE($t8)], SAL=[$t1],
$condition=[$t9])
EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], cs=[$t1], DEPTNO=[$t0])
- EnumerableWindow(window#0=[window(partition {0} aggs
[FIRST_VALUE($1)])], constants=[[false]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[false], expr#3=[1],
expr#4=[<=($t1, $t3)], cs=[$t2], DEPTNO=[$t0], rn=[$t1], $condition=[$t4])
+ EnumerableWindow(window#0=[window(partition {0} rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])], constants=[[false]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2400,16 +2392,17 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
+EnumerableCalc(expr#0..5=[{inputs}], expr#6=[NOT($t3)], expr#7=[IS NOT
NULL($t3)], expr#8=[OR($t6, $t7)], expr#9=[IS NOT TRUE($t8)], SAL=[$t1],
$condition=[$t9])
EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], cs=[$t1], DEPTNO1=[$t0])
- EnumerableWindow(window#0=[window(partition {0} aggs
[FIRST_VALUE($1)])], constants=[[true]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER NOT NULL], expr#5=[=($t3, $t4)], DEPTNO=[$t0],
$condition=[$t5])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[<=($t2, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
+ EnumerableSort(sort0=[$1], dir0=[ASC])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], cs=[$t2],
DEPTNO1=[$t0], rn=[$t1])
+ EnumerableWindow(window#0=[window(partition {0} rows between
UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])], constants=[[true]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER NOT NULL], expr#5=[=($t3, $t4)], DEPTNO=[$t0],
$condition=[$t5])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN nullable correlated
@@ -2433,16 +2426,17 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
+EnumerableCalc(expr#0..5=[{inputs}], expr#6=[NOT($t3)], expr#7=[IS NOT
NULL($t3)], expr#8=[OR($t6, $t7)], expr#9=[IS NOT TRUE($t8)], SAL=[$t1],
$condition=[$t9])
EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableSort(sort0=[$1], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], cs=[$t2], DEPTNO=[$t0])
- EnumerableWindow(window#0=[window(partition {0} order by [1 DESC]
range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[FIRST_VALUE($1)])])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], DEPTNO=[$t0], $1=[$t8], $condition=[$t11])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[<=($t2, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
+ EnumerableSort(sort0=[$1], dir0=[ASC])
+ EnumerableCalc(expr#0..2=[{inputs}], cs=[$t1], DEPTNO=[$t0], rn=[$t2])
+ EnumerableWindow(window#0=[window(partition {0} order by [1 DESC]
rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], DEPTNO=[$t0], $1=[$t8], $condition=[$t11])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN required is unknown correlated
@@ -6804,4 +6798,350 @@ FROM t0;
!ok
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+select * from emp
+where exists (
+select 1 from dept where emp.deptno=dept.deptno limit 1);
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT deptno, ename
+FROM
+ (SELECT DISTINCT deptno FROM emp) t1,
+ LATERAL (
+ SELECT ename, sal
+ FROM emp
+ WHERE deptno = t1.deptno
+ ORDER BY sal
+ DESC LIMIT 3) s;
++--------+--------+
+| DEPTNO | ENAME |
++--------+--------+
+| 10 | CLARK |
+| 10 | KING |
+| 10 | MILLER |
+| 20 | FORD |
+| 20 | JONES |
+| 20 | SCOTT |
+| 30 | ALLEN |
+| 30 | BLAKE |
+| 30 | TURNER |
++--------+--------+
+(9 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE d.deptno IN (
+ SELECT e.deptno
+ FROM emp e
+ WHERE d.deptno = e.deptno
+)
+LIMIT 2
+OFFSET 1;
++--------+----------+---------+
+| DEPTNO | DNAME | LOC |
++--------+----------+---------+
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+----------+---------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE d.deptno IN (
+ SELECT e.deptno
+ FROM emp e
+ WHERE d.deptno = e.deptno
+ LIMIT 10
+ OFFSET 2
+)
+LIMIT 2
+OFFSET 1;
++--------+----------+---------+
+| DEPTNO | DNAME | LOC |
++--------+----------+---------+
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+----------+---------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE d.deptno IN (
+ SELECT e.deptno
+ FROM emp e
+ WHERE d.deptno = e.deptno
+)
+OFFSET 1;
++--------+----------+---------+
+| DEPTNO | DNAME | LOC |
++--------+----------+---------+
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+----------+---------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE d.deptno IN (
+ SELECT e.deptno
+ FROM emp e
+ WHERE d.deptno = e.deptno
+ OFFSET 2
+)
+OFFSET 1;
++--------+----------+---------+
+| DEPTNO | DNAME | LOC |
++--------+----------+---------+
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+----------+---------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE EXISTS (
+ SELECT *
+ FROM emp e
+ WHERE d.deptno = e.deptno
+ OFFSET 2
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+------------+----------+
+(3 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+JOIN LATERAL (
+ SELECT *
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ OFFSET 2
+) s ON TRUE;
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE
| SAL | COMM | DEPTNO0 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 |
1982-01-23 | 1300.00 | | 10 |
+| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 |
1987-04-19 | 3000.00 | | 20 |
+| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 |
1987-05-23 | 1100.00 | | 20 |
+| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 |
1981-12-03 | 3000.00 | | 20 |
+| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 |
1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 |
1981-01-05 | 2850.00 | | 30 |
+| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 |
1981-09-08 | 1500.00 | 0.00 | 30 |
+| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 |
1981-12-03 | 950.00 | | 30 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+(8 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE d.deptno IN (
+ SELECT e.deptno
+ FROM emp e
+ OFFSET 2
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+------------+----------+
+(3 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE (
+ SELECT SUM(e.sal)
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ OFFSET 2
+) > 2;
++--------+-------+-----+
+| DEPTNO | DNAME | LOC |
++--------+-------+-----+
++--------+-------+-----+
+(0 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT e.ename
+FROM emp e
+WHERE EXISTS (
+ SELECT MAX(d.deptno) AS a
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ GROUP BY d.loc
+ ORDER BY d.loc
+ OFFSET 1
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT e.ename
+FROM emp e
+JOIN LATERAL (
+ SELECT MAX(d.deptno) AS a
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ GROUP BY d.loc
+ ORDER BY d.loc
+ OFFSET 1
+) s ON TRUE;
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT e.ename
+FROM emp e
+JOIN LATERAL (
+ SELECT MAX(d.deptno) AS a
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ GROUP BY d.loc
+ ORDER BY d.loc
+ LIMIT 2
+ OFFSET 1
+) s ON TRUE;
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT e.ename
+FROM emp e
+WHERE EXISTS (
+ SELECT MAX(d.deptno) AS a
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ GROUP BY d.loc
+ ORDER BY d.loc
+ LIMIT 2
+ OFFSET 1
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+JOIN LATERAL (
+ SELECT *
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ LIMIT 1
+ OFFSET 2
+) s ON TRUE;
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE
| SAL | COMM | DEPTNO0 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 |
1982-01-23 | 1300.00 | | 10 |
+| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 |
1987-04-19 | 3000.00 | | 20 |
+| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 |
1981-09-28 | 1250.00 | 1400.00 | 30 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+(3 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT *
+FROM dept d
+WHERE EXISTS (
+ SELECT *
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ LIMIT 1
+ OFFSET 2
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
++--------+------------+----------+
+(3 rows)
+
+!ok
+
+# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
+SELECT deptno FROM dept WHERE 1000.00 >
+(SELECT sal FROM emp WHERE dept.deptno = emp.deptno order by emp.sal limit 1
offset 10);
++--------+
+| DEPTNO |
++--------+
++--------+
+(0 rows)
+
+!ok
+
# End sub-query.iq
diff --git a/site/_docs/history.md b/site/_docs/history.md
index 399d0bfcec..ea6fd13726 100644
--- a/site/_docs/history.md
+++ b/site/_docs/history.md
@@ -49,6 +49,9 @@ ## <a
href="https://github.com/apache/calcite/releases/tag/calcite-1.42.0";>1.42.
#### Breaking Changes
{: #breaking-1-42-0}
+* [<a
href="https://issues.apache.org/jira/browse/CALCITE-6942";>CALCITE-6942</a>]
+Rename the method `decorrelateFetchOneSort` to `decorrelateSortWithRowNumber`.
+
#### New features
{: #new-features-1-42-0}
