[
https://issues.apache.org/jira/browse/CALCITE-7856?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-7856.
----------------------------------
Fix Version/s: 1.43.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/4e9750ad7bb30670143e1fef8cb626da8979ca70]
Thank you for the contribution [~jooger]
> MERGE with a correlated sub-query in the UPDATE SET clause resolves a target
> column to the same-named source column
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7856
> URL: https://issues.apache.org/jira/browse/CALCITE-7856
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0, 1.43.0
> Reporter: Iurii Gerzhedovich
> Assignee: Iurii Gerzhedovich
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> In a MERGE statement, a correlated sub-query in `WHEN MATCHED THEN UPDATE
> SET` that references a target column
> whose name is also a column of the source silently reads the source column
> instead.
> Reproducer (SqlToRelConverterTest style, `withExpand(false)`):
> {code:sql}
> merge into empnullables e
> using (select * from emp) t
> on e.empno = t.empno
> when matched then update
> set sal = (select d.deptno from dept d where d.deptno = e.deptno)
> {code}
> `e.deptno` must refer to the target (EMPNULLABLES.DEPTNO, field `DEPTNO0` of
> the join row), but the generated plan reads
> the source field:
> {noformat}
> LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
> updateColumnList=[[SAL]], flattened=[true])
> LogicalProject(EMPNO0=[$9], ..., $f9=[$SCALAR_QUERY({
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($0, $cor0.DEPTNO)]) <-- $cor0 is typed with
> the target row type only, index 7
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> })])
> LogicalJoin(condition=[=($9, $0)], joinType=[inner])
> ...
> {noformat}
> Note that the project has no {{variablesSet}}, so {{$cor0}} is not bound to
> the join row; at index 7 of the join row
> is the *source* DEPTNO. Expected: {{$cor0.DEPTNO0}} (index 16) and
> {{variablesSet=[[$cor0]]}}.
> Same with plain tables:
> {code:sql}
> create table merge_t (id int primary key, d int, v int);
> create table merge_s (id int primary key, d int, v int);
> create table ref_t (k int primary key, val int);
> -- merge_t: (1, 100, 10); merge_s: (1, 200, 20); ref_t: (100, 1), (200, 2)
> merge into merge_t as t using merge_s as s on t.id = s.id
> when matched then update set d = (select r.val from ref_t r where r.k = t.d)
> -- expected merge_t.d = 1, actual plan reads s.d and yields 2
> {code}
> Root cause:
> {{SqlValidatorImpl.rewriteMerge}} builds the select list of the MERGE source
> select by {{SqlNode.clone}}-ing the
> select list of the UPDATE's own source select. The clone is shallow: the
> sub-query {{SqlSelect}} and its identifiers
> are shared between the two selects. The validator keeps one scope per node,
> so the sub-query is registered twice and
> the last registration wins, which is the scope of the UPDATE source select
> (target table only). While converting the
> MERGE source select, {{e.deptno}} is therefore resolved against a scope that
> contains only the target table. The
> correlation variable gets the target row type and index 7, and
> {{getCorrelationUse}} considers it as not being in the
> current scope and does not set it. {{convertMerge}} also rebuilds the top
> projection with
> {{relBuilder.project(projects)}}, which drops {{variablesSet}} even when it
> was set.
> Fix: deep-copy the select list in {{rewriteMerge}}, and keep {{variablesSet}}
> of the source projection in
> {{convertMerge}}.
> Not covered: with {{expand = true}} the sub-query is expanded into a
> {{LogicalCorrelate}} and {{convertMerge}} fails
> on its {{(LogicalJoin) mergeSourceRel.getInput(0)}} cast (without the fix it
> fails earlier with
> "correlation id $cor0 not found in correlation list []"). This looks like a
> separate problem.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
