[
https://issues.apache.org/jira/browse/CALCITE-7319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18044322#comment-18044322
]
Zhen Chen commented on CALCITE-7319:
------------------------------------
I believe this case can reproduce the issue you mentioned.
{code:java}
@Test void testFilterCorrelateMissingVariableCor() {
final String sql = "SELECT E.EMPNO\n"
+ "FROM EMP E\n"
+ "JOIN DEPT D ON E.DEPTNO = D.DEPTNO\n"
+ "WHERE D.DEPTNO = (\n"
+ " SELECT MIN(D_INNER.DEPTNO)\n"
+ " FROM DEPT D_INNER\n"
+ " WHERE D_INNER.DEPTNO = E.DEPTNO)";
final String expected = "SELECT \"EMP\".\"EMPNO\"\n"
+ "FROM \"SCOTT\".\"EMP\"\n"
+ "INNER JOIN \"SCOTT\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"\n"
+ "WHERE \"DEPT\".\"DEPTNO\" = (((SELECT MIN(\"DEPTNO\")\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
+ "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\")))";
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleClass(JoinToCorrelateRule.class);
builder.addRuleClass(FilterCorrelateRule.class);
HepPlanner hepPlanner = new HepPlanner(builder.build());
RuleSet rules =
RuleSets.ofList(CoreRules.JOIN_TO_CORRELATE,
CoreRules.FILTER_CORRELATE);
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.withCalcite()
.optimize(rules, hepPlanner)
.ok(expected);
} {code}
Some error following:
variable $cor0 is not found
java.lang.NullPointerException: variable $cor0 is not found
at java.base/java.util.Objects.requireNonNull(Objects.java:334)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.getAliasContext(SqlImplementor.java:1589)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1623)
......
Suppressed: java.lang.Throwable: Error while converting RelNode to SqlNode:
LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
at org.apache.calcite.util.Util.throwAsRuntime(Util.java:973)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:217)
... 100 more
Suppressed: java.lang.Throwable: Error while converting RelNode to SqlNode:
LogicalProject(EMPNO=[$0])
LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[\{7}])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
LogicalFilter(condition=[=($0, $SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
}))])
LogicalFilter(condition=[=($cor1.DEPTNO, $0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
at org.apache.calcite.util.Util.throwAsRuntime(Util.java:973)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:217)
> 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)
