yashlimbad commented on code in PR #4840:
URL: https://github.com/apache/calcite/pull/4840#discussion_r3016340799
##########
core/src/main/java/org/apache/calcite/plan/RelOptUtil.java:
##########
@@ -4752,6 +4759,17 @@ public ImmutableBitSet build() {
}
return super.visitCall(call);
}
+
+ @Override public Void visitSubQuery(RexSubQuery subQuery) {
Review Comment:
verified a scenario where nested subquery has 2 correlated variable which
belongs to inner scope and outer scope.
exaple query:
```
SELECT e.ename, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno --------- main query / outer scope
AND e.sal IN (
SELECT e2.sal
FROM emp e2 -------- outer subquery / inner scope
WHERE e2.job IN (
SELECT e3.job
FROM emp e3 -------- inner subquery / inner most scope
WHERE e3.sal = e.sal
AND e3.sal = e2.sal
)
) where e.sal > 1000 and d.dname = 'SALES'
```
plan
```
LogicalProject(ENAME=[$1], DNAME=[$9])
LogicalFilter(condition=[AND(>(CAST($5):DECIMAL(12, 2), 1000.00), =($9,
'SALES'))])
LogicalJoin(condition=[AND(=($7, $8), IN($5, {
LogicalProject(SAL=[$5])
LogicalFilter(condition=[IN($2, {
LogicalProject(JOB=[$2])
LogicalFilter(condition=[AND(=($5, $cor0.SAL), =($5, $cor1.SAL))])
LogicalTableScan(table=[[scott, EMP]])
})], variablesSet=[[$cor1]])
LogicalTableScan(table=[[scott, EMP]])
}))], joinType=[inner], variablesSet=[[$cor0]])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
```
when `FilterJoinRule` is called, it's called on **main query** scope's join
which has `variablesSet=[[$cor0]]`
so when `InputFinder` is used on the condition of join which contains
subquery the method `RelOptUtil.getVariablesUsed` will return `$cor0` and not
all variables; this is already handled so we will not get both variables here.
Hence `InputFinder` and `RexInputConverter` will get correct variableSet.
and if we are concerned about **outer subquery** getting variableSet of all
correlationId then also we shouldn't be worries because it will not be queried
directly unless we decorrelate all nested subqueries.
--
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]
