[
https://issues.apache.org/jira/browse/CALCITE-7319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18044316#comment-18044316
]
Mihai Budiu commented on CALCITE-7319:
--------------------------------------
I think that the following change will work; testing it now:+
{code:java}
+ List<RexNode> ineligible = new ArrayList<>();
+ List<RexNode> eligible = new ArrayList<>();
+ for (RexNode f: aboveFilters) {
+ if (RexUtil.containsCorrelation(f)) {
+ ineligible.add(f);
+ } else {
+ eligible.add(f);
+ }
+ }
+ aboveFilters = eligible;
// TODO - add logic to derive additional filters. E.g., from
// (t1.a = 1 AND t2.a = 2) OR (t1.b = 3 AND t2.b = 4), you can
// derive table filters:
// (t1.a = 1 OR t1.b = 3)
// (t2.a = 2 OR t2.b = 4) // Try to push down above filters.
These are typically where clause
// filters. They can be pushed down if they are not on the NULL
// generating side.
boolean filterPushed =
RelOptUtil.classifyFilters(join,
aboveFilters,
joinType.canPushIntoFromAbove(),
joinType.canPushLeftFromAbove(),
joinType.canPushRightFromAbove(),
joinFilters,
leftFilters,
rightFilters);
+ aboveFilters.addAll(ineligible);{code}
> FILTER_INTO_JOIN rule loses correlation variable context in HepPlanner
> ----------------------------------------------------------------------
>
> Key: CALCITE-7319
> URL: https://issues.apache.org/jira/browse/CALCITE-7319
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Environment: Java
> Reporter: Matthew White
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> FILTER_INTO_JOIN Calcite HepPlanner rules loose variable context for
> subqueries
> For queries that involve a subplan and therefore correlation variables,
> applying some HepPlanner rules means
> that the correlation variables. (The variable set in the nodes) is lost.
> As an example start with this SQL query with a subquery.
> ```sql
> final String sql = """
> SELECT
> P.P_PARTKEY
> FROM
> PART P
> JOIN PARTSUPP PS ON P.P_PARTKEY = PS.PS_PARTKEY
> WHERE
> PS.PS_SUPPLYCOST = (
> SELECT
> MIN(PS_INNER.PS_SUPPLYCOST)
> FROM
> PARTSUPP PS_INNER
> WHERE
> PS_INNER.PS_PARTKEY = P.P_PARTKEY
> )
> """;
> ```
> When this is parsed and converted to Calcite RelNodes, there is a reference
> to `$cor0.P_PARTKEY` and the `LogicalFilter` has a varaibleSet present. The
> 'explain()' format of the nodes gives:
> ```
> LogicalProject(P_PARTKEY=[$0])
> LogicalFilter(condition=[=($12, $SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
> LogicalProject(PS_SUPPLYCOST=[$3])
> LogicalFilter(condition=[=($0, $cor0.P_PARTKEY)])
> LogicalTableScan(table=[[TPCH, PARTSUPP]])
> }))], variablesSet=[[$cor0]])
> LogicalJoin(condition=[=($0, $9)], joinType=[inner])
> LogicalTableScan(table=[[TPCH, PART]])
> LogicalTableScan(table=[[TPCH, PARTSUPP]])
> ```
> Applying the `FILTER_INTO_JOIN` rule. (we believe that other rules can also
> have the same effect) result in an optimized query, with the calcite nodes
> being as follows
> ```
> LogicalProject(P_PARTKEY=[$0])
> LogicalJoin(condition=[=($0, $9)], joinType=[inner])
> LogicalTableScan(table=[[TPCH, PART]])
> LogicalFilter(condition=[=($3, $SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
> LogicalProject(PS_SUPPLYCOST=[$3])
> LogicalFilter(condition=[=($0, $cor0.P_PARTKEY)])
> LogicalTableScan(table=[[TPCH, PARTSUPP]])
> }))])
> LogicalTableScan(table=[[TPCH, PARTSUPP]])
> ```
> Note that the `LogicalFilter` still has the condition `[=($0,
> $cor0.P_PARTKEY)]` - but no variableset present on any node.
> Attempting to convert this back into SQL via Calcite gives
> ```
> java.lang.NullPointerException: variable $cor0 is not found
> ```
> The complete code is available in this [github
> gist](https://gist.github.com/mbwhite/4899dfd959839a6eedd6ee1542c9fb08)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
