> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Steve Haresnape > Sent: Wednesday, September 19, 2012 2:37 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
