I was really suprised by something today. I have a SELECT query which
works properly, returning a set of values. When I used the same query
to create a view, the result is smaller. I believe the difference has
to do with the treatment of the AS keyword.

This is MySQL 5.0.22. The table, simplified, looks like this:
create table people (
userid varchar(20) primary key,
category varchar(20),
supervisor varchar(20),
active int unsigned,
foreign key (supervisor) references people (userid)
) engine = "innodb";

Here's my query:

mysql> select userid,supervisor as super,category from people
where active = true
and (category = "affiliated" or (select category from people
where userid = super) = "affiliated");

This finds every entry in the "people" table where the category is "affiliated"
or the person's supervisor has category "affiliated". When I make a view
like this:

mysql> create view affil_people as select userid,supervisor as super,category
from people where active = true and (category = "affiliated" or
(select category from people where userid = super) = "affiliated");

it doesn't get the ones with "affiliated" supervisors. When I look at "show
create table affil_people" the meat of it looks like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`myself`@`localhost` SQL SECURITY DEFINER
VIEW `affil_people` AS select `people`.`userid` AS `userid`,
`people`.`supervisor` AS `super`,`people`.`category` AS `category`
from `people` where ((`people`.`active` = 1)
and ((`people`.`category` = _latin1'affiliated') or
((select `people`.`category` AS `category` from `people`
where (`people`.`userid` = `people`.`supervisor`)) = _latin1'affiliated')))

The interesting thing is that my "super" has been changed to
"`people`.`supervisor`" in the last line. Thus the last WHERE clause finds
rows where people are their own supervisors, rather than finding each person's
supervisor.

I wonder what to do about this. Is it a bug or a feature? Is there an obvious,
simple way around it?