pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ganeshmmahesh@gmail.com
Subject: BUG #17068: Incorrect ordering of a particular row.
Date: Tue, 22 Jun 2021 00:05:34 +0000
Message-ID: <17068-18d0626f1d26394d@postgresql.org> (raw )
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.
view thread (7+ messages) latest in thread
Message-ID: <17068-18d0626f1d26394d@postgresql.org>
Permalink: ../17068-18d0626f1d26394d@postgresql.org/
Also on: postgresql.org/message-id/17068-18d0626f1d26394d@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ganeshmmahesh@gmail.com
Subject: Re: BUG #17068: Incorrect ordering of a particular row.
In-Reply-To: <17068-18d0626f1d26394d@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox