[
https://issues.apache.org/jira/browse/CALCITE-6873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17933175#comment-17933175
]
xiong duan commented on CALCITE-6873:
-------------------------------------
Fixed in
[802fce3|https://github.com/apache/calcite/commit/802fce3c41be76fca899ddb8c856d72c754d1520].
Thanks for the review [~asolimando] and [~zabetak] .
> FilterProjectTransposeRule should not push the Filter past the Project when
> the Filter contains a Subquery with correlation
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6873
> URL: https://issues.apache.org/jira/browse/CALCITE-6873
> Project: Calcite
> Issue Type: Bug
> Reporter: xiong duan
> Assignee: xiong duan
> Priority: Major
> Labels: pull-request-available
>
> According to the documentation of FilterProjectTransposeRule, when
> Correlation is included in the Filter, it is not allowed to push the Filter
> pass to the Project. However, when the filter condition contains a subquery
> with Correlation, it is pushed down. This would result in incorrect column
> references in the SubqueryRemoveRule.
> Now in Calcite:
> {code:java}
> LogicalFilter(condition=[EXISTS({
> LogicalFilter(condition=[AND(=($0, $cor0.empid), =($1, $cor0.name))])
> LogicalProject(subset=[rel#227:RelSubset#1.NONE.[]], empid=[$0], name=[$2])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]], table=[[hr, emps]])
> })])
> LogicalProject(subset=[rel#227:RelSubset#1.NONE.[]], empid=[$0], name=[$2])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]], table=[[hr,
> emps]]) {code}
> after FilterProjectTransposeRule will become :
> {code:java}
> LogicalProject(empid=[$0], name=[$2])
> LogicalFilter(condition=[EXISTS({
> LogicalFilter(condition=[AND(=($0, $cor0.empid), =($1, $cor0.name))])
> LogicalProject(subset=[rel#227:RelSubset#1.NONE.[]], empid=[$0], name=[$2])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]], table=[[hr, emps]])
> })])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]], table=[[hr,
> emps]]) {code}
> Then after SubqueryRemoveRule will become:
> {code:java}
> LogicalProject(empid=[$0], name=[$2])
> LogicalCorrelate(subset=[rel#282:RelSubset#10.NONE.[]],
> correlation=[$cor0], joinType=[inner], requiredColumns=[{0, 1}])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]], table=[[hr, emps]])
> LogicalAggregate(subset=[rel#266:RelSubset#8.NONE.[]], group=[{0}])
> LogicalProject(subset=[rel#264:RelSubset#7.NONE.[]], i=[true])
> LogicalFilter(subset=[rel#262:RelSubset#6.NONE.[]],
> condition=[AND(=($0, $cor0.empid), =($1, $cor0.name))])
> LogicalProject(subset=[rel#227:RelSubset#1.NONE.[]], empid=[$0],
> name=[$2])
> LogicalTableScan(subset=[rel#225:RelSubset#0.NONE.[]],
> table=[[hr, emps]]) {code}
> The Correlate `requiredColumns=[\{0, 1}]` should be `requiredColumns=[\{0,
> 2}]`.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
