Iurii Gerzhedovich created CALCITE-7861:
-------------------------------------------
Summary: MERGE ... WHEN NOT MATCHED THEN INSERT silently inserts
wrong values when a sub-query in VALUES is expanded (expand = true)
Key: CALCITE-7861
URL: https://issues.apache.org/jira/browse/CALCITE-7861
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Iurii Gerzhedovich
With {{expand = true}} (the default of {{SqlToRelConverter.Config}}) a
sub-query in the VALUES of the INSERT branch of a
MERGE is converted into a plan that inserts a wrong value. No error is reported.
{code:sql}
merge into empnullables e using (select * from emp) t on e.empno = t.empno
when matched then update set sal = 1
when not matched then insert (empno, sal) values (t.empno, (select max(deptno)
from dept))
{code}
Actual plan (SqlToRelConverterTest catalog, {{expand = true}}). The value for
SAL is {{EMPNO0=[$9]}}, that is EMPNO of
the target table, and the join with DEPT is gone:
{noformat}
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
updateColumnList=[[SAL]], flattened=[true])
LogicalProject(EMPNO=[$0], $f1=[null:VARCHAR(20)], $f2=[null:VARCHAR(10)],
$f3=[null:INTEGER], $f4=[null:TIMESTAMP(0)], EMPNO0=[$9], $f6=[null:INTEGER],
..., EMPNO00=[$9], ENAME0=[$10], ..., $f18=[1])
LogicalJoin(condition=[=($9, $0)], joinType=[left])
LogicalProject(EMPNO=[$0], ..., SLACKER=[$8])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
{noformat}
With {{expand = false}} the plan is correct: the SAL value is a
{{$SCALAR_QUERY}}.
Root cause: {{SqlToRelConverter.convertMerge}} converts the INSERT with
{{convertInsert}} and then copies
{{level1InsertExprs}}/{{level2InsertExprs}} of its projection into the
projection over the MERGE join. When the
sub-query is expanded, those expressions are expressed over the input of the
insert source, which is the source table
joined with the sub-query. Its indexes (for example {{$9}}, the sub-query
result) are then used unchanged over the
join of the source and the target, where {{$9}} is a field of the target. The
expression has to be rebased on the
expanded sub-query, or the sub-query has to be expanded over the MERGE join.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
