wrong results due to qual pushdown

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.

regards, tender wang