Re: wrong results due to qual pushdown

On Mon, Mar 6, 2023 at 3:00 PM tender wang <tndrwang(at)gmail(dot)com> wrote:

> tender wang <tndrwang(at)gmail(dot)com>
> [image: 附件]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