Обсуждение: BUG #16548: Order by on array element giving disparity in result

The following bug has been logged on the website:

Bug reference:      16548
Logged by:          Manvendra Panwar
Email address:      manvendra2525@gmail.com
PostgreSQL version: 12.2
Operating system:   Ubuntu 18.04.1 LTS
Description:

create table bint (a int[]);
 insert into bint values (array[14]);
 insert into bint values (array[14]);
 insert into bint values (array[10]);
 insert into bint values (array[10,14,14,14]);
 insert into bint values (array[10,14,10,10]);
 insert into bint values (array[14,14,10,14]);
 insert into bint values (array[10,14,14,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[14,14,14,14]);
 insert into bint values (array[10,14,10,10]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[14,14,14,10]);
 insert into bint values (array[14,14,14,10]);
 insert into bint values (array[14,14,14,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14]);
 insert into bint values (array[10,14,14,14]);
 insert into bint values (array[10,14]);
 commit;


postgres=# select * from bint order by a[2] desc;
       a       
---------------
 {14}
 {14}
 {10}
 {10,14,14,14}
 {10,14,10,10}
 {14,14,10,14}
 {10,14,14,14}
 {10,14}
 {10,14}
 {14,14,14,14}
 {10,14,10,10}
 {10,14}
 {10,14}
 {10,14}
 {10,14}
 {10,14}
 {14,14,14,10}
 {14,14,14,10}
 {14,14,14,14}
 {10,14}
 {10,14}
 {10,14,14,14}
 {10,14}
(23 rows)

postgres=# select * from bint order by a[2] desc limit 5;
       a       
---------------
 {14}
 {10}
 {14}
 {10,14,10,10} 
 {10,14,14,14} 
(5 rows)

PG Bug reporting form <noreply@postgresql.org> writes:
> postgres=# select * from bint order by a[2] desc limit 5;
>        a       
> ---------------
>  {14}
>  {10}
>  {14}
>  {10,14,10,10} 
>  {10,14,14,14} 
> (5 rows)

You failed to say what you think is a bug here, but I'm going
to guess that you're unhappy that this is not the same as
the first five rows of the not-limited sort output.  We do not
consider that a bug, because the sort key is very underspecified
here.  The first three rows share the same sort key (null) and
can legitimately come out in any order.  Likewise, rows with
a[2] = 14 can come out in any order.

The underlying implementation reason why it acts differently
is that sort-with-limit uses a different sorting method.

            regards, tom lane

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.