[
https://issues.apache.org/jira/browse/CALCITE-7319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen updated CALCITE-7319:
-------------------------------
Summary: FILTER_INTO_JOIN rule loses correlation variable context in
HepPlanner (was: FILTER_INTO_JOIN Calcite HepPlanner rules loose variable
context for subqueries)
> 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
> Priority: Major
>
> 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)
