On Sun, Apr 17, 2011 at 6:00 PM, Tom Lane <tgl@sss.pgh.pa.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