pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18857: Abnormal string comparison results
3+ messages / 3 participants
[nested] [flat]
* BUG #18857: Abnormal string comparison results
@ 2025-03-20 09:53 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2025-03-20 09:53 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2322370369@qq.com
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
(1 row)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18857: Abnormal string comparison results
@ 2025-03-20 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-03-20 14:46 UTC (permalink / raw)
To: 2322370369@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18857: Abnormal string comparison results
@ 2025-03-20 15:00 Bruce Momjian <bruce@momjian.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2025-03-20 15:00 UTC (permalink / raw)
To: 2322370369@qq.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-20 15:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-20 09:53 BUG #18857: Abnormal string comparison results PG Bug reporting form <noreply@postgresql.org>
2025-03-20 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-20 15:00 ` Bruce Momjian <bruce@momjian.us>
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