[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728910#comment-17728910
]
ZheHu commented on CALCITE-5745:
--------------------------------
Hi [~julianhyde], I also checked CONCAT function in MySQL and Postgresql, it
seems that we can't treat them as the same(currently we use CONCAT_FUNCTION to
enable it, for Oracle we use CONCAT2).
||DB Product||Argument type in CONCAT||Argument num in CONCAT||Result||
|Oracle|string|exactly 2|return null only when two operand are null, otherwise
return string(ignore null if there is a null operand)|
|MySQL|string|at least 1|returns null if any argument is null, otherwise return
string|
|MSSQL|string|between 2 and 254|always return string, null is treated as empty
string|
|Postgresql|any(cast to string)|at least 1|always return string, null is
treated as empty string|
I think we may need several tasks to finish this, what do you think?
Here are the docs in case someone needs to refer to:
*
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CONCAT.html#GUID-D8723EA5-C93A-45C3-83FB-1F3D2A4CEAF2]
*
[MySQL|https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat]
*
[MSSQL|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16]
* [Postgresql|https://www.postgresql.org/docs/current/functions-string.html]
> CONCAT2 function(enabled in Oracle library) gets wrong result when one of the
> argument is NULL
> ----------------------------------------------------------------------------------------------
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: ZheHu
> Assignee: ZheHu
> Priority: Major
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
