[
https://issues.apache.org/jira/browse/CALCITE-5884?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750033#comment-17750033
]
Mihai Budiu commented on CALCITE-5884:
--------------------------------------
Sure, here is a test from Postgres:
{code:sql}
select array_to_string(array['1','2','3','4',NULL,'6'], ',', NULL);
{code}
The result is the string '1,2,3,4,6'.
The Calcite version of this function is slightly different, because Postgres
automatically casts all arguments to string (which is very useful), but the
result is the right one for arrays of strings. In fact, the last argument being
NULL says that NULLs in the input ARRAY should be ignored, and the two-argument
version of the function just calls the three-argument function with a NULL for
the last argument.
> Nullability of of ARRAY_TO_STRING result does not depend on third argument
> nullability
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-5884
> URL: https://issues.apache.org/jira/browse/CALCITE-5884
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.35.0
> Reporter: Mihai Budiu
> Priority: Trivial
>
> This is the current definition of the function ARRAY_TO_STRING in
> SqlLibraryOperators:
> {code:java}
> /** The "ARRAY_TO_STRING(array, delimiter [, nullText ])" function. */
> @LibraryOperator(libraries = {BIG_QUERY})
> public static final SqlFunction ARRAY_TO_STRING =
> SqlBasicFunction.create(SqlKind.ARRAY_TO_STRING,
> ReturnTypes.VARCHAR_NULLABLE,
> OperandTypes.STRING_ARRAY_CHARACTER_OPTIONAL_CHARACTER);
> {code}
> So the result is nullable if any of the arguments is nullable. However, the
> nullability of the last argument does not influence the result nullabillity:
> a NULL value for the third optional argument will not cause a NULL value to
> be output.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
