MySQL Forums
Forum List  »  Partitioning

Re: Inner Joins, Compostie Keys and Partition issues
Posted by: Roger Pack
Date: May 30, 2008 02:00PM

I think I just had a problem similar to this.

If I run

mysql> SELECT schools.name,schools.enabled FROM `programs` LEFT OUTER JOIN `schools` ON `schools`.id = `programs`.school_id WHERE programs.enabled = 1 and schools.id IN (7,84,4,9,31,18,8,30,38,40,41,45,49,54,57,60,61,77,78,79) AND schools.is_online = 1;
...
279 rows in set (0.00 sec)


this is good. Now if I add another if clause [note that all 279 had schools.enabled = 1]


mysql> SELECT schools.name,schools.enabled FROM `programs` LEFT OUTER JOIN `schools` ON `schools`.id = `programs`.school_id WHERE programs.enabled = 1 and schools.id IN (7,84,4,9,31,18,8,30,38,40,41,45,49,54,57,60,61,77,78,79) AND schools.is_online = 1 AND schools.enabled = 1;
...
127 rows in set (0.00 sec)

It should have been 279 but it was 127.
Not expected.

It also returns a 'correct' query if I take out some of the id's listed in the where clause. Weird.

I think it might be broken in
Server version: 5.0.51b MySQL Community Server (GPL)

and working in 5.0.45. At least that's my theory.
[the broken one I'm looking at is mac os x 5.0.51b]

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Inner Joins, Compostie Keys and Partition issues
2485
May 30, 2008 02:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.