[
https://issues.apache.org/jira/browse/CALCITE-7861?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Iurii Gerzhedovich closed CALCITE-7861.
---------------------------------------
Resolution: Duplicate
> 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
> Assignee: Iurii Gerzhedovich
> Priority: Major
>
> 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
(v10.3.26#10030026)
