xiedeyantu commented on PR #4574:
URL: https://github.com/apache/calcite/pull/4574#issuecomment-3392753612
I tried running the plan for this SQL (the second one in sub-query.iq). I'm
not very familiar with this part of the code, but the current results look good.
```
<TestCase name="testCorrelatedFilterWithVariable2">
<Resource name="sql">
<![CDATA[SELECT dept.deptno, (
SELECT max(emp.empno)
FROM emp
WHERE empno = (SELECT max(empno) AS maxDept
FROM emp e2
WHERE e2.deptno = dept.deptno)
AND emp.deptno = dept.deptno),
dept.name
FROM dept]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
LogicalProject(variablesSet=[[$cor0]], DEPTNO=[$0], EXPR$1=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[AND(=($0, $SCALAR_QUERY({
LogicalAggregate(group=[{}], MAXDEPT=[MAX($0)])
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})), =($7, $cor0.DEPTNO))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
<Resource name="planMid">
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[$2], NAME=[$1])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[AND(=($0, $9), =($7, $cor0.DEPTNO))])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], MAXDEPT=[MAX($0)])
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[$3], NAME=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{0}], EXPR$0=[MAX($1)])
LogicalProject(DEPTNO9=[$7], EMPNO=[$0])
LogicalFilter(condition=[=($0, $9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], MAXDEPT=[$10])
LogicalJoin(condition=[=($0, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], MAXDEPT=[MAX($1)])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
```
--
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]
