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

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

ZheHu created CALCITE-5800:
------------------------------

             Summary: 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


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)