Re: Seemingly inconsistent ORDER BY behavior

On Wed, Aug 14, 2013 at 12:01 PM, Richard Hipp <drh@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
certainlyis 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
firstinstinct is to find out what PostgreSQL does and try to do the same thing.  SQLite version 3.7.15 was behaving the
sameway as PostgreSQL 9.1.5 (unbeknownst to us at the time).  Then a bug was written about the inconsistent behavior of
ORDERBY.  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
questionis, how should symbolic names in the ORDER BY clause be resolved?  Should column names in the source table take
precedenceover 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);