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)
