On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 08 Aug 2016, at 20:19, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Alexander Farber <alexander.farber@gmail.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 }"
