[PG Bug reporting form <noreply@postgresql.org>]

The following bug has been logged on the website:

Bug reference:      19412
Logged by:          Sergey Shinderuk
Email address:      s.shinderuk@postgrespro.ru
PostgreSQL version: 17.8
Operating system:   Ubuntu 24.04
Description:        

Using this script (repro.sql):

drop table if exists a, x, y;

create table a (id int, x_id int, y_id int);
insert into a values (1, 1, 1), (1, 2, 2), (1, 3, 3);
create table x (id int, nm text, constraint pk_x_id primary key (id));
insert into x values (1, 'x1'), (2, 'x2'), (3, 'x3');
create table y (id int, nm text, constraint pk_y_id primary key (id));
insert into y values (1, 'y1'), (3, 'y3'), (4, 'y4');

select a.id, z.id
from a
join x on x.id = a.x_id
left join y on y.id = a.y_id
join lateral(select x.id
             union all
             select y.id) z on z.id is not null;

alter table y drop constraint pk_y_id;
alter table y alter column id drop not null;

select a.id, z.id
from a
join x on x.id = a.x_id
left join y on y.id = a.y_id
join lateral(select x.id
             union all
             select y.id) z on z.id is not null;

on PostgreSQL 17.8 I get:

postgres=# \i repro.sql
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
 id | id
----+----
  1 |  1
  1 |  1
  1 |  2
  1 |
  1 |  3
  1 |  3
(6 rows)

ALTER TABLE
ALTER TABLE
 id | id
----+----
  1 |  1
  1 |  1
  1 |  2
  1 |  3
  1 |  3
(5 rows)

Something seems to be wrong with IS NOT NULL optimization. v18 and master
show the same, v16 is fine.