Re: RFE: Column aliases in WHERE clauses

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Steve Haresnape
> Sent: Wednesday, September 19, 2012 2:37 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] RFE: Column aliases in WHERE clauses
>
> Hi There,
>
> I've snipped a piece for the daily digest because I take issue with what's
> asserted here as a reason for not allowing aliases in where clauses.
>
> << snip This isn't just academic nit-picking either, because the SELECT
> expressions might not be valid for rows that don't pass WHERE etc.
> Consider
> SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation
> *must* apply WHERE before computing the SELECT
> expressions, or it'll get zero-divide failures that should not happen.
end
> snip>>
>
> Irrespective of whether the standard prohibits aliases in where clauses,
the
> reasoning here is irrelevant to the discussion at hand.
>
> If I say:
> SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for
> arguments sake) (in a SQL dialect that supports it) then I must expect
inverse
> to be evaluated for every row, exactly as if I said:
> SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5
>
> It's surely not the role of the standard to protect us from the
consequences
> of our own folly.
>
> Since some dialects support the idiom and others don't there can't be any
> compelling reason to withhold support. It's really a matter of style.
> For my money the DRY style is better.
>

There are any number of idioms that other dialects (and even PostgreSQL)
support for compatibility or standards reasons that, if decided upon now,
would not be included.

The SQL execution model is, from what I can infer, single-pass and linear
and while necessitating sometimes verbose syntax it makes execution
considerably less-problematic and more deterministic by the simple fact that
column names are more narrowly scoped. While I can and have seen situations
where such a feature would be handy working around it is not that difficult.
For really complex expressions coding the formula into a (ideally immutable)
function is a better solution anyway.

As to Tom's example its main implication is that the WHERE-clause has to be
evaluated before the SELECT-list in the single-pass linear model. That is
quite relevant if not the MAIN point of the example.

I could maybe see something like the following having some value:

SELECT inverse
FROM data
WHERE x<>0 AND inverse > .5
MACRO inverse (1/x)

Apart from all this I'll simply say that because the feature itself has
value it is the means of implementation that needs to be discussed and not
the merits of the feature itself. Since no new capabilities are being
added, just verbosity reduction, the hurdle to spend development time on
this is pretty darn high. To phrase it differently I do not believe that
the core team would outright reject the idea of making aliases work IF they
were presented with a suitable implementation; it just sounds like they are
"withholding support" to the extent that they have not been convinced to do
the work themselves. As an outsider I can understand, and in this case
agree with, that position.

David J.