array_min / array_max behavior with NaN and NULL

[amitkdutta]

In Presto, array_max or array_min both returns NAN if at least one element in the array is NAN.

SELECT array_max(ARRAY [4.0, nan(), NULL])  --> NAN
SELECT array_min(ARRAY [4.0, nan(), NULL])  --> NAN

Wondering if the behavior is correct or not.

CC: @kaikalur @prithvip @mbasmanova

[mbasmanova]

CC: @tdcmeehan @aditi-pandit

[kaikalur]

It's unfortunate but it's what it is :( We can't change without breaking existing stuff. Maybe we lump all these things together in a legacy flag so we can fix them in native. I would generally like ARRAY_MAX having similar behavior as MAX, but its not:

maxpresto:di> select max(x) from (values 4.0,nan(),null) T(x);
 _col0
-------
   4.0


presto:di> select array_max(x) from (values array[4.0,nan(),null]) T(x);
 _col0
-------
   NaN

[mbasmanova]

@kaikalur I see that min and max ignore NULL and NaN:

presto:di> select min(x), max(x) from (values 4.0,nan(),null) T(x);
 _col0 | _col1
-------+-------
   4.0 |   4.0
(1 row)

presto:di> select min(x), max(x) from (values 4.0,null) T(x);
 _col0 | _col1
-------+-------
   4.0 |   4.0
(1 row)

array_min, array_max return NaN if any element is NaN, NULL if no element is NaN, but there is a NULL element, min/max otherwise.

[Akanksha-kedia]

@amitkdutta can someone help to assign this i want to work on this.

[tdcmeehan]

It seems like the path forward here is to fix this and gate it by a flag. I agree with @kaikalur that it seems like consistency with min/max is the way to go.

[amitkdutta]

Thanks @tdcmeehan. Let's keep the legacy behavior by default.
@mbasmanova In Velox, we want legacy and new behavior (where new is array_min and array_max matches min and max) both and keep legacy enabled by default?

[mbasmanova]

@amitkdutta Amit, ideally, we would avoid introducing legacy behavior in Velox, but it may not be possible due to existing workloads. Assuming this is not an option, let's keep the legacy behavior by default and add a flag to enable "new" behavior consistent with min and max aggregate functions.

Going forward, let's make sure to document edge cases of functions to allow for consistency check when function is introduced for the first time.