Re: Inconsistent behavior on Array & Is Null?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Hm. My idea of the index lower bound is "the smallest index for which
> there is an array member" --- so I agree with Joe that it's not very
> well defined for an empty array.

Hm. The problem is that they have other implications. Like the upper bound is
one less than the index an element added to the upper end will get, and
similarly the lower bound is one more than the index that would be assigned to
an element added on the low end.

Currently there is a lower bound and upper bound in the implementation even
for empty arrays. I have empty arrays in my table that have a lower bound of
0, and they behave slightly differently than arrays with lower bounds of 1.

> I think that maybe we ought to question these two properties:
> * empty array is different from NULL ... really? Why?
> * storing a value into an element of a NULL array yields
> a NULL array instead of a singleton array.

Well that breaks other things. Then lots of functions have to become
non-strict to work properly because they should have valid output when passed
null values. Ick.

I'm leaning towards suggesting that postgres should follow sql-99 here and
normalize all array indexes to have a lower bound of 1. Then array_lower and
array_upper become entirely unnecessary. Instead we just have array_length
which is exactly equivalent to my idea of array_upper.

--
greg