BUG #18429: Inconsistent results on similar queries with join lateral - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18429
Logged by:          Benoît Ryder
Email address:      b.ryder@ateme.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