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

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

     [ https://issues.apache.org/jira/browse/IGNITE-21035?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Maksim Zhuravkov updated IGNITE-21035:
--------------------------------------
    Labels: ignite-3  (was: )

> 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
>            Priority: Major
>              Labels: ignite-3
>             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)