[
https://issues.apache.org/jira/browse/IGNITE-19031?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andrey Mashenkov updated IGNITE-19031:
--------------------------------------
Labels: ignite-3 (was: calcite3-required ignite-3)
> Sql. Joins with USING condition do not use type coercion rules.
> ---------------------------------------------------------------
>
> Key: IGNITE-19031
> URL: https://issues.apache.org/jira/browse/IGNITE-19031
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0
> Reporter: Maksim Zhuravkov
> Priority: Minor
> Labels: ignite-3
>
> JOINs with USING condition do not use type coercion and that causes some
> queries to fail with ClassCastException, even though equivalent JOINs
> complete successfully.
> {code:java}
> sql("CREATE TABLE T11 (c1 int primary key, c2 INTEGER)");
> sql("CREATE TABLE T12 (c1 BIGINT primary key, c2 BIGINT)");
> Transaction tx = CLUSTER_NODES.get(0).transactions().begin();
> sql(tx, "INSERT INTO T11 VALUES(1, 2)");
> sql(tx, "INSERT INTO T11 VALUES(2, 3)");
> sql(tx, "INSERT INTO T12 VALUES(1, 2)");
> sql(tx, "INSERT INTO T12 VALUES(2, 4)");
> tx.commit();
> sql("SELECT * FROM t11 JOIN t12 USING (c1)");
> {code}
> Error:
> {code:java}
> Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be
> cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in
> module java.base of loader 'bootstrap')
> at
> org.apache.ignite.internal.util.ColocationUtils.append(ColocationUtils.java:71)
> at
> org.apache.ignite.internal.sql.engine.util.HashFunctionFactoryImpl$TypesAwareHashFunction.hashOf(HashFunctionFactoryImpl.java:116)
> at
> org.apache.ignite.internal.sql.engine.trait.Partitioned.targets(Partitioned.java:47)
> at
> org.apache.ignite.internal.sql.engine.exec.rel.Outbox.flush(Outbox.java:242)
> at
> org.apache.ignite.internal.sql.engine.exec.rel.Outbox.push(Outbox.java:151)
> at
> org.apache.ignite.internal.sql.engine.exec.rel.SortNode.flush(SortNode.java:193)
> at
> org.apache.ignite.internal.sql.engine.exec.rel.SortNode.end(SortNode.java:154)
> {code}
> An equivalent query passes with no issues:
> {code:java}
> sql("SELECT * FROM t11 JOIN t12 ON t11.c1 = t12.c1");
> {code}
> *Solution*
> Because JOINs that use USING join condition are equivalent to JOINs that use
> ON condition, It would be better to rewrite USING condition with equivalent
> ON condition prior to optimisation (even at the parsing stage) to leverage
> the code that handles type coercion.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
