Iurii Gerzhedovich created CALCITE-7859:
-------------------------------------------
Summary: SqlToRelConverter.convertMerge produces a wrong plan or
fails when a sub-query in MERGE ... UPDATE SET is expanded (expand = true)
Key: CALCITE-7859
URL: https://issues.apache.org/jira/browse/CALCITE-7859
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.42.0
Reporter: Iurii Gerzhedovich
{{SqlToRelConverter.convertMerge}} assumes that the input of the projection of
the MERGE source select is the join of the
source and the target:
{code:java}
LogicalJoin join = (LogicalJoin) mergeSourceRel.getInput(0);
int nSourceFields = join.getLeft().getRowType().getFieldCount();
...
relBuilder.push(join).project(projects, ...);
{code}
With {{expand = true}} (the default of {{SqlToRelConverter.Config}}) a
sub-query in the SET expression is expanded into a
join or a correlate on top of that join, so the assumption does not hold.
1. Correlated sub-query: the input is a {{LogicalCorrelate}} and the conversion
fails with
{noformat}
java.lang.ClassCastException: class LogicalCorrelate cannot be cast to class
LogicalJoin
at SqlToRelConverter.convertMerge
{noformat}
2. Uncorrelated sub-query: the input is a {{LogicalJoin}} of the source/target
join and the sub-query, so the cast
succeeds, but {{join.getLeft()}} is the source/target join, and
{{nSourceFields}} is the width of both tables instead
of the width of the source. All target columns are dropped from the projection,
and the join with the sub-query
is discarded or referenced wrongly. Query (SqlToRelConverterTest catalog):
{code:sql}
merge into empnullables e
using (select * from emp) t
on e.empno = t.empno
when matched then update
set sal = (select max(d.deptno) from dept d)
{code}
Actual plan (the update row has only the SET expression, the 9 target columns
are missing):
{noformat}
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
updateColumnList=[[SAL]], flattened=[true])
LogicalProject(EXPR$0=[$18])
LogicalJoin(condition=[true], joinType=[left])
...
{noformat}
Expected:
{noformat}
LogicalProject(EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12],
HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17],
EXPR$0=[$18])
{noformat}
Fix: compute the number of source fields from the validator namespace of the
source table reference, and build the new
projection over the real input of the source projection instead of over the
first join found.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
