[jira] [Created] (IGNITE-21035) Sql. Fix type inference for dynamic parameters in operators.

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

Maksim Zhuravkov created IGNITE-21035:
-----------------------------------------

             Summary: Sql. Fix type inference for dynamic parameters in operators.
                 Key: IGNITE-21035
                 URL: https://issues.apache.org/jira/browse/IGNITE-21035
             Project: Ignite
          Issue Type: Bug
          Components: sql
            Reporter: Maksim Zhuravkov
             Fix For: 3.0.0-beta2


Dynamic parameters used in operators require additional type cast to work properly, since calcite's type checkers use type families which consist of multiple types. 

Several examples:

{code:java}
// val is VARCHAR
INSERT INTO t1 (val, id) SELECT ? || 'asd', ?
// Error:
org.apache.ignite.sql.SqlException: IGN-SQL-6 TraceId:557e7c5d-6a7a-4e3f-ba1f-e095c662f675 Failed to validate query. From line 1, column 33 to line 1, column 42: Ambiguous operator ||(<UNKNOWN>, <CHAR(3)>). Dynamic parameter requires adding explicit type cast. Supported form(s): 
'<STRING> || <STRING>'
'<EQUIVALENT_TYPE> || <EQUIVALENT_TYPE>'
{code}


Function call:
{code:java}
"SELECT substring('asd', ?) FROM t1"

org.apache.ignite.sql.SqlException: IGN-SQL-6 TraceId:a2a35703-34e6-447f-aebd-bbd5f070eaa2 Failed to validate query. From line 1, column 8 to line 1, column 26: Ambiguous operator SUBSTRING(<CHAR(3)>, <UNKNOWN>). Dynamic parameter requires adding explicit type cast. Supported form(s): 
'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
{code}

Let's fix this behaviour by selecting the most common type in a type family. 

For the first case it means that the validator should select use a type of a specified argument, when both arguments are not set it should return an error.

Here is INTEGER is a type family - TINYINT, SMALLINT, INT, BIGINT.
For the first argument we can select VARCHAR, for the second an INTEGER.




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