BUG #17976: Inconsistent results of SELECT using CASE WHEN clause - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17976
Logged by:          Zuming Jiang
Email address:      zuming.jiang@inf.ethz.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