Pavel Pereslegin created CALCITE-6412:
-----------------------------------------
Summary: SqlToRelConverter fails to convert SqlMerge AST to
relational expression
Key: CALCITE-6412
URL: https://issues.apache.org/jira/browse/CALCITE-6412
Project: Calcite
Issue Type: Bug
Reporter: Pavel Pereslegin
{{SqlToRelConverter#convertMerge}} fails if the insert node contains all
columns, but in an order different from the order specified by the schema.
{noformat}
class org.apache.calcite.rel.logical.LogicalTableScan cannot be cast to class
org.apache.calcite.rel.logical.LogicalProject
(org.apache.calcite.rel.logical.LogicalTableScan and
org.apache.calcite.rel.logical.LogicalProject are in unnamed module of loader
'app')
java.lang.ClassCastException: class
org.apache.calcite.rel.logical.LogicalTableScan cannot be cast to class
org.apache.calcite.rel.logical.LogicalProject
(org.apache.calcite.rel.logical.LogicalTableScan and
org.apache.calcite.rel.logical.LogicalProject are in unnamed module of loader
'app')
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertMerge(SqlToRelConverter.java:4343)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3818)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:614)
at
org.apache.calcite.sql.test.AbstractSqlTester.convertSqlToRel2(AbstractSqlTester.java:545)
at
org.apache.calcite.sql.test.AbstractSqlTester.assertSqlConvertsTo(AbstractSqlTester.java:486)
at
org.apache.calcite.sql.test.AbstractSqlTester.assertConvertsTo(AbstractSqlTester.java:464)
at
org.apache.calcite.test.SqlToRelFixture.convertsTo(SqlToRelFixture.java:106)
at org.apache.calcite.test.SqlToRelFixture.ok(SqlToRelFixture.java:94)
{noformat}
Some more details.
The following query is working fine.
{code:sql}
merge into empnullables e
using emp t on e.empno = t.empno
when matched then update
set ename = t.ename, deptno = t.deptno, sal = t.sal * .1
when not matched then insert (empno, ename, job, mgr, hiredate, sal, comm,
deptno, slacker)
values(t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.COMM, t.deptno,
t.slacker)
{code}
But if we swap the "empno" and "ename" columns in the "not matched" section
{code:sql}
...
when not matched then insert (ename, empno, job, mgr, hiredate, sal, comm,
deptno, slacker)
values(t.ename, t.empno, t.job, t.mgr, t.hiredate, t.sal, t.COMM, t.deptno,
t.slacker)
{code}
{{convertMerge}} fails due to missing projection in {{LogicalTableModify}}
input.
I'm not sure if projection is mandatory in this case (then it's a bug of
convertInsert()), if not, then convertMerge() should handle the lack of
projection properly.
Full reproducer (for SqlToRelConverterTest).
{code:java}
@Test void testMergeAllColumns() {
final String sql = "merge into empnullables e\n"
+ "using emp t on e.empno = t.empno\n"
+ "when matched then update\n"
+ "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1\n"
+ "when not matched then insert (ename, empno, job, mgr, hiredate, sal,
comm, deptno, slacker)\n"
+ "values(t.ename, t.empno, t.job, t.mgr, t.hiredate, t.sal, t.COMM,
t.deptno, t.slacker)";
sql(sql).ok();
}
{code}
{code:xml}
<TestCase name="testMergeAllColumns">
<Resource name="sql">
<![CDATA[merge into empnullables e
using emp t on e.empno = t.empno
when matched then update
set ename = t.ename, deptno = t.deptno, sal = t.sal * .1
when not matched then insert (ename, empno, job, mgr, hiredate, sal, comm,
deptno, slacker)
values(t.ename, t.empno, t.job, t.mgr, t.hiredate, t.sal, t.COMM, t.deptno,
t.slacker)]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
updateColumnList=[[ENAME, DEPTNO, SAL]], flattened=[true])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17], ENAME1=[$1], DEPTNO1=[$7], $f20=[*($5,
0.1:DECIMAL(1, 1))])
LogicalJoin(condition=[=($9, $0)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
</TestCase>
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
