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

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)