[
https://issues.apache.org/jira/browse/CALCITE-7430?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18062750#comment-18062750
]
Silun Dong edited comment on CALCITE-7430 at 3/4/26 3:56 PM:
-------------------------------------------------------------
The root cause of the first two is that {{FilterJoinRule}} was applied before
the subquery was removed, and when [building the top
Filter|https://github.com/apache/calcite/blob/7654dd82162508231cb3ed44e60fed356e931403/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java#L251],
the {{variablesSet}} was omitted.
{{SubQueryRemoveRule.FILTER_SUB_QUERY_TO_MARK_CORRELATE}} performs a strict
check on {{variablesSet}} when removing a subquery: if it is empty, a {{Join}}
will be generated instead of a {{{}Correlate{}}}. As a result, the plan does
not contain a {{{}Correlate{}}}, but correlated variables remain. Fixing the
{{FilterJoinRule}} can resolve this issue.
The third is caused by the sql
{code:java}
dname = ANY ( select mgr from emp ){code}
being converted to an {{IN}} subquery like:
{code:java}
IN($1, {
LogicalProject(MGR=[$3])
LogicalTableScan(table=[[scott, EMP]])
} {code}
In this expression, the type of {{$1}} is VARCHAR while the type of {{MGR}} is
SMALLINT, and no type alignment is performed. When removing the subquery,
{{SubQueryRemoveRule.PROJECT_ENABLE_MARK_JOIN}} generates an EQUALS expression,
and the rule assumes types are aligned. Perhaps we should ensure type alignment
when constructing subquery expressions.
I think we should create two Jira tickets to address the issues above.
was (Author: JIRAUSER308615):
The root cause of the first two is that {{FilterJoinRule}} was applied before
the subquery was removed, and when [building the top
Filter|https://github.com/apache/calcite/blob/7654dd82162508231cb3ed44e60fed356e931403/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java#L251],
the {{variablesSet}} was omitted.
{{SubQueryRemoveRule.FILTER_SUB_QUERY_TO_MARK_CORRELATE}} performs a strict
check on {{variablesSet}} when removing a subquery: if it is empty, a {{Join}}
will be generated instead of a {{{}Correlate{}}}. As a result, the plan does
not contain a {{{}Correlate{}}}, but correlated variables remain. Fixing the
{{FilterJoinRule}} can resolve this issue.
The third is caused by the sql
{code:java}
dname = ANY ( select mgr from emp ){code}
being converted to an IN subquery like:
{code:java}
IN($1, {
LogicalProject(MGR=[$3])
LogicalTableScan(table=[[scott, EMP]])
} {code}
In this expression, the type of {{$1}} is VARCHAR while the type of {{MGR}} is
SMALLINT, and no type alignment is performed. When removing the subquery,
{{SubQueryRemoveRule.PROJECT_ENABLE_MARK_JOIN}} generates an EQUALS expression,
and the rule assumes types are aligned. Perhaps we should ensure type alignment
when constructing subquery expressions.
I think we should create two Jira tickets to address the issues above.
> 3 queries in RelOptRulesTest fail due to TopDownGeneralDecorrelator bugs
> ------------------------------------------------------------------------
>
> Key: CALCITE-7430
> URL: https://issues.apache.org/jira/browse/CALCITE-7430
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> I had to disable the '!ok' command in three tests added as part of
> CALCITE-7420 because they failed in CoreQuidemTest2 (even though they worked
> in CoreQuidemTest). Presumably these are bugs in
> {{TopDownGeneralDecorrelator}}.
>
> The tests are visible in the
> [julianhyde/7420-rule-test|https://github.com/julianhyde/calcite/tree/7420-rule-test]
> branch, or you can run the following queries:
> {noformat}
> # testFilterIntoJoinMissingVariableCor (in filter-into-join.iq)
> SELECT E.EMPNO
> FROM EMP E
> JOIN DEPT D ON E.DEPTNO = D.DEPTNO
> WHERE E.EMPNO > 10 AND D.DEPTNO = (
> SELECT MIN(D_INNER.DEPTNO)
> FROM DEPT D_INNER
> WHERE D_INNER.DEPTNO = E.DEPTNO);
> # testCorrelationScalarAggAndFilter (in reduce-expressions.iq)
> SELECT e1.empno
> FROM emp e1,
> dept d1
> where e1.deptno = d1.deptno
> and e1.deptno < 10 and d1.deptno < 15
> and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno);
> # testAnyInProjectNullable (in filter-sub-query-to-correlate.iq)
> select deptno, dname = ANY (
> select mgr from emp) as b
> from dept;
> {noformat}
>
> The first two give "{{java.lang.AssertionError: Correlation variable $cor0
> should be defined at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.getCorrelVariableGetter(EnumerableRelImplementor.java:476)}}";
> the third gives "{{java.lang.RuntimeException: while resolving method
> 'eq[class java.lang.String, short]' in class class
> org.apache.calcite.runtime.SqlFunctions}}".
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
