Re: BUG #16548: Order by on array element giving disparity in result

Manvendra:

On Tue, Jul 21, 2020 at 4:47 PM Manvendra <manvendra2525@gmail.com> wrote:
> Alright! Just wanted to know how limit works here - How limit is showing the different output
> postgres=# select * from bint order by a[2] desc limit 5;
>        a
> ---------------
>  {14}
>  {10}
>  {14}
>  {10,14,10,10}  <-- It comes prior to 5th record and consistently whereas " select * from bint order by a[2] desc;"
showingsomething else consistently.
 
>  {10,14,14,14}
> (5 rows)

It comes in a different order because your query does not fully order
the rows, either with or without limit.

Your order field, a[2], is null, null, null, 14, 14, 14. The server is
free to shuffle the set of rows in any order in the groups which have
the same value for a[2]. It does not shuffle, as it would be a waste
of time, but it is also free to do the following:

1.- Without limit: build the result set and use a quick sort. Or do an
index scan. Or do a stable merge sort. Or a heap sort.
2.- With limit: Scan the rows keeping the top 5 ( this is easy to do
with a heap ), no need to keep all the rows ( I think this comes out
as top-n heapsort or something similar in EXPLAIN ).

It is like what happens if I handle you a shuffled deck of cards and
tell you to order by rank, You will produce 4 aces, for deuces, ...
but the suits will be unordered in each group ( unless you decide to
work extra ). Also, if I ask you to pick the top 6 you may just spread
them on the tabla and handle me the four aces and two deuces, but they
may be in a different order than what you returned on the first
problem as you used a different method more suited for this problem.

Francisco Olarte.