Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C8EE3474E49 for ; Wed, 3 Apr 2002 16:46:51 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g33LkpF12795; Wed, 3 Apr 2002 16:46:51 -0500 (EST) To: Bernd von den Brincken Cc: pgsql-sql@postgresql.org Subject: Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) In-reply-to: <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de> References: <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de> Comments: In-reply-to Bernd von den Brincken message dated "Wed, 03 Apr 2002 21:15:44 +0200" Date: Wed, 03 Apr 2002 16:46:51 -0500 Message-ID: <12792.1017870411@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/42 X-Sequence-Number: 7307 Bernd von den Brincken writes: > on the occasion of a subselect I found a behavior in PostgreSQL 7.1 that I > consider faulty, but would like to hear comments before complaining to the > hackers: Too late ;-) > Here is the example - this fails with "ERROR: Attribute 'max_date' not > found": > SELECT content, datetime AS max_date > FROM cftext > ORDER by date_part('hour', max_date) > Whereas this works both: > a) SELECT content, datetime AS max_date > FROM cftext > ORDER by max_date > b) SELECT content, datetime AS max_date > FROM cftext > ORDER by date_part('hour', datetime) Right. The SQL92 spec disallows expressions in ORDER BY, and states that the ORDER BY items are either names or numbers of *output columns* of the SELECT. Thus, only your alternative (a) is legal per spec. Postgres chooses to also allow sorting on expressions that are not in the output list --- but such expressions are considered to be additional expressions over the input columns. Essentially it's a way to write SELECT content, datetime AS max_date, date_part('hour', datetime) AS order_val FROM cftext ORDER BY order_val (which would be a spec-legal construct) and then tell the system you didn't really want to see the order_val column in your output. SQL99 seems to have (incompatibly) redefined ORDER BY to allow expressions over the output column names, but I don't have a lot of appetite for breaking existing applications in order to conform to the SQL99 definition. I do not have any sympathy for the notion that we should accept either input or output column names in such expressions, if that's what you were hoping to suggest. It's ambiguous. > Now in this example the alternative works fine, but if you use a Sub-SELECT > there is no alternative to a named result column. Why not? You can always do something like SELECT * FROM (SELECT ... ) AS T ORDER BY x; which is legal under both SQL specs. regards, tom lane