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

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

```

狂奔的蜗牛
1105066510(at)qq(dot)com

 

------------------ 原始邮件 ------------------
发件人: "Aleksander Alekseev" <aleksander(at)timescale(dot)com&gt;;
发送时间:&nbsp;2024年8月6日(星期二) 下午5:09
收件人:&nbsp;"pgsql-bugs"<pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;;
抄送:&nbsp;"Tender Wang"<tndrwang(at)gmail(dot)com&gt;;"狂奔的蜗牛"<1105066510(at)qq(dot)com&gt;;
主题:&nbsp;Re: BUG #18568: BUG: Result wrong when do group by on partition table!

Hi,

&gt; [...]
&gt; I continue to find out why the collation id of partkey is 16384(e.g. case_insensitive). The partkey expr info is
&gt; set in set_baserel_partition_key_exprs(), which it uses partkey-&gt;parttypcoll[cnt] value not&nbsp; partkey-&gt;partcollation value.
&gt;
&gt; And partkey-&gt;parttypcoll[cnt] is assigned from pg_attribute , which is the column c meta data.
&gt; 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
&gt; failed regress.

```
+SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+ c | count
+---+-------
+ e |&nbsp;&nbsp; 600
+ D |&nbsp;&nbsp; 600
+ C |&nbsp;&nbsp; 600
+ B |&nbsp;&nbsp; 600
+ A |&nbsp;&nbsp; 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