Order By query ignores punctuation marks

Question

This is with Postgresql.

A column in a table contains string values with punctuations. The values are "aac", ".aaa", "aa_b", etc. When this column is specified in order by clause, the order of results is almost random. The strings starting with a period should appear at the top, which doesn't happen. They appear somewhere in the middle.

Surprisingly, this behavior is seen with only one database. The same query works fine on database on other host.

What could be the possible reason for this?

Answer 1

The "order by" (string comparison) behaviour depends on the cluster's locale.

Answer 2

First, check the EXPLAIN and see how it's doing the sort.

• If it's calling an user-defined comparison function, look at that function.
• If it's walking an index, see if that index is using an incorrect sorting function (one that's not transitive or some such).

If EXPLAIN doesn't show anything odd, check the cluster's locale - perhaps it's doing the comparison using a locale that ignores certain characters.