[jira] [Commented] (CALCITE-5800) NVL function should respect implicit conversion among the arguments

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17737416#comment-17737416 ] 

Julian Hyde commented on CALCITE-5800:
--------------------------------------

Can you propose a design to fix this? What existing Calcite functions have the desired behavior? How do they achieve it?

> NVL function should respect implicit conversion among the arguments
> -------------------------------------------------------------------
>
>                 Key: CALCITE-5800
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5800
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.34.0
>            Reporter: ZheHu
>            Assignee: ZheHu
>            Priority: Minor
>
> The following SQLs in SqlOperatorTest would fail as the NVL function(enabled in Oracle) has "SAME_SAME" operand checker. However, they both result in "1" in Oracle.
> {code}
> select nvl(1, '2') as alia from dual;
> {code}
> {code}
> select nvl('1', 2) as alia from dual;
> {code}
> According to [Oracle-NVL|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2], if arguments' data types are different, then Oracle Database implicitly converts one to the other.
> Conversion rules(take *nvl(expr1, expr2)* as example):
> # If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1
> # If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type



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