libenchao commented on code in PR #3193:
URL: https://github.com/apache/calcite/pull/3193#discussion_r1209219091
##########
core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java:
##########
@@ -866,6 +866,10 @@ private static void matchFilter(SubQueryRemoveRule rule,
LogicVisitor.find(RelOptUtil.Logic.TRUE, ImmutableList.of(c), e);
final Set<CorrelationId> variablesSet =
RelOptUtil.getVariablesUsed(e.rel);
+ // Only consider the correlated variables which originated from this
sub-query level
+ if (!filter.getVariablesSet().isEmpty()) {
Review Comment:
NIT: how about add a comment for this condition to mention about what we've
discussed, I think it's valuable for others to understand it.
##########
core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml:
##########
@@ -14683,6 +14683,115 @@ LogicalProject(DEPTNO=[$0], DEPTNO0=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testTwoLevelDecorrelate">
+ <Resource name="sql">
+ <![CDATA[SELECT d1.name, d1.deptno + ( SELECT e1.empno FROM emp e1
WHERE d1.deptno = e1.deptno and e1.sal = (SELECT max(sal)
FROM emp e2 WHERE e1.sal = e2.sal and
e1.deptno = e2.deptno and d1.deptno < e2.deptno))
FROM dept d1]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[+($0, $SCALAR_QUERY({
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $7), =($5, $SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[AND(=($cor1.SAL, $5), =($cor1.DEPTNO, $7),
<($cor0.DEPTNO, $7))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})))], variablesSet=[[$cor1]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
Review Comment:
Although you are using `withLateDecorrelate(true)`, actually it does nothing
because it handles `Correlate` instread `RexSubQuery`. Could you add
`withSubQueryRules()` to make decorrelation work?
##########
core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml:
##########
@@ -14683,6 +14683,115 @@ LogicalProject(DEPTNO=[$0], DEPTNO0=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testTwoLevelDecorrelate">
+ <Resource name="sql">
+ <![CDATA[SELECT d1.name, d1.deptno + ( SELECT e1.empno FROM emp e1
WHERE d1.deptno = e1.deptno and e1.sal = (SELECT max(sal)
FROM emp e2 WHERE e1.sal = e2.sal and
e1.deptno = e2.deptno and d1.deptno < e2.deptno))
FROM dept d1]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[+($0, $SCALAR_QUERY({
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $7), =($5, $SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[AND(=($cor1.SAL, $5), =($cor1.DEPTNO, $7),
<($cor0.DEPTNO, $7))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})))], variablesSet=[[$cor1]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[+($0, $SCALAR_QUERY({
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $7), =($5, $SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[AND(=($cor1.SAL, $5), =($cor1.DEPTNO, $7),
<($cor0.DEPTNO, $7))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})))], variablesSet=[[$cor1]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
Review Comment:
As mentioned before, you can delete this 'planAfter'.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
