Re: BUG #16959: Unnesting null from string_to_array silently removes whole rows from result

PG Bug reporting form <noreply@postgresql.org> writes:
> Sample data:
> create table test_rows as
> SELECT * FROM (VALUES (1, null), (2, 'second')) AS t (num,letter);

> Query with the unexpected result (I expected 2 rows):
> select num, unnest(string_to_array(letter, ',')) from test_rows;
> num | unnest
> ----+--------
>   2 | second
> (1 row)

Well, you could perhaps argue that string_to_array with NULL input
should produce an empty array rather than a NULL.  But UNNEST()
would produce zero rows in either case, and I fail to see why you
find that surprising, much less buggy.  It would be a bug if it
manufactured a value out of nothing.

Having said that, you could inject the value you prefer using
COALESCE, say

# select num, unnest(coalesce(string_to_array(letter, ','), '{""}')) from test_rows;
 num | unnest
-----+--------
   1 |
   2 | second
(2 rows)

Alternatively, perhaps you'd consider a lateral left join to be
less-surprising behavior:

# select num, u from test_rows left join lateral unnest(string_to_array(letter, ',')) u on true;
 num |   u
-----+--------
   1 |
   2 | second
(2 rows)

The behavior you're getting from SRF-in-the-targetlist is basically
equivalent to a lateral plain join, rather than left join.  See

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

            regards, tom lane