Subject: Order By weirdness?

From: Carl Sopchak <[email-masked]>
Date: Sat, 7 Jan 2023 15:35:41 -0500

I'm seeing something (very) unexpected with ORDER BY.  If I run
this query:

select txt

from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'),
('x34567'), ('xz3456') ) a(txt)

order by txt;

I get expected results with x<#> being sorted before xz.
However, if I replace the z's with ~, giving

select txt

from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'),
('x34567'), ('x~3456') ) a(txt)

order by txt;

I get this???

txt

--------

x~1234

x12345

x~2345

x23456

x~3456

x34567

Which appears to mean that ~ is treated differently than z
(basically ~ is ignored).  Same if I use other special characters,
such as @.

Up until stumbling into this, I have never seen such behavior
from a database.  (Windows OS, yes, but I won't go there...)
Character-based text always sorted in an alphabetic order (which
puts special characters in different places in the ordering
depending on encoding, but it's consistent).

Two questions (which may be the same way of asking the same
question):

- How is this correct?  I can see where this could be useful in
limited scenarios, but IMHO it makes no sense as a default sort
order.

- What do I need to do to get a strictly character-based sort in
ORDER BY?

I am using postgres version 14.3 on Fedora 37.

Thanks for the help.

Carl

---

From: Erik Brandsberg <[email-masked]>
Date: Sat, 7 Jan 2023 15:38:16 -0500

This will relate to collation order, which is something that you can
specify.  Please see:
https://stackoverflow.com/questions/17225652/how-can-i-sort-the-postgres-column-with-certain-special-characters

On Sat, Jan 7, 2023 at 3:35 PM Carl Sopchak <[email-masked]> wrote:

> I'm seeing something (very) unexpected with ORDER BY.  If I run this query:
>
> select txt
> from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'), ('x34567'),
> ('xz3456') ) a(txt)
> order by txt;
>
> I get expected results with x<#> being sorted before xz.  However, if I
> replace the z's with ~, giving
>
> select txt
> from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'), ('x34567'),
> ('x~3456') ) a(txt)
> order by txt;
>
> I get this???
>
>   txt
> --------
>  x~1234
>  x12345
>  x~2345
>  x23456
>  x~3456
>  x34567
>
> Which appears to mean that ~ is treated differently than z (basically ~ is
> ignored).  Same if I use other special characters, such as @.
>
> Up until stumbling into this, I have never seen such behavior from a
> database.  (Windows OS, yes, but I won't go there...)  Character-based text
> always sorted in an alphabetic order (which puts special characters in
> different places in the ordering depending on encoding, but it's
> consistent).
>
> Two questions (which may be the same way of asking the same question):
>
> - How is this correct?  I can see where this could be useful in limited
> scenarios, but IMHO it makes no sense as a default sort order.
>
> - What do I need to do to get a strictly character-based sort in ORDER BY?
>

---

From: Carl Sopchak <[email-masked]>
Date: Sat, 7 Jan 2023 15:45:10 -0500

Thanks, that did the trick.  The surprising thing in the link is
"Most locales would ignore the leading # for
sorting. "  I guess I've been around too long and hadn't noticed.
:-)

On 1/7/23 15:38, Erik Brandsberg wrote:

This will relate to collation order, which is
something that you can specify.  Please see:  https://stackoverflow.com/questions/17225652/how-can-i-sort-the-postgres-column-with-certain-special-characters

On Sat, Jan 7, 2023 at 3:35 PM
Carl Sopchak <[email-masked]>
wrote:

I'm seeing something (very) unexpected with ORDER BY.  If
I run this query:

select txt

from ( values('x12345'), ('xz1234'), ('x23456'),
('xz2345'), ('x34567'), ('xz3456') ) a(txt)

order by txt;

I get expected results with x<#> being sorted
before xz.  However, if I replace the z's with ~, giving

select txt

from ( values('x12345'), ('x~1234'), ('x23456'),
('x~2345'), ('x34567'), ('x~3456') ) a(txt)

order by txt;

I get this???

txt

--------

x~1234

x12345

x~2345

x23456

x~3456

x34567

Which appears to mean that ~ is treated differently than
z (basically ~ is ignored).  Same if I use other special
characters, such as @.

Up until stumbling into this, I have never seen such
behavior from a database.  (Windows OS, yes, but I won't
go there...)  Character-based text always sorted in an
alphabetic order (which puts special characters in
different places in the ordering depending on encoding,
but it's consistent).

Two questions (which may be the same way of asking the
same question):

- How is this correct?  I can see where this could be
useful in limited scenarios, but IMHO it makes no sense as
a default sort order.

- What do I need to do to get a strictly character-based
sort in ORDER BY?

Thanks for the help.

Carl

---

From: Samed YILDIRIM <[email-masked]>
Date: Sat, 7 Jan 2023 23:01:09 +0200

Hi Carl,

This can be related to glibc2.38 update. I recommend you to check following
documents.

https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
https://wiki.postgresql.org/wiki/Locale_data_changes

Best regards.
Samed YILDIRIM