[
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)
