Re: BUG #17340: Unnest discards rows with empty or null arrays

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(dot)falch(at)gmail(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.