Seemingly inconsistent ORDER BY behavior - Mailing list pgsql-general

From Richard Hipp
Subject Seemingly inconsistent ORDER BY behavior
Date
Msg-id CALwJ=Mx_0-Tzdn7uEwXCa2JL3oyFN5zLAzWMHwHRd2F_DYWm+A@mail.gmail.com
Whole thread Raw
Responses Re: Seemingly inconsistent ORDER BY behavior  (Scott Marlowe <scott.marlowe@gmail.com>)
Re: Seemingly inconsistent ORDER BY behavior  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Seemingly inconsistent ORDER BY behavior  (rob stone <floriparob@gmail.com>)
List pgsql-general
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.
--
D. Richard Hipp
drh@sqlite.org

pgsql-general by date:

Previous
From: Tim Kane
Date:
Subject: Re: WHERE 'Something%' LIKE ANY (array_field)
Next
From: Kevin Grittner
Date:
Subject: Re: WHERE 'Something%' LIKE ANY (array_field)