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

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Friday, April 12, 2024, PG Bug reporting form <noreply(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)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