From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | zuming(dot)jiang(at)inf(dot)ethz(dot)ch |
Subject: | BUG #17976: Inconsistent results of SELECT using CASE WHEN clause |
Date: | 2023-06-14 15:59:47 |
Message-ID: | 17976-4b638b525e9a983b@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17976
Logged by: Zuming Jiang
Email address: zuming(dot)jiang(at)inf(dot)ethz(dot)ch
PostgreSQL version: 16beta1
Operating system: Ubuntu 20.04
Description:
My fuzzer finds a logical bug in Postgres, which makes Postgres return
inconsistent results.
--- Set up database ---
create table t0 (c2 text);
create table t1 (pkey int4, c4 int4, c5 text, c6 int4, c8 float8, c9
int4);
create table t2 (c10 text, c12 timestamp);
CREATE VIEW t3 AS
SELECT '1' AS c_0
FROM (( SELECT ref_0.c2 AS c_0
FROM t0 ref_0
GROUP BY ref_0.c2) subq_0
FULL JOIN t2 ref_1 ON ((subq_0.c_0 = ref_1.c10)))
WHERE (ref_1.c12 > ref_1.c12);
CREATE VIEW t4 AS
SELECT
ref_1.c9 AS c_2,
ref_1.c8 AS c_3,
ref_1.c4 AS c_4,
1 AS c_6,
ref_1.c6 AS c_9
FROM (t3 ref_0
RIGHT JOIN t1 ref_1 ON ((ref_0.c_0 = ref_1.c5)));
insert into t1 values (11000, 0, null::text, 0, 0.0, 15);
---
The fuzzer generates Test case 1:
--- Test case 1 ---
select count(*) as c_6
from
(t1 as ref_15
left outer join t4 as ref_16
on (ref_15.pkey = ref_16.c_2))
where (case when (((ref_16.c_9 >= ref_16.c_4)
or (not (ref_16.c_9 >= ref_16.c_4)))
or ((ref_16.c_9 >= ref_16.c_4) is null))
then ref_16.c_3 else ref_16.c_3 end
) = pg_catalog.dcbrt(case when (((ref_15.c5 like '7%z')
and (not (ref_15.c5 like '7%z')))
and ((ref_15.c5 like '7%z') is not
null))
then ref_16.c_6 else ref_15.c8 end);
---
Because `ref_16.c_9 >= ref_16.c_4` could only be TRUE, FALSE, or NULL,
`(((ref_16.c_9 >= ref_16.c_4) or (not (ref_16.c_9 >= ref_16.c_4))) or
((ref_16.c_9 >= ref_16.c_4) is null))` must be TRUE. Therefore, I replace
`(((ref_16.c_9 >= ref_16.c_4) or (not (ref_16.c_9 >= ref_16.c_4))) or
((ref_16.c_9 >= ref_16.c_4) is null))` with TRUE, and get Test case 2:
--- Test case 2 ---
select count(*) as c_6
from
(t1 as ref_15
left outer join t4 as ref_16
on (ref_15.pkey = ref_16.c_2))
where (case when true
then ref_16.c_3 else ref_16.c_3 end
) = pg_catalog.dcbrt(case when (((ref_15.c5 like '7%z')
and (not (ref_15.c5 like '7%z')))
and ((ref_15.c5 like '7%z') is not
null))
then ref_16.c_6 else ref_15.c8 end);
---
--- Expected behavior ---
Test case 1 and Test case 2 return the same results.
--- Actual behavior ---
Test case 1 returns 1, while Test case returns 0.
--- Postgres version ---
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tristan Partin | 2023-06-14 16:42:03 | Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG |
Previous Message | PG Bug reporting form | 2023-06-14 15:17:31 | BUG #17975: Nested Loop Index Scan returning wrong result |