MySQL alias replaced by column name when creating view involving subquery

Question

Why is a column alias being replaced by the original column name when I create a view from a script? The script works, the view fails.

The script selects records using an outer query / inner query a.k.a. query / subquery. The subquery is used in the SELECT clause. Each subquery is itself a SELECT clause which becomes a column in the result set. See http://www.techonthenet.com/mysql/subqueries.php.

The alias used inside the subquery's SELECT clauses is replaced with its original column name. The alias used to give the subquery a short name is not replaced.

Here is a meta version so you can see the structure.

select `t1`.`Date` as **`*When*`**,
( select avg(t1.column) 
  from t1 
  where `t1.`Date` = `***When***`
) as `Short column name`,
from t1
group by `Date`
order by `Date`

In the View version, with aliases replaced, the subquery becomes;

(
 select avg(t1.column) 
 from t1 
 where ***`t1.`Date` = `t1.`Date`***
) as `Short column name`,

The effect of this is that the average is calculated across all dates rather than just for the date specified as When in the outer query.

Another script built the same way translates into a view without a problem. The alias is kept.

There is a difference between the clauses used in the bad and good views but it is not obvious to me that it should cause the problem.

The bad view ends with;

group by `Date`
order by `Date`

while the good one ends only with a group by clause.

Another difference is that the column being aliased in the bad view is probably of field type DATETIME, while the one in the good view ia probably one of the INT types. (it's actually week(t1.Date).

Using:

• MySQL 5.5
• MySQL Workbench 6.0.8
• Ubuntu 14.04

Answer 1

The aliases in the SELECT refer to the output of the query block, not to the processing of the query block.

The correct way to do what you want is to use a correlated subquery with table aliases:

select touter.`Date` as **`*When*`**,
        (select avg(tinner.column) 
         from t1 tinner
         where `tinner.`Date` = touter.date
        ) as `Short column name`,
from t1 as touter
group by `Date`
order by `Date`;

I have no idea why the average would be calculated for all the dates. I would expect it to return an error, or perhaps a NULL value. Perhaps your real where clause is t1.Date = Date and you expect MySQL to magically know what the second Date refers to. Don't depend on magic. Use table aliases and be explicit.