MySQL Bugs: #1941: Allow aliases in WHERE clauses for queries without GROUP BY

Bug #1941	Allow aliases in WHERE clauses for queries without GROUP BY
Submitted:	24 Nov 2003 21:26	Modified:	27 Nov 2003 4:57
Reporter:	Bob Terrell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
I realize that it is documented (at http://www.mysql.com/doc/en/Problems_with_alias.html and 
http://www.mysql.com/doc/en/SELECT.html) that you cannot use an alias in a WHERE clause, and 
that it is not standard SQL.

However, it would be nice if a query does not contain a GROUP BY clause or other aggregate 
function allowed aliases in a WHERE clause, since WHERE is generally considered "better" than 
HAVING. For example, the (illegal) query:

SELECT children.cid, DATE_FORMAT( children.birthday, '%b %e' ) AS birthday, TO_DAYS( 
DATE_FORMAT( birthday, '00-%m-%d' ) ) - TO_DAYS( DATE_FORMAT( CURDATE( ) , '00-%m-%d' ) ) 
AS days FROM children
WHERE ( days > 0 AND days < 60 ) OR ( days < ( 60 - 365 ) ) ORDER BY days

does not depend on anything other than the current row to determine days. And clearly, the above 
is easier to read than copying what days is aliasing into the WHERE clause three times. (I would 
assume it also saves four function calls per row, as well.)

How to repeat:
Just try running that (or a similar) query. It doesn't work.

Suggested fix:
Yeah, it'd be a pain in the neck to work this out, but it'd be nice.

That's impossible.
At first glance your request sounds nice and usefull, but there is very serious problem with it.

Imagine simpler example:

SELECT a + b AS my_sum FROM my_table WHERE my_sum = 1;

Now imagine you are Server who has to execute this query. What you ened to to do step by step is:

1) Open my_table
2) Find rows in table for which WHERE clause is True
3) Extract values of a and b for each of these rows
4) For each row send to client result of (a + b) labeled as my_sum

Do you see the problem? Alias does not exist before step 4) hence it is impossible to use it in step 1)

This is why HAVING clause will work with alias as HAVING is always "applied" last. In our notaton above it would be

5) Filter only rows satisfying HAVING clause

The way to rewrite our example query is:
SELECT a + b AS my_sum FROM my_table WHERE a + b = 1;

Yes I know in your case it will mean long WHERE caluse, but this is how it works with Relational Databases

This will sound weird, but...

I see why it can't work, but I don't see why it can't work. The server has to parse the query anyway, 
and my understanding is that it doesn't take that much time compared to actually running the 
query itself. Maybe in the parsing stage a text replacement could occur? Such that the server, when 
it actually runs it, sees "...WHERE a + b = 1" but the user could write "...WHERE my_sum=1".

Another thing I would find useful (and might also be possible at a parser level) is a query like,

SELECT 1 AS a, 2 AS b, a + b AS c

in which aliases are used later in the select statement. (You can imagine how this would help my 
example. And I shortened it considerably!)

I realize this can all fall apart with a group by, but aside from that, it seems it could be done.

Again, I realize this isn't standard SQL. I realize this is not a bug. (Isn't there a better option for 
that? Maybe just, "Denied"?) But it would make queries easier to maintain.

(As a side note, what would likely be faster? A having clause, or calling the date-related functions 
an extra two times per row?)

If I understand you well you propose something like Macro preprocessor in parser where Alias is assumed Macro and replaced everywhere in the query with original content.

First of all there is no benefit in terms of speed or Optimization.

Second this is impossible as Alias is allowded to override Column name.

Consider following query:

SELECT a * 10 AS a FROM table WHERE a > 100 HAVING a > 1000;

Ok. Suppose your Alias=Macro prerpocessor exist. What WHERE clause should match on this case with your proposed prerpocessing? Column `a` or alias `a`?

As you see even if someone implements something like this it wll do more harm than it will help.

In case above SQL standard does not allow dual interpretation. There is one and only one way to interpret this query.

 WHERE clause compares `a`-the-column to 100 while HAVING clause compares `a`-the-alias to 1000

> (As a side note, what would likely be faster? A having clause, or
> calling the date-related functions 
> an extra two times per row?)

This question is more for Support or general public list mysql@lists.mysql.com not for Bugs database.

By the way if you reread my examples you will find the answer is obvious.