public inbox for [email protected]
help / color / mirror / Atom feedOrder By weirdness?
4+ messages / 3 participants
[nested] [flat]
* Order By weirdness?
@ 2023-01-07 20:35 Carl Sopchak <[email protected]>
2023-01-07 20:38 ` Re: Order By weirdness? Erik Brandsberg <[email protected]>
2023-01-07 21:01 ` Re: Order By weirdness? Samed YILDIRIM <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Carl Sopchak @ 2023-01-07 20:35 UTC (permalink / raw)
To: [email protected]
<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<#> 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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Order By weirdness?
2023-01-07 20:35 Order By weirdness? Carl Sopchak <[email protected]>
@ 2023-01-07 20:38 ` Erik Brandsberg <[email protected]>
2023-01-07 20:45 ` Re: Order By weirdness? Carl Sopchak <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Erik Brandsberg @ 2023-01-07 20:38 UTC (permalink / raw)
To: Carl Sopchak <[email protected]>; +Cc: [email protected]
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...
On Sat, Jan 7, 2023 at 3:35 PM Carl Sopchak <[email protected]> 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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Order By weirdness?
2023-01-07 20:35 Order By weirdness? Carl Sopchak <[email protected]>
2023-01-07 20:38 ` Re: Order By weirdness? Erik Brandsberg <[email protected]>
@ 2023-01-07 20:45 ` Carl Sopchak <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Carl Sopchak @ 2023-01-07 20:45 UTC (permalink / raw)
To: [email protected]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Thanks, that did the trick. The surprising thing in the link is
"Most locales would ignore the leading <code>#</code> for
sorting. " I guess I've been around too long and hadn't noticed.
:-)<br>
</p>
<div class="moz-cite-prefix">On 1/7/23 15:38, Erik Brandsberg wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAFcck8GQXkUQGsTgtdD65_+9VMPkx8iW8t+_3Oe_=J3oyWf7QA@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">This will relate to collation order, which is
something that you can specify. Please see: <a
href="https://stackoverflow.com/questions/17225652/how-can-i-sort-the-postgres-column-with-certain-special...;
moz-do-not-send="true" class="moz-txt-link-freetext">https://stackoverflow.com/questions/17225652/how-can-i-sort-the-postgres-column-with-certain-special...;
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Sat, Jan 7, 2023 at 3:35 PM
Carl Sopchak <<a href="mailto:[email protected]"
moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<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<#> 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>
</div>
</blockquote>
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Order By weirdness?
2023-01-07 20:35 Order By weirdness? Carl Sopchak <[email protected]>
@ 2023-01-07 21:01 ` Samed YILDIRIM <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Samed YILDIRIM @ 2023-01-07 21:01 UTC (permalink / raw)
To: Carl Sopchak <[email protected]>; +Cc: [email protected]
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 <[email protected]> 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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-07 21:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-07 20:35 Order By weirdness? Carl Sopchak <[email protected]>
2023-01-07 20:38 ` Erik Brandsberg <[email protected]>
2023-01-07 20:45 ` Carl Sopchak <[email protected]>
2023-01-07 21:01 ` Samed YILDIRIM <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox