Re: select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}

On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

>
> > On 08 Aug 2016, at 20:19, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
> >> I wonder, why the following returns NULL and not 0 in 9.5.3?
> >
> >> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
> >
> > Because the result of the array_remove is an empty array, which is
> > defined to be zero-dimensional in PG.
>
> Reading this, I'm a bit confused about why:
> select array_remove(ARRAY[NULL, NULL, NULL], NULL);
>
> Results in:
>
> array_remove
> --------------
> {}
> (1 row)
>
> How does it now which unknown value to remove from that array of unknown
> values? Shouldn't the result be:
> {NULL,NULL,NULL}?
>

​Is this a philosophical or technical question?

For the former I don't see why one would choose to define this function in
any other way. If you accept that the searching value can be NULL then it
follows that you must compare two NULLs as equal. If you don't accept that
comparison then specifying NULL should result in an error (if you really
don't want to remove anything don't call the function). Having it error
when useful behavior can be defined seems wasteful - this way there isn't a
need to write a "strip_nulls" function.

For the later its pretty much a simple "if (source is null and target is
null) then {remove} else if (compares equal using equality operator) then {
remove } else { leave }"

see /src/backend/utils/adt/arrayfuncs(dot)c(at)6098

David J.