Обсуждение: Different sort result between PostgreSQL 8.4 and 12.5

Поиск
Список
Период
Сортировка

Different sort result between PostgreSQL 8.4 and 12.5

От
gzh
Дата:

Hi, 


I have had a Perl Website working for 7 years and have had no problems

until a few weeks ago I replaced my database server with a newer one.


Database server (old): PostgreSQL 8.4 32bit

Database server (new): PostgreSQL 12.5 64bit


I run following sql in PostgreSQL 8.4 and PostgreSQL 12.5, it returns different sort results.


--PostgreSQL 8.4

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "! ascid"

 "001"

(2 rows)


--PostgreSQL 12.5

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "001"

 "! ascid"

(2 rows)


What is the reason for this and is there any easy way to maintain compatibility?


Regards,

--


gzh

Re: Different sort result between PostgreSQL 8.4 and 12.5

От
Magnus Hagander
Дата:


On Mon, Jun 27, 2022 at 1:31 PM gzh <gzhcoder@126.com> wrote:

Hi, 


I have had a Perl Website working for 7 years and have had no problems

until a few weeks ago I replaced my database server with a newer one.


Database server (old): PostgreSQL 8.4 32bit

Database server (new): PostgreSQL 12.5 64bit


I run following sql in PostgreSQL 8.4 and PostgreSQL 12.5, it returns different sort results.


--PostgreSQL 8.4

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "! ascid"

 "001"

(2 rows)


--PostgreSQL 12.5

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "001"

 "! ascid"

(2 rows)


What is the reason for this and is there any easy way to maintain compatibility?



Are these two really running on the same operating system?

This looks a lot like the locale changes included in newer versions of glibc, and is in that case dependent on an upgrade of the operating system, not an upgrade of PostgreSQL. See https://wiki.postgresql.org/wiki/Locale_data_changes for details.
 
--

Re:Re: Different sort result between PostgreSQL 8.4 and 12.5

От
gzh
Дата:


Dear Magnus：


The information you sent through to me was perfect. 
After I checked the operating system, I found that they are really different.

--PostgreSQL 8.4
LANG=ja_JP.UTF-8

--PostgreSQL 12.5
LANG=en_US.UTF-8

After I added the following syntax after the "order by ascid" in PostgreSQL 12.5 database, I got the same result as PostgreSQL 8.4

COLLATE "ja-JP-x-icu"

Thank you for being so helpful.



 2022-06-27 19:33:01，"Magnus Hagander" <magnus@hagander.net> ：



On Mon, Jun 27, 2022 at 1:31 PM gzh <gzhcoder@126.com> wrote:

Hi, 


I have had a Perl Website working for 7 years and have had no problems

until a few weeks ago I replaced my database server with a newer one.


Database server (old): PostgreSQL 8.4 32bit

Database server (new): PostgreSQL 12.5 64bit


I run following sql in PostgreSQL 8.4 and PostgreSQL 12.5, it returns different sort results.


--PostgreSQL 8.4

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "! ascid"

 "001"

(2 rows)


--PostgreSQL 12.5

---------------

pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;

   ascid

-----------

 "001"

 "! ascid"

(2 rows)


What is the reason for this and is there any easy way to maintain compatibility?



Are these two really running on the same operating system?

This looks a lot like the locale changes included in newer versions of glibc, and is in that case dependent on an upgrade of the operating system, not an upgrade of PostgreSQL. See https://wiki.postgresql.org/wiki/Locale_data_changes for details.
 
--