Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19412: Wrong query result with not null constraint
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: s.shinderuk@postgrespro.ru
Reply-To: s.shinderuk@postgrespro.ru, pgsql-bugs@lists.postgresql.org
Date: Tue, 17 Feb 2026 11:19:36 +0000
Message-ID: <19412-1d0318089b86859e@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19412-1d0318089b86859e%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D a.x_id
left join y on y.id =3D 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 =3D a.x_id
left join y on y.id =3D 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=3D# \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.