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(dot)mccusker(at)gmail(dot)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(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)
>>
>>