[
https://issues.apache.org/jira/browse/CALCITE-4861?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17514886#comment-17514886
]
Sean Broeder commented on CALCITE-4861:
---------------------------------------
There is a similar issue in the query "select cast(2328602934.4917483 as
integer"
SqlServer returns "SQL Error [8115] [S0002]: Arithmetic overflow error
converting expression to data type int.".
Postgres returns "SQL Error [22003]: ERROR: integer out of range".
However Calcite returns EXPR$0=-1966364362.
Perhaps the issue is not that the casts are chained, but rather that
SqlCastFunction.inferReturnType doesn't detect an invalid cast and throw an
exception. I made a test patch and it seems that at least for INT_TYPES it
would be fairly trivial to detect this and throw an exception if the value is
out of range.
This approach solves both the original query CAST(CAST(CAST(123456 AS TINYINT)
AS INT) AS BIGINT) as well as the one I've just added.
[~julianhyde] has good points on truncating decimal types, but I have not
explored that area, perhaps it's a separate issue
> Optimisation of chained cast calls can lead to unexpected behaviour
> -------------------------------------------------------------------
>
> Key: CALCITE-4861
> URL: https://issues.apache.org/jira/browse/CALCITE-4861
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Marios Trivyzas
> Priority: Minor
>
> Simplification of Cast chained calls can lead to unexpected behaviour:
> CAST(CAST(CAST(123456 AS TINYINT) AS INT) AS BIGINT)
> is simplified to
> {noformat}
> CAST(123456 AS BIGINT){noformat}
> and returns *123456* with *BIGINT* data type, where the first inner cast as
> TINYINT should already fail because the value is out of range.
> For example, for PostgreSQL:
> {noformat}
> postgres=# select 123456::smallint::int::bigint;
> ERROR: smallint out of range{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
