pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug or strange result of Max() on arrays containing NULL values
3+ messages / 3 participants
[nested] [flat]
* Bug or strange result of Max() on arrays containing NULL values
@ 2024-08-24 12:54 Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>
2024-08-24 14:54 ` Re: Bug or strange result of Max() on arrays containing NULL values Tom Lane <tgl@sss.pgh.pa.us>
2024-08-24 15:28 ` Re: Bug or strange result of Max() on arrays containing NULL values David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Plettenbacher, Tobias (LWF) @ 2024-08-24 12:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
I'm getting strange results when using the Max() aggregate function on arrays containing NULL values.
With Max(Value) I get the expected result (in this case 2):
SELECT Max(Val) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val, ID);
With Max(ARRAY[]) I get a strange result (in this case {NULL,7}):
SELECT Max(ARRAY[Val, ID]) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val, ID);
But with Min(ARRAY[]) I get the expected result (in this case {-2,6}):
SELECT Min(ARRAY[-Val, ID]) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val, ID);
Is this a bug or the correct result of Max(ARRAY[]), i.e. should Max() return NULL as the maximum value?
I often use Max(ARRAY[]) to get the ID of the maximum value. As a workaround
I must use (Min(ARRAY[-Val, ID]))[2] or (Max(ARRAY[Coalesce(Val, 0), ID]))[2].
I'm using PostgreSQL 13.16 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit
With kind regards
Tobias Plettenbacher
SB
Abt.3
LWF
Hans-Carl-von-Carlowitz-Platz 1
85354 Freising
Telefon +49 8161 4591-317
Tobias.Plettenbacher@lwf.bayern.de
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug or strange result of Max() on arrays containing NULL values
2024-08-24 12:54 Bug or strange result of Max() on arrays containing NULL values Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>
@ 2024-08-24 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-08-24 14:54 UTC (permalink / raw)
To: Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"Plettenbacher, Tobias (LWF)" <Tobias.Plettenbacher@lwf.bayern.de> writes:
> With Max(ARRAY[]) I get a strange result (in this case {NULL,7}):
> SELECT Max(ARRAY[Val, ID]) FROM (VALUES (1, 5), (2, 6), (NULL, 7)) AS T(Val, ID);
This is the expected result, because
=# select array[null, 7] > array[2, 6];
?column?
----------
t
(1 row)
When comparing array elements (or members of any container type),
we treat two nulls as equal and a null as larger than any non-null.
You might think that such a comparison should yield null, but if
we did that then the comparisons would fail to provide a total
order for the container type. That would, among other things,
break the ability to build b-tree indexes on such types.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug or strange result of Max() on arrays containing NULL values
2024-08-24 12:54 Bug or strange result of Max() on arrays containing NULL values Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>
@ 2024-08-24 15:28 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2024-08-24 15:28 UTC (permalink / raw)
To: Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sat, Aug 24, 2024 at 5:54 AM Plettenbacher, Tobias (LWF) <
Tobias.Plettenbacher@lwf.bayern.de> wrote:
> I often use Max(ARRAY[]) to get the ID of the maximum value.
>
You should probably start writing lateral queries with limits instead of
hacking aggregation to determine rank.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-24 15:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-24 12:54 Bug or strange result of Max() on arrays containing NULL values Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>
2024-08-24 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-08-24 15:28 ` David G. Johnston <david.g.johnston@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