Re: BUG #4509: array_cat's null behaviour is inconsistent - Mailing list pgsql-bugs

Kev <kevinjamesfield@gmail.com> writes:
>> I'm loath to change just this one thing though.  If we're going to
>> monkey with it, I think we should start by having a thought-through
>> proposal about the behavior of all the array functions for corner
>> cases like null arrays and empty arrays (cf recent discussion about
>> zero-dimensional arrays).

> If those are the only corner cases, here's something to work with.

I don't think it's quite as easy as you think ...

> Looking at http://www.postgresql.org/docs/8.3/interactive/functions-array.html,
> I'd propose that every one of those operators and functions should
> return NULL::(normal-return-type) if any of their operands are NULL,

It is *certainly* not the case that array_append and array_prepend
should return NULL when their scalar argument is NULL --- that would
break a key component of the support for null elements in arrays.
As for the array argument, consider this plpgsql code:

    declare arr int[];
            r record;
    begin
    for r in select ... loop
        arr := arr || r.intfield;
    end loop;

Right now that will "do the right thing" because array_append treats
a null array input as though it were empty.  Now we could decree that
this code is wrong and the user should have initialized arr to '{}' or
some such, but ISTM that that's basically breaking code to no purpose.
There isn't any real type-safety or other code quality benefit to be
gained from making this fail.

(After having thought about it a bit, I believe that the rationale for
having array_cat treat null arrays as it does was similar.)

> || (both array-array cases) return other array if one is empty (if
> left is empty, return right, elsif right is empty, return left)

We could do that, but note that || has two different behaviors depending
on whether the arrays have the same rank, and it throws an error if the
ranks are more than one apart.  So doing the above amounts to saying
that an empty array can have any rank depending on context.  Not sure
that I like that --- this gets back to the whole issue of exactly what
we define an empty array as meaning.  (Note that array_cat does in fact
currently behave as you suggest.  So changing it could break existing
code; but I'm thinking that there would be far fewer such cases than
for the append-a-scalar situation.)

> array_lower raise exception if array is empty (there are no dimensions
> to inquire about)
> array_upper raise exception if array is empty (there are no dimensions
> to inquire about)

Well, these beg the question: is an empty array zero-dimensional, or
is it a one-dimensional array of no elements, or perhaps both of those
as well as higher-dimensional cases where any axis has zero elements,
or ???

It's really all kind of messy ... we need to trade off simplicity of
definition, ease of use, backwards compatibility, and standards
compliance (though the standard has only 1-D arrays so it's of just
limited help here).

            regards, tom lane