From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: cheng.zhi.qiang@outlook.com
Subject: BUG #18847: Different Query Results with and without a Primary Key Constraint
Date: Fri, 14 Mar 2025 08:12:43 +0000
Message-ID: <18847-692e3ecb7fa1d870@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, cheng.zhi.qiang@outlook.com
Subject: Re: BUG #18847: Different Query Results with and without a Primary Key Constraint
In-Reply-To: <18847-692e3ecb7fa1d870@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox