MySQL alias replaced by column name when creating view involving subquery -

why column alias being replaced original column name when create view script? script works, view fails.

the script selects records using outer query / inner query a.k.a. query / subquery. subquery used in select clause. each subquery select clause becomes column in result set. see http://www.techonthenet.com/mysql/subqueries.php.

the alias used inside subquery's select clauses replaced original column name. alias used give subquery short name not replaced.

here meta version can see structure.

select `t1`.`date` **`*when*`**, ( select avg(t1.column)    t1    `t1.`date` = `***when***` ) `short column name`, t1 group `date` order `date` 

in view version, aliases replaced, subquery becomes;

(  select avg(t1.column)   t1   ***`t1.`date` = `t1.`date`*** ) `short column name`, 

the effect of average calculated across dates rather date specified when in outer query.

another script built same way translates view without problem. alias kept.

there difference between clauses used in bad , views not obvious me should cause problem.

the bad view ends with;

group `date` order `date` 

while 1 ends group by clause.

another difference column being aliased in bad view of field type datetime, while 1 in view ia 1 of int types. (it's week(t1.date).

using:

• mysql 5.5
• mysql workbench 6.0.8
• ubuntu 14.04

the aliases in select refer output of query block, not processing of query block.

the correct way want use correlated subquery table aliases:

select touter.`date` **`*when*`**,         (select avg(tinner.column)           t1 tinner          `tinner.`date` = touter.date         ) `short column name`, t1 touter group `date` order `date`; 

i have no idea why average calculated dates. expect return error, or perhaps null value. perhaps real where clause t1.date = date , expect mysql magically know second date refers to. don't depend on magic. use table aliases , explicit.