Question about CONCAT_NULL_OUTPUTS_STRING

[mpf82]

Why is CONCAT_NULL_OUTPUTS_STRING = True set in postgres?

The comment on CONCAT_NULL_OUTPUTS_STRING says "A NULL arg in CONCAT yields NULL by default"

Does this mean in this case, that NULL is treated as an empty string in postgres (basically being ignored), which is why this flag is true in postgres?

I'm asking, because this breaks postgres.

SELECT CONCAT('abcde', 2, NULL, 22) should output abcde222
( this is directly from the postgres docs: https://www.postgresql.org/docs/current/functions-string.html )

However, it's converted into SELECT CONCAT(COALESCE('abcde', ''), COALESCE(2, ''), COALESCE(NULL, ''), COALESCE(22, '')); which will not run in postgres, because all arguments of COALESCE must "be convertible to a common data type" (see https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL)

Which is why COALESCE(2, '') fails (int <> string).

I am not sure, what problem this flag is trying to solve.

Maybe it should be a generator setting instead of a parser setting?

[georgesittas]

EDIT: fixed in 1b62c0a and deployed with v16.3.0

---

Hey @mpf82,

Sorry for the inconvenience, I'll take a look and try to resolve this issue. The motivation behind this flag was to improve
the transpilation of CONCAT when targeting dialects that have strict requirements regarding the types of its arguments,
e.g. Trino. I must've missed the case you're referring to in my implementation; I didn't expect COALESCE to break here
(e.g. your example doesn't break in DuckDB and T-SQL, which also set the flag to True).

Thanks for the report.