Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #16548: Order by on array element giving disparity in result - Mailing list pgsql-bugs

From Kieran McCusker
Subject Re: BUG #16548: Order by on array element giving disparity in result
Date
Msg-id CAGgUQ6EtOu8-puWpbC_77Ztg9HPoikYYvj2YxXYdD2L+R3kpcw@mail.gmail.com
Whole thread Raw
In response to BUG #16548: Order by on array element giving disparity in result  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #16548: Order by on array element giving disparity in result
List pgsql-bugs
Tree view
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 <noreply@postgresql.org> wrote:
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)

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #16548: Order by on array element giving disparity in result
Next
From: Charles Zeng
Date:
Subject: cannot find postgresqllogreaderadapter