pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17340: Unnest discards rows with empty or null arrays
5+ messages / 4 participants
[nested] [flat]
* BUG #17340: Unnest discards rows with empty or null arrays
@ 2021-12-20 10:38 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2021-12-20 10:38 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: magnus.falch@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17340: Unnest discards rows with empty or null arrays
@ 2021-12-22 20:31 Bruce Momjian <bruce@momjian.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2021-12-22 20:31 UTC (permalink / raw)
To: magnus.falch@gmail.com; pgsql-bugs@lists.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17340: Unnest discards rows with empty or null arrays
@ 2021-12-22 20:43 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-12-22 20:43 UTC (permalink / raw)
To: magnus.falch@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Dec 20, 2021 at 3:42 AM PG Bug reporting form <
noreply@postgresql.org> 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
Yes, this is under-documented.
and it feels incorrect.
>
That may be, but this is how it works. Placing any set-returning function
call directly in the select target list performs an INNER JOIN. The
LATERAL JOIN feature was added to allow the user more flexibility - the
syntax you are complaining about should be considered obsolete.
While this hasn't been complained about in a while you are the second to
question it in the past month or so:
https://www.postgresql.org/message-id/flat/15736efd9ae84d9ebbef14e3e4e8e844%40politie.nl#14f68d4b9a1...
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17340: Unnest discards rows with empty or null arrays
@ 2021-12-22 20:48 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-12-22 20:48 UTC (permalink / raw)
To: magnus.falch@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 1:43 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Mon, Dec 20, 2021 at 3:42 AM PG Bug reporting form <
> noreply@postgresql.org> 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
>
>
> Yes, this is under-documented.
>
>
Specifically, it is documented here [1].
[1]
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17340: Unnest discards rows with empty or null arrays
@ 2021-12-22 21:26 Magnus Falch <magnus.falch@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Magnus Falch @ 2021-12-22 21:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
I got an explanation from depesz that boils down to what Bruce explained.
Returning an unambiguous answer and discarding data is more explicit than
the other alternatives and is therefore the optimal compromise.
I submitted a doc patch suggestion for this.
Once documented and explained it's not an issue. And coalescing the target
array to an array containing the desired failover value is a good & easy
workaround to get what corresponds to what my gut feeling expected.
On Wed, Dec 22, 2021, 21:48 David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Wed, Dec 22, 2021 at 1:43 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Mon, Dec 20, 2021 at 3:42 AM PG Bug reporting form <
>> noreply@postgresql.org> 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
>>
>>
>> Yes, this is under-documented.
>>
>>
> Specifically, it is documented here [1].
>
> [1]
> https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
>
> David J.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-12-22 21:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-20 10:38 BUG #17340: Unnest discards rows with empty or null arrays PG Bug reporting form <noreply@postgresql.org>
2021-12-22 20:31 ` Bruce Momjian <bruce@momjian.us>
2021-12-22 20:43 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-22 20:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-22 21:26 ` Magnus Falch <magnus.falch@gmail.com>
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