Horton
Menu

Thread

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

    PG Bug reporting form <noreply@postgresql.org> — 2020-07-21T12:52:45Z

    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)

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

    Kieran McCusker <kieran.mccusker@gmail.com> — 2020-07-21T13:25:16Z

    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)
>
>

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

    Manvendra <manvendra2525@gmail.com> — 2020-07-21T13:51:30Z

    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)
>>
>>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-07-21T14:15:15Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> 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)

You failed to say what you think is a bug here, but I'm going
to guess that you're unhappy that this is not the same as
the first five rows of the not-limited sort output.  We do not
consider that a bug, because the sort key is very underspecified
here.  The first three rows share the same sort key (null) and
can legitimately come out in any order.  Likewise, rows with
a[2] = 14 can come out in any order.

The underlying implementation reason why it acts differently
is that sort-with-limit uses a different sorting method.

			regards, tom lane

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

    Francisco Olarte <folarte@peoplecall.com> — 2020-07-21T15:10:31Z

    Manvendra:

On Tue, Jul 21, 2020 at 4:47 PM Manvendra <manvendra2525@gmail.com> wrote:
> 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)

It comes in a different order because your query does not fully order
the rows, either with or without limit.

Your order field, a[2], is null, null, null, 14, 14, 14. The server is
free to shuffle the set of rows in any order in the groups which have
the same value for a[2]. It does not shuffle, as it would be a waste
of time, but it is also free to do the following:

1.- Without limit: build the result set and use a quick sort. Or do an
index scan. Or do a stable merge sort. Or a heap sort.
2.- With limit: Scan the rows keeping the top 5 ( this is easy to do
with a heap ), no need to keep all the rows ( I think this comes out
as top-n heapsort or something similar in EXPLAIN ).

It is like what happens if I handle you a shuffled deck of cards and
tell you to order by rank, You will produce 4 aces, for deuces, ...
but the suits will be unordered in each group ( unless you decide to
work extra ). Also, if I ask you to pick the top 6 you may just spread
them on the tabla and handle me the four aces and two deuces, but they
may be in a different order than what you returned on the first
problem as you used a different method more suited for this problem.

Francisco Olarte.