From: 狂奔的蜗牛 <1105066510@qq.com>
To: Tender Wang <tndrwang@gmail.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: 回复： BUG #18568: BUG: Result wrong when do group by on partition table!
Date: Tue, 6 Aug 2024 19:33:16 +0800
Message-ID: <tencent_30047C85C6C819CECD4318951F101077CA06@qq.com> (raw)
In-Reply-To: <CAHewXNnKLrZYG4iqaYw=uB3XWRrYRZHo7VtcMsbUEbdbajQg2Q@mail.gmail.com>
References: <18568-2a9afb6b9f7e6ed3@postgresql.org>
<CAHewXNnKLrZYG4iqaYw=uB3XWRrYRZHo7VtcMsbUEbdbajQg2Q@mail.gmail.com>
Partkey's collation stored in RelOptInfo->part_scheme, and I use it to fix the bug.
The attachment is my solution!
狂奔的蜗牛
1105066510@qq.com
------------------ 原始邮件 ------------------
发件人: "Tender Wang" <tndrwang@gmail.com>;
发送时间: 2024年8月6日(星期二) 下午4:42
收件人: "狂奔的蜗牛"<1105066510@qq.com>;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re: BUG #18568: BUG: Result wrong when do group by on partition table!
PG Bug reporting form <noreply@postgresql.org> 于2024年8月6日周二 15:01写道：
The following bug has been logged on the website:
Bug reference: 18568
Logged by: Webbo Han
Email address: 1105066510@qq.com
PostgreSQL version: 16.3
Operating system: centos 7.6
Description:
First, we create one case-insensitive collation use ICU:
```sql
CREATE COLLATION case_insensitive (
provider = icu,
locale = 'und-u-ks-level2',
deterministic = false
);
```
Then, we create the partition table, meanwhile we set the collation of
column c to `case_insensitive`,
and set partkey's collation to 'C'.
```sql
SET enable_partitionwise_aggregate TO true;
SET enable_partitionwise_join TO true;
SET max_parallel_workers_per_gather TO 0;
SET enable_incremental_sort TO off;
CREATE TABLE pagg_tab (c text collate case_insensitive) PARTITION BY LIST(c
collate "C");
CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('a', 'b',
'c', 'd');
CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('e', 'f',
'A');
CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('B', 'C',
'D', 'E');
INSERT INTO pagg_tab SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM
generate_series(0, 2999) i;
ANALYZE pagg_tab;
```
We do group by on the table pagg_tab use `case_insensitive` collation, we
hope group key is case-insensitive.
but we find the execution result is not what we expected.
```shell
postgres=# SELECT c collate case_insensitive, count(c) FROM pagg_tab GROUP
BY c collate case_insensitive;
c | count
---+-------
A | 300
e | 300
E | 300
D | 300
C | 300
B | 300
d | 300
c | 300
b | 300
a | 300
(10 rows)
```
The reason is the function group_by_has_partkey() do not check partkey's
collation, that lead to explain error.
```shell
postgres=# EXPLAIN SELECT c collate case_insensitive, count(c) FROM
pagg_tab GROUP BY c collate case_insensitive ;
QUERY PLAN
--------------------------------------------------------------------------------------
Append (cost=12.00..60.15 rows=10 width=10)
-> HashAggregate (cost=12.00..12.02 rows=2 width=10)
Group Key: pagg_tab.c
-> Seq Scan on pagg_tab_p2 pagg_tab (cost=0.00..9.00 rows=600
width=2)
-> HashAggregate (cost=24.00..24.04 rows=4 width=10)
Group Key: pagg_tab_1.c
-> Seq Scan on pagg_tab_p3 pagg_tab_1 (cost=0.00..18.00
rows=1200 width=2)
-> HashAggregate (cost=24.00..24.04 rows=4 width=10)
Group Key: pagg_tab_2.c
-> Seq Scan on pagg_tab_p1 pagg_tab_2 (cost=0.00..18.00
rows=1200 width=2)
(10 rows)
```
So, group_by_has_partkey() need to verify if the partkey's collation matches
the groupkey,
meanwhile, if groupkey is RelabelType node and it's collation equal to
partkey's, it should
also set variable `found` to true.
Yeah, I can reproduce $subject on HEAD.But I found this when debug into group_by_has_partkey(), as below:
call nodeToString(groupexprs):
VAR : varcollid 16384
call nodeToString(partexpr):
VAR: varcollid 16384
So the collid of partkey and groupexpr is same, so add check here may not fix this issue.
I continue to find out why the collation id of partkey is 16384(e.g. case_insensitive). The partkey expr info is
set in set_baserel_partition_key_exprs(), which it uses partkey->parttypcoll[cnt] value not partkey->partcollation value.
And partkey->parttypcoll[cnt] is assigned from pg_attribute , which is the column c meta data.
Should we use partkey->partcollation value? I try to fix that in the attached patch. I add your case in the test, and I don't find
failed regress.
--
Tender Wang
Attachments:0001-fix-group_by_has_partkey-bug.patch (application/octet-stream, 1.6K) download
From 01df112261a8cd2e6e7b6befd163ebdee5d01304 Mon Sep 17 00:00:00 2001
From: hanweibo <hanweibo@highgo.com>
Date: Tue, 6 Aug 2024 19:26:09 +0800
Subject: [PATCH] fix group_by_has_partkey bug
---
src/backend/optimizer/plan/planner.c | 35 +++++++++++++++++++++++++---
1 file changed, 32 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 948afd9094..8cdece261f 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -8025,13 +8025,42 @@ group_by_has_partkey(RelOptInfo *input_rel,
foreach(lc, partexprs)
{
+ ListCell *lg;
Expr *partexpr = lfirst(lc);
+ Oid partcoll = input_rel->part_scheme->partcollation[cnt];- if (list_member(groupexprs, partexpr))+ foreach (lg, groupexprs)
{
- found = true;- break;+ Expr *groupexpr = lfirst(lg);++ /*+ * GROUP BY xx COLLATE xxx+ * Set collation in group by clause using COLLATE syntax, and the collation IS NOT equal+ * to column's default collation.+ */+ if (nodeTag(groupexpr) == T_RelabelType)+ {+ RelabelType *rt = (RelabelType *) groupexpr;++ if (equal(rt->arg, partexpr) && rt->resultcollid == partcoll)+ {+ found = true;+ break;+ }+ }+ /*+ * Not set collation in group by clause, or the collation+ * is the same as column's.+ */+ else if (equal(groupexpr, partexpr) && exprCollation((Node *) groupexpr) == partcoll)+ {+ found = true;+ break;+ }
}
++ if (found)+ break;
}
/*
--
2.25.1
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: pgsql-bugs@postgresql.org
Cc: 1105066510@qq.com, tndrwang@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: 回复： BUG #18568: BUG: Result wrong when do group by on partition table!
In-Reply-To: <tencent_30047C85C6C819CECD4318951F101077CA06@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox