public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Junwang Zhao <[email protected]>
Cc: Tender Wang <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.
Date: Fri, 1 Nov 2024 16:07:00 +0800
Message-ID: <CACJufxGjLcpNLh75_YSbXXmfaTyKSU+P0rs8M293VYa276QQdQ@mail.gmail.com> (raw)
In-Reply-To: <CA+HiwqEQHVbAdFWNcrnDEZXk=m-Mk5ZtCoFq_tKkyoHPRATuag@mail.gmail.com>
References: <CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.com>
<CAHewXN=sGCF7cp3r+8PyZXP0TT+Q1imoEe-7Z-0Sxxb76eWAXg@mail.gmail.com>
<CA+HiwqFmtOjVX0Gy2HCMGDRJg3zKNmhdt=o=K=pTvE2VhEe-1A@mail.gmail.com>
<CAHewXNk1K2G56C7kXvuYzQexeQs2EuHA3x-7v68ihn843svEZQ@mail.gmail.com>
<CACJufxFefXhnD+Z3swU9A0SCK3AfmEbTSx7hj=TCu-0hr2OOgA@mail.gmail.com>
<CAHewXNnoTXxsAuJKxEjs-TASjG_-soH9Y_+tup8a5teggS4caw@mail.gmail.com>
<CACJufxF1bmHSc+L7Hg7QxnH9UDbyP+qMsK8Z2ZBU8kux6C=t3A@mail.gmail.com>
<CAEG8a3Ks7_5b+0bnqbSceAbofj=o1RmP4qedv1Jr43Nz1wY-vA@mail.gmail.com>
<CA+HiwqEHx++qTjCGmWyYZDiPR=_fLGPjR7RcFEVnSNZVc-Qj2g@mail.gmail.com>
<CACJufxG1pKrjf+_ugjYJFmww_kGd9Wn8OpU=XoH5w2hUqSXFbg@mail.gmail.com>
<CA+HiwqEQHVbAdFWNcrnDEZXk=m-Mk5ZtCoFq_tKkyoHPRATuag@mail.gmail.com>
just a quick reply while testing v4-0001.
tests copy from src/test/regress/sql/partition_aggregate.sql first 40 lines.
drop table if exists pagg_tab;
CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY
LIST(c collate "C");
CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000',
'0001', '0002', '0003', '0004');
CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005',
'0006', '0007', '0008');
CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009',
'0010', '0011');
INSERT INTO pagg_tab SELECT (i % 20), (i % 30), to_char(i % 12,
'FM0000'), i % 30 FROM generate_series(0, 2999) i;
ANALYZE pagg_tab;
EXPLAIN (COSTS OFF, settings)
SELECT a, sum(b), avg(b), count(*), max(b) FROM pagg_tab GROUP BY a;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize HashAggregate
Group Key: pagg_tab.a
-> Append
-> Partial HashAggregate
Group Key: pagg_tab.a
-> Seq Scan on pagg_tab_p1 pagg_tab
-> Partial HashAggregate
Group Key: pagg_tab_1.a
-> Seq Scan on pagg_tab_p2 pagg_tab_1
-> Partial HashAggregate
Group Key: pagg_tab_2.a
-> Seq Scan on pagg_tab_p3 pagg_tab_2
Settings: enable_partitionwise_aggregate = 'on',
enable_partitionwise_join = 'on', max_parallel_workers_per_gather =
'0', enable_increm
ental_sort = 'off'
drop table if exists pagg_tab;
CREATE TABLE pagg_tab (a text, b int, c text, d int) PARTITION BY
LIST(c collate "C");
CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000',
'0001', '0002', '0003', '0004');
CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005',
'0006', '0007', '0008');
CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009',
'0010', '0011');
INSERT INTO pagg_tab SELECT (i % 20)::text, (i % 30), to_char(i % 12,
'FM0000'), i % 30 FROM generate_series(0, 2999) i;
ANALYZE pagg_tab;
EXPLAIN (COSTS OFF, settings)
SELECT a, sum(b), avg(b), count(*), max(b) FROM pagg_tab GROUP BY a;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate
Group Key: pagg_tab.a
-> Append
-> Seq Scan on pagg_tab_p1 pagg_tab_1
-> Seq Scan on pagg_tab_p2 pagg_tab_2
-> Seq Scan on pagg_tab_p3 pagg_tab_3
Settings: enable_partitionwise_aggregate = 'on',
enable_partitionwise_join = 'on', max_parallel_workers_per_gather =
'0', enable_increm
ental_sort = 'off'
it seems "PARTITION BY LIST(c collate "C");" collation compare with
"GROUP BY a;".
set collation_incompatible returned true.
make it cannot do PARTITIONWISE_AGGREGATE_PARTIAL.
but here "group by a", "a" is text data type, we can still do
PARTITIONWISE_AGGREGATE_PARTIAL
?
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.
In-Reply-To: <CACJufxGjLcpNLh75_YSbXXmfaTyKSU+P0rs8M293VYa276QQdQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox