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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16548
Logged by:          Manvendra Panwar
Email address:      [hidden email]
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)

Reply | Threaded
Open this post in threaded view
|

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

kieran.mccusker
Hi

If you read the documentation https://www.postgresql.org/docs/8.3/queries-order.html you will see that nulls first is the default for desc. What you are seeing is the rows with nulls first and they can appear in any order as you have only ordered by [2] which these rows don't have. add nulls last after desc to get the order you want.

Kieran


On Tue, 21 Jul 2020 at 14:19, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16548
Logged by:          Manvendra Panwar
Email address:      [hidden email]
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)

Reply | Threaded
Open this post in threaded view
|

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

Manvendra
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;" showing something else consistently.   
 {10,14,14,14}
(5 rows)


On Tue, Jul 21, 2020 at 6:55 PM Kieran McCusker <[hidden email]> wrote:
Hi

If you read the documentation https://www.postgresql.org/docs/8.3/queries-order.html you will see that nulls first is the default for desc. What you are seeing is the rows with nulls first and they can appear in any order as you have only ordered by [2] which these rows don't have. add nulls last after desc to get the order you want.

Kieran


On Tue, 21 Jul 2020 at 14:19, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16548
Logged by:          Manvendra Panwar
Email address:      [hidden email]
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)

Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by apt.postgresql.org Repository Update
PG Bug reporting form <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Francisco Olarte
In reply to this post by Manvendra
Manvendra:

On Tue, Jul 21, 2020 at 4:47 PM Manvendra <[hidden email]> 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;" showing something 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.