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)