From: Bruce Momjian <bruce@momjian.us>
To: magnus.falch@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17340: Unnest discards rows with empty or null arrays
Date: Wed, 22 Dec 2021 15:31:58 -0500
Message-ID: <20211222203158.GA4015@momjian.us> (raw)
In-Reply-To: <17340-74d17bbe94cb4190@postgresql.org>
References: <17340-74d17bbe94cb4190@postgresql.org>
On Mon, Dec 20, 2021 at 10:38:28AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17340
> Logged by: Magnus Falch
> Email address: magnus.falch@gmail.com
> PostgreSQL version: 14.1
> Operating system: Ubuntu 20.04
> Description:
>
> No part of the documentation covers this behaviour and it feels incorrect.
>
> A function called in the select part of a query discarding other data does
> not make sense and feels like it breaks with expectations without being
> documented.
>
> select name,unnest(test_values.int_array) as array_item from
> (select 'test_a' as name,null :: int[] as int_array union all
> select 'test_b' as name ,array[1,2,3] as int_array ) test_values
>
> Actual result set:
> name | array_item
> test_b | 1
> test_b | 2
> test_b | 3
>
>
> Expected result set:
> name | array_item
> test_a | null
> test_b | 1
> test_b | 2
> test_b | 3
Well, this is a good one. :-) I think the reason for the difference is
how unnest() treats NULL vs arrays containing NULLs. Look at this:
SELECT null::int[];
int4
--------
(null)
SELECT array[null]::int[];
array
--------
{NULL}
-- returns nothing
SELECT unnest(null::int[]);
unnest
--------
-->
SELECT unnest(array[null]::int[]);
unnest
--------
(null)
Be aware that (null) is a null, not an array of nulls. (That last query
returns a null that was extracted from the array.) In the query you
supplied, null::int[] is a null that unnest() ignores since the null is
not in an array. If you do array[null]::int[] you get the result you
expected:
SELECT name, unnest(test_values.int_array) AS array_item
FROM (
SELECT 'test_a' AS name, ARRAY[NULL]::INT[] AS int_array
UNION ALL
SELECT 'test_b' AS name, ARRAY[1, 2, 3] AS int_array) test_values;
name | array_item
--------+------------
test_a | (null)
test_b | 1
test_b | 2
test_b | 3
I hope that helps.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
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: bruce@momjian.us, magnus.falch@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17340: Unnest discards rows with empty or null arrays
In-Reply-To: <20211222203158.GA4015@momjian.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