Order By weirdness? - Carl Sopchak

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Carl Sopchak <[email protected]>
To: [email protected]
Subject: Order By weirdness?
Date: Sat, 7 Jan 2023 15:35:41 -0500
Message-ID: <[email protected]> (raw)

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Order By weirdness?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox