#1384 MEDIAN() should ignore NULLs

[Lukas Eder]

The following query yields 1:

select median(x) from unnest(array[1, 2, 3, null]) t(x);

(see also https://sourceforge.net/p/hsqldb/bugs/1383/). Unlike other aggregate functions, MEDIAN() seems to keep nulls in the aggregation set. This is probably not the expected behaviour, e.g. in the SQL standard PERCENTILE_CONT(0.5) function, where NULLs are excluded.

This flaw is particularly interesting when emulating the SQL standard FILTER clause (as supported by PostgreSQL), e.g.

select median(x) filter (x <> 4) 
from unnest(array[1, 2, 3, 4]) t(x);

In HSQLDB, the FILTER clause would be emulated as follows

select median(case when x <> 4 then x end)
from unnest(array[1, 2, 3, 4]) t(x);

In this emulation, again, NULLs are not ignored by HSQLDB, which leads to the MEDIAN() being wrong.

[Fred Toussi]

Thanks for reporting. Fixed the issue with nulls. They should be removed from the set.

The SQL Standard syntax for FILTER requires WHERE and is supported by HyperSQL:

select median(x) filter (where x <> 4)
from unnest(array[1, 2, 3, 4]) t(x);

[Lukas Eder]

Yep, I forgot the WHERE keyword. Interesting, I wasn't aware of HyperSQL supporting that. Nice!

Thanks for the quick fix