From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: b.ryder@ateme.com <b.ryder@ateme.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18429: Inconsistent results on similar queries with join lateral
Date: Fri, 12 Apr 2024 10:50:17 -0400
Message-ID: <441100.1712933417@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
References: <18429-8982d4a348cc86c6@postgresql.org>
<CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, April 12, 2024, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> -- `where` clause should return false: (14 - 6) / 4 = (12 - 6) / 4 → false
>> select 1 from c2 where (14 - c2.t) / c2.d = (12 - c2.t) / c2.
> You are doing integer division here and the right hand side equals, 1.5; I
> suppose something may have used to round that up to the integer 2 which
> would make both sides equals but now (v16) rounds it down (or more
> accurately truncates it) to 1.
I don't think that's relevant. It's clear that the older versions
are returning inconsistent answers. Comparing EXPLAIN output shows
why --- current HEAD produces (for Q1)
Nested Loop Left Join (cost=0.16..3.48 rows=1 width=4)
Filter: (((14 - COALESCE(t.wt, 0)) / arrayd.ad) = ((12 - COALESCE(t.wt, 0)) / arrayd.ad))
-> Function Scan on unnest arrayd (cost=0.00..0.01 rows=1 width=4)
-> Limit (cost=0.15..3.45 rows=1 width=4)
-> Index Only Scan Backward using t_pkey on t (cost=0.15..36.35 rows=11 width=4)
Index Cond: (wd = arrayd.ad)
while v15 produces
Nested Loop Left Join (cost=0.16..3.50 rows=1 width=4)
Filter: (((14 - COALESCE(c.t, 0)) / arrayd.ad) = ((12 - COALESCE(c.t, 0)) / arrayd.ad))
-> Function Scan on unnest arrayd (cost=0.00..0.01 rows=1 width=4)
-> Subquery Scan on c (cost=0.15..3.47 rows=1 width=4)
Filter: (((14 - COALESCE(c.t, 0)) / arrayd.ad) = ((12 - COALESCE(c.t, 0)) / arrayd.ad))
-> Limit (cost=0.15..3.45 rows=1 width=4)
-> Index Only Scan Backward using t_pkey on t (cost=0.15..36.35 rows=11 width=4)
Index Cond: (wd = arrayd.ad)
So there's the problem: a copy of the upper WHERE clause is being
inappropriately applied below the outer join, and that filters out the
only row of the "c" subselect. Then when we re-apply the WHERE at
top level, the COALESCEs produce 0 allowing the condition to evaluate
as true.
(I didn't check Q3-Q5 in detail, but probably they've got variants
of the same issue.)
"git bisect" fingers this commit as the first one producing correct
answers:
commit 2489d76c4906f4461a364ca8ad7e0751ead8aa0d
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Jan 30 13:16:20 2023 -0500
Make Vars be outer-join-aware.
This is kind of exciting for me, as IIRC it's the first field-detected
bug that that work fixes. However, I'm not real sure right now how
we might fix it in the back branches ...
regards, tom lane
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: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, b.ryder@ateme.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18429: Inconsistent results on similar queries with join lateral
In-Reply-To: <441100.1712933417@sss.pgh.pa.us>
* 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