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

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(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16548
> Logged by: Manvendra Panwar
> Email address: manvendra2525(at)gmail(dot)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)
>
>