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

same query but different result on pg16devel and pg15.2

From
tender wang
Date:
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;

on pg16devel:
c0 | c1 | c2 | c3 | c4
----+----+----+----+----
  1 |    |    |    |
(1 row)
plan:
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ref_0.ps_suppkey, c_acctbal, o_totalprice, o_orderpriority, o_clerk
         ->  Nested Loop Left Join
               ->  Seq Scan on partsupp ref_0
               ->  Result
                     One-Time Filter: false
(7 rows)

on pg15.2:
 c0 | c1 | c2 | c3 | c4
----+----+----+----+----
(0 rows)
plan:
                                                                      QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ref_0.ps_suppkey, c_acctbal, o_totalprice, o_orderpriority, o_clerk
         ->  Hash Left Join
               Hash Cond: ((n_comment)::text = (c_name)::text)
               Filter: (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))
               ->  Nested Loop Left Join
                     ->  Seq Scan on partsupp ref_0
                     ->  Result
                           One-Time Filter: false
               ->  Hash
                     ->  Result
                           One-Time Filter: false
(13 rows)



                                                         regards, tender wang

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

From
tender wang
Date:
Attached file included table schema information, but no data.

tender wang <tndrwang@gmail.com> 于2023年4月4日周二 10:53写道:
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;

on pg16devel:
c0 | c1 | c2 | c3 | c4
----+----+----+----+----
  1 |    |    |    |
(1 row)
plan:
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ref_0.ps_suppkey, c_acctbal, o_totalprice, o_orderpriority, o_clerk
         ->  Nested Loop Left Join
               ->  Seq Scan on partsupp ref_0
               ->  Result
                     One-Time Filter: false
(7 rows)

on pg15.2:
 c0 | c1 | c2 | c3 | c4
----+----+----+----+----
(0 rows)
plan:
                                                                      QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ref_0.ps_suppkey, c_acctbal, o_totalprice, o_orderpriority, o_clerk
         ->  Hash Left Join
               Hash Cond: ((n_comment)::text = (c_name)::text)
               Filter: (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))
               ->  Nested Loop Left Join
                     ->  Seq Scan on partsupp ref_0
                     ->  Result
                           One-Time Filter: false
               ->  Hash
                     ->  Result
                           One-Time Filter: false
(13 rows)



                                                         regards, tender wang
Attachment

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

From
Richard Guo
Date:

On Tue, Apr 4, 2023 at 10:53 AM tender wang <tndrwang@gmail.com> 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