You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2023/06/23 17:49:00 UTC
[jira] [Resolved] (CALCITE-5741) Add support for CONCAT_WS function (enabled in MSSQL, MySQL and Postgres)
[ https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde resolved CALCITE-5741.
----------------------------------
Resolution: Fixed
Fixed in [9df9b536|https://github.com/apache/calcite/commit/9df9b536b77463229bc46322167667c36414a828]; thanks for the PR, [~VAE]!
> Add support for CONCAT_WS function (enabled in MSSQL, MySQL and Postgres)
> -------------------------------------------------------------------------
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.34.0
> Reporter: ZheHu
> Assignee: ZheHu
> Priority: Minor
> Labels: pull-request-available
>
> 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:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> 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:
> {code:java}
> select concat_ws(null, 'a', 'b', null, null, 'c');
> {code}
> - {*}MSSQL{*}: never returns null and treats null as empty string, so the above SQL results in "abc" (For more details, see [mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16])
> - {*}MySQL and Postgresql{*}: returns null when the separator is null, so the example SQL returns NULL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)