Received: from athene.i.eunet.no (athene.i.eunet.no [193.71.2.52]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5QFPPa09013 for ; Tue, 26 Jun 2001 11:25:25 -0400 (EDT) (envelope-from tih@athene.i.eunet.no) Received: by athene.i.eunet.no (Postfix, from userid 1001) id 1A9E3240F; Tue, 26 Jun 2001 17:25:14 +0200 (CEST) To: Tom Lane Cc: Weiping He , pgsql-docs@postgresql.org Subject: Re: doc question about column name alias References: <3B374CF6.C1AB82F0@zhengmai.com.cn> <15864.993483354@sss.pgh.pa.us> From: Tom Ivar Helbekkmo Date: 26 Jun 2001 17:25:14 +0200 In-Reply-To: <15864.993483354@sss.pgh.pa.us> (Tom Lane's message of "Mon, 25 Jun 2001 11:35:54 -0400") Message-ID: <867kxzjmat.fsf@athene.i.eunet.no> Lines: 24 User-Agent: Gnus/5.090004 (Oort Gnus v0.04) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200106/42 X-Sequence-Number: 840 Tom Lane writes: >> SELECT a AS b FROM table1 ORDER BY a; >> I think it should be: >> SELECT a AS b FROM table1 ORDER BY b; > > You are correct that the latter is the SQL-standard version. In fact, this becomes pretty obvious when you consider the theoretical ordering of the evaluation of the various parts of the statement. The ORDER BY is executed *after* the SELECT part, so it only knows about the column name 'b', and doesn't know what 'a' refers to. Actually, ORDER BY isn't even part of the SELECT statement, really, but hides an implicit CURSOR operation taking place during data output. Allowing the first version to work is of questionable value, since it might cause errors and confusion when code is modified. Consider the case where you have "SELECT a AS b, b AS a ...". I've done it! :-) The order of evaluation is: FROM, WHERE, GROUP BY, HAVING, SELECT, and, finally, ORDER BY. -tih -- The basic difference is this: hackers build things, crackers break them.