From: 狂奔的蜗牛 <1105066510@qq.com>
To: Aleksander Alekseev <aleksander@timescale.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Cc: Tender Wang <tndrwang@gmail.com>
Subject: 回复： BUG #18568: BUG: Result wrong when do group by on partition table!
Date: Tue, 6 Aug 2024 19:45:59 +0800
Message-ID: <tencent_6767E6371361847E7540172904F06C1A1908@qq.com> (raw)
In-Reply-To: <CAJ7c6TPD+KMeT19jaoXjPf8_4mdQWOYXzOEyCuFDGanv=_k8UQ@mail.gmail.com>
References: <18568-2a9afb6b9f7e6ed3@postgresql.org>
<CAHewXNnKLrZYG4iqaYw=uB3XWRrYRZHo7VtcMsbUEbdbajQg2Q@mail.gmail.com>
<CAJ7c6TPD+KMeT19jaoXjPf8_4mdQWOYXzOEyCuFDGanv=_k8UQ@mail.gmail.com>
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@qq.com
------------------ 原始邮件 ------------------
发件人: "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!
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: 1105066510@qq.com, aleksander@timescale.com, pgsql-bugs@lists.postgresql.org, tndrwang@gmail.com
Subject: Re: =?gb18030?B?u9i4tKO6IEJVRyAjMTg1Njg6IEJVRzogUmVzdWx0?= =?gb18030?B?IHdyb25nIHdoZW4gZG8gZ3JvdXAgYnkgb24gcGFy?= =?gb18030?B?dGl0aW9uIHRhYmxlIQ==?=
In-Reply-To: <tencent_6767E6371361847E7540172904F06C1A1908@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox