Thread
-
Order By weirdness?
Carl Sopchak <carl@sopchak.me> — 2023-01-07T20:35:41Z
<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> -
Re: Order By weirdness?
Erik Brandsberg <erik@heimdalldata.com> — 2023-01-07T20:38:16Z
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 <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 > > > -
Re: Order By weirdness?
Carl Sopchak <carl@sopchak.me> — 2023-01-07T20:45:10Z
<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-characters" 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-characters</a></div> <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:carl@sopchak.me" moz-do-not-send="true" class="moz-txt-link-freetext">carl@sopchak.me</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> -
Re: Order By weirdness?
Samed YILDIRIM <samed@reddoc.net> — 2023-01-07T21:01:09Z
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 > > >