From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: join of array |
Date: | 2003-08-15 17:41:15 |
Message-ID: | 3F3D1B3B.5030802@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-patches |
Tom Lane wrote:
> 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.
>
Thanks -- I found the corresponding paragraph in SQL200x (6.35) and it
pretty much reads the same.
> Probably. AFAICS this doesn't affect the data copying at all, only
> the way in which the result's dimension values are computed, right?
Looks that way to me.
> 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.
This is mostly the way it currently works:
regression=# create table arr(f1 int[]);
CREATE TABLE
regression=# insert into arr values ('{}');
INSERT 2498103 1
regression=# update arr set f1[-2] = 1;
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
-2
(1 row)
regression=# select array_lower(f1 || 2, 1) from arr;
array_lower
-------------
-2
(1 row)
regression=# select array_lower(0 || f1, 1) from arr;
array_lower
-------------
-3
(1 row)
regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
UPDATE 1
regression=# select array_lower(f1,1) from arr;
array_lower
-------------
1
(1 row)
regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
array_lower
-------------
1
(1 row)
regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
array_lower
-------------
1
(1 row)
It looks like the only "wrong" case is the last one. Will fix.
> 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).
OK, will do.
Thanks,
Joe
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Nasser | 2003-08-15 17:47:50 | Re: query tuning |
Previous Message | Joe Conway | 2003-08-15 17:36:54 | Re: join of array |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-08-15 17:58:35 | Re: join of array |
Previous Message | Joe Conway | 2003-08-15 17:36:54 | Re: join of array |