[jira] [Updated] (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:all-tabpanel ]

Maksim Zhuravkov updated IGNITE-18677:
--------------------------------------
    Description: 
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.



  was:
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
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.




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