[IGNITE-18644] Sql. Type system. Types in predicate expressions may have unnecessary casts.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 3.0
• Component/s: sql
• Labels: calcite2-required calcite3-required ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 26/Jan/23 09:24
• Updated: 04/Sep/24 16:19

Description

Types in predicate expressions may have unnecessary casts that makes a result of a query execution dependent on data that resides in a table. Observed for IN operator and OR expressions b/c IN operator is rewritten as X in (a, b) is rewritten into X = a OR X = b.

Table:

CREATE TABLE t1 (ID INTEGER PRIMARY KEY, c1 INTEGER);

Query:

SELECT c1 FROM t1 WHERE c1 IN (1, 2, 'b')  

Execution of the following query depends on data that is stored in a table:

• if c1 is 1 or 2 the following query does not raise an error.

• If c2 is neither of those the query fails at runtime with Integer parse error.

it would be better to have consistent behaviour (this requires removing the rendudant casts in predicate expressions).

Reproducer:

sql("CREATE TABLE T11 (c1 int primary key, c2 INTEGER)");

Transaction tx = CLUSTER_NODES.get(0).transactions().begin();
sql(tx, "INSERT INTO T11 VALUES(1, 2)");
sql(tx, "INSERT INTO T11 VALUES(2, 3)");
tx.commit();

// ok
assertQuery("SELECT T11.c2 FROM T11 WHERE c2 IN (2, 'b')").check();
// ok
assertQuery("SELECT T11.c2 FROM T11 WHERE c2 IN (3, 'b')").check();
// fails with java.lang.NumberFormatException: For input string: "b"
assertQuery("SELECT T11.c2 FROM T11 WHERE c2 IN (4, 'b')").check(); 

 

Issue Links

• is related to: IGNITE-18645 Sql. Type System. Reject plans with not matching dynamic parameters types during query validation.
• is related to: IGNITE-19997 Sql. Enhancing test coverage of type coercion