The following bug has been logged on the website:
Bug reference: 17985
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 can be reproduced even after applying the
fixing patches for
https://www.postgresql.org/message-id/flat/17976-4b638b525e9a983b%40postgresql.org
and
https://www.postgresql.org/message-id/flat/17978-12f3d93a55297266%40postgresql.org
--- Set up database ---
create table t0 (c2 text);
create table t2 (c10 text);
create table t5 (vkey int4, pkey int4, c27 text, c28 text, c29 text, c30
text);
insert into t0 values ('');
insert into t2 values ('');
insert into t5 values (1, 2, 'a', 'a', 'a', 'a'), (0, 1, '', '', 'a',
'L');
---
The fuzzer generates Test case 1:
--- Test case 1 ---
select * from t5
where (t5.pkey >= t5.vkey) <> (t5.c30 = (
select
t5.c29 as c_0
from
(t2 as ref_0
inner join t0 as ref_1
on (ref_0.c10 = ref_1.c2))
where ((case when (((ref_0.c10 like 'z~%')
and (not (ref_0.c10 like 'z~%')))
and ((ref_0.c10 like 'z~%') is not null)) then
t5.c28 else t5.c28 end)
= (case when (((ref_1.c2 not like '_%%')
and (not (ref_1.c2 not like '_%%')))
and ((ref_1.c2 not like '_%%') is not null)) then
t5.c29 else t5.c27 end))
order by c_0 desc limit 1));
---
Because the then branch and else branch of the CASE WHEN expression '((case
when (((ref_0.c10 like 'z~%') and (not (ref_0.c10 like 'z~%'))) and
((ref_0.c10 like 'z~%') is not null)) then t5.c28 else t5.c28 end)' are the
same (both are t5.c28), I simplify this CASE WHEN expression by replacing it
with t5.c28, and get Test case 2:
--- Test case 2 ---
select * from t5
where (t5.pkey >= t5.vkey) <> (t5.c30 = (
select
t5.c29 as c_0
from
(t2 as ref_0
inner join t0 as ref_1
on (ref_0.c10 = ref_1.c2))
where (t5.c28
= (case when (((ref_1.c2 not like '_%%')
and (not (ref_1.c2 not like '_%%')))
and ((ref_1.c2 not like '_%%') is not null)) then
t5.c29 else t5.c27 end))
order by c_0 desc limit 1));
---
--- 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:
vkey | pkey | c27 | c28 | c29 | c30
------+------+-----+-----+-----+-----
(0 rows)
Output of Test case 2:
vkey | pkey | c27 | c28 | c29 | c30
------+------+-----+-----+-----+-----
0 | 1 | | | a | L
(1 row)
--- Postgres version ---
Github commit: efeb12ef0bfef0b5aa966a56bb4dbb1f936bda0c
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
