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

The following bug has been logged on the website:

Bug reference: 18429
Logged by: Benoît Ryder
Email address: b(dot)ryder(at)ateme(dot)com
PostgreSQL version: 15.6
Operating system: Debian
Description:

Hi,
I stumbled over a behavior difference between two PostgreSQL versions while
migrating from 9.4 to 15.6.
I managed to create a minimal, easy to reproduce setup, with few queries
that should produce the same result, but don't, depending on various
tweaks.
Note that I'm not an SQL/PostgreSQL expert, and may have overlooked
something or misused "join lateral".

Script used: all select queries are expected to return nothing, but they
sometimes return a single row.
```

-- Setup
drop schema weird cascade;
create schema if not exists weird;
create table weird.t (
wd int not null,
wt int not null,

primary key (wd, wt)
);
insert into weird.t values (4, 6);

-- Q1
with c2 as (
-- Return 1 row `(4, 6)` when fetched separately
select arrayd.ad d, coalesce(c.t, 0) t
from unnest(ARRAY[4]) as arrayd(ad)
left join lateral (
select wt t from weird.t
where wd = arrayd.ad
order by wt desc limit 1
) c on true
)
-- `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.d;

-- Q2 (simplified sub-query)
with c2 as (
select wd d, wt t from weird.t
)
select 1 from c2 where (14 - c2.t) / c2.d = (12 - c2.t) / c2.d;

-- Q3 (sub-select instead of `with`)
select 1 from (
select arrayd.ad d, coalesce(c.t, 0) t
from unnest(ARRAY[4]) as arrayd(ad)
left join lateral (
select wt t from weird.t
where wd = arrayd.ad
order by wt desc limit 1
) c on true
) c2
where (14 - c2.t) / c2.d = (12 - c2.t) / c2.d;

-- Q4 (remove `order by limit` from Q3)
select 1 from (
select arrayd.ad d, coalesce(c.t, 0) t
from unnest(ARRAY[4]) as arrayd(ad)
left join lateral (
select wt t from weird.t
where wd = arrayd.ad
) c on true
) c2
where (14 - c2.t) / c2.d = (12 - c2.t) / c2.d;

-- Q5 (remove `coalesce` from Q4)
select 1 from (
select arrayd.ad d, c.t t
from unnest(ARRAY[4]) as arrayd(ad)
left join lateral (
select wt t from weird.t
where wd = arrayd.ad
) c on true
) c2
where (14 - c2.t) / c2.d = (12 - c2.t) / c2.d;

```

I ran the same queries on various PostgreSQL server versions, either from
local Debian packages, or using the official docker images.
All queries are run using psql 15.6 (Debian 15.6-0+deb12u1), by copy-pasting
the script above.

Results
```
Q1 Q2 Q3 Q4 Q5 `select version();`
docker 16.2 ✓ ✓ ✓ ✓ ✓ PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
docker 16.0 ✓ ✓ ✓ ✓ ✓ PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
docker 15.6 ✗ ✓ ✗ ✗ ✓ PostgreSQL 15.6 (Debian 15.6-1.pgdg120+2) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
debian 15.6 ✗ ✓ ✗ ✗ ✓ PostgreSQL 15.6 (Debian 15.6-0+deb12u1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
debian 9.4 ✓ ✓ ✗ ✓ ✓ PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu,
compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
```

Note that the trivial version (Q2) and the version without coalesce (Q5) are
correct on every version.
When fetched separately, the inner select for c2 returns the same one-raw
table.

Thanks