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

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0
• Component/s: sql
• Labels: ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 31/Jan/23 09:39
• Updated: 02/Oct/24 13:43
• Resolved: 02/Oct/24 13:43

Description

Current implementation allows the following queries (1,2,3,4 and 5 fails at runtime):

# 1
SELECT 1::TINYINT + 1::VARCHAR

# 2 
SELECT 1::TINYINT + '1'

# 3
SELECT c + '1' FROM (VALUES(1, '1')) t(c, d);

# 4
SELECT c + d FROM (VALUES(1, '1')) t(c, d);

# 5 - fails at runtime
SELECT 1::TINYINT + 'b'

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)

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.

Issue Links

• is fixed by: IGNITE-23042 Sql. Forbid implicit casts between different type families