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

On Thu, 15 Jun 2023 at 04:27, PG Bug reporting form
<noreply@postgresql.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