On Wed, Aug 14, 2013 at 12:01 PM, Richard Hipp <d...@sqlite.org> wrote:
>
> Consider the following SQL:
>
> ---------------------------------------------------
> CREATE TABLE t1(m VARCHAR(4));
> INSERT INTO t1 VALUES('az');
> INSERT INTO t1 VALUES('by');
> INSERT INTO t1 VALUES('cx');
>
> SELECT '1', substr(m,2) AS m
> FROM t1
> ORDER BY m;
>
> SELECT '2', substr(m,2) AS m
> FROM t1
> ORDER BY lower(m);
> ---------------------------------------------------
>
> Using PostgreSQL 9.1.5, the first query returns x-y-z while the second
> returns z-y-x. Is this "correct"? It certainly is surprising to me.
>
> I'm asking because the same question has come up in SQLite and whenever there
> is a language dispute in SQLite, our first instinct is to find out what
> PostgreSQL does and try to do the same thing. SQLite version 3.7.15 was
> behaving the same way as PostgreSQL 9.1.5 (unbeknownst to us at the time).
> Then a bug was written about the inconsistent behavior of ORDER BY. We fixed
> that bug so that the latest SQLite answers x-y-z in both cases. Now someone
> is complaining that the "fix" was really a 'break". Is it? Or is there an
> equivalent bug in PostgreSQL?
>
> There are, of course, many ways to resolve the ambiguity (such as using a
> unique label for the result column, or by saying "t1.m" instead of just "m"
> when you mean the column of the table). But that is not really the point
> here. The question is, how should symbolic names in the ORDER BY clause be
> resolved? Should column names in the source table take precedence over
> result column name, or should it be the other way around?
>
> Any insights are appreciated. Please advise if a different mailing list
> would be more appropriate for this question.
My guess without testing is that order by lower(m) is not what you think it is.
substr(m,2) as m
is bad form. Always use a new and unique alias, like m1. How does this work:
SELECT '2', substr(m,2) AS m1
FROM t1
ORDER BY lower(m1);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
