List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:April 7 2004 3:53am
Subject:Re: Why can't I use an "AS" value in the WHERE clause.
View as plain text  
Pete Harlan wrote:

> On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote:
> 
>>At 17:29 -0700 4/5/04, Daevid Vincent wrote:
>>
>>>I'm curious when will I be able to do something like this:
>>>
>>>SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as
>>>active FROM wifi_table WHERE active = 1;
>>
>>I think you'll never be able to do it.
>>
>>The stuff after the SELECT is calculated based on the rows selected
>>by the WHERE.  The WHERE therefore cannot be based on the stuff after
>>the SELECT.
>  
> The parser has seen the 'as', though, and could expand it in the where
> clause so the user doesn't have to do it (and do it correctly, and
> maintain it in parallel).
> 
> The problem is MySQL can't just start doing this without breaking
> queries that depend on it not happening.  (If the 'as' alias is the
> same as a field name, for example.)
> 
> So I doubt it will happen, but not because it couldn't be done.
> 
> --Pete

That's all true, but even ignoring the consequences, it's probably not worth 
the effort, because the alias does work in a HAVING clause, as Matt W 
already pointed out.  Furthermore, as Paul says, a function of a column 
cannot be used to decide which rows to look at, because you have to look at 
the rows to calculate the value of the function.  Hence, there is no 
advantage to putting the calculated condition in the WHERE clause relative 
to putting it in the HAVING clause.  That is, either way, it will be used to 
screen rows after looking at them.

Michael

Thread
Why can't I use an "AS" value in the WHERE clause.Daevid Vincent6 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Paul DuBois6 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.Joe Rhett6 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.(Pete Harlan)6 Apr
      • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen7 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Matt W6 Apr
  • Re: Why can't I use an "AS" value in the WHERE clause.Adam7 Apr
    • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen7 Apr
      • Re: Why can't I use an "AS" value in the WHERE clause.Adam8 Apr
        • Re: Why can't I use an "AS" value in the WHERE clause.Michael Stassen8 Apr
          • Re: Why can't I use an "AS" value in the WHERE clause.Adam10 Apr