wrong query result due to wang plan

From: tender wang <tndrwang(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: wrong query result due to wang plan
Date: 2023-02-16 07:16:23
Message-ID: CAHewXNk9eJ35ru5xATWioTV4+xZPHptjy9etdcNPjUfY9RQ+uQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,
I have this query as shown below:

select ref_1.r_comment as c0, subq_0.c1 as c1 from public.region as
sample_0 right join public.partsupp as sample_1 right join public.lineitem
as sample_2 on (cast(null as path) = cast(null as path)) on (cast(null as
"timestamp") < cast(null as "timestamp")) inner join public.lineitem as
ref_0 on (true) left join (select sample_3.ps_availqty as c1,
sample_3.ps_comment as c2 from public.partsupp as sample_3 where false
order by c1, c2 ) as subq_0 on (sample_1.ps_supplycost = subq_0.c1 ) right
join public.region as ref_1 on (sample_1.ps_availqty = ref_1.r_regionkey )
where ref_1.r_comment is not NULL order by c0, c1;

*This query has different result on pg12.12 and on HEAD*,
on pg12.12:
c0
| c1
-----------------------------------------------------------------------------------------------------------------+----
even, ironic theodolites according to the bold platelets wa
|
furiously unusual packages use carefully above the unusual, exp
|
silent, bold requests sleep slyly across the quickly sly dependencies.
furiously silent instructions alongside |
special, bold deposits haggle foxes. platelet
|
special Tiresias about the furiously even dolphins are furi
|
(5 rows)

its plan :
QUERY PLAN
------------------------------------------------------
Sort
Sort Key: ref_1.r_comment, c1
-> Hash Left Join
Hash Cond: (ref_1.r_regionkey = ps_availqty)
-> Seq Scan on region ref_1
Filter: (r_comment IS NOT NULL)
-> Hash
-> Result
One-Time Filter: false
(9 rows)

But on HEAD(pg16devel), its results below:
c0 | c1
----+----
(0 rows)

its plan:
QUERY PLAN
----------------------------------------
Sort
Sort Key: ref_1.r_comment, subq_0.c1
-> Result
One-Time Filter: false
(4 rows)

Attached file included table schema info.
regards, tender wang

Attachment Content-Type Size
dbt3-s0.01-janm.sql application/octet-stream 4.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2023-02-16 07:17:16 Re: DDL result is lost by CREATE DATABASE with WAL_LOG strategy
Previous Message Joel Jacobson 2023-02-16 07:08:37 Re: Missing free_var() at end of accum_sum_final()?