Thread: Strange results of ORDER BY clause when item begins with slash or backslash

I am seeing some unexpected results for an ORDER BY in a query.   It looks
to me as if the sorting is confused about how to handle the slash or
backslash character in a string.  It acts as if ignoring it. Here is a
sample:


          Table "public.test_table"
 Column  |          Type          | Modifiers
---------+------------------------+-----------
 item1   | character varying(500) |
 numitem | integer                |


testdb1=> select * from test_table order by upper(item1);
      item1      | numitem
-----------------+---------
 aaaaa           |     123
 bbbbb           |     234
 test            |     666
 \test\item1     |     555
 total info      |     876
 userdir example |     787
 /usr/otherdir   |     999
 variation       |     777
 \var\overland   |     444
 /var/somedir    |     888
(10 rows)

testdb1=> show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

I would have expected all the items beginning with a backslash to sort
together, and not be interspersed like this.  Can anyone advise on how I
can avoid this?   I would just reorder the data myself, but I am using
OFFSET and LIMIT, and think this will cause problems if the database does
not handle the sorting.

The backslashes were all properly escaped before insertion.

Any ideas appreciated.

Thanks,
Susan C.





----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------

On Wed, 2005-01-19 at 11:24 -0800, SCassidy@overlandstorage.com wrote:
> [non-word character being ignored by ORDER BY]
...
> testdb1=> show LC_COLLATE;
>  lc_collate
> -------------
>  en_US.UTF-8
> (1 row)

this is a 'feature' of your en_US locale:

bash$ export LC_COLLATE=en_US
bash$ (echo "usra";echo "usrq";echo "/usr/lib")| sort
usra
/usr/lib
usrq
bash$ export LC_COLLATE=C
bash$ (echo "usra";echo "usrq";echo "/usr/lib")| sort
/usr/lib
usra
usrq

maybe you should have run initdb with LC_COLLATE=C

gnari