Thread

1. BUG #18857: Abnormal string comparison results

   PG Bug reporting form <noreply@postgresql.org> — 2025-03-20T09:53:32Z

   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)
   
   

2. Re: BUG #18857: Abnormal string comparison results

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-20T14:46:15Z

   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
   
   
   
   

3. Re: BUG #18857: Abnormal string comparison results

   Bruce Momjian <bruce@momjian.us> — 2025-03-20T15:00:58Z

   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.