[jira] [Commented] (IGNITE-18677) Sql. Arithmetic operation between numeric type and varchar columns and literals

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

    [ https://issues.apache.org/jira/browse/IGNITE-18677?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17684533#comment-17684533 ] 

Maksim Zhuravkov commented on IGNITE-18677:
-------------------------------------------

There are multiple ways to fix this problem:
a) Update IgniteSqlValidator public RelDataType deriveType(SqlValidatorScope scope, SqlNode expr) and add type checks there.

b) Update IgniteSqlOperatorTable and replace an existing with another one that does type checking via SqlOperandTypeChecker that prohibits arithmetic operations between types that neither support arithmetic operators
nor support type coercion.

Both A and B allow to reject plans that contain type errors at the validation stage.

P.S. Similar approach can also be use to handle invalid casts between types that can not be converted into one another.
And if applied to dynamic parameters this would allow to reject plans with invalid values w/o at validation stage.
 

> Sql. Arithmetic operation between numeric type and varchar columns and literals
> -------------------------------------------------------------------------------
>
>                 Key: IGNITE-18677
>                 URL: https://issues.apache.org/jira/browse/IGNITE-18677
>             Project: Ignite
>          Issue Type: Improvement
>          Components: sql
>            Reporter: Maksim Zhuravkov
>            Priority: Major
>              Labels: ignite-3
>             Fix For: 3.0.0-beta2
>
>
> Current implementation allows the following queries (1,2,3,4 and 5 fails at runtime):
> {code:java}
> # 1
> SELECT 1::TINYINT + 1::VARCHAR
> {code}
> {code:java}
> # 2 
> SELECT 1::TINYINT + '1'
> {code}
> {code:java}
> # 3
> SELECT c + '1' FROM (VALUES(1, '1')) t(c, d);
> {code}
> {code:java}
> # 4
> SELECT c + d FROM (VALUES(1, '1')) t(c, d);
> {code}
> {code:java}
> # 5 - fails at runtime
> SELECT 1::TINYINT + 'b'
> {code}
> {code:java}
> Caused by: java.lang.NumberFormatException: For input string: "b"
> 	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
> 	at java.base/java.lang.Integer.parseInt(Integer.java:652)
> 	at java.base/java.lang.Byte.parseByte(Byte.java:152)
> 	at java.base/java.lang.Byte.parseByte(Byte.java:178)
> 	at SC.execute(Unknown Source)
> 	at org.apache.ignite.internal.sql.engine.exec.exp.ExpressionFactoryImpl$ProjectImpl.apply(ExpressionFactoryImpl.java:652)
> 	at org.apache.ignite.internal.sql.engine.exec.rel.ProjectNode.push(ProjectNode.java:69)
> 	at org.apache.ignite.internal.sql.engine.exec.rel.ScanNode.push(ScanNode.java:111)
> 	at org.apache.ignite.internal.sql.engine.exec.ExecutionContext.lambda$execute$0(ExecutionContext.java:299)
> {code}
> PostgreSQL:
> Query 1 is rejected because there is no addition operation between tinyint and varchar (we explicitly casted '1' to varchar)
> Query 2 is accepted because the second operand is implicitly converted to number since the literal looks like a number
> Query 3 is also accepted see (2)
> Query 4 is rejected because is no addition operation between tinyint and varchar because no implicit casts are added to columns (they have known type)
> We should consider whether we would like the same behaviour as PostgreSQL since it looks more consistent.



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