This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch site
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/site by this push:
new 80c7aaa1ee [CALCITE-6942] Support decorrelated for sub-queries with
LIMIT 1 and OFFSET
80c7aaa1ee is described below
commit 80c7aaa1ee466e783a593e4345ffe0e190446f0e
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 | 86 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 13 +-
core/src/test/resources/sql/sub-query.iq | 1802 +++++++++++++++++++-
site/_docs/history.md | 3 +
6 files changed, 2115 insertions(+), 115 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 42e3a84f96..8c05f8131b 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -73,6 +73,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;
@@ -560,16 +561,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;
@@ -1030,30 +1048,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<>();
@@ -1085,29 +1080,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 40b6212778..d522e890ae 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -377,4 +377,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 1c3ab3e668..c15418967e 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2643,12 +2643,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>
@@ -2692,8 +2692,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>
@@ -2806,8 +2806,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>
@@ -2847,8 +2847,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>
@@ -8449,6 +8449,74 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], EXPR$0=[$9],
EXPR$1=[$10])
LogicalProject(EMPNO=[$0], ENAME=[$1], EXPR$0=[$9], EXPR$1=[$10])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[5], $f10=[5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLateralTransposeWithDecorrelateFalse">
+ <Resource name="sql">
+ <![CDATA[WITH t1(a, ts) AS (VALUES('a', 1)), t2(a, ts, x) AS (SELECT
ename as a, empno as ts, mgr as x FROM emp)
+SELECT * FROM t1
+LEFT JOIN LATERAL (
+ SELECT x FROM t2
+ WHERE t2.a = t1.a AND t2.ts <= t1.ts
+ LIMIT 1
+) ON true
+LEFT JOIN LATERAL (
+ SELECT x
+ FROM t2
+ WHERE t2.a = t1.a
+) ON true]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(A=[$0], TS=[$1], X=[$2], X0=[$3])
+ LogicalCorrelate(correlation=[$cor2], joinType=[left], requiredColumns=[{0}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0, 1}])
+ LogicalValues(tuples=[[{ 'a', 1 }]])
+ LogicalSort(fetch=[1])
+ LogicalProject(X=[$2])
+ LogicalFilter(condition=[AND(=($0, CAST($cor0.A):VARCHAR(20) NOT
NULL), <=($1, $cor0.TS))])
+ LogicalProject(A=[$1], TS=[$0], X=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(X=[$2])
+ LogicalFilter(condition=[=($0, CAST($cor2.A):VARCHAR(20) NOT NULL)])
+ LogicalProject(A=[$1], TS=[$0], X=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLateralTransposeWithDecorrelateTrue">
+ <Resource name="sql">
+ <![CDATA[WITH t1(a, ts) AS (VALUES('a', 1)), t2(a, ts, x) AS (SELECT
ename as a, empno as ts, mgr as x FROM emp)
+SELECT * FROM t1
+LEFT JOIN LATERAL (
+ SELECT x FROM t2
+ WHERE t2.a = t1.a AND t2.ts <= t1.ts
+ LIMIT 1
+) ON true
+LEFT JOIN LATERAL (
+ SELECT x
+ FROM t2
+ WHERE t2.a = t1.a
+) ON true]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(A=[$0], TS=[$1], X=[$2], X0=[$4])
+ LogicalJoin(condition=[=($3, $5)], joinType=[left])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], X=[$4],
EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
+ 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 }]])
+ 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]])
+ LogicalProject(EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20) NOT
NULL])
+ LogicalValues(tuples=[[{ 'a', 1 }]])
+ LogicalProject(X=[$3], A=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
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 a530308abf..88adcf67b7 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>
@@ -2200,8 +2199,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 182f3f4e31..75a2acdee7 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..1=[{inputs}], cs=[$t1], DEPTNO=[$t0])
- EnumerableWindow(window#0=[window(partition {0} aggs
[FIRST_VALUE($1)])], constants=[[true]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER],
expr#4=[10], 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..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
@@ -5388,4 +5382,1712 @@ and exists(
!ok
+
+!use scott
+# [CALCITE-5223] AdjustProjectForCountAggregateRule throws
ArrayIndexOutOfBoundsException
+SELECT deptno, ename, job, sal,
+ sal / (SELECT SUM(sal) FROM Emp WHERE deptno = e.deptno) AS pct_dept
+FROM Emp AS e
+WHERE job = 'CLERK'
+ORDER BY deptno;
++--------+--------+-------+---------+---------------------+
+| DEPTNO | ENAME | JOB | SAL | PCT_DEPT |
++--------+--------+-------+---------+---------------------+
+| 10 | MILLER | CLERK | 1300.00 | 0.1485714285714286 |
+| 20 | SMITH | CLERK | 800.00 | 0.07356321839080460 |
+| 20 | ADAMS | CLERK | 1100.00 | 0.1011494252873563 |
+| 30 | JAMES | CLERK | 950.00 | 0.1010638297872340 |
++--------+--------+-------+---------+---------------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-5223] AdjustProjectForCountAggregateRule throws
ArrayIndexOutOfBoundsException
+SELECT deptno, ename, job, sal,
+ sal / (SELECT SUM(sal) FROM Emp WHERE deptno = e.deptno) AS pct_dept,
+ sal / (SELECT SUM(sal) FROM Emp) AS pct_total
+FROM Emp AS e
+WHERE job = 'CLERK'
+ORDER BY deptno;
++--------+--------+-------+---------+---------------------+---------------------+
+| DEPTNO | ENAME | JOB | SAL | PCT_DEPT | PCT_TOTAL
|
++--------+--------+-------+---------+---------------------+---------------------+
+| 10 | MILLER | CLERK | 1300.00 | 0.1485714285714286 |
0.04478897502153316 |
+| 20 | SMITH | CLERK | 800.00 | 0.07356321839080460 |
0.02756244616709733 |
+| 20 | ADAMS | CLERK | 1100.00 | 0.1011494252873563 |
0.03789836347975883 |
+| 30 | JAMES | CLERK | 950.00 | 0.1010638297872340 |
0.03273040482342808 |
++--------+--------+-------+---------+---------------------+---------------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+SELECT deptno
+FROM emp e
+WHERE EXISTS
+ (SELECT *
+ FROM dept d
+ WHERE EXISTS
+ (SELECT *
+ FROM bonus b
+ WHERE b.ename = e.ename
+ AND d.deptno = e.deptno));
++--------+
+| DEPTNO |
++--------+
++--------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+SELECT deptno
+FROM emp e
+WHERE EXISTS (
+ SELECT *
+ FROM dept d
+ WHERE EXISTS(
+ SELECT *
+ FROM bonus b
+ WHERE b.ename = e.ename
+ AND b.job = d.dname
+ AND d.deptno = e.deptno));
++--------+
+| DEPTNO |
++--------+
++--------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (1, 2), (2, 1), (2, 2), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a
+FROM t0 e
+WHERE EXISTS
+ (SELECT *
+ FROM t1 d
+ WHERE EXISTS
+ (SELECT *
+ FROM t2 b
+ WHERE b.t2b = e.t0b
+ AND d.t1a = e.t0a));
++-----+
+| T0A |
++-----+
+| 1 |
+| 1 |
++-----+
+(2 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+SELECT *,
+ (SELECT COUNT(*)
+ FROM
+ (
+ SELECT empno, ename, job
+ FROM emp
+ WHERE emp.deptno = dept.deptno) AS sub
+ GROUP BY deptno) AS num_dept_groups
+FROM dept;
++--------+------------+----------+-----------------+
+| DEPTNO | DNAME | LOC | NUM_DEPT_GROUPS |
++--------+------------+----------+-----------------+
+| 10 | ACCOUNTING | NEW YORK | 3 |
+| 20 | RESEARCH | DALLAS | 5 |
+| 30 | SALES | CHICAGO | 6 |
+| 40 | OPERATIONS | BOSTON | |
++--------+------------+----------+-----------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+SELECT *,
+ (SELECT COUNT(*)
+ FROM
+ (
+ SELECT empno, ename, job, comm
+ FROM emp
+ WHERE emp.deptno = dept.deptno
+ ORDER BY empno LIMIT 1
+ ) AS sub
+ GROUP BY sub.comm
+ ) AS num_dept_groups
+FROM dept;
++--------+------------+----------+-----------------+
+| DEPTNO | DNAME | LOC | NUM_DEPT_GROUPS |
++--------+------------+----------+-----------------+
+| 10 | ACCOUNTING | NEW YORK | 1 |
+| 20 | RESEARCH | DALLAS | 1 |
+| 30 | SALES | CHICAGO | 1 |
+| 40 | OPERATIONS | BOSTON | |
++--------+------------+----------+-----------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+select * from (values (4, 5)) as t(c, d)
+cross join lateral
+(select c, a*c as f
+from (values 2) as s(a)
+where c+d=a*c);
++---+---+----+---+
+| C | D | C0 | F |
++---+---+----+---+
++---+---+----+---+
+(0 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+select * from (values (2,2), (2,2)) as t(c,d)
+cross join lateral
+(select c,a*c as f from (values 2) as s(a)
+where c+d=a*c);
++---+---+----+---+
+| C | D | C0 | F |
++---+---+----+---+
+| 2 | 2 | 2 | 4 |
+| 2 | 2 | 2 | 4 |
++---+---+----+---+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH
+ customers(id, name, city) AS (
+ VALUES
+ (1, 'Alice', 'New York'),
+ (2, 'Bob', 'San Francisco'),
+ (3, 'Charlie', 'Los Angeles')
+ ),
+ orders(id, customer_id, total_amount) AS (
+ VALUES
+ (100, 1, 500.00),
+ (101, 2, 150.00),
+ (102, 1, 300.00)
+ ),
+ lineitems(id, order_id, product_name, price) AS (
+ VALUES
+ (1, 100, 'Laptop', 1000.00),
+ (2, 100, 'Mouse', 20.00),
+ (3, 100, 'Keyboard', 50.00),
+ (4, 101, 'Monitor', 150.00)
+ ),
+ payments(id, customer_id, amount) AS (
+ VALUES
+ (1, 1, 200.00),
+ (2, 1, 200.00),
+ (3, 1, 100.00),
+ (4, 2, 150.00)
+ )
+SELECT c.id, c.name
+FROM customers c
+WHERE EXISTS (
+ SELECT 1
+ FROM orders o
+ WHERE o.customer_id = c.id
+ AND (
+ SELECT SUM(cnt)
+ FROM (
+ SELECT COUNT(*) AS cnt
+ FROM lineitems li
+ WHERE li.order_id = o.id
+ UNION ALL
+ SELECT COUNT(*) AS cnt
+ FROM payments p
+ WHERE p.customer_id = c.id
+ ) AS union_sub
+ ) > 5
+);
++----+---------+
+| ID | NAME |
++----+---------+
+| 1 | Alice |
++----+---------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT d.dname
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ AND (
+ SELECT SUM(x)
+ FROM (
+ SELECT COUNT(*) as x
+ FROM bonus b
+ WHERE b.ename = e.ename
+ UNION ALL
+ SELECT COUNT(*) as x
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ ) t
+ ) > 5
+);
++-------+
+| DNAME |
++-------+
+| SALES |
++-------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT d.dname
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM emp e
+ WHERE (
+ SELECT SUM(x)
+ FROM (
+ SELECT COUNT(*) as x
+ FROM bonus b
+ WHERE b.ename = e.ename
+ UNION ALL
+ SELECT COUNT(*) as x
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ ) t
+ ) > 5
+);
++-------+
+| DNAME |
++-------+
+| SALES |
++-------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT ename,
+ (SELECT sum(c)
+ FROM
+ (SELECT deptno AS c
+ FROM dept
+ WHERE dept.deptno = emp.deptno
+ UNION ALL
+ SELECT 2 AS c
+ FROM bonus
+ WHERE bonus.job = emp.job) AS union_subquery
+ ) AS correlated_sum
+FROM emp
+ORDER BY ename;
++--------+----------------+
+| ENAME | CORRELATED_SUM |
++--------+----------------+
+| ADAMS | 20 |
+| ALLEN | 30 |
+| BLAKE | 30 |
+| CLARK | 10 |
+| FORD | 20 |
+| JAMES | 30 |
+| JONES | 20 |
+| KING | 10 |
+| MARTIN | 30 |
+| MILLER | 10 |
+| SCOTT | 20 |
+| SMITH | 20 |
+| TURNER | 30 |
+| WARD | 30 |
++--------+----------------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT ename,
+ (SELECT sum(c)
+ FROM
+ (SELECT deptno AS c
+ FROM dept
+ WHERE dept.deptno = emp.deptno
+ UNION ALL
+ SELECT 2 AS c
+ FROM bonus) AS union_subquery
+ ) AS correlated_sum
+FROM emp
+ORDER BY ename;
++--------+----------------+
+| ENAME | CORRELATED_SUM |
++--------+----------------+
+| ADAMS | 20 |
+| ALLEN | 30 |
+| BLAKE | 30 |
+| CLARK | 10 |
+| FORD | 20 |
+| JAMES | 30 |
+| JONES | 20 |
+| KING | 10 |
+| MARTIN | 30 |
+| MILLER | 10 |
+| SCOTT | 20 |
+| SMITH | 20 |
+| TURNER | 30 |
+| WARD | 30 |
++--------+----------------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT *,
+ (SELECT COUNT(*)
+ FROM (
+ SELECT * FROM emp WHERE emp.deptno = dept.deptno
+ UNION ALL
+ SELECT * FROM emp) AS sub
+ GROUP BY deptno) AS num_dept_groups
+FROM dept;
+more than one value in agg SINGLE_VALUE
+!error
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | 7 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 5 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 2 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ UNION ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | -2 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | 7 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 5 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 2 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ UNION DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
++-----+-----+
+(0 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ INTERSECT ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
++-----+-----+
+(0 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ INTERSECT DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ EXCEPT ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ EXCEPT DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!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}
