Date: Wed, 22 Dec 2021 15:31:58 -0500
From: Bruce Momjian <bruce@momjian.us>
To: magnus.falch@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17340: Unnest discards rows with empty or null arrays
Message-ID: <20211222203158.GA4015@momjian.us>
References: <17340-74d17bbe94cb4190@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <17340-74d17bbe94cb4190@postgresql.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20211222203158.GA4015%40momjian.us>
Precedence: bulk

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.