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

Started by Nonameabout 21 years ago2 messagesgeneral
Jump to latest
#1Noname
SCassidy@overlandstorage.com

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
----------------------------------------------------------------------------------------------

#2Ragnar Hafstað
gnari@simnet.is
In reply to: Noname (#1)
Re: Strange results of ORDER BY clause when item begins

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