Re: BUG #18429: Inconsistent results on similar queries with join lateral

"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