回复： BUG #18568: BUG: Result wrong when do group by on partition table! - Mailing list pgsql-bugs
Hi,
I am working to compatible with Oracle in PG,
in oracle, the result used case-insensitive collation is not unique.
There are two oracle's test case:
first case:
```sql
CREATE TABLE group_by_ci_test_1 (c varchar2(20));
INSERT INTO group_by_ci_test_1 VALUES ('a');
INSERT INTO group_by_ci_test_1 VALUES ('a');
INSERT INTO group_by_ci_test_1 VALUES ('A');
SELECT c collate binary_ci, count(c) FROM group_by_ci_test_1 GROUP BY c collate binary_ci;
```
first result
```shell
CCOLLATEBINARY_CI COUNT(C)
-------------------- ----------
a 3
```
second case:
```sql
CREATE TABLE group_by_ci_test_2 (c varchar2(20));
INSERT INTO group_by_ci_test_2 VALUES ('A');
INSERT INTO group_by_ci_test_2 VALUES ('a');
INSERT INTO group_by_ci_test_2 VALUES ('a');
SELECT c collate binary_ci, count(c) FROM group_by_ci_test_2 GROUP BY c collate binary_ci;
```
second result:
```shell
CCOLLATEBINARY_CI COUNT(C)
-------------------- ----------
A 6
```
------------------ 原始邮件 ------------------
发件人: "Aleksander Alekseev" <aleksander@timescale.com>;
发送时间: 2024年8月6日(星期二) 下午5:09
收件人: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
抄送: "Tender Wang"<tndrwang@gmail.com>;"狂奔的蜗牛"<1105066510@qq.com>;
主题: Re: BUG #18568: BUG: Result wrong when do group by on partition table!
> [...]
> 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
pgsql-bugs by date: