回复： BUG #18568: BUG: Result wrong when do group by on partition table!

this case will enter `if (IsA(groupexpr, RelabelType))` branch. 


We set "C" as groupkey's collation, and it's not equal to column's.
In transformGroupClause(), syntax `COLLATE xxx` will create CollateExpr node, and CollateExpr's arg is Var node.
And then, planner will call eval_const_expressions_mutator() to transform CollateExpr to RelableType if CollateExpr->collOid not equal to Collate->arg's collation.




About V3 patch, PartCollMatchesExprColl() may be not suitable, because collation of partkey must be equal to groupkey's, even though they are all InvalidOid.




```sql
CREATE COLLATION case_insensitive (
	provider = icu,
	locale = 'und-u-ks-level2',
	deterministic = false
);


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;
drop table pagg_tab;


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;


EXPLAIN SELECT c collate "C", count(c) FROM pagg_tab GROUP BY c collate "C" ;
SELECT c collate "C", count(c) FROM pagg_tab GROUP BY c collate "C";

```












狂奔的蜗牛
1105066510@qq.com



 




------------------ 原始邮件 ------------------
发件人:                                                                                                                        "Tender Wang"                                                                                    <tndrwang@gmail.com&gt;;
发送时间:&nbsp;2024年8月7日(星期三) 中午12:01
收件人:&nbsp;"狂奔的蜗牛"<1105066510@qq.com&gt;;
抄送:&nbsp;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;
主题:&nbsp;Re: BUG #18568: BUG: Result wrong when do group by on partition table!



I think what I have&nbsp;done in v2 is not the right way. Because partition prune logic first checks whether it is equalor not, then it will check the collation match or not. So I should not change the&nbsp;set_baserel_partition_key_exprs() logic.


I look through your patch and make some&nbsp;changes.


1.&nbsp;
&nbsp; git am your patch, report warnings, some code format issue.
2.&nbsp;&nbsp;
&nbsp;I removed this branch: if (IsA(groupexpr, RelabelType)).

&nbsp;Because in your added test case, it didn't enter this branch. I didn't figure out what&nbsp; kind of group by clause is RelableType.
&nbsp;You can provide&nbsp;a test case based on the v3 patch.


3. Tweek a little about the test case.


By&nbsp;the way, your last two emails only sent to me, please cc the pgsql-bugs.






狂奔的蜗牛 <1105066510@qq.com&gt; 于2024年8月6日周二 19:33写道：

Partkey's collation&nbsp;stored in&nbsp;RelOptInfo-&gt;part_scheme, and I use it to fix the bug.
The attachment is my solution!



狂奔的蜗牛
1105066510@qq.com



&nbsp;




------------------&nbsp;原始邮件&nbsp;------------------
发件人:                                                                                                                        "Tender Wang"                                                                                    <tndrwang@gmail.com&gt;;
发送时间:&nbsp;2024年8月6日(星期二) 下午4:42
收件人:&nbsp;"狂奔的蜗牛"<1105066510@qq.com&gt;;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;

主题:&nbsp;Re: BUG #18568: BUG: Result wrong when do group by on partition table!






PG Bug reporting form <noreply@postgresql.org&gt; 于2024年8月6日周二 15:01写道：

The following bug has been logged on the website:
 
 Bug reference:&nbsp; &nbsp; &nbsp; 18568
 Logged by:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Webbo Han
 Email address:&nbsp; &nbsp; &nbsp; 1105066510@qq.com
 PostgreSQL version: 16.3
 Operating system:&nbsp; &nbsp;centos 7.6
 Description:&nbsp; &nbsp; &nbsp; &nbsp; 
 
 First, we create one case-insensitive collation use ICU:
 ```sql
 &nbsp; &nbsp; &nbsp; &nbsp; CREATE COLLATION case_insensitive (
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; provider = icu,
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; locale = 'und-u-ks-level2',
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; deterministic = false
 &nbsp; &nbsp; &nbsp; &nbsp; );
 ```
 
 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
 &nbsp; &nbsp; &nbsp; &nbsp; SET enable_partitionwise_aggregate TO true;
 &nbsp; &nbsp; &nbsp; &nbsp; SET enable_partitionwise_join TO true;
 &nbsp; &nbsp; &nbsp; &nbsp; SET max_parallel_workers_per_gather TO 0;
 &nbsp; &nbsp; &nbsp; &nbsp; SET enable_incremental_sort TO off;
 &nbsp; &nbsp; &nbsp; &nbsp; CREATE TABLE pagg_tab (c text collate case_insensitive) PARTITION BY LIST(c
 collate "C");
 &nbsp; &nbsp; &nbsp; &nbsp; CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('a', 'b',
 'c', 'd');
 &nbsp; &nbsp; &nbsp; &nbsp; CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('e', 'f',
 'A');
 &nbsp; &nbsp; &nbsp; &nbsp; CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('B', 'C',
 'D', 'E');
 &nbsp; &nbsp; &nbsp; &nbsp; INSERT INTO pagg_tab SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM
 generate_series(0, 2999) i;
 &nbsp; &nbsp; &nbsp; &nbsp; 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
 &nbsp; &nbsp; &nbsp; &nbsp; postgres=# SELECT c collate case_insensitive, count(c) FROM pagg_tab GROUP
 BY c collate case_insensitive;
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c | count
 &nbsp; &nbsp; &nbsp; &nbsp; ---+-------
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;e |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a |&nbsp; &nbsp;300
 &nbsp; &nbsp; &nbsp; &nbsp; (10 rows)
 ```
 
 The reason is the function group_by_has_partkey() do not check partkey's
 collation, that lead to explain error.
 ```shell
 &nbsp; &nbsp; &nbsp; &nbsp; postgres=# EXPLAIN SELECT c collate case_insensitive, count(c) FROM
 pagg_tab GROUP BY c collate case_insensitive ;
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN
 &nbsp; &nbsp; &nbsp; &nbsp; --------------------------------------------------------------------------------------
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Append&nbsp; (cost=12.00..60.15 rows=10 width=10)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; HashAggregate&nbsp; (cost=12.00..12.02 rows=2 width=10)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Group Key: pagg_tab.c
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; Seq Scan on pagg_tab_p2 pagg_tab&nbsp; (cost=0.00..9.00 rows=600
 width=2)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; HashAggregate&nbsp; (cost=24.00..24.04 rows=4 width=10)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Group Key: pagg_tab_1.c
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; Seq Scan on pagg_tab_p3 pagg_tab_1&nbsp; (cost=0.00..18.00
 rows=1200 width=2)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; HashAggregate&nbsp; (cost=24.00..24.04 rows=4 width=10)
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Group Key: pagg_tab_2.c
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt;&nbsp; Seq Scan on pagg_tab_p1 pagg_tab_2&nbsp; (cost=0.00..18.00
 rows=1200 width=2)
 &nbsp; &nbsp; &nbsp; &nbsp; (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&nbsp; 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-&gt;parttypcoll[cnt] value not&nbsp; partkey-&gt;partcollation value.


And partkey-&gt;parttypcoll[cnt] is assigned from pg_attribute , which is the column c meta data.&nbsp;
Should we use partkey-&gt;partcollation value?&nbsp; 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









-- 
Tender Wang