Horton

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

Aleksander Alekseev <aleksander@timescale.com>

View thread View on postgr.es
From: Aleksander Alekseev <aleksander@timescale.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Tender Wang <tndrwang@gmail.com>, 1105066510@qq.com
Date: 2024-08-06T09:09:30Z
Lists: pgsql-bugs 
Hi,

> [...]
> 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.

```
+SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+ c | count
+---+-------
+ e |   600
+ D |   600
+ C |   600
+ B |   600
+ A |   600
+(5 rows)
```

Shouldn't we use UPPER(c) and ORDER BY in the test case to make the
results deterministic?

-- 
Best regards,
Aleksander Alekseev

Commits

  1. Disallow partitionwise grouping when collations don't match

  2. For partitionwise join, match on partcollation, not parttypcoll.