From: Tom Lane <tgl@sss.pgh.pa.us>
To: posuch@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16959: Unnesting null from string_to_array silently removes whole rows from result
Date: Sun, 11 Apr 2021 20:46:37 -0400
Message-ID: <2734126.1618188397@sss.pgh.pa.us> (raw)
In-Reply-To: <16959-4c11c2e812e2b52c@postgresql.org>
References: <16959-4c11c2e812e2b52c@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, posuch@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16959: Unnesting null from string_to_array silently removes whole rows from result
In-Reply-To: <2734126.1618188397@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox