This is an automated email from the ASF dual-hosted git repository.
rubenql 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 0a13d9905a [CALCITE-6652] RelDecorrelator can't decorrelate query with
limit 1
0a13d9905a is described below
commit 0a13d9905a1620b4b1ff1ab710dead8d32852c59
Author: suibianwanwan <[email protected]>
AuthorDate: Fri Feb 14 18:46:10 2025 +0800
[CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 166 ++++++++-
.../main/java/org/apache/calcite/util/Util.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 105 ++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 400 +++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 18 +-
core/src/test/resources/sql/sub-query.iq | 346 +++++++++++++++---
6 files changed, 975 insertions(+), 62 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 6a7376f132..0f0eb0cdc3 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -31,6 +31,7 @@
import org.apache.calcite.plan.hep.HepRelVertex;
import org.apache.calcite.rel.BiRel;
import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelHomogeneousShuttle;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
@@ -73,6 +74,7 @@
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.runtime.PairList;
+import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlExplainFormat;
import org.apache.calcite.sql.SqlExplainLevel;
import org.apache.calcite.sql.SqlFunction;
@@ -94,6 +96,7 @@
import org.apache.calcite.util.mapping.Mappings;
import org.apache.calcite.util.trace.CalciteTrace;
+import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.ImmutableSortedMap;
@@ -523,6 +526,19 @@ protected RexNode removeCorrelationExpr(
return null;
}
+ 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;
+ }
+
final RelNode newInput = frame.r;
Mappings.TargetMapping mapping =
@@ -767,16 +783,6 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
public @Nullable Frame getInvoke(RelNode r, boolean isCorVarDefined,
@Nullable RelNode parent) {
final Frame frame = dispatcher.invoke(r, isCorVarDefined);
currentRel = parent;
- if (frame != null && isCorVarDefined && r instanceof Sort) {
- final Sort sort = (Sort) r;
- // 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.
- if (sort.offset != null || sort.fetch != null) {
- return null;
- }
- }
if (frame != null) {
map.put(r, frame);
}
@@ -795,6 +801,146 @@ 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.
+ final Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
+ final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
+
+ final PairList<RexNode, String> corVarProjects = PairList.of();
+ List<RelDataTypeField> fieldList = frame.r.getRowType().getFieldList();
+ for (Map.Entry<CorDef, Integer> entry : frame.corDefOutputs.entrySet()) {
+ corDefOutputs.put(entry.getKey(),
+ sort.getRowType().getFieldCount() + corVarProjects.size());
+ RexInputRef.add2(corVarProjects, entry.getValue(), fieldList);
+ }
+
+ final List<RexNode> sortExprs =
+ new ArrayList<>(sort.getCollation().getFieldCollations().size());
+ for (RelFieldCollation collation :
sort.getCollation().getFieldCollations()) {
+ Integer newIdx =
requireNonNull(frame.oldToNewOutputs.get(collation.getFieldIndex()));
+ RexNode node = RexInputRef.of(newIdx, fieldList);
+ if (collation.direction == RelFieldCollation.Direction.DESCENDING) {
+ node = relBuilder.desc(node);
+ }
+ if (collation.nullDirection == RelFieldCollation.NullDirection.FIRST) {
+ node = relBuilder.nullsFirst(node);
+ } else if (collation.nullDirection ==
RelFieldCollation.NullDirection.LAST) {
+ node = relBuilder.nullsLast(node);
+ }
+ sortExprs.add(node);
+ }
+
+ final PairList<RexNode, String> newProjExprs = PairList.of();
+ 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.addAll(corVarProjects);
+ RelNode result = relBuilder.push(frame.r)
+ .project(newProjExprs.leftList(), newProjExprs.rightList())
+ .distinct().build();
+
+ return register(sort, result, mapOldToNewOutputs, corDefOutputs);
+ }
+
+ protected @Nullable Frame decorrelateSortAsAggregate(Sort sort, final Frame
frame) {
+ final Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
+ final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
+ if (sort.getCollation().getFieldCollations().size() == 1
+ && sort.getRowType().getFieldCount() == 1
+ && !frame.corDefOutputs.isEmpty()) {
+ //
+ // Rewrite logic:
+ //
+ // If sorted with no OFFSET and FETCH = 1, and only one collation field,
+ // rewrite the Sort as Aggregate using MIN/MAX function.
+ // Example:
+ // Sort(sort0=[$0], dir0=[ASC], fetch=[1])
+ // input
+ // Rewrite to:
+ // Aggregate(group=(corVar), agg=[min($0))
+ //
+ // Note: MIN/MAX is not strictly equivalent to LIMIT 1. When the input
has 0 rows,
+ // MIN/MAX returns NULL, while LIMIT 1 returns 0 rows.
+ // However, in the decorrelate, we add correlated variables to the group
list
+ // to ensure equivalence when Correlate is transformed to Join. When the
group list
+ // is non-empty, MIN/MAX will also return 0 rows if the input has 0 rows.
+ // So in this case, the transformation is legal.
+ RelFieldCollation collation =
Util.first(sort.getCollation().getFieldCollations());
+
+ if (collation.nullDirection != RelFieldCollation.NullDirection.LAST) {
+ return null;
+ }
+
+ SqlAggFunction aggFunction;
+ switch (collation.getDirection()) {
+ case ASCENDING:
+ case STRICTLY_ASCENDING:
+ aggFunction = SqlStdOperatorTable.MIN;
+ break;
+ case DESCENDING:
+ case STRICTLY_DESCENDING:
+ aggFunction = SqlStdOperatorTable.MAX;
+ break;
+ default:
+ return null;
+ }
+
+ final int newIdx =
requireNonNull(frame.oldToNewOutputs.get(collation.getFieldIndex()));
+ RelBuilder.AggCall aggCall = relBuilder.push(frame.r)
+ .aggregateCall(aggFunction,
relBuilder.fields(ImmutableList.of(newIdx)));
+
+ // As with the aggregate decorrelate, add correlated variables to the
group list.
+ final List<RexInputRef> groupKey = new ArrayList<>();
+ for (Map.Entry<CorDef, Integer> entry : frame.corDefOutputs.entrySet()) {
+ groupKey.add(RexInputRef.of(entry.getValue(), frame.r.getRowType()));
+ corDefOutputs.put(entry.getKey(), corDefOutputs.size());
+ }
+
+ RelNode aggregate = relBuilder.aggregate(relBuilder.groupKey(groupKey),
aggCall).build();
+
+ // Add the mapping for the added aggregate fields.
+ mapOldToNewOutputs.put(0, groupKey.size());
+ return register(sort, aggregate, mapOldToNewOutputs, corDefOutputs);
+ }
+ return null;
+ }
+
public @Nullable Frame decorrelateRel(LogicalProject rel, boolean
isCorVarDefined) {
return decorrelateRel((Project) rel, isCorVarDefined);
}
diff --git a/core/src/main/java/org/apache/calcite/util/Util.java
b/core/src/main/java/org/apache/calcite/util/Util.java
index b9b8e2085b..d911103f26 100644
--- a/core/src/main/java/org/apache/calcite/util/Util.java
+++ b/core/src/main/java/org/apache/calcite/util/Util.java
@@ -2086,7 +2086,7 @@ public static <T> Iterable<T> orEmpty(@Nullable
Iterable<T> v0) {
*
* @throws java.lang.IndexOutOfBoundsException if the list is empty
*/
- public <E> E first(List<E> list) {
+ public static <E> E first(List<E> list) {
return list.get(0);
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 61518e7701..3fa407691c 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -8577,6 +8577,111 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule
rule) {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateProjectWithFetchOne() {
+ final String query = "SELECT name, "
+ + "(SELECT sal FROM emp where dept.deptno = emp.deptno order by sal
limit 1) "
+ + "FROM dept";
+ sql(query).withRule(CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateProjectWithFetchOneDesc() {
+ final String query = "SELECT name, "
+ + "(SELECT emp.sal FROM emp WHERE dept.deptno = emp.deptno "
+ + "ORDER BY emp.sal desc nulls last LIMIT 1) "
+ + "FROM dept";
+ sql(query).withRule(CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateFilterWithFetchOne() {
+ final String query = "SELECT name FROM dept "
+ + "WHERE 10 > (SELECT emp.sal FROM emp where dept.deptno = emp.deptno "
+ + "ORDER BY emp.sal limit 1)";
+ sql(query).withRule(CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateFilterWithFetchOneDesc() {
+ final String query = "SELECT name FROM dept "
+ + "WHERE 10 > (SELECT emp.sal FROM emp where dept.deptno = emp.deptno "
+ + "ORDER BY emp.sal desc nulls last limit 1)";
+ sql(query).withRule(CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateFilterWithFetchOneDesc1() {
+ final String query = "SELECT name FROM dept "
+ + "WHERE 10 > (SELECT emp.sal FROM emp where dept.deptno = emp.deptno "
+ + "ORDER BY emp.sal desc limit 1)";
+ sql(query).withRule(CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateProjectWithMultiKeyAndFetchOne() {
+ final String query = "SELECT name, "
+ + "(SELECT sal FROM emp where dept.deptno = emp.deptno "
+ + "order by year(hiredate), emp.sal limit 1) FROM dept";
+ sql(query).withRule(CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateProjectWithMultiKeyAndFetchOne1() {
+ final String query = "SELECT name, "
+ + "(SELECT sal FROM emp where dept.deptno = emp.deptno and dept.name =
emp.ename "
+ + "order by year(hiredate), emp.sal limit 1) FROM dept";
+ sql(query).withRule(CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6652";>[CALCITE-6652]
+ * RelDecorrelator can't decorrelate query with limit 1</a>.
+ */
+ @Test void testDecorrelateFilterWithMultiKeyAndFetchOne() {
+ final String query = "SELECT name FROM dept "
+ + "WHERE 10 > (SELECT emp.sal FROM emp where dept.deptno = emp.deptno "
+ + "order by year(hiredate), emp.sal desc limit 1)";
+ sql(query).withRule(CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
+ .withLateDecorrelate(true)
+ .check();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-434";>[CALCITE-434]
* Converting predicates on date dimension columns into date ranges</a>,
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 3d2f5bf0ee..67402c92e6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2274,6 +2274,406 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalProject(i=[true])
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateFilterWithFetchOne">
+ <Resource name="sql">
+ <![CDATA[SELECT name FROM dept WHERE 10 > (SELECT emp.sal FROM emp where
dept.deptno = emp.deptno ORDER BY emp.sal limit 1)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalFilter(condition=[>(10, $SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[>(10, $2)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($2):INTEGER],
$f1=[CAST($3):INTEGER])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[>(10, $1)])
+ LogicalAggregate(group=[{1}], agg#0=[MIN($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateFilterWithFetchOneDesc">
+ <Resource name="sql">
+ <![CDATA[SELECT name FROM dept WHERE 10 > (SELECT emp.sal FROM emp where
dept.deptno = emp.deptno ORDER BY emp.sal desc nulls last limit 1)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalFilter(condition=[>(10, $SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[DESC-nulls-last], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[>(10, $2)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[DESC-nulls-last], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($2):INTEGER],
$f1=[CAST($3):INTEGER])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[>(10, $1)])
+ LogicalAggregate(group=[{1}], agg#0=[MAX($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateFilterWithFetchOneDesc1">
+ <Resource name="sql">
+ <![CDATA[SELECT name FROM dept WHERE 10 > (SELECT emp.sal FROM emp where
dept.deptno = emp.deptno ORDER BY emp.sal desc limit 1)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalFilter(condition=[>(10, $SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[>(10, $2)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], SAL=[CAST($2):INTEGER],
DEPTNO0=[CAST($3):INTEGER])
+ 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])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateFilterWithMultiKeyAndFetchOne">
+ <Resource name="sql">
+ <![CDATA[SELECT name FROM dept WHERE 10 > (SELECT emp.sal FROM emp where
dept.deptno = emp.deptno order by year(hiredate), emp.sal desc limit 1)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalFilter(condition=[>(10, $SCALAR_QUERY({
+LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC], fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[>(10, $2)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC],
fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], SAL=[CAST($2):INTEGER],
DEPTNO0=[CAST($3):INTEGER])
+ LogicalJoin(condition=[=($0, $3)], joinType=[inner])
+ 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])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectFetchOne">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT mgr FROM emp where dept.deptno =
emp.deptno order by mgr limit 1) FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(DEPTNO=[$7], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectFetchOneDesc">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT emp.mgr FROM emp WHERE dept.deptno =
emp.deptno ORDER BY emp.mgr desc LIMIT 1) FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], agg#0=[MAX($1)])
+ LogicalProject(DEPTNO=[$7], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectWithFetchOne">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT sal FROM emp where dept.deptno =
emp.deptno order by sal limit 1) FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{1}], agg#0=[MIN($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectWithFetchOneDesc">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT emp.sal FROM emp WHERE dept.deptno =
emp.deptno ORDER BY emp.sal desc nulls last LIMIT 1) FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalSort(sort0=[$0], dir0=[DESC-nulls-last], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[DESC-nulls-last], fetch=[1])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{1}], agg#0=[MAX($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectWithMultiKeyAndFetchOne">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT sal FROM emp where dept.deptno =
emp.deptno order by year(hiredate), emp.sal limit 1) FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+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])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDecorrelateProjectWithMultiKeyAndFetchOne1">
+ <Resource name="sql">
+ <![CDATA[SELECT name, (SELECT sal FROM emp where dept.deptno =
emp.deptno and dept.name = emp.ename order by year(hiredate), emp.sal limit 1)
FROM dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $7),
=(CAST($cor0.NAME):VARCHAR(20) NOT NULL, $1))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$2])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0,
1}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(SAL=[$0])
+ LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC], fetch=[1])
+ LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $7),
=(CAST($cor0.NAME):VARCHAR(20) NOT NULL, $1))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[$4])
+ LogicalJoin(condition=[AND(=($0, $5), =($3, $6))], joinType=[left])
+ 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])
+ 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 78f0f6a769..07a33d50f8 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2058,16 +2058,14 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9])
- LogicalFilter(condition=[IS NOT NULL($9)])
- LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{}], agg#0=[MIN($0)])
- LogicalProject($f0=[true])
- LogicalSort(fetch=[1])
- LogicalProject(EXPR$0=[1])
- LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($9):INTEGER],
$f1=[CAST($10):BOOLEAN])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ 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])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 01bfadc4b8..37523909f2 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -549,6 +549,271 @@ where sal + 100 not in (
!ok
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+SELECT dname FROM "scott".dept WHERE 2000 > (SELECT emp.sal FROM "scott".emp
where dept.deptno = emp.deptno ORDER BY emp.sal limit 1);
+
+EnumerableCalc(expr#0..1=[{inputs}], DNAME=[$t1])
+ EnumerableHashJoin(condition=[=($0, $2)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2000.00:DECIMAL(12, 2)],
expr#3=[CAST($t1):DECIMAL(12, 2)], expr#4=[>($t2, $t3)], proj#0..1=[{exprs}],
$condition=[$t4])
+ EnumerableAggregate(group=[{7}], agg#0=[MIN($5)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++------------+
+| DNAME |
++------------+
+| ACCOUNTING |
+| RESEARCH |
+| SALES |
++------------+
+(3 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+SELECT dname FROM "scott".dept WHERE 4000 > (SELECT emp.sal FROM "scott".emp
where dept.deptno = emp.deptno ORDER BY emp.sal desc nulls last limit 1);
+
+EnumerableCalc(expr#0..1=[{inputs}], DNAME=[$t1])
+ EnumerableHashJoin(condition=[=($0, $2)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[4000.00:DECIMAL(12, 2)],
expr#3=[CAST($t1):DECIMAL(12, 2)], expr#4=[>($t2, $t3)], proj#0..1=[{exprs}],
$condition=[$t4])
+ EnumerableAggregate(group=[{7}], agg#0=[MAX($5)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++----------+
+| DNAME |
++----------+
+| RESEARCH |
+| SALES |
++----------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# The case of the subquery that returns 0 rows
+SELECT dname FROM "scott".dept WHERE 2000 > (SELECT emp.sal FROM "scott".emp
where dept.deptno = emp.deptno and mgr > 8000 ORDER BY emp.sal limit 1);
+
+EnumerableCalc(expr#0..1=[{inputs}], DNAME=[$t1])
+ EnumerableHashJoin(condition=[=($0, $2)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2000.00:DECIMAL(12, 2)],
expr#3=[CAST($t1):DECIMAL(12, 2)], expr#4=[>($t2, $t3)], proj#0..1=[{exprs}],
$condition=[$t4])
+ EnumerableAggregate(group=[{7}], agg#0=[MIN($5)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):INTEGER],
expr#9=[8000], expr#10=[>($t8, $t9)], expr#11=[IS NOT NULL($t7)],
expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++-------+
+| DNAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+SELECT dname FROM "scott".dept WHERE 2000 > (SELECT emp.sal FROM "scott".emp
where dept.deptno = emp.deptno ORDER BY year(hiredate), emp.sal limit 1);
+
+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..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
++----------+
+| DNAME |
++----------+
+| RESEARCH |
+| SALES |
++----------+
+(2 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# The case of the subquery that returns 0 rows
+SELECT dname FROM "scott".dept WHERE 2000 > (SELECT emp.sal FROM "scott".emp
where dept.deptno = emp.deptno and mgr > 8000 ORDER BY year(hiredate), emp.sal
limit 1);
+
+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..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
++-------+
+| DNAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+SELECT dname, (SELECT emp.sal FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY emp.sal desc nulls last limit 1) FROM "scott".dept;
+
+EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t3])
+ EnumerableMergeJoin(condition=[=($0, $2)], joinType=[left])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableAggregate(group=[{7}], agg#0=[MAX($5)])
+ 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 |
++------------+---------+
+| ACCOUNTING | 5000.00 |
+| OPERATIONS | |
+| RESEARCH | 3000.00 |
+| SALES | 2850.00 |
++------------+---------+
+(4 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# 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..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]])
+!plan
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | |
++------------+--------+
+(4 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# subquery contains null
+SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno
ORDER BY emp.comm limit 1) FROM "scott".dept;
+
+EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t3])
+ EnumerableMergeJoin(condition=[=($0, $2)], joinType=[left])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableAggregate(group=[{7}], agg#0=[MIN($6)])
+ 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 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | 0.00 |
++------------+--------+
+(4 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# The case of the subquery that returns 0 rows
+SELECT dname, (SELECT emp.sal FROM "scott".emp where dept.deptno = emp.deptno
and mgr > 8000 ORDER BY emp.sal limit 1) FROM "scott".dept;
+
+EnumerableCalc(expr#0..3=[{inputs}], DNAME=[$t1], EXPR$1=[$t3])
+ EnumerableMergeJoin(condition=[=($0, $2)], joinType=[left])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableAggregate(group=[{7}], agg#0=[MIN($5)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):INTEGER],
expr#9=[8000], expr#10=[>($t8, $t9)], expr#11=[IS NOT NULL($t7)],
expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | |
++------------+--------+
+(4 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+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])
+ 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]])
+!plan
++------------+---------+
+| DNAME | EXPR$1 |
++------------+---------+
+| ACCOUNTING | 2450.00 |
+| OPERATIONS | |
+| RESEARCH | 800.00 |
+| SALES | 950.00 |
++------------+---------+
+(4 rows)
+
+!ok
+
+# [CALCITE-6652] RelDecorrelator can't decorrelate query with limit 1
+# The case of the subquery that returns 0 rows
+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])
+ 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]])
+!plan
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | |
++------------+--------+
+(4 rows)
+
+!ok
+
# Condition that happens to eliminate all NULL keys.
# The one missing row has {ename: 'MARTIN', comm: 1400}
# Tested on Oracle.
@@ -1943,14 +2208,15 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[RAND()],
expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[2], expr#6=[MOD($t4, $t5)],
expr#7=[3], expr#8=[=($t6, $t7)], expr#9=[OR($t8, $t2)], SAL=[$t0],
$condition=[$t9])
- EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
- EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableLimit(fetch=[1])
- EnumerableSort(sort0=[$0], dir0=[DESC])
- EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t0, $t5)], cs=[$t3], $condition=[$t6])
+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])
+ 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..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2032,14 +2298,15 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
- EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
- EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableLimit(fetch=[1])
- EnumerableSort(sort0=[$0], dir0=[DESC])
- EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], cs=[$t3], $condition=[$t6])
+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])
+ 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..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2103,14 +2370,16 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
- EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
- EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableLimit(fetch=[1])
- EnumerableSort(sort0=[$0], dir0=[DESC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], expr#7=[10],
expr#8=[CAST($t0):INTEGER NOT NULL], expr#9=[=($t7, $t8)], expr#10=[AND($t6,
$t9)], cs=[$t3], $condition=[$t10])
- EnumerableTableScan(table=[[scott, DEPT]])
+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])
+ 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]])
!plan
# Test filter literal NOT IN nullable correlated
@@ -2134,14 +2403,16 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
- EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
- EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableLimit(fetch=[1])
- EnumerableSort(sort0=[$0], dir0=[DESC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[$cor0], expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)],
expr#7=[CAST($t0):INTEGER], expr#8=[10], expr#9=[=($t7, $t8)],
expr#10=[AND($t6, $t9)], cs=[$t3], $condition=[$t10])
- EnumerableTableScan(table=[[scott, DEPT]])
+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])
+ 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]])
!plan
# Test filter null IN required is unknown correlated
@@ -2168,15 +2439,8 @@ select sal from "scott".emp e
(14 rows)
!ok
-EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t0])
- EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
- EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableLimit(fetch=[1])
- EnumerableSort(sort0=[$0], dir0=[DESC])
- EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], cs=[$t3], $condition=[$t6])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
