The following bug has been logged on the website:
Bug reference: 18261
Logged by: Zuming Jiang
Email address: zuming.jiang@inf.ethz.ch
PostgreSQL version: 16.1
Operating system: Ubuntu 20.04
Description:
My fuzzer finds a logic bug in Postgres, which makes Postgres return
inconsistent results.
--- Set up database ---
create table t0 (pkey int4, c0 numeric, primary key(c0));
create table t1 (c3 int4);
create table t5 (vkey int4, pkey int4);
create view t6 as
select
1 as c_7
from
((select
0.0 as c_0,
-4 as c_1
from
t0 as ref_0
) as subq_0
right outer join ((select
ref_3.pkey as c_6,
ref_3.c0 as c_7
from
t0 as ref_3
) as subq_1
right outer join t0 as ref_4
on (subq_1.c_7 = ref_4.c0 ))
on (subq_0.c_0 = subq_1.c_7 ))
where 'xxx' ~~* (ltrim('xxx',
(overlay('xxx', 'xxx', subq_0.c_1, subq_1.c_6))));
insert into t5 values (68, 78000);
---
The fuzzer generates Test case 1:
--- Test case 1 ---
select 1 as c_0
from
((select
ref_2.vkey as c_3,
ref_2.pkey as c_4,
ref_2.pkey as c_5
from
t5 as ref_2
) as subq_0
full outer join (select
ref_4.c_7 as c_0
from
t6 as ref_4
where null::bool) as subq_1
on (subq_0.c_3 = subq_1.c_0))
where subq_0.c_4 < (case when ((exists (
select
ref_16.c3 as c_9
from
t1 as ref_16
where null::bool))) then (subq_1.c_0 # null::int4) else
(subq_1.c_0 # null::int4) end);
Because the then branch and else branch of the CASE WHEN expression '(case
when ((exists (select ref_16.c3 as c_9 from t1 as ref_16 where null::bool)))
then (subq_1.c_0 # null::int4) else (subq_1.c_0 # null::int4) end)' are the
same (both are subq_1.c_0 # null::int4), I simplify this CASE WHEN
expression by replacing it with (subq_1.c_0 # null::int4), and get Test case
2:
--- Test case 2 ---
select 1 as c_0
from
((select
ref_2.vkey as c_3,
ref_2.pkey as c_4,
ref_2.pkey as c_5
from
t5 as ref_2
) as subq_0
full outer join (select
ref_4.c_7 as c_0
from
t6 as ref_4
where null::bool) as subq_1
on (subq_0.c_3 = subq_1.c_0 ))
where subq_0.c_4 < (subq_1.c_0 # null::int4);
--- Expected behavior ---
Test case 1 and Test case 2 return the same results.
--- Actual behavior ---
Test case 1 returns 0 rows, while Test case 2 returns 1 row.
Output of Test case 1:
c_0
-----
(0 rows)
Output of Test case 2:
c_0
-----
1
(1 row)
--- Postgres version ---
Github commit: 0eac3c798c2d223d6557a5440d7534317dbd4fa0
Version: PostgreSQL 17devel 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
