pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedObscure behavior of ORDER BY
2+ messages / 2 participants
[nested] [flat]
* Obscure behavior of ORDER BY
@ 2011-03-21 09:29 Tambet Matiisen <tambet.matiisen@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tambet Matiisen @ 2011-03-21 09:29 UTC (permalink / raw)
To: pgsql-sql
Hi everyone!
I recently noticed obscure behavior of ORDER BY. Consider this example:
select email from
(
select '@'::text as email
union all
select '.'::text as email
) a
order by email;
The result is:
email
-------
.
@
(2 rows)
This is all normal - I expect, that dot is smaller than ampersand. But
if I add anything after dot, the order is reversed:
select email from
(
select '@'::text as email
union all
select '.m'::text as email
) a
order by email
The result is:
email
-------
@
.m
(2 rows)
Why is this happening? As dot is smaller than ampersand, anything after
dot shouldn't matter.
I'm using PostgreSQL 8.4.7 on 32-bit Debian.
Thanks in advance,
Tambet
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Obscure behavior of ORDER BY
@ 2011-03-23 21:09 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tambet Matiisen <tambet.matiisen@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2011-03-23 21:09 UTC (permalink / raw)
To: Tambet Matiisen <tambet.matiisen@gmail.com>; +Cc: pgsql-sql
Hello
this behave depends on your language rules. So this behave can be ok.
pavel=# select * from (values('.'),('@'),('.xxx'),(' xxxx')) x order by 1;
column1
─────────
.
@
.xxx
xxxx
(4 rows)
you can se so string with space on start is on end and this is
correct, because spaces and white chars are ignored.
Regards
Pavel Stehule.
2011/3/21 Tambet Matiisen <tambet.matiisen@gmail.com>:
> Hi everyone!
>
> I recently noticed obscure behavior of ORDER BY. Consider this example:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.'::text as email
> ) a
> order by email;
>
> The result is:
> email
> -------
> .
> @
> (2 rows)
>
> This is all normal - I expect, that dot is smaller than ampersand. But if I
> add anything after dot, the order is reversed:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.m'::text as email
> ) a
> order by email
>
> The result is:
> email
> -------
> @
> .m
> (2 rows)
>
> Why is this happening? As dot is smaller than ampersand, anything after dot
> shouldn't matter.
>
> I'm using PostgreSQL 8.4.7 on 32-bit Debian.
>
> Thanks in advance,
> Tambet
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-03-23 21:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-21 09:29 Obscure behavior of ORDER BY Tambet Matiisen <tambet.matiisen@gmail.com>
2011-03-23 21:09 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox