BUG #17068: Incorrect ordering of a particular row. - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #17068: Incorrect ordering of a particular row.
Date
Msg-id 17068-18d0626f1d26394d@postgresql.org
Whole thread Raw
Responses Re: BUG #17068: Incorrect ordering of a particular row.
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17068
Logged by:          ganesh mahesh
Email address:      ganeshmmahesh@gmail.com
PostgreSQL version: 10.15
Operating system:   Ubuntu
Description:

Version:
                                                               version
                                                         

--------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Query in question: 
SELECT ALL nullnamesb.id, alias3.* FROM nullnamesb  RIGHT JOIN (SELECT ALL
alias1.yearsTenured, alias1.firstName FROM nullnames alias1 ORDER BY
alias1.firstName, alias1.yearsTenured) AS alias1 ON
((nullnamesb.yearsTenured <= alias1.yearsTenured) OR (nullnamesb.id =
alias1.yearsTenured)) LEFT JOIN (SELECT DISTINCT alias3.lastName FROM
nullnamesb alias3 ORDER BY alias3.lastName) AS alias3 ON
(((nullnamesb.lastName != alias3.lastName)) OR NOT (nullnamesb.salary <= ANY
(SELECT DISTINCT alias4.salary FROM nullnames alias4 ORDER BY alias4.salary
LIMIT 1))) WHERE nullnamesb.exempt = FALSE ORDER BY alias3.*,
nullnamesb.id;

Info on the tables itself:
\d+ nullnames:

                                           Table "public.nullnames"
    Column    |         Type          | Collation | Nullable | Default |
Storage  | Stats target | Description 
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id           | integer               |           | not null |         |
plain    |              | 
 firstname    | character varying(30) |           |          |         |
extended |              | 
 lastname     | character varying(30) |           |          |         |
extended |              | 
 salary       | numeric               |           | not null |         |
main     |              | 
 exempt       | boolean               |           | not null |         |
plain    |              | 
 yearstenured | integer               |           |          |         |
plain    |              | 
Indexes:
    "nullnames_pkey" PRIMARY KEY, btree (id)

\d+ nullnamesb:

                                           Table "public.nullnamesb"
    Column    |         Type          | Collation | Nullable | Default |
Storage  | Stats target | Description 
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id           | integer               |           | not null |         |
plain    |              | 
 firstname    | character varying(30) |           |          |         |
extended |              | 
 lastname     | character varying(30) |           |          |         |
extended |              | 
 salary       | numeric               |           | not null |         |
main     |              | 
 exempt       | boolean               |           | not null |         |
plain    |              | 
 yearstenured | integer               |           | not null |         |
plain    |              | 
Indexes:
    "nullnamesb_pkey" PRIMARY KEY, btree (id)

Data in the table:
select * from nullnames;

 id | firstname | lastname |   salary   | exempt | yearstenured 
----+-----------+----------+------------+--------+--------------
  0 | Zero      | Cool     |   25000.01 | t      |           10
  1 | Acid      | Burn     |   62530.56 | f      |            5
  2 | Cereal    | Killer   |          0 | f      |             
  3 | Lord      | Nikon    | 2000567.49 | t      |            2
  4 | Joey      |          |          0 | f      |             
  5 | Zero      | Cool     |   25000.01 | t      |           10
(6 rows)

select * from nullnamesb;
nullnames=> select * from nullnamesb;
 id | firstname | lastname |   salary   | exempt | yearstenured 
----+-----------+----------+------------+--------+--------------
  0 | Zero      | Cool     |   25000.01 | f      |           20
  1 | Acid      | Burn     |   62530.56 | f      |            5
  2 | Cereal    | Killer   |          0 | t      |            0
  3 | Lord      | Nikon    | 2000567.49 | f      |            2
  4 | Joey      |          |          0 | f      |            0
  5 | Zero      | Cool     |   25000.01 | f      |           20
(6 rows)

Partial Query result:
```
.
.
  5 | Nikon
  1 | 
  1 | 
  1 | 
  3 | 
  3 | 
  3 | 
  3 | 
  5 | 
  4 | 
  4 | 
  4 | 
  4 | 
(44 rows)
```

Problem: 
`5|` ordering is incorrect.

Result expected:
`5|` row should be the last row in the output.


pgsql-bugs by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: BUG #17059: postgresql 13 version problem related to query.
Next
From: Thomas Munro
Date:
Subject: Re: BUG #17058: Unable to create collation in version 13.