[
https://issues.apache.org/jira/browse/CALCITE-5800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17737422#comment-17737422
]
ZheHu commented on CALCITE-5800:
--------------------------------
What if we don't constrain the arguments' types to be the same, like
OperandTypes.sequence(any, any), so we can do the conversion in
StandardConvertletTable.convertNvl(). Take *nvl(1, '2')* as example, it's
equivelant to *nvl(1, cast('2' as integer))*.
Hence, if arg1's type is different from arg2's, we convert the call to
*nvl(arg1, cast(arg2 as arg1Type))*. WDYT [~julianhyde]?
> 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)
