[CALCITE-5745] CONCAT2 function (enabled in Oracle library) gets wrong result when one of the arguments is NULL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: ZheHu
• Reporter: ZheHu
• Created: 02/Jun/23 15:09
• Updated: 27/Jul/23 10:22
• Resolved: 11/Jun/23 01:07

Description

Tested CONCAT function in Oracle and got the corresponding results.

select concat('a', cast(null as varchar(20))) from DUAL;
--- results in 'a'

select concat('a', null) from DUAL;
select 'a' ||  null from DUAL;
--- both result in 'a'

In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function gets null when running:

concat('a', cast(null as varchar))

Issue Links

• is related to: CALCITE-815 Add an option to allow empty strings to represent null values
• is related to: CALCITE-5741 Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres libraries)
• links to: GitHub Pull Request #3241