BUG #18857: Abnormal string comparison results

PG Bug reporting form <noreply@postgresql.org> writes:

As shown in the example below, on the ASCII table ‘?’ is larger than ‘&’. I
don't think ‘t’ should be returned here. In the MySQL database it does
return 0.
Is there some special consideration here?

Read about collations at

https://www.postgresql.org/docs/current/collation.html

You are probably using a non-C collation. You could say

postgres=# SELECT ('?' <= '&*CcCV5' collate "C");
?column?
----------
f
(1 row)

but more likely you want to switch it at the database level.

regards, tom lane

On Thu, Mar 20, 2025 at 09:53:32AM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 18857
Logged by: dlyixue Xu
Email address: 2322370369@qq.com
PostgreSQL version: 17.3
Operating system: Ubuntu 22.04
Description:

When I use a conditional query, I find that it returns results that are not
as expected. After checking, I found that there is a problem with string
comparison here.
As shown in the example below, on the ASCII table ‘?’ is larger than ‘&’. I
don't think ‘t’ should be returned here. In the MySQL database it does
return 0.
Is there some special consideration here?

postgres=# SELECT ('?' <= '&*CcCV5');
?column?
----------
t

I think your collation is ignoring punctuation:

SELECT '?Z' <= '&A';
?column?
----------
f

SELECT '?A' <= '&Z';
?column?
----------
t

SELECT '?' <= '&*CcCV5' COLLATE "C";
?column?
----------
f

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.