[
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)
