Patrick,

> 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");

Have you tried properly aliasses your tables in order to avoid
MySQL trying to replace table names?

eg:

select p.userid, p.supervisor, p.category
from people p
where p.active = true
and (p.category = 'affiliated' or (select p2.category
from people p2 where p2.userid = p.supervisor) = 'affiliated')

?


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> 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?