[CALCITE-5741] Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres libraries)

• Type: Improvement
• Status: Closed
• Priority: Minor
• 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 08:31
• Updated: 28/Jun/24 10:58
• Resolved: 23/Jun/23 17:48

Description

This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. It separates those concatenated string values with the delimiter specified in the first function argument. It has the following syntax:

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:

select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'

One important thing to be noted is that CONCAT_WS has two NULL semantics for the separator argument in different Database products(So we need two function in Calcite to represent).

Taking the following SQL as an example:

select concat_ws(null, 'a', 'b', null, null, 'c');

• MSSQL: never returns null and treats null as empty string, so the above SQL results in "abc" (For more details, see mssql-concat_ws)

• MySQL and Postgresql: returns null when the separator is null, so the example SQL returns NULL.

Issue Links

• is related to: CALCITE-1924 Support operator "+" as string concat operator(enabled in MSSQL library)
• relates to: CALCITE-5745 CONCAT2 function (enabled in Oracle library) gets wrong result when one of the arguments is NULL
• relates to: CALCITE-5771 Apply two different NULL semantics for CONCAT function(enabled in MySQL, Postgresql, BigQuery and MSSQL)
• relates to: CALCITE-6450 Postgres CONCAT_WS function throws exception when parameter type is (<CHAR(1)>, <INTEGER ARRAY>)
• links to: GitHub Pull Request #3271