Обсуждение: Bug #738: "order by" returning different results in 7.2 and 7.21

Kareem Badr (kbadr@journyx.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
"order by" returning different results in 7.2 and 7.21

Long Description
When doing a query that uses the "order by" command, version 7.2 and version 7.21 return the records in a different
order.All of the locale environment variables are identical, and the query is identical. Here is the comparison: 

bash-2.05$ hostname
demo02.journyx.com
bash-2.05$ locale
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
LC_ALL=
bash-2.05$ psql --version
psql (PostgreSQL) 7.2
contains support for: readline, history, multibyte
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.

    id_user    |      pname
---------------+-----------------
 amber         | User
 Appenzeller_K | User
 bbunny        | User
 Birdzell_D    | User
 brad          | User
 Bradshaw_P    | User
 Brundage_C    | User
 Chen_A        | User
 Childs_D      | User
 Cooney_A      | User




bash-2.04$ hostname
dev2.int.journyx.com
bash-2.04$ locale
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
LC_ALL=
bash-2.04$ psql --version
psql (PostgreSQL) 7.2.1
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.

    id_user    |      pname
---------------+-----------------
 Appenzeller_K | User
 Birdzell_D    | User
 Bradshaw_P    | User
 Brundage_C    | User
 Chen_A        | User
 Childs_D      | User
 Cooney_A      | User
 amber         | User
 bbunny        | User
 brad          | User


Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> When doing a query that uses the "order by" command, version 7.2 and
> version 7.21 return the records in a different order. All of the
> locale environment variables are identical, and the query is
> identical.

You are showing us the *client*'s locale environment, which has little
to do with what the server's environment is, and even less to do with
what locale initdb was run in.  But it's initdb's locale that determines
what the server will consider sort ordering to be.  In this case it
looks like the 7.2 server is using en_US (or something like it) while
the 7.2.1 server is pretty clearly using C locale.  If you don't like
the setting you'll need to re-initdb ...

            regards, tom lane