Subject: Re: same query but different result on pg16devel and pg15.2

From: Richard Guo <[email-masked]>
Date: Tue, 4 Apr 2023 14:47:19 +0800

On Tue, Apr 4, 2023 at 10:53 AM tender wang <[email-masked]> wrote:

> Hi hackers,
>     I encounter a problem, as shown below:
>
> query:
>   select
>   ref_0.ps_suppkey as c0,
>   ref_1.c_acctbal as c1,
>   ref_2.o_totalprice as c2,
>   ref_2.o_orderpriority as c3,
>   ref_2.o_clerk as c4
> from
>   public.partsupp as ref_0
>       left join public.nation as sample_0
>         inner join public.customer as sample_1
>         on (false)
>       on (true)
>     left join public.customer as ref_1
>         right join public.orders as ref_2
>         on (false)
>       left join public.supplier as ref_3
>       on (false)
>     on (sample_0.n_comment = ref_1.c_name )
> where (8 <= NULLIF(CASE WHEN (o_orderkey IS NOT NULL) THEN 4 ELSE 4 END,
> CASE WHEN (o_orderdate >= o_orderdate) THEN 95 ELSE 95 END))
> order by c0, c1, c2, c3, c4 limit 1;
>

It is the same issue as discussed in [1].  In this query the WHERE
condition is placed at the wrong place.

[1]
https://www.postgresql.org/message-id/flat/0b819232-4b50-f245-1c7d-c8c61bf41827%40postgrespro.ru

Thanks
Richard