Seemingly inconsistent ORDER BY behavior

From: Richard Hipp <drh(at)sqlite(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Seemingly inconsistent ORDER BY behavior
Date: 2013-08-14 18:01:36
Message-ID: CALwJ=Mx_0-Tzdn7uEwXCa2JL3oyFN5zLAzWMHwHRd2F_DYWm+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sqlite(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2013-08-14 18:23:50 Re: WHERE 'Something%' LIKE ANY (array_field)
Previous Message Tim Kane 2013-08-14 18:01:20 Re: WHERE 'Something%' LIKE ANY (array_field)