From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: zuming.jiang@inf.ethz.ch
Subject: BUG #17982: Inconsistent results of SELECT with CTE caused by subquery comparison
Date: Mon, 19 Jun 2023 12:07:42 +0000
Message-ID: <17982-3fa239feecd6c1b3@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17982
Logged by: Zuming Jiang
Email address: zuming.jiang@inf.ethz.ch
PostgreSQL version: 16beta1
Operating system: Ubuntu 20.04
Description:
My fuzzer finds a correctness bug in Postgres, which makes Postgres return
inconsistent results. This bug still can be triggered after this fixing
(https://github.com/postgres/postgres/commit/f4c00d138f6dea4c9d8af8ec280b7edc9b0a29e1)
--- Set up database ---
create table t0 (vkey int4, c0 float8, c1 timestamp, c3 text);
create table t1 (vkey int4, pkey int4, c4 timestamp);
create view t3 as select true as c_0 from (select distinct true as c_4) as
subq_1;
insert into t0 values (2, 3.41, make_timestamp(2031, 8, 6, 13, 33, 35),
'3');
---
The fuzzer generates Test case 1:
--- Test case 1 ---
WITH cte_3 AS (select
bttextcmp(ref_24.c3, ref_24.c3) as c_0,
(ref_25.c4 <> ref_24.c1) as c_6,
case when (((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1))
or (not (ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1))))
or ((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1)) is null))
then ref_24.c0 else tanh(ref_24.c0) end as c_7
from
(t0 as ref_24
full outer join t1 as ref_25
on (ref_24.vkey = ref_25.vkey))
where (null::bool)
)
select
ref_31.c1 as c_0,
ref_30.c_7 as c_1
from
(((select
ref_28.c_0 as c_0,
ref_28.c_0 as c_1,
ref_28.c_0 as c_2,
ref_28.c_0 as c_3,
ref_28.c_0 as c_4
from
t3 as ref_28) as subq_4
right outer join cte_3 as ref_30
on (subq_4.c_1 = ref_30.c_6))
right outer join t0 as ref_31
on (ref_30.c_0 = ref_31.vkey))
where ref_31.c1 is not null;
---
Because `(ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183 where
false order by c_0 asc limit 1))` could only be TRUE, FALSE, or NULL,
`(((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183 where false
order by c_0 asc limit 1)) or (not (ref_25.vkey > (select ref_25.pkey as c_0
from t0 as ref_183 where false order by c_0 asc limit 1)))) or ((ref_25.vkey
> (select ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc
limit 1)) is null))` must be TRUE. Therefore, I replace `(((ref_25.vkey >
(select ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc
limit 1)) or (not (ref_25.vkey > (select ref_25.pkey as c_0 from t0 as
ref_183 where false order by c_0 asc limit 1)))) or ((ref_25.vkey > (select
ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc limit 1))
is null))` with TRUE, and get Test case 2:
--- Test case 2 ---
WITH cte_3 AS (select
bttextcmp(ref_24.c3, ref_24.c3) as c_0,
(ref_25.c4 <> ref_24.c1) as c_6,
case when true then ref_24.c0 else tanh(ref_24.c0) end as c_7
from
(t0 as ref_24
full outer join t1 as ref_25
on (ref_24.vkey = ref_25.vkey))
where (null::bool)
)
select
ref_31.c1 as c_0,
ref_30.c_7 as c_1
from
(((select
ref_28.c_0 as c_0,
ref_28.c_0 as c_1,
ref_28.c_0 as c_2,
ref_28.c_0 as c_3,
ref_28.c_0 as c_4
from
t3 as ref_28) as subq_4
right outer join cte_3 as ref_30
on (subq_4.c_1 = ref_30.c_6))
right outer join t0 as ref_31
on (ref_30.c_0 = ref_31.vkey))
where ref_31.c1 is not null;
---
--- Expected behavior ---
Test case 1 and Test case 2 return the same results.
--- Actual behavior ---
Test case 1 returns 1 row ({2031-08-06 13:33:35|}), while Test case returns
0 rows.
--- Postgres version ---
Github commit: 7fcd7ef2a9c372b789f95b40043edffdc611c566
Version: PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
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, zuming.jiang@inf.ethz.ch
Subject: Re: BUG #17982: Inconsistent results of SELECT with CTE caused by subquery comparison
In-Reply-To: <17982-3fa239feecd6c1b3@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