Re: UNNEST result order vs Array data

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