Re: Seemingly inconsistent ORDER BY behavior

On Wed, Aug 14, 2013 at 12:01 PM, Richard Hipp <drh(at)sqlite(dot)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);