Re: Inconsistent behavior on Array & Is Null? - Mailing list pgsql-hackers

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
thevalue of     <array primary>.     Case:     a) If either AV1 or AV2 is the null value, then the result of the
<arrayconcatenation> is the null value.     b) If the sum of the cardinality of AV1 and the cardinality of AV2
isgreater 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