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>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18429: Inconsistent results on similar queries with join
 lateral
In-reply-to: 
 <CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
References: <18429-8982d4a348cc86c6@postgresql.org>
 <CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 12 Apr 2024 06:52:54 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <441099.1712933417.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 12 Apr 2024 10:50:17 -0400
Message-ID: <441100.1712933417@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/441100.1712933417%40sss.pgh.pa.us>
Precedence: bulk

"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 =3D (12 - 6) / 4 =E2=
=86=92 false
>> select 1 from c2 where (14 - c2.t) / c2.d =3D (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=3D0.16..3.48 rows=3D1 width=3D4)
   Filter: (((14 - COALESCE(t.wt, 0)) / arrayd.ad) =3D ((12 - COALESCE(t.w=
t, 0)) / arrayd.ad))
   ->  Function Scan on unnest arrayd  (cost=3D0.00..0.01 rows=3D1 width=3D=
4)
   ->  Limit  (cost=3D0.15..3.45 rows=3D1 width=3D4)
         ->  Index Only Scan Backward using t_pkey on t  (cost=3D0.15..36.=
35 rows=3D11 width=3D4)
               Index Cond: (wd =3D arrayd.ad)

while v15 produces

 Nested Loop Left Join  (cost=3D0.16..3.50 rows=3D1 width=3D4)
   Filter: (((14 - COALESCE(c.t, 0)) / arrayd.ad) =3D ((12 - COALESCE(c.t,=
 0)) / arrayd.ad))
   ->  Function Scan on unnest arrayd  (cost=3D0.00..0.01 rows=3D1 width=3D=
4)
   ->  Subquery Scan on c  (cost=3D0.15..3.47 rows=3D1 width=3D4)
         Filter: (((14 - COALESCE(c.t, 0)) / arrayd.ad) =3D ((12 - COALESC=
E(c.t, 0)) / arrayd.ad))
         ->  Limit  (cost=3D0.15..3.45 rows=3D1 width=3D4)
               ->  Index Only Scan Backward using t_pkey on t  (cost=3D0.1=
5..36.35 rows=3D11 width=3D4)
                     Index Cond: (wd =3D 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