[jira] [Created] (IGNITE-19031) Sql. Joins with USING condition do not use type coercion.

[Maksim Zhuravkov (Jira)]

Maksim Zhuravkov created IGNITE-19031:
-----------------------------------------

             Summary: Sql. Joins with USING condition do not use type coercion.
                 Key: IGNITE-19031
                 URL: https://issues.apache.org/jira/browse/IGNITE-19031
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 3.0.0-beta2
            Reporter: Maksim Zhuravkov


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}
Equivalent query passes with no issues:

{code:java}
sql("SELECT * FROM t11 JOIN t12 ON t11.c1 = t12.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}

*Solution*

Because JOINs that use USING join condition are equivalent to JOINs that use ON 
condition, It would be better to replace USING condition with equivalent ON 
condition prior to optimisation to leverage the solution handles type coercion.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)