Same query/data returns empty set on MySQL 5.1.49-3 (Debian Squeeze) when it returns expected row on MySQL 5.0.51a-24+lenny4
Here's my problem query:
SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
I've outlined the issue very thoroughly here (table structure, data, queries, etc.):
http://stackoverflow.com/questions/7102799/
I'll cut to the chase from the answer I posted there:
...the query will return an empty set when the roles_users table has more than one distinct user_id for a role_id.
I.e. if I have this:
mysql> SELECT `roles_users`.role_id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id order by role_id;
+---------+---------+
| role_id | user_id |
+---------+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
+---------+---------+
6 rows in set (0.01 sec)
I get what I expect for this:
mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1) AND ((`roles_users`.user_id = 1)) LIMIT 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
but when I have this (an addition user_id that has role_id 1):
mysql> SELECT `roles_users`.role_id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id order by role_id;
+---------+---------+
| role_id | user_id |
+---------+---------+
| 1 | 1 |
| 1 | 5 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 4 | 5 |
| 5 | 1 |
| 6 | 2 |
+---------+---------+
8 rows in set (0.00 sec)
I get this:
mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE (`roles`.`id` = 1 ) AND ((`roles_users`.user_id = 1 )) LIMIT 1 ;
Empty set (0.00 sec)
----
So, I'm wondering if this is a bug in MySQL 5.1.49-3 (Debian Squeeze) and whether it has already been fixed in a later version or if it persists.
Here's the a dump snippet to make it easier to recreate:
https://gist.github.com/1164428 # note that it contains a WORKING and NOT WORKING version of inserts for the roles_users table.