[CALCITE-5771] Apply two different NULL semantics for CONCAT function(enabled in MySQL, Postgresql, BigQuery and MSSQL)

• 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: 08/Jun/23 07:18
• Updated: 27/Jul/23 10:22
• Resolved: 28/Jun/23 13:05

Description

We now have one CONCAT_FUNCTION which is enabled in MySQL, Postgresql, BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments.

The following table describes how different database products define CONCAT function:

DB Product	Argument type in CONCAT	Argument num in CONCAT	Result
MySQL	string	at least 1	returns null if any argument is null, otherwise return string
BigQuery	string	at least 1	returns null if any argument is null, otherwise return string
Postgresql	any(cast to string)	at least 1	always return string, null is treated as empty string
MSSQL	string	2 ~ 254	always return string, null is treated as empty string

For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql and MSSQL that treats NULL as empty String. And they both accept at least 1 argument.

Issue Links

• is related to: CALCITE-5741 Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres libraries)
• links to: GitHub Pull Request #3258