Thread: Bug or strange result of Max() on arrays containing NULL values
Bug or strange result of Max() on arrays containing NULL values
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
"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
I often use Max(ARRAY[]) to get the ID of the maximum value.