Re: Inner Joins, Compostie Keys and Partition issues
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]