Re: wrong results due to qual pushdown - Mailing list pgsql-hackers

Results on 16devel:

c0 | c3 | c6 | c7 |            c8
----+----+----+----+---------------------------
  0 |    |    |    | ALGERIA
  0 |    |    |    | ETHIOPIA
  0 |    |    |    | KENYA
  0 |    |    |    | MOROCCO
  0 |    |    |    | MOZAMBIQUE
  1 |    |    |    | ARGENTINA
  1 |    |    |    | BRAZIL
  1 |    |    |    | CANADA
  1 |    |    |    | PERU
  1 |    |    |    | UNITED STATES
  2 |    |    |    | CHINA
  2 |    |    |    | INDIA
  2 |    |    |    | INDONESIA
  2 |    |    |    | JAPAN
  2 |    |    |    | VIETNAM
  3 |    |    |    | FRANCE
  3 |    |    |    | GERMANY
  3 |    |    |    | ROMANIA
  3 |    |    |    | RUSSIA
  3 |    |    |    | UNITED KINGDOM
  4 |    |    |    | EGYPT
  4 |    |    |    | IRAN
  4 |    |    |    | IRAQ
  4 |    |    |    | JORDAN
  4 |    |    |    | SAUDI ARABIA
(25 rows)

Results on 15.2:

 c0 | c3 | c6 | c7 | c8
----+----+----+----+----
(0 rows)

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 于2023年3月6日周一 22:14写道：

On Mon, Mar 6, 2023 at 3:00 PM tender wang <tndrwang@gmail.com> wrote:

tender wang <tndrwang@gmail.com>	14:51 (2小时前)
发送至 pgsql-hackers

Hi hackers.

   This query has different result on 16devel and 15.2.

select  
    sample_3.n_regionkey as c0,  
    ref_7.l_linenumber as c3,  
    sample_4.l_quantity as c6,
    sample_5.n_nationkey as c7,
    sample_3.n_name as c8
  from
    public.nation as sample_3
        left join public.lineitem as ref_5
        on ((cast(null as text) ~>=~ cast(null as text))
            or (ref_5.l_discount is NULL))
      left join public.time_statistics as ref_6
            inner join public.lineitem as ref_7
            on (ref_7.l_returnflag = ref_7.l_linestatus)
          right join public.lineitem as sample_4
            left join public.nation as sample_5
            on (cast(null as tsquery) = cast(null as tsquery))
          on (cast(null as "time") <= cast(null as "time"))
        right join public.customer as ref_8
        on (sample_4.l_comment = ref_8.c_name )
      on (ref_5.l_quantity = ref_7.l_quantity )
  where (ref_7.l_suppkey is not NULL)
    or ((case when cast(null as lseg) >= cast(null as lseg) then cast(null as inet) else cast(null as inet) end
           && cast(null as inet))
      or (pg_catalog.getdatabaseencoding() !~~ case when (cast(null as int2) <= cast(null as int8))
            or (EXISTS (
              select  
                  ref_9.ps_comment as c0,
                  5 as c1,
                  ref_8.c_address as c2,
                  58 as c3,
                  ref_8.c_acctbal as c4,
                  ref_7.l_orderkey as c5,
                  ref_7.l_shipmode as c6,
                  ref_5.l_commitdate as c7,
                  ref_8.c_custkey as c8,
                  sample_3.n_nationkey as c9
                from
                  public.partsupp as ref_9
                where cast(null as tsquery) @> cast(null as tsquery)
                order by c0, c1, c2, c3, c4, c5, c6, c7, c8, c9 limit 38)) then cast(null as text) else cast(null as text) end
          ))
  order by c0, c3, c6, c7, c8 limit 137;

plan on 16devel:

                                                                           QUERY PLAN                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   InitPlan 1 (returns $0)
     ->  Result
           One-Time Filter: false
   ->  Sort
         Sort Key: sample_3.n_regionkey, l_linenumber, l_quantity, n_nationkey, sample_3.n_name
         ->  Nested Loop Left Join
               ->  Seq Scan on nation sample_3
               ->  Materialize
                     ->  Nested Loop Left Join
                           Join Filter: (ref_5.l_quantity = l_quantity)
                           Filter: ((l_suppkey IS NOT NULL) OR (getdatabaseencoding() !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text ELSE NULL::text END))
                           ->  Seq Scan on lineitem ref_5
                                 Filter: (l_discount IS NULL)
                           ->  Result
                                 One-Time Filter: false
(16 rows)

plan on 15.2:

                                                                    QUERY PLAN                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   InitPlan 1 (returns $0)
     ->  Result
           One-Time Filter: false
   ->  Sort
         Sort Key: sample_3.n_regionkey, l_linenumber, l_quantity, n_nationkey, sample_3.n_name
         ->  Nested Loop Left Join
               Filter: ((l_suppkey IS NOT NULL) OR (getdatabaseencoding() !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text ELSE NULL::text END))
               ->  Seq Scan on nation sample_3
               ->  Materialize
                     ->  Nested Loop Left Join
                           Join Filter: (ref_5.l_quantity = l_quantity)
                           ->  Seq Scan on lineitem ref_5
                                 Filter: (l_discount IS NULL)
                           ->  Result
                                 One-Time Filter: false
(16 rows)

It looks wrong that the qual (e.g ((l_suppkey IS NOT NULL) OR (getdatabaseencoding() !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text ELSE NULL::text END))) is pushdown.

Is that because $0 comes from a peer plan?

An example of the difference in the results would help.

--

Best Wishes,

Ashutosh Bapat