Re: Inconsistent behavior on Array & Is Null?

Joe Conway <mail(at)joeconway(dot)com> writes:

> Same here; this would be:
>
> array_upper(a || b) == array_upper(a) + CARDINALITY(b)
>
> and would work just fine. Note that if array-a is NULL, then the spec defines a
> || b as NULL. See section 6.35:

Why are you talking about when a is NULL? The original question was for when a
was an empty array. That's not an unknown value, it's known to be an array
containing no elements.

It sounds like this is the same type of confusion that led to Oracle treating
empty strings as NULL, which causes no end of headaches.

> Anyway, CARDINALITY is what you really need -- hopefully I'll be able to find
> time to address that and some ather array items before the 7.5 freeze. In the
> meantime, if you have a custom array_length function already, why not make it
> return 0 for empty arrays -- then your problems disappear:
>
> create or replace function array_length(anyarray)
> returns int as '
> select
> case
> when $1 = ''{}'' then 0
> else array_upper($1, 1) - array_lower($1, 1) + 1
> end
> ' language sql;

My argument was that having to write a special case here makes it pretty clear
the idea of equating {} with NULL is the wrong interface.

--
greg