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 |
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()? |