Re: Using column aliasses in the same query

On Sun, Apr 17, 2011 at 6:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yes, we've heard that before.  Many times.  It's not going to happen,
> and here's why: it's flat out contrary to the SQL specification, as well
> as to the basic intuitive semantics of SQL.  The SELECT list is supposed
> to be evaluated as the last step of a query (well, last except for ORDER
> BY, which is why there's an exception for that).  It's nonsensical for
> WHERE etc to depend on the results of the SELECT list.
>
> As an example of why this is important, consider
>
>        SELECT x/y AS z FROM tab WHERE y <> 0
>
> If the WHERE clause doesn't act before the SELECT list is computed,
> the query is going to fail with divisions-by-zero, exactly what the
> WHERE clause is trying to prevent.  So it'd be nonsensical to refer
> to z in the WHERE clause.

Well, refering to the computed value may be nonsensical, but
couldn't it be some sort of query rewrite? So that...

SELECT x/y AS z FROM tab WHERE y <> 0 AND z > 2

... is a shorthand for

SELECT x/y AS z FROM tab WHERE y <> 0 AND x/y > 2

No big deal, since there are lots of other ways to do this.

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2011 Tore Halvorsen || +052 0553034554