Order By weirdness?

Carl Sopchak <carl@sopchak.me>
From: Carl Sopchak <carl@sopchak.me>
To: pgsql-sql@lists.postgresql.org
Date: 2023-01-07T20:35:41Z
Lists: pgsql-sql
Attachments

(unnamed) (text/html)
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>I'm seeing something (very) unexpected with ORDER BY.  If I run
      this query:</p>
    <p>select txt<br>
      from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'),
      ('x34567'), ('xz3456') ) a(txt)<br>
      order by txt;</p>
    <p>I get expected results with x&lt;#&gt; being sorted before xz. 
      However, if I replace the z's with ~, giving</p>
    <p>select txt<br>
      from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'),
      ('x34567'), ('x~3456') ) a(txt)<br>
      order by txt;</p>
    <p>I get this???</p>
    <p>  txt   <br>
      --------<br>
       x~1234<br>
       x12345<br>
       x~2345<br>
       x23456<br>
       x~3456<br>
       x34567<br>
    </p>
    <p>Which appears to mean that ~ is treated differently than z
      (basically ~ is ignored).  Same if I use other special characters,
      such as @.</p>
    <p>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).</p>
    <p>Two questions (which may be the same way of asking the same
      question):</p>
    <p>- 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.<br>
    </p>
    <p>- What do I need to do to get a strictly character-based sort in
      ORDER BY?</p>
    <p>I am using postgres version 14.3 on Fedora 37.<br>
    </p>
    <p>Thanks for the help.</p>
    <p>Carl</p>
    <p><br>
    </p>
  </body>
</html>