Re: wrong results due to qual pushdown

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)

tender wang <tndrwang(at)gmail(dot)com> 于2023年3月6日周一 22:48写道：

> 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(dot)bapat(dot)oss(at)gmail(dot)com> 于2023年3月6日周一 22:14写道：
>
>>
>>
>> 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
>>
>