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.

--
Steve Haresnape
Creative Integrity Ltd