pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18847: Different Query Results with and without a Primary Key Constraint
2+ messages / 2 participants
[nested] [flat]
* BUG #18847: Different Query Results with and without a Primary Key Constraint
@ 2025-03-14 08:12 PG Bug reporting form <noreply@postgresql.org>
2025-03-14 13:50 ` BUG #18847: Different Query Results with and without a Primary Key Constraint David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-03-14 08:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cheng.zhi.qiang@outlook.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* BUG #18847: Different Query Results with and without a Primary Key Constraint
2025-03-14 08:12 BUG #18847: Different Query Results with and without a Primary Key Constraint PG Bug reporting form <noreply@postgresql.org>
@ 2025-03-14 13:50 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2025-03-14 13:50 UTC (permalink / raw)
To: cheng.zhi.qiang@outlook.com <cheng.zhi.qiang@outlook.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Friday, March 14, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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
>
16beta1 is not a valid version to be reporting bugs against or asking for
support on. Or to be running.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-03-14 13:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-14 08:12 BUG #18847: Different Query Results with and without a Primary Key Constraint PG Bug reporting form <noreply@postgresql.org>
2025-03-14 13:50 ` David G. Johnston <david.g.johnston@gmail.com>
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