agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feed"order by" weirdness
7+ messages / 6 participants
[nested] [flat]
* "order by" weirdness
@ 1999-09-27 00:59 Hein Roehrig <hein@acm.org>
0 siblings, 0 replies; 7+ messages in thread
From: Hein Roehrig @ 1999-09-27 00:59 UTC (permalink / raw)
To: pgsql-sql
Hello,
consider these three statements:
create table t (id integer, date datetime ) ;
select id from t group by id ;
select id from t group by id order by max(date) ;
Is it correct behavior that the second select returns one row where
as the first select returns zero rows?
Thanks in advance for any help,
Hein
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "order by" weirdness
@ 1999-09-27 07:52 Dipankar Chakrabarti <dipanc@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dipankar Chakrabarti @ 1999-09-27 07:52 UTC (permalink / raw)
To: pgsql-sql@hub.org
>Hello,
Dear Hein,
What are you trying to do with order by max(date)?
Order by date will give proper result. For that
matter group by clause already does ordering, no need
to put a order by there.
Regards,
Dipankar
>consider these three statements:
>
>create table t (id integer, date datetime ) ;
>select id from t group by id ;
>select id from t group by id order by max(date) ;
>
>Is it correct behavior that the second select returns
>one row where
>as the first select returns zero rows?
>Thanks in advance for any help,
>Hein
__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Re: "order by" weirdness
@ 1999-09-27 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dipankar Chakrabarti <dipanc@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 1999-09-27 13:47 UTC (permalink / raw)
To: pgsql-sql@hub.org
>> consider these three statements:
>>
>> create table t (id integer, date datetime ) ;
>> select id from t group by id ;
>> select id from t group by id order by max(date) ;
>>
>> Is it correct behavior that the second select returns
>> one row whereas the first select returns zero rows?
No, it is not. (Although it took a few rounds of discussion in the
mailing lists to get everyone to agree on that... if you check the
archives you will find this issue has come up repeatedly.)
I have in fact just fixed this in current sources. So, in 6.6 and
later, both statements will return zero rows if t is empty.
Note that with aggregates and no GROUP BY, you will get a row:
select count(id) from t ;
count
-----
0
(1 row)
select max(id) from t ;
max
---
(1 row)
which is correct behavior and will not change.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Order By weirdness?
@ 2023-01-07 20:35 Carl Sopchak <carl@sopchak.me>
0 siblings, 2 replies; 7+ messages in thread
From: Carl Sopchak @ 2023-01-07 20:35 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
<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] 7+ messages in thread
* Re: Order By weirdness?
@ 2023-01-07 20:38 Erik Brandsberg <erik@heimdalldata.com>
parent: Carl Sopchak <carl@sopchak.me>
1 sibling, 1 reply; 7+ messages in thread
From: Erik Brandsberg @ 2023-01-07 20:38 UTC (permalink / raw)
To: Carl Sopchak <carl@sopchak.me>; +Cc: pgsql-sql@lists.postgresql.org
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 <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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Order By weirdness?
@ 2023-01-07 20:45 Carl Sopchak <carl@sopchak.me>
parent: Erik Brandsberg <erik@heimdalldata.com>
0 siblings, 0 replies; 7+ messages in thread
From: Carl Sopchak @ 2023-01-07 20:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
<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: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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Order By weirdness?
@ 2023-01-07 21:01 Samed YILDIRIM <samed@reddoc.net>
parent: Carl Sopchak <carl@sopchak.me>
1 sibling, 0 replies; 7+ messages in thread
From: Samed YILDIRIM @ 2023-01-07 21:01 UTC (permalink / raw)
To: Carl Sopchak <carl@sopchak.me>; +Cc: pgsql-sql@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-01-07 21:01 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-27 00:59 "order by" weirdness Hein Roehrig <hein@acm.org>
1999-09-27 07:52 Re: "order by" weirdness Dipankar Chakrabarti <dipanc@yahoo.com>
1999-09-27 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-07 20:35 Order By weirdness? Carl Sopchak <carl@sopchak.me>
2023-01-07 20:38 ` Re: Order By weirdness? Erik Brandsberg <erik@heimdalldata.com>
2023-01-07 20:45 ` Re: Order By weirdness? Carl Sopchak <carl@sopchak.me>
2023-01-07 21:01 ` Re: Order By weirdness? Samed YILDIRIM <samed@reddoc.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox