pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17068: Incorrect ordering of a particular row.
7+ messages / 4 participants
[nested] [flat]
* BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 00:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2021-06-22 00:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ganeshmmahesh@gmail.com
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 13:06 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 7+ messages in thread
From: David Rowley @ 2021-06-22 13:06 UTC (permalink / raw)
To: ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, 22 Jun 2021 at 21:39, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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.
That seems very bug-like to me. Thanks for reporting it.
I've attached an SQL file to make it easier to reproduce.
The top-level sort does appear to contain all the correct columns and
as far as I can tell the sort operation is using all the correct
functions during the comparison. record_cmp() seems to be correctly
returning 0 when the final columns being compared are both NULL.
Putting some debug inside btint4fastcmp(), it seems to receive:
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 1, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
It seems a bit fishy that the only 4s that appear here are being
compared to 4. How does qsort figure out that 5 > 4 or 4 < 5?
David
Attachments:
bug17068.sql (application/octet-stream, 2.2K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 13:25 Thomas Munro <thomas.munro@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Thomas Munro @ 2021-06-22 13:25 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jun 23, 2021 at 1:07 AM David Rowley <dgrowleyml@gmail.com> wrote:
> I've attached an SQL file to make it easier to reproduce.
If you change the SELECT list to output alias3::text, there's a
different value there:
id | alias3
----+----------
1 | (Burn)
1 | (Burn)
1 | (Burn)
3 | (Burn)
3 | (Burn)
3 | (Burn)
3 | (Burn)
5 | (Burn)
1 | (Cool)
1 | (Cool)
1 | (Cool)
3 | (Cool)
3 | (Cool)
3 | (Cool)
3 | (Cool)
5 | (Cool)
1 | (Killer)
1 | (Killer)
1 | (Killer)
3 | (Killer)
3 | (Killer)
3 | (Killer)
3 | (Killer)
5 | (Killer)
1 | (Nikon)
1 | (Nikon)
1 | (Nikon)
3 | (Nikon)
3 | (Nikon)
3 | (Nikon)
3 | (Nikon)
5 | (Nikon)
1 | ()
1 | ()
1 | ()
3 | ()
3 | ()
3 | ()
3 | ()
5 | ()
4 |
4 |
4 |
4 |
(44 rows)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 13:32 David Rowley <dgrowleyml@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: David Rowley @ 2021-06-22 13:32 UTC (permalink / raw)
To: ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, 23 Jun 2021 at 01:06, David Rowley <dgrowleyml@gmail.com> wrote:
> It seems a bit fishy that the only 4s that appear here are being
> compared to 4. How does qsort figure out that 5 > 4 or 4 < 5?
Even if I change the < 7 qsort optimization in sort_template.h
(working in master here) to be < 7000 so we bubble sort everything
here, I still don't see 4 being compared to any other value. Something
else must be going on.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 13:46 David Rowley <dgrowleyml@gmail.com>
parent: Thomas Munro <thomas.munro@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-06-22 13:46 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@gmail.com>; +Cc: ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, 23 Jun 2021 at 01:26, Thomas Munro <thomas.munro@gmail.com> wrote:
> If you change the SELECT list to output alias3::text, there's a
> different value there:
Yeah, if I change it to ORDER BY alias3.lastname, nullnamesb.id;
instead of ORDER BY alias3.*, nullnamesb.id; then the tie break using
btint4fastcmp() is called more often. It's also getting called with
the missing 4s which I mentioned upthread.
See the two outputs. That points me towards something weird going on
in record_cmp().
David
Attachments:
order_by_alias3_lastname.txt (text/plain, 2.6K) download
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 4, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 1, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 4, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 4, b = 3, compare = 1
NOTICE: a = 4, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 4, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 4, b = 3, compare = 1
NOTICE: a = 4, b = 5, compare = -1
NOTICE: a = 5, b = 4, compare = 1
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 5, b = 4, compare = 1
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 5, b = 4, compare = 1
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
order_by_alias3_star.txt (text/plain, 2.2K) download
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 1, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 4, b = 4, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 3, b = 5, compare = -1
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 5, b = 3, compare = 1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 3, compare = -1
NOTICE: a = 3, b = 3, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
NOTICE: a = 1, b = 1, compare = 0
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 14:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-06-22 14:00 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Thomas Munro <thomas.munro@gmail.com>; ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 23 Jun 2021 at 01:26, Thomas Munro <thomas.munro@gmail.com> wrote:
>> If you change the SELECT list to output alias3::text, there's a
>> different value there:
> Yeah, if I change it to ORDER BY alias3.lastname, nullnamesb.id;
> instead of ORDER BY alias3.*, nullnamesb.id; then the tie break using
> btint4fastcmp() is called more often. It's also getting called with
> the missing 4s which I mentioned upthread.
> See the two outputs. That points me towards something weird going on
> in record_cmp().
I believe Munro's point is that in some rows alias3.* is a NULL composite
value, while in other rows it is a composite containing one NULL, and they
don't sort the same. Presumably the former are from left-join extension
while the latter come from actual table rows having NULL in that column.
(I'd suspected something of the kind, but being caffeine-deprived I'd
first added "alias3.* IS NULL" to the query, which of course fails to
expose the difference. Thanks SQL.)
In short, I see no bug here. It is kind of obscure though.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17068: Incorrect ordering of a particular row.
@ 2021-06-22 14:19 David Rowley <dgrowleyml@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: David Rowley @ 2021-06-22 14:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Munro <thomas.munro@gmail.com>; ganeshmmahesh@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, 23 Jun 2021 at 02:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> (I'd suspected something of the kind, but being caffeine-deprived I'd
> first added "alias3.* IS NULL" to the query, which of course fails to
> expose the difference. Thanks SQL.)
>
> In short, I see no bug here. It is kind of obscure though.
Oh right. Thanks for looking.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-06-22 14:19 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-22 00:05 BUG #17068: Incorrect ordering of a particular row. PG Bug reporting form <noreply@postgresql.org>
2021-06-22 13:06 ` David Rowley <dgrowleyml@gmail.com>
2021-06-22 13:25 ` Thomas Munro <thomas.munro@gmail.com>
2021-06-22 13:46 ` David Rowley <dgrowleyml@gmail.com>
2021-06-22 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-22 14:19 ` David Rowley <dgrowleyml@gmail.com>
2021-06-22 13:32 ` David Rowley <dgrowleyml@gmail.com>
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