pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting Discrepancy in PostgreSQL 14.13
4+ messages / 4 participants
[nested] [flat]
* Sorting Discrepancy in PostgreSQL 14.13
@ 2024-11-14 12:49 [3반]김민지_4904 <hzuiw33@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: [3반]김민지_4904 @ 2024-11-14 12:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
==============================================
POSTGRESQL BUG REPORT TEMPLATE
==============================================
Your name : minji-kim
Your email address : hzuiw33@gmail.com
# System Configuration:
---------------------
Architecture (example: Intel Pentium) : Intel(R)
Core(TM) Ultra 7 155H
Operating System (example: Linux 2.4.18) : VMware Workstation
Pro (Ubuntu-22.04)
PostgreSQL version (example: PostgreSQL 9.6.6) : PostgreSQL 14.13
Compiler used (example: gcc 3.3.5) : X (sudo apt
install postgresql postgresql-contrib)
# Please enter a FULL description of your problem:
------------------------------------------------
Sorting Discrepancy in PostgreSQL 14.13
When running the following command in PostgreSQL 14.13:
```sql
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 (c0) VALUES ('-10'), ('20'), ('-5'), ('15'), ('-25');
SELECT c0, MIN(ABS(CAST(c0 AS BIGINT))) OVER (ORDER BY c0 NULLS FIRST) AS
min_function_cast FROM t0;
DROP TABLE IF EXISTS t0;
```
The result is:
```
c0 | min_function_cast
-----+-------------------
-10 | 10
15 | 10
20 | 10
-25 | 10
-5 | 5
(5 rows)
```
However, in other DBMS (SQLite, MySQL, OracleDB) and PostgreSQL version
17.0, the output is:
```
c0 | min_function_cast
-----+-------------------
-10 | 10
-25 | 10
-5 | 5
15 | 5
20 | 5
(5 rows)
```
This discrepency is due to different string sorting orders.
The minimized PoC is:
```sql
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 (c0) VALUES ('-10'), ('20'), ('-5'), ('15'), ('-25');
SELECT c0 FROM t0 ORDER BY c0;
```
In PostgreSQL 14.13, the order is incorrect:
```
-10
15
20
-25
-5
```
While the correct order should be:
```
-10
-25
-5
10
20
```
as '-' is smaller than '1', or '2' in ascii.
I'm doubtful this is a collation issue, as most collations basically
respect ASCII order.
Even if this issue appears to be related to collation, no warnings are
provided when migrating from this version.
# Please describe a way to repeat the problem.
# Please try to provide a concise reproducible example, if at all possible:
----------------------------------------------------------------------
Running the following command in PostgreSQL 14.13:
```sql
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 (c0) VALUES ('-10'), ('20'), ('-5'), ('15'), ('-25');
SELECT c0, MIN(ABS(CAST(c0 AS BIGINT))) OVER (ORDER BY c0 NULLS FIRST) AS
min_function_cast FROM t0;
DROP TABLE IF EXISTS t0;
```
Results in the incorrect sorting behavior.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Using the correct sort criteria will solve this problem.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sorting Discrepancy in PostgreSQL 14.13
@ 2024-11-14 14:48 Tomas Vondra <tomas@vondra.me>
parent: [3반]김민지_4904 <hzuiw33@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tomas Vondra @ 2024-11-14 14:48 UTC (permalink / raw)
To: [3반]김민지_4904 <hzuiw33@gmail.com>; pgsql-bugs@lists.postgresql.org
Hi,
On 11/14/24 13:49, [3반]김민지_4904 wrote:
>
> ...
>
> The minimized PoC is:
>
> ```sql
> CREATE TABLE t0 (c0 TEXT);
> INSERT INTO t0 (c0) VALUES ('-10'), ('20'), ('-5'), ('15'), ('-25');
> SELECT c0 FROM t0 ORDER BY c0;
> ```
>
> In PostgreSQL 14.13, the order is incorrect:
> ```
> -10
> 15
> 20
> -25
> -5
> ```
>
> While the correct order should be:
>
> ```
> -10
> -25
> -5
> 10
> 20
> ```
>
> as '-' is smaller than '1', or '2' in ascii.
>
> I'm doubtful this is a collation issue, as most collations basically
> respect ASCII order.
>
This is 99.999% due to the collation, so which collations are being used
on these systems? Also, I don't get this "incorrect" behavior on 14.13,
it behaves the same as 17 for me, producing the expected result.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sorting Discrepancy in PostgreSQL 14.13
@ 2024-11-15 04:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomas Vondra <tomas@vondra.me>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-11-15 04:25 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: [3반]김민지_4904 <hzuiw33@gmail.com>; pgsql-bugs@lists.postgresql.org
Tomas Vondra <tomas@vondra.me> writes:
> On 11/14/24 13:49, [3반]김민지_4904 wrote:
>> I'm doubtful this is a collation issue, as most collations basically
>> respect ASCII order.
> This is 99.999% due to the collation, so which collations are being used
> on these systems? Also, I don't get this "incorrect" behavior on 14.13,
> it behaves the same as 17 for me, producing the expected result.
It surely is a collation issue. Using a glibc-based system, I get
u8=# CREATE TABLE t0 (c0 TEXT);
CREATE TABLE
u8=# INSERT INTO t0 (c0) VALUES ('-10'), ('20'), ('-5'), ('15'), ('-25');
INSERT 0 5
u8=# select * from t0 order by c0 collate "C";
c0
-----
-10
-25
-5
15
20
(5 rows)
u8=# select * from t0 order by c0 collate "en_US";
c0
-----
-10
15
20
-25
-5
(5 rows)
(In point of fact, most glibc collations do NOT "respect ASCII order".
They tend to ignore punctuation until it's needed as a tiebreaker.)
So this is surely down to the PG 14.13 installation having a different
default collation than whatever it's compared to, which most likely
is caused by having run initdb with a different locale environment.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sorting Discrepancy in PostgreSQL 14.13
@ 2024-11-20 08:38 Peter Eisentraut <peter@eisentraut.org>
parent: [3반]김민지_4904 <hzuiw33@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2024-11-20 08:38 UTC (permalink / raw)
To: [3반]김민지_4904 <hzuiw33@gmail.com>; pgsql-bugs@lists.postgresql.org
On 14.11.24 13:49, [3반]김민지_4904 wrote:
> as '-' is smaller than '1', or '2' in ascii.
>
> I'm doubtful this is a collation issue, as most collations basically
> respect ASCII order.
See also here for a possible explanation:
https://peter.eisentraut.org/blog/2023/04/12/how-collation-of-punctuation-and-whitespace-works
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-11-20 08:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-14 12:49 Sorting Discrepancy in PostgreSQL 14.13 [3반]김민지_4904 <hzuiw33@gmail.com>
2024-11-14 14:48 ` Tomas Vondra <tomas@vondra.me>
2024-11-15 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-20 08:38 ` Peter Eisentraut <peter@eisentraut.org>
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