I suspected a rounding issue at some point, but using floor didn't helped. Displaying the values of `c2.t` shows more:
```
with c2 as (
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
)
select c2.t, c2.d from c2
where (14 - c2.t) / c2.d = (12 - c2.t) / c2.d;
t | d
---+---
0 | 4
(1 row)
with c2 as (
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
)
select c2.t, c2.d from c2
where true;
t | d
---+---
6 | 4
(1 row)
```
The only difference is the where clause, and it changes the returned value of `c2.t`.
If `c2.t = 0`, the where condition is true with proper integer rounding/truncation, which explains the result from Q1.
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, April 12, 2024 15:52
To: Benoit Ryder <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
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
On Friday, April 12, 2024, PG Bug reporting form <
noreply@postgresql.org> wrote:
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:
-- `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.
David J.