Thread: BUG #18847: Different Query Results with and without a Primary Key Constraint

The following bug has been logged on the website:

Bug reference:      18847
Logged by:          zhiqiang cheng
Email address:      cheng.zhi.qiang@outlook.com
PostgreSQL version: 16.1
Operating system:   Ubuntu 20.04
Description:

Description:
When executing a RIGHT JOIN query involving multiple tables, the presence or
absence of a primary key constraint on one of the tables (t2) affects the
query result. The expectation is that adding or removing an index or
constraint should not change the logical query result, yet in this case, the
query returns different results.

Steps to Reproduce:
Create test tables and insert data:
CREATE TABLE t0 (
    c0 numeric,
    c1 timestamp without time zone
);

INSERT INTO t0 (c0, c1) VALUES
(0.0, '1980-12-24 16:02:50');

CREATE TABLE t1 (
    c2 numeric,
    c3 timestamp without time zone,
    c4 integer
);

INSERT INTO t1 (c2, c3, c4) VALUES
( -4.8, '2026-12-11 03:35:51',5);

CREATE TABLE t2 (
    c5 integer
);

INSERT INTO t2 (c5) VALUES
(1);

query1 (with primary key constraint on t2):
ALTER TABLE ONLY t2
    ADD CONSTRAINT t2_key PRIMARY KEY (c5);

select 
  ref_1.c1 as c_1, 
  ref_2.c3 as c_2
from 
  (((select  
            1 as c_1
           FROM (t2 ref_0
             RIGHT JOIN t1 ref_3 ON ((ref_0.c5 = ref_3.c4)))
           as ref_4
            ) as subq_0
      right  join t0 as ref_1
      on (subq_0.c_1 = ref_1.c0 ))
    right  join t1 as ref_2
    on (subq_0.c_1 = ref_2.c2 ))
where ( ( ((ref_1.c1) <= (ref_2.c3))));

output:
         c_1         |         c_2         
---------------------+---------------------
 1980-12-24 16:02:50 | 2026-12-11 03:35:51
(1 row)

query2 (Drop the primary key constraint,execute the same query again):
ALTER TABLE ONLY t2
    DROP CONSTRAINT t2_key;

select 
  ref_1.c1 as c_1, 
  ref_2.c3 as c_2
from 
  (((select  
            1 as c_1
           FROM (t2 ref_0
             RIGHT JOIN t1 ref_3 ON ((ref_0.c5 = ref_3.c4)))
           as ref_4
            ) as subq_0
      right  join t0 as ref_1
      on (subq_0.c_1 = ref_1.c0 ))
    right  join t1 as ref_2
    on (subq_0.c_1 = ref_2.c2 ))
where ( ( ((ref_1.c1) <= (ref_2.c3))));

output:
 c_1 | c_2 
-----+-----
(0 rows)   

Expected behavior:
Regardless of whether an index is added or not, the results of the query
should not change

Actual behavior:
query results changed

Postgres version:
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
Version:  PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

Operating system:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17
13:27:16
UTC 2025 x86_64 x86_64 x86_64 GNU/Linux

The following bug has been logged on the website:

Bug reference:      18847
Logged by:          zhiqiang cheng
Email address:      cheng.zhi.qiang@outlook.com
PostgreSQL version: 16.1
Operating system:   Ubuntu 20.04
Description:       

Postgres version:
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
Version:  PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit