Iurii Gerzhedovich created CALCITE-7856:
-------------------------------------------
Summary: 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
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)
