Horton

Thread

  1. UNNEST result order vs Array data

    gmb <gmbouwer@gmail.com> — 2013-06-20T10:40:57Z

    Hi all
I just want to confirm something regarding UNNEST function used with arrays.
I cannot see that it is specifically mentioned in the documentation , but
maybe because it is obvious.

Is the order of the result guaranteed to be the order of the array

I.e. is it possible that:
SELECT UNNEST( ARRAY[1,2,3] )

will sometimes return:
 unnest
--------
      2
      1
      3
(3 rows)

instead of:
 unnest
--------
      1
      2
      3
(3 rows)

Help will be appreciated



--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

  2. Re: UNNEST result order vs Array data

    Vik Fearing <vik.fearing@dalibo.com> — 2013-06-20T10:45:22Z

    On 06/20/2013 12:40 PM, gmb wrote:
> Hi all
> I just want to confirm something regarding UNNEST function used with arrays.
> I cannot see that it is specifically mentioned in the documentation , but
> maybe because it is obvious.
>
> Is the order of the result guaranteed to be the order of the array

Order is never guaranteed unless you specify an ORDER BY clause.

  3. Re: UNNEST result order vs Array data

    gmb <gmbouwer@gmail.com> — 2013-06-20T11:00:33Z

    Hi Vik
Thanks for the reply. 
Can you please give me an example of how the order is specified?
I want the result of the UNNEST to be in the order of the array field
E.g.
SELECT UNNEST ( ARRAY[ 'abc' , 'ggh' , '12aa' , '444f' ] );
Should always return:

 unnest
--------
 abc
 ggh
 12aa
 444f

How should the ORDER BY be implemented in the syntax?

Regards




--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760092.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

  4. Re: UNNEST result order vs Array data

    Achilleas Mantzios <achill@matrix.gatewaynet.com> — 2013-06-20T11:15:13Z

    On 20/06/2013 13:45, Vik Fearing wrote:
> On 06/20/2013 12:40 PM, gmb wrote:
>> Hi all
>> I just want to confirm something regarding UNNEST function used with arrays.
>> I cannot see that it is specifically mentioned in the documentation , but
>> maybe because it is obvious.
>>
>> Is the order of the result guaranteed to be the order of the array
> Order is never guaranteed unless you specify an ORDER BY clause.
>
>
I really think that the order should exactly reflect the order in the array.
Think of ARRAY as Relation + ORDER. Relation + Order gives an ARRAY, 
ARRAY should give Relation *and* ORDER in return.

-- 
Achilleas Mantzios

  5. Re: UNNEST result order vs Array data

    Richard Huxton <dev@archonet.com> — 2013-06-20T11:37:57Z

    On 20/06/13 12:15, Achilleas Mantzios wrote:
> On 20/06/2013 13:45, Vik Fearing wrote:
>> On 06/20/2013 12:40 PM, gmb wrote:
>>> Hi all
>>> I just want to confirm something regarding UNNEST function used with
>>> arrays.
>>> I cannot see that it is specifically mentioned in the documentation ,
>>> but
>>> maybe because it is obvious.
>>>
>>> Is the order of the result guaranteed to be the order of the array
>> Order is never guaranteed unless you specify an ORDER BY clause.
>>
>>
> I really think that the order should exactly reflect the order in the
> array.
> Think of ARRAY as Relation + ORDER. Relation + Order gives an ARRAY,
> ARRAY should give Relation *and* ORDER in return.

An array is ordered. If you turn it into a relation then it isn't 
ordered any more until you apply ORDER BY.

An unnest() will almost certainly scan the array in-order, but once you 
embed that in a large query the ordering is no longer guaranteed.

-- 
   Richard Huxton
   Archonet Ltd

  6. Re: UNNEST result order vs Array data

    Vik Fearing <vik.fearing@dalibo.com> — 2013-06-20T12:11:35Z

    On 06/20/2013 01:00 PM, gmb wrote:
> Can you please give me an example of how the order is specified?
> I want the result of the UNNEST to be in the order of the array field
> E.g.
> SELECT UNNEST ( ARRAY[ 'abc' , 'ggh' , '12aa' , '444f' ] );
> Should always return:
>
>  unnest
> --------
>  abc
>  ggh
>  12aa
>  444f
>
> How should the ORDER BY be implemented in the syntax?

There are two ways I can think of right now.  The best, which you won't
like, is to wait for 9.4 where unnest() will most likely have a WITH
ORDINALITY option and you can sort on that.  The other is to make your
own unnest function that will return the values plus the position.  That
would look something like this:

CREATE OR REPLACE FUNCTION unnest_with_ordinality(anyarray, OUT value
anyelement, OUT ordinality integer)
  RETURNS SETOF record AS
$$
SELECT $1[i], i FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$$
LANGUAGE sql IMMUTABLE;

and then

select value from unnest_with_ordinality(ARRAY[ 'abc' , 'ggh' , '12aa' ,
'444f']) order by ordinality;

  7. Re: UNNEST result order vs Array data

    gmb <gmbouwer@gmail.com> — 2013-06-20T12:13:14Z

    >> An array is ordered. If you turn it into a relation then it isn't 
>> ordered any more until you apply ORDER BY.
>>
>> An unnest() will almost certainly scan the array in-order, but once you 
>> embed that in a large query the ordering is no longer guaranteed.

Thanks, I assumed as much. Appreciate your help.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760106.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

  8. Re: UNNEST result order vs Array data

    gmb <gmbouwer@gmail.com> — 2013-06-20T12:32:24Z

    >>   The best, which you won't 
>> like, is to wait for 9.4 where unnest() will most likely have a WITH 
>> ORDINALITY option and you can sort on that.

The fact that this type of thing is on the 9.4 roadmap indicates (to me, in
any case) that there are problems with the UNNEST functionality in the
current version (I'm running 9.2).

Thanks Vik, I'll take a look at the implementation you suggested.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760107.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

  9. Re: UNNEST result order vs Array data

    David Johnston <polobo@yahoo.com> — 2013-06-20T13:34:17Z

    gmb wrote
>>>   The best, which you won't 
>>> like, is to wait for 9.4 where unnest() will most likely have a WITH 
>>> ORDINALITY option and you can sort on that.
> 
> The fact that this type of thing is on the 9.4 roadmap indicates (to me,
> in any case) that there are problems with the UNNEST functionality in the
> current version (I'm running 9.2).
> 
> Thanks Vik, I'll take a look at the implementation you suggested.

To recap:

unnest() returns its output in the same order as the input.  Since an array
is ordered it will be returned in the same output order by unnest.  However,
since unnest() only returns a single column (though possibly of a composite
type) it cannot provide the row number in the output thus in order to
maintain the same order elsewhere in the query it is necessary to use
"ROW_NUMBER() OVER (...)" on the output of the unnest() - and before joining
it with any other unnest calls or tables - before supplying it to the rest
of the query.  The "WITH ORDINALITY" functionality proposed for 9.4 will
cause the unnest() [and other] function to output this additional column
along with the usual output.  This is, I am pretty such, a usability
enhancement that makes easier something that can be done today using
CTE/WITH and/or sub-queries.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760126.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.