Обсуждение: BUG #17986: Inconsistent results of SELECT affected by btree index

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

On 6/20/23 22:54, PG Bug reporting form wrote:
> 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
> 
> ...
> 
> --- 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
> 

I can't reproduce this with current master - perhaps one of the commits
since efeb12ef0b fixes this too? Those should be for the patches you
mentioned, but it's likely a bit improved.

Can you try with current master?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> On 6/20/23 22:54, PG Bug reporting form wrote:
>> My fuzzer finds a correctness bug in Postgres, which makes Postgres return
>> inconsistent results.

> I can't reproduce this with current master - perhaps one of the commits
> since efeb12ef0b fixes this too?

It looks like this is another manifestation of the hashjoin bug
fixed in 45392626c.

            regards, tom lane

Thanks for your information! I checked it again. Seems this bug can be reproduced when I used "quick-fix-for-bug-17985.patch" for bug 17985, but cannot be reproduced after I used "better-fix-for-bug-17985.patch". So it was fixed by the patch "better-fix-for-bug-17985.patch".

Best wishes,
Zuming

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:

On 6/20/23 22:54, PG Bug reporting form wrote:

My fuzzer finds a correctness bug in Postgres, which makes Postgres return
inconsistent results.

I can't reproduce this with current master - perhaps one of the commits
since efeb12ef0b fixes this too?

It looks like this is another manifestation of the hashjoin bug
fixed in 45392626c.
			regards, tom lane

Zu-Ming Jiang <zuming.jiang@inf.ethz.ch> writes:
> Thanks for your information! I checked it again. Seems this bug can be 
> reproduced when I used "quick-fix-for-bug-17985.patch" for bug 17985, 
> but cannot be reproduced after I used "better-fix-for-bug-17985.patch". 
> So it was fixed by the patch "better-fix-for-bug-17985.patch".

Oh, that's very interesting.  There must be some other code path in
the executor that responds to the Hash node's allParams bit, and
doesn't do the right thing unless it's set.  Not totally surprising
I guess; the quick-fix thing was definitely a hack.

            regards, tom lane