Thread: BUG #18568: BUG: Result wrong when do group by on partition table!

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.

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");

Partkey's collation stored in RelOptInfo->part_scheme, and I use it to fix the bug.

The attachment is my solution!

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

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.

PartCollMatchesExprColl()  is not strict enough.

If partcoll == InvalidOid and groupcoll != InvalidOid, PartCollMatchesExprColl() return true always.

Just determine whether groupcoll is equal to partcoll, like this "partcoll == groupcoll".

We cannot delete "if (IsA(groupexpr, RelableTyple) " branch, 

becasuse if groupexpr is RelabelType and  partcoll equal to groupcoll,  the "equal(groupexpr, partexpr) && PartKeyCollMatchesExprColl(partcoll, groupexpr_coll)" condition return false!!!

This is not what we expect.

We could rename "groupexpr_coll" to groupcoll, it looks more elegant.