Re: Inconsistent behavior on Array & Is Null?

Greg Stark wrote:
> length(a) != array_upper(a)-array_lower(a)

[You really meant "array_upper(a) - array_lower(a) + 1" I'd guess]

length(A) is a missing function at the moment; the spec actually calls
it CARDINALITY. Once available, you would use it to determine array
length. SQL2003 says:

The result of <cardinality expression> is the number of elements of
the result of the <collection value expression>.

So, when A is an empty array, CARDINALITY(A) = 0, by definition.

> array_upper(a||b) == array_upper(a)+length(b)

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:

2) If <array concatenation> is specified, then let AV1 be the value of
<array value expression 1> and let AV2 be the value of
<array primary>.
Case:
a) If either AV1 or AV2 is the null value, then the result of the
<array concatenation> is the null value.
b) If the sum of the cardinality of AV1 and the cardinality of AV2
is greater than IMDC, then an exception condition is raised:
data exception — array data, right truncation.
c) Otherwise, the result is the array comprising every element of
AV1 followed by every element of AV2.

> If someone implements pop and push it sure makes things weird that push
> doesn't always increment the length pop doesn't decrement the length until 0.

I have no idea what you're trying to say here. Current behavior
certainly increments length by one when you push an element (which is
what "array || element" effectively does). An empty array has length 0
before pushing an element on to it, and length 1 afterward. Pushing an
element onto a NULL array yields NULL, which is not explicitly defined
by the spec (that I can find), but is certainly consistent with the above.

As far as array_pop is concerned, we discussed the fact that it makes no
sense in the context of Postgres arrays -- see the archives from last
year in May.

> Perhaps you're worried that you have pick an arbitrary lower and upper bound
> and, strangely, that the upper bound would actually be one less than the lower
> bound such as [1,0]. However this isn't really any different than the normal
> case. All arrays in postgres have arbitrary lower bounds.

Again, I have no idea what you mean here.

> Fwiw, the int_aggregate function from int_aggregate.c in the contrib section
> makes arrays that don't violate these invariants. For empty arrays the
> dimensions are [0,-1].

Seems rather arbitrary to me. As I said to Josh, an empty array has
undefined bounds, literally.

> This isn't hypothetical for me. Whenever I end up replacing int_aggregate with
> a standard function that constructs your style arrays my app would break. I
> guess I'll have to add a coalesce(...,0) to my array_length() function to work
> around it. Which only seems like strong evidence it's the wrong behaviour.

Sorry, but these are not "my style" arrays, they are "Berkley style" ;-).

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;

CREATE FUNCTION
regression=# select array_length(array[1,2,3]);
array_length
--------------
3
(1 row)

regression=# select array_length('{}'::int4[]);
array_length
--------------
0
(1 row)

regression=# select array[1,2,3] || '{}'::int4[];
?column?
----------
{1,2,3}
(1 row)

regression=# select array_upper(array[1,2,3], 1) +
array_length('{}'::int4[]);
?column?
----------
3
(1 row)

HTH,

Joe