[ https://issues.apache.org/jira/browse/CALCITE-6412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pavel Pereslegin updated CALCITE-6412: -------------------------------------- Affects Version/s: 1.37.0 > 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 > Affects Versions: 1.37.0 > Reporter: Pavel Pereslegin > Priority: Minor > > {{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)