From: Plettenbacher, Tobias (LWF) <Tobias.Plettenbacher@lwf.bayern.de>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Bug or strange result of Max() on arrays containing NULL values
Date: Sat, 24 Aug 2024 12:54:26 +0000
Message-ID: <325d78c062e945ebb286091f0eaa8b72@lwf.bayern.de> (raw)
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
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: Tobias.Plettenbacher@lwf.bayern.de, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug or strange result of Max() on arrays containing NULL values
In-Reply-To: <325d78c062e945ebb286091f0eaa8b72@lwf.bayern.de>
* 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