SQL: CONCAT should return NULL when the field involved has a NULL value?

[astefan]

SQL Server 2017, by default, will consider null values in CONCAT() function as empty strings and return a non-null value. But, it also offers the option of changing this setting by using CONCAT_NULL_YIELDS_NULL setting. They do warn, though, that this option will be ON in a future version of SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver15. The preview docs for SQL Server 2019 does still have the option default as OFF (so null is considered empty string).

PostgreSQL will also "ignore" null values and treat them as empty strings: https://www.postgresql.org/docs/11/functions-string.html

[elasticmachine]

Pinging @elastic/es-search

[astefan]

CC @bpintea

[nik9000]

Generally I'd do what PostgreSQL does unless PostgreSQL is being crazy. It looks like PostgreSQL is fairly reasonable:

test=# SELECT CONCAT(NULL) = '';
 ?column? 
----------
 t
(1 row)

test=# SELECT CONCAT('a', NULL, 'b');
 concat 
--------
 ab
(1 row)

This looks like what SQL Server does by default too. That warning in the PostgreSQL docs seem to be about if you end up with a NULL array and you pass it to CONCAT with the VARIADIC keyword.

[matriv]

Weirdly enough for the || operator postgres behaves differently:

test=# select (null || 'marios') is null;
 ?column?
----------
 t
(1 row)

[matriv]

I would vote to stick with PostgreSQL behaviour as well here.

[costin]

+1. Let's go with Postgres.

[astefan]

master (7.0.0): 2ed6ab9
6.x (6.6.0): e187710