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?

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?

On 2007-09-26, Martijn Tonies <m.tonies@upscene.removethis.com> wrote:
> 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')
>

Thanks. That fixes the problem. I should have known that it was
something simple. I recently upgraded from version 3.x to 5.0,
and now I am learning about all the new features. The lesson
for today is subqueries.

On 26 Sep, 23:47, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
> On 2007-09-26, Martijn Tonies <m.ton...@upscene.removethis.com> wrote:
>
>
>
>
>
> > 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')
>
> Thanks. That fixes the problem. I should have known that it was
> something simple. I recently upgraded from version 3.x to 5.0,
> and now I am learning about all the new features. The lesson
> for today is subqueries.- Hide quoted text -
>
> - Show quoted text -

I would also replace your subselect with a LEFT JOIN

On 2007-09-27, Captain Paralytic <paul_lautman@yahoo.com> wrote:
>
> I would also replace your subselect with a LEFT JOIN
>
Thanks. I have always been a bit frightened of LEFT JOINs, but I
decided to give it a try. I was able to come up with a query that
works and looks simpler:

mysql> select p.* from people p left join people s
on s.userid = p.supervisor where (p.category = "affiliated" or
s.category = "affiliated") and p.active = true;

Meanwhile, I also decided to learn about embedded functions.
I produced one which also simplifies the query:

CREATE FUNCTION super_cat (id varchar(20)) RETURNS varchar(20)
begin
declare x,y varchar(20);
select supervisor into x from people where userid = id;
if x is null then
return null;
else
select category into y from people where userid = x;
return y;
end if;
end

The query looks like
select * from people where active = true and (category = "affiliated"
or super_cat(userid) = "affiliated");

The query is quite simple, at the expense of making a specialized
function. Is either approach to be preferred for efficiency?

Patrick,

> CREATE FUNCTION super_cat (id varchar(20)) RETURNS varchar(20)
> begin
> declare x,y varchar(20);
> select supervisor into x from people where userid = id;
> if x is null then
> return null;
> else
> select category into y from people where userid = x;
> return y;
> end if;
> end

You might want to take a loko at that first part there --

if there's no resultset, what does X become? If you initialize X with
something, eg:

declare x, y varchar(20);

x = 'test';
select supervisor into x from people where userid = id;

is X NULL or 'test'?

I'm wondering, cause I don't think the SELECT will put anything
into X if there's no resultset.


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

On 2007-09-28, Martijn Tonies <m.tonies@upscene.removethis.com> wrote:
> Patrick,
>
>> CREATE FUNCTION super_cat (id varchar(20)) RETURNS varchar(20)
>> begin
>> declare x,y varchar(20);
>> select supervisor into x from people where userid = id;
>> if x is null then
>> return null;
>> else
>> select category into y from people where userid = x;
>> return y;
>> end if;
>> end
>
> You might want to take a loko at that first part there --
>
> if there's no resultset, what does X become? If you initialize X with
> something, eg:
>
> declare x, y varchar(20);
>
> x = 'test';
> select supervisor into x from people where userid = id;
>
> is X NULL or 'test'?
>
> I'm wondering, cause I don't think the SELECT will put anything
> into X if there's no resultset.
>
According to the online manual, the default value is NULL if there
is no DEFAULT clause. I assumed that if there is no result set
it would continue to be NULL. I also assumed that y will continue
to be NULL if category is NULL. If this behavior is not guaranteed,
then a few more tests would be justified.

My first version didn't have the "if x is null" test. It returned
the correct results, but it generated warnings in the "select
category" line when x was NULL.