MIME-Version: 1.0
References: <25e94e6e-5b18-9d45-0b7b-c23bd4c9a17b@sopchak.me>
In-Reply-To: <25e94e6e-5b18-9d45-0b7b-c23bd4c9a17b@sopchak.me>
From: Samed YILDIRIM <samed@reddoc.net>
Date: Sat, 7 Jan 2023 23:01:09 +0200
Message-ID: 
 <CAAo1mb=ocWe3_uoud3o_qGWSOqpApUwvgPr8Av60YUUqg4gcWg@mail.gmail.com>
Subject: Re: Order By weirdness?
To: Carl Sopchak <carl@sopchak.me>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fbe95d05f1b2d559"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAo1mb%3DocWe3_uoud3o_qGWSOqpApUwvgPr8Av60YUUqg4gcWg%40mail.gmail.com>
Precedence: bulk

--000000000000fbe95d05f1b2d559
Content-Type: text/plain; charset="UTF-8"

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


On Sat, 7 Jan 2023 at 22:35, Carl Sopchak <carl@sopchak.me> 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?
>
> I am using postgres version 14.3 on Fedora 37.
>
> Thanks for the help.
>
> Carl
>
>
>

--000000000000fbe95d05f1b2d559
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Carl,</div><div><br></div><div>This can be related=
 to glibc2.38 update. I recommend you to check following documents.<br></di=
v><div><br></div><div><a href=3D"https://postgresql.verite.pro/blog/2018/08=
/27/glibc-upgrade.html">https://postgresql.verite.pro/blog/2018/08/27/glibc=
-upgrade.html</a></div><div><a href=3D"https://wiki.postgresql.org/wiki/Loc=
ale_data_changes">https://wiki.postgresql.org/wiki/Locale_data_changes</a><=
/div><div><div><br></div><div><div><div><div dir=3D"ltr" class=3D"gmail_sig=
nature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div>Best regar=
ds.</div><div>Samed YILDIRIM<br></div></div></div></div><br></div></div></d=
iv></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Sat, 7 Jan 2023 at 22:35, Carl Sopchak &lt;<a href=3D"mailto:carl@sop=
chak.me">carl@sopchak.me</a>&gt; wrote:<br></div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex">
 =20

   =20
 =20
  <div>
    <p>I&#39;m seeing something (very) unexpected with ORDER BY.=C2=A0 If I=
 run
      this query:</p>
    <p>select txt<br>
      from ( values(&#39;x12345&#39;), (&#39;xz1234&#39;), (&#39;x23456&#39=
;), (&#39;xz2345&#39;),
      (&#39;x34567&#39;), (&#39;xz3456&#39;) ) a(txt)<br>
      order by txt;</p>
    <p>I get expected results with x&lt;#&gt; being sorted before xz.=C2=A0
      However, if I replace the z&#39;s with ~, giving</p>
    <p>select txt<br>
      from ( values(&#39;x12345&#39;), (&#39;x~1234&#39;), (&#39;x23456&#39=
;), (&#39;x~2345&#39;),
      (&#39;x34567&#39;), (&#39;x~3456&#39;) ) a(txt)<br>
      order by txt;</p>
    <p>I get this???</p>
    <p>=C2=A0 txt=C2=A0 =C2=A0<br>
      --------<br>
      =C2=A0x~1234<br>
      =C2=A0x12345<br>
      =C2=A0x~2345<br>
      =C2=A0x23456<br>
      =C2=A0x~3456<br>
      =C2=A0x34567<br>
    </p>
    <p>Which appears to mean that ~ is treated differently than z
      (basically ~ is ignored).=C2=A0 Same if I use other special character=
s,
      such as @.</p>
    <p>Up until stumbling into this, I have never seen such behavior
      from a database.=C2=A0 (Windows OS, yes, but I won&#39;t go there...)=
=C2=A0
      Character-based text always sorted in an alphabetic order (which
      puts special characters in different places in the ordering
      depending on encoding, but it&#39;s consistent).</p>
    <p>Two questions (which may be the same way of asking the same
      question):</p>
    <p>- How is this correct?=C2=A0 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>
  </div>


</blockquote></div>

--000000000000fbe95d05f1b2d559--