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.
