Обсуждение: BUG #17982: Inconsistent results of SELECT with CTE caused by subquery comparison

The following bug has been logged on the website:

Bug reference:      17982
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 still can be triggered after this fixing
(https://github.com/postgres/postgres/commit/f4c00d138f6dea4c9d8af8ec280b7edc9b0a29e1)

--- Set up database ---
create table t0 (vkey int4, c0 float8, c1 timestamp, c3 text);
create table t1 (vkey int4, pkey int4, c4 timestamp);
create view t3 as select true as c_0 from (select distinct true as c_4) as
subq_1;
insert into t0 values (2, 3.41, make_timestamp(2031, 8, 6, 13, 33, 35),
'3');
---

The fuzzer generates Test case 1:

--- Test case 1 ---
WITH cte_3 AS (select
    bttextcmp(ref_24.c3, ref_24.c3) as c_0,
    (ref_25.c4 <> ref_24.c1) as c_6,
case when (((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1))
        or (not (ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1))))
        or ((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183
where false order by c_0 asc limit 1)) is null))
     then ref_24.c0 else tanh(ref_24.c0) end as c_7
   from
    (t0 as ref_24
      full outer join t1 as ref_25
      on (ref_24.vkey = ref_25.vkey))
  where (null::bool)
)
select
    ref_31.c1 as c_0,
    ref_30.c_7 as c_1
  from
   (((select
              ref_28.c_0 as c_0,
              ref_28.c_0 as c_1,
              ref_28.c_0 as c_2,
              ref_28.c_0 as c_3,
              ref_28.c_0 as c_4
            from
              t3 as ref_28) as subq_4
        right outer join cte_3 as ref_30
        on (subq_4.c_1 = ref_30.c_6))
      right outer join t0 as ref_31
      on (ref_30.c_0 = ref_31.vkey))
  where ref_31.c1 is not null;
---

Because `(ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183 where
false order by c_0 asc limit 1))` could only be TRUE, FALSE, or NULL,
`(((ref_25.vkey > (select ref_25.pkey as c_0 from t0 as ref_183 where false
order by c_0 asc limit 1)) or (not (ref_25.vkey > (select ref_25.pkey as c_0
from t0 as ref_183 where false order by c_0 asc limit 1)))) or ((ref_25.vkey
> (select ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc
limit 1)) is null))` must be TRUE. Therefore, I replace `(((ref_25.vkey >
(select ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc
limit 1)) or (not (ref_25.vkey > (select ref_25.pkey as c_0 from t0 as
ref_183 where false order by c_0 asc limit 1)))) or ((ref_25.vkey > (select
ref_25.pkey as c_0 from t0 as ref_183 where false order by c_0 asc limit 1))
is null))` with TRUE, and get Test case 2:

--- Test case 2 ---
WITH cte_3 AS (select
    bttextcmp(ref_24.c3, ref_24.c3) as c_0,
    (ref_25.c4 <> ref_24.c1) as c_6,
case when true then ref_24.c0 else tanh(ref_24.c0) end as c_7
   from
    (t0 as ref_24
      full outer join t1 as ref_25
      on (ref_24.vkey = ref_25.vkey))
  where (null::bool)
)
select
    ref_31.c1 as c_0,
    ref_30.c_7 as c_1
  from
   (((select
              ref_28.c_0 as c_0,
              ref_28.c_0 as c_1,
              ref_28.c_0 as c_2,
              ref_28.c_0 as c_3,
              ref_28.c_0 as c_4
            from
              t3 as ref_28) as subq_4
        right outer join cte_3 as ref_30
        on (subq_4.c_1 = ref_30.c_6))
      right outer join t0 as ref_31
      on (ref_30.c_0 = ref_31.vkey))
  where ref_31.c1 is not null;
---

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

--- Actual behavior ---
Test case 1 returns 1 row ({2031-08-06 13:33:35|}), while Test case returns
0 rows.

--- Postgres version ---
Github commit: 7fcd7ef2a9c372b789f95b40043edffdc611c566
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

PG Bug reporting form <noreply@postgresql.org> writes:
> My fuzzer finds a correctness bug in Postgres, which makes Postgres return
> inconsistent results.

This appears to be the same issue you already reported in bug #17978.
At least, the patch I just posted in that thread fixes this too.

            regards, tom lane

Thanks for your information and the fixing! I will then apply your patch and test postgres again.

Best,
Zuming

PG Bug reporting form <noreply@postgresql.org> writes:

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

This appears to be the same issue you already reported in bug #17978.
At least, the patch I just posted in that thread fixes this too.
			regards, tom lane