[jira] [Created] (IGNITE-19032) Sql. Conditions in NATURAL JOINs do not use type coercion rules

["Maksim Zhuravkov (Jira)" <ji...@apache.org>]

Maksim Zhuravkov created IGNITE-19032:
-----------------------------------------

             Summary: Sql. Conditions in NATURAL JOINs do not use type coercion rules
                 Key: IGNITE-19032
                 URL: https://issues.apache.org/jira/browse/IGNITE-19032
             Project: Ignite
          Issue Type: Bug
          Components: sql
            Reporter: Maksim Zhuravkov
             Fix For: 3.0.0-beta2


NATURAL JOINs 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 NATURAL JOIN t12");
{code}

Error:
{code:java}
Caused by: java.lang.ClassCastException: class java.lang.Long cannot be cast to class java.lang.Integer (java.lang.Long and java.lang.Integer are in module java.base of loader 'bootstrap')
	at org.apache.ignite.internal.util.ColocationUtils.append(ColocationUtils.java:67)
	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)
	a
{code}

An equivalent query passes with no issues:

{code:java}
SELECT * FROM t11 JOIN t12 ON t11.c1 = t12.c1 AND t11.c2 = t12.c2
{code}

*Solution*

Because NATURAL JOINs equivalent to JOINs that with ON condition, it would be better to rewrite them with equivalent JOINs with ON conditions 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)