Re: join of array

Joe Conway <mail(at)joeconway(dot)com> writes:
> Hmmm, it made sense to me, at at least at some point ;-). Here's the
> SQL99 guidance (SQL200X doesn't give any more detailed guidance):

> 4.11.3.2 Operators that operate on array values and return array values
> <array concatenation> is an operation that returns the array value made
> by joining its array value operands in the order given.

That's about as clear as mud :-( ... but I found a clearer statement in
SQL99 6.31:

2) If <array concatenation> is specified, then:

a) Let AV1 be the value of <array value expression 1> and let
AV2 be the value of <array value expression 2>.

b) If either AV1 or AV2 is the null value, then the result of
the <array concatenate function> is the null value.

c) Otherwise, the result is the array comprising every element
of AV1 followed by every element of AV2.

(c) seems to be pretty clearly what Pavel wants for the 1-D case, but
it's not immediately clear how to apply it to multidimensional arrays.

> We also have
> ARRAY[1,2] || 3 == '{1,2,3}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[5,6] == '{{1,2},{3,4},{5,6}}'
> and
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{{1,2},{3,4}},{{1,2},{3,4}}}'

> I think the first two still make sense. I guess the third case ought to be:
> ARRAY[[1,2],[3,4]] || ARRAY[[1,2],[3,4]] ==
> '{{1,2},{3,4},{1,2},{3,4}}'
> ?

Probably. AFAICS this doesn't affect the data copying at all, only the
way in which the result's dimension values are computed, right?

Also, we might want to take another look at the rules for selecting the
lower-bounds of the result array. In the cases where we're joining
N+1-D to N-D (including 1-D to scalar) it still seems to make sense to
preserve the subscripts of the higher-dimensional object, so the lower-
dimensional one is "pushed" onto one end or the other. In the N-D to
N-D case I can't see any really principled way to do it; for lack of
a better idea, I suggest preserving the subscripts of the lefthand
input (ie, using its lower-bound).

regards, tom lane