[
https://issues.apache.org/jira/browse/CALCITE-6412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18126212#comment-18126212
]
Iurii Gerzhedovich commented on CALCITE-6412:
---------------------------------------------
h3. Update: the missing projection is one symptom of a broader problem
The {{ClassCastException}} above is one of several ways {{convertMerge}} fails.
They all have the same cause. {{convertMerge}} takes the INSERT values from
{{convertInsert(insertCall)}} and assumes that the INSERT input is a "level 1"
{{LogicalProject}} over a "level 2" {{LogicalProject}} whose expressions refer
to the fields of the MERGE source. The converter's {{RelBuilder}} does not keep
that shape:
* it drops a projection that turns out to be an identity (the case in this
ticket);
* it merges the INSERT projection into the projection of a source subquery
({{bloat = 100}} by default), and then simplifies the merged expressions;
* it folds a projection of literals over {{Values}}, or over an {{Aggregate}}
without {{GROUP BY}}, into a new {{Values}}.
In the last two cases the INSERT expressions refer to the *input* of the source
subquery (for example, the source table scan), not to the source fields that
the MERGE join exposes. They are still applied to the join's row type.
h4. Reproducers (1.42.0, assertions enabled; {{convertMerge}} is unchanged on
{{main}})
Tables {{T}} and {{S}} both have the columns {{(ID INTEGER, D DECIMAL(19, 10),
V INTEGER)}}:
{code:java}
AbstractTable table = new AbstractTable() {
@Override public RelDataType getRowType(RelDataTypeFactory f) {
return f.builder()
.add("ID", SqlTypeName.INTEGER)
.add("D", f.createSqlType(SqlTypeName.DECIMAL, 19, 10))
.add("V", SqlTypeName.INTEGER)
.build();
}
};
SchemaPlus root = Frameworks.createRootSchema(true);
root.add("T", table);
root.add("S", table);
Planner planner =
Frameworks.getPlanner(Frameworks.newConfigBuilder().defaultSchema(root).build());
planner.rel(planner.validate(planner.parse(sql)));
{code}
# The shape of this ticket, which ends with {{ClassCastException:
LogicalTableScan cannot be cast to LogicalProject}}:
{code:sql}
MERGE INTO T AS t USING S AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET v = s.v
WHEN NOT MATCHED THEN INSERT (d, id, v) VALUES (s.d, s.id, s.v)
{code}
# A subquery source with a projection fails with {{AssertionError: type
mismatch: ref: DECIMAL(19, 10) NOT NULL, input: DECIMAL(10, 5) NOT NULL}}. The
INSERT expression for {{D}} is {{CAST(CAST($1):DECIMAL(10, 5)):DECIMAL(19,
10)}}. Its {{$1}} refers to {{S.D}}, while field 1 of the join is the
subquery's {{DECIMAL(10, 5)}} column.
{code:sql}
MERGE INTO T AS t USING (SELECT id, CAST(d AS DECIMAL(10, 5)) AS d, v FROM S)
AS s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (d, id, v) VALUES (s.d, s.id, s.v)
{code}
# Literal values from a constant source fail with {{ClassCastException:
LogicalValues cannot be cast to LogicalProject}}:
{code:sql}
MERGE INTO T AS t USING (VALUES (1, 10)) AS s(id, v) ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, d, v) VALUES (1, 0, 2)
{code}
When a merged expression is simplified, a source projection can disappear from
it entirely: {{CAST(CAST(v AS BIGINT) AS INTEGER)}} becomes a bare reference to
{{S.V}}. Neither the level 1/level 2 logic nor matching source projection
expressions can recover such a reference. Depending on the column layout, the
leftover reference fails with a type mismatch or an
{{ArrayIndexOutOfBoundsException}}. It can also produce a valid plan that reads
a different column of the join with the same type, for example a column of the
target table. This happens, for example, in the Apache Ignite 3 copy of
{{convertMerge}}:
{code:sql}
MERGE INTO T AS t USING (SELECT id, CAST(v AS BIGINT) AS v FROM S) AS s ON t.id
= s.id
WHEN MATCHED THEN UPDATE SET v = s.v
WHEN NOT MATCHED THEN INSERT (id, v) VALUES (s.id, s.v)
{code}
There, the inserted {{V}} is read from the target's {{ID}} column, which is
{{NULL}} for rows that do not match. The statement succeeds and writes {{NULL}}
instead of the source value.
h4. Possible fix
Do not depend on the shape of the {{convertInsert}} result. Rebase its
expressions onto the MERGE source explicitly:
# Convert the INSERT branch with a {{RelBuilder}} that keeps the source
relation in the tree: {{withBloat(-1).withSimplifyValues(false)}}. The
conversion result is only used to collect the INSERT expressions; the MERGE
plan itself is built as before.
# During that conversion, capture by identity the relation that the INSERT
source's FROM item converts to. This FROM item is the clone of the source table
reference that {{SqlValidatorImpl#rewriteMerge}} creates; a {{convertFrom}}
override in a helper converter is enough.
# Compose every projection between the {{TableModify}} input and that relation
with {{RelOptUtil.pushPastProject}}. If the input *is* that relation (the
identity case of this ticket), the INSERT expressions are just input references.
# Check that the field types of the captured relation are a prefix of the
join's left input. The left input may carry extra fields for expressions pushed
down from the {{ON}} condition, appended after the source fields. Then use the
composed expressions over the join as they are.
This replaces the level 1/level 2 handling and fixes all the cases above. All
three reproducers convert correctly with this approach in a copy of
{{convertMerge}}. Tests there also cover sources with a filter, a nested
subquery, {{DISTINCT}}, {{GROUP BY}} or a join, dynamic parameters, and a
correlated subquery in the INSERT values.
Suggested summary: "SqlToRelConverter#convertMerge assumes a fixed shape of the
INSERT input and produces invalid or wrong input references".
> 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)
