pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bernd von den Brincken <bvdb@asa.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
Date: Wed, 03 Apr 2002 16:46:51 -0500
Message-ID: <12792.1017870411@sss.pgh.pa.us> (raw)
In-Reply-To: <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de>
References: <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de>

Bernd von den Brincken <bvdb@asa.de> 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



view thread (2+ messages)

Message-ID: <12792.1017870411@sss.pgh.pa.us>
Permalink:  ../12792.1017870411@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12792.1017870411@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, bvdb@asa.de
  Subject: Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
  In-Reply-To: <12792.1017870411@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox