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

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 <kieran.mccusker@gmail.com>
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 <
> 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)
>>
>>