Re: BUG #17976: Inconsistent results of SELECT using CASE WHEN clause

On Thu, 15 Jun 2023 at 04:27, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> My fuzzer finds a logical bug in Postgres, which makes Postgres return
> inconsistent results.
> --- 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.

Thank you for the report.

The first bad commit seems to be b448f1c8

using your setup and the following query:

explain select *
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);

b448f1c8d8 gives:

QUERY PLAN
--------------------------------------------------------------------------------
Hash Right Join (cost=31.83..71760.52 rows=1881800 width=80)
Hash Cond: (ref_1.c9 = ref_15.pkey)
-> Nested Loop Left Join (cost=0.00..4895.70 rows=388000 width=24)
-> Seq Scan on t1 ref_1 (cost=0.00..19.70 rows=970 width=52)
-> Materialize (cost=0.00..27.00 rows=400 width=32)
-> Seq Scan on t2 ref_1_1 (cost=0.00..25.00 rows=400 width=32)
Filter: (c12 > c12)
-> Hash (cost=19.70..19.70 rows=970 width=56)
-> Seq Scan on t1 ref_15 (cost=0.00..19.70 rows=970 width=56)
(9 rows)

whereas the prior commit gives:

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=63.33..6475.96 rows=47 width=80)
Hash Cond: (ref_1.c9 = ref_15.pkey)
Filter: (CASE WHEN ((ref_1.c6 >= ref_1.c4) OR (ref_1.c6 < ref_1.c4)
OR ((ref_1.c6 >= ref_1.c4) IS NULL)) THEN ref_1.c8 ELSE ref_1.c8 END =
dcbrt(CASE WHEN ((ref_15.c5 ~~ '7%z'::text) AND (ref_15.
c5 !~~ '7%z'::text) AND ((ref_15.c5 ~~ '7%z'::text) IS NOT NULL)) THEN
((1))::double precision ELSE ref_15.c8 END))
-> Nested Loop Left Join (cost=31.50..5898.27 rows=1940 width=24)
Join Filter: ('1'::text = ref_1.c5)
-> Seq Scan on t1 ref_1 (cost=0.00..19.70 rows=970 width=52)
-> Materialize (cost=31.50..59.57 rows=400 width=0)
-> Hash Left Join (cost=31.50..57.57 rows=400 width=0)
Hash Cond: (ref_1_1.c10 = ref_0.c2)
-> Seq Scan on t2 ref_1_1 (cost=0.00..25.00
rows=400 width=32)
Filter: (c12 > c12)
-> Hash (cost=29.00..29.00 rows=200 width=32)
-> HashAggregate (cost=27.00..29.00
rows=200 width=32)
Group Key: ref_0.c2
-> Seq Scan on t0 ref_0
(cost=0.00..23.60 rows=1360 width=32)
-> Hash (cost=19.70..19.70 rows=970 width=56)
-> Seq Scan on t1 ref_15 (cost=0.00..19.70 rows=970 width=56)
(17 rows)

so it looks like the join filter is being lost somewhere along the way.

David