[
https://issues.apache.org/jira/browse/CALCITE-7411?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18056999#comment-18056999
]
Silun Dong commented on CALCITE-7411:
-------------------------------------
I think the empty {{variablesSet}} in the top-level {{Project}} is the root
cause of the failure. It should be:
{code:java}
LogicalProject(variablesSet=[[$cor0]], JOB=[$0], PROFITMARGIN=[$SCALAR_QUERY({
......{code}
> When a SCALAR_QUERY in PROJECT contains correlated variables execution fails
> using TopDownGeneralDecorrelator
> -------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7411
> URL: https://issues.apache.org/jira/browse/CALCITE-7411
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Zhen Chen
> Priority: Major
> Fix For: 1.42.0
>
>
> Decorrelation fails when executing the following case in RelOptRulesTest
> {code:java}
> @Test void testTopDownGeneralDecorrelateForMeasure() {
> final String sql = "SELECT job,\n"
> + " (SELECT\n"
> + " CAST(SUM(i.sal) - SUM(COALESCE(i.comm, 0)) AS DECIMAL(10,
> 2)) / SUM(i.sal)\n"
> + " FROM emp AS i\n"
> + " WHERE i.job = e.job) AS profitMargin,\n"
> + " COUNT(*) AS \"count\"\n"
> + "FROM emp AS e\n"
> + "GROUP BY job"; sql(sql)
> .withRule(
> CoreRules.PROJECT_SUB_QUERY_TO_MARK_CORRELATE,
> CoreRules.PROJECT_MERGE,
> CoreRules.PROJECT_REMOVE)
> .withLateDecorrelate(true)
> .withTopDownGeneralDecorrelate(true)
> .check();
> } {code}
> SQL to Rel:
> {code:java}
> LogicalProject(JOB=[$0], PROFITMARGIN=[$SCALAR_QUERY({
> LogicalProject(EXPR$0=[/(CAST(-($0, $1)):DECIMAL(10, 2), $0)])
> LogicalAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[SUM($1)])
> LogicalProject(SAL=[$5], $f1=[$6])
> LogicalFilter(condition=[=($2, $cor0.JOB)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], count=[$1])
> LogicalAggregate(group=[{0}], count=[COUNT()])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> There's a problem here: the top-level PROJECT doesn't carry a variablesSet,
> and I'm not sure if it should.
> However after applying the subquery removal rule an incorrect plan is
> produced—it should have generated a LogicalCorrelate but instead generated a
> LogicalJoin.
> {code:java}
> LogicalProject(JOB=[$0], PROFITMARGIN=[$2], count=[$1])
> LogicalJoin(condition=[true], joinType=[left]) <--- here
> LogicalAggregate(group=[{0}], count=[COUNT()])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EXPR$0=[/(CAST(-($0, $1)):DECIMAL(10, 2), $0)])
> LogicalAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[SUM($1)])
> LogicalProject(SAL=[$5], $f1=[$6])
> LogicalFilter(condition=[=($2, $cor0.JOB)]) <--- corr var
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> I suspect the issue lies in the PROJECT_SUB_QUERY_TO_MARK_CORRELATE rule.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
