Re: BUG #18857: Abnormal string comparison results

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(at)qq(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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