BUG #17986: Inconsistent results of SELECT affected by btree index - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17986
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

https://www.postgresql.org/message-id/flat/17982-3fa239feecd6c1b3%40postgresql.org
https://www.postgresql.org/message-id/flat/17985-748b66607acd432e%40postgresql.org

--- Set up database ---
create table t0 (vkey int4);
create table t2 (vkey int4, pkey int4, c11 int4, c12 timestamp, c13
float8);
create table t3 (pkey int4, c16 float8, c17 timestamp);
insert into t0 values (29), (34);
insert into t2 values (21, 31000, 34, make_timestamp(2073, 8, 26, 1, 50, 3),
0.0);
insert into t2 values (38, 48000, -12, make_timestamp(2036, 7, 25, 22, 49,
37), 56.44);
insert into t3 values (50000, 4.37, make_timestamp(2021, 4, 18, 12, 41,
57));
CREATE INDEX i0 ON t2 USING btree (c13);
---

The fuzzer generates Test case 1:

--- Test case 1 ---
select * from t0
where exists (
  select
        ref_2.c13 as c_4
      from
        ((t2 as ref_0 right outer join t2 as ref_1 on (ref_0.c13 =
ref_1.c13))
          left outer join t2 as ref_2 on (ref_0.c11 = ref_2.vkey))
      where ref_0.c11 = (
        select
            t0.vkey as c_0
          from
            t3 as ref_4
          where (case when (((ref_4.pkey in (select ref_2.pkey))
                           or (not (ref_4.pkey in (select ref_2.pkey))))
                           or ((ref_4.pkey in (select ref_2.pkey)) is
null))
                      then ref_4.c17 else make_timestamp(2032, 9, 19, 6, 55,
5) end
                ) < ref_1.c12
          order by c_0 asc limit 1)
  union all select t3.c16 from t3 where false);
---

Because `(ref_4.pkey in (select ref_2.pkey))` could only be TRUE, FALSE, or
NULL, `(((ref_4.pkey in (select ref_2.pkey)) or (not (ref_4.pkey in (select
ref_2.pkey)))) or ((ref_4.pkey in (select ref_2.pkey)) is null))` must be
TRUE. Therefore, I replace`(((ref_4.pkey in (select ref_2.pkey)) or (not
(ref_4.pkey in (select ref_2.pkey)))) or ((ref_4.pkey in (select
ref_2.pkey)) is null))` with TRUE, and get Test case 2:

--- Test case 2 ---
select * from t0
where exists (
  select
        ref_2.c13 as c_4
      from
        ((t2 as ref_0 right outer join t2 as ref_1 on (ref_0.c13 =
ref_1.c13))
          left outer join t2 as ref_2 on (ref_0.c11 = ref_2.vkey))
      where ref_0.c11 = (
        select
            t0.vkey as c_0
          from
            t3 as ref_4
          where (case when true
                      then ref_4.c17 else make_timestamp(2032, 9, 19, 6, 55,
5) end
                ) < ref_1.c12
          order by c_0 asc limit 1)
  union all select t3.c16 from t3 where false);
---

--- Expected behavior ---
Test case 1 and Test case 2 return the same results.

--- Actual behavior ---
Test case 1 returns 1 row, while Test case 2 returns 0 rows.

Output of Test case 1:
 vkey 
------
   34
(1 row)

Output of Test case 2:
 vkey 
------
(0 rows)

--- 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