I've done an awful lot of searching and troubleshooting to try and solve this myself.
My situation is that I've updated a host to Debian Squeeze (MySQL 5.1.49) from Debian Lenny (5.0-ish) and a previously working query that returned the expected result now returns an empty set.
So here goes on the background. In order to see if the data was problematic, I did a mysqldump of the database. I then copied that to my development machine (Mac OS X 10.6.8 with MySQL installed via Homebrew) and set up the database there. The query returned the results expected!
The code is based on an open source application, so I went back and did a clean install and database bootstrap on a Lenny host. I confirmed that the query returns what is expected.
I also did a fresh install of the application on a clean installed (not upgraded host) on Squeeze. The fresh install on Squeeze of the open source application works as expected.
I did a mysqldump from the fresh install of the application on Lenny. I then, on the clean Squeeze host, created a new database and loaded the Lenny dump into it. The query no longer returned the expected result. The same situation as the database upgraded through to Squeeze.
Here's a description of how things stand with the data.
First the tables:
mysql> describe roles;
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | NULL | |
| authorizable_type | varchar(30) | YES | | NULL | |
| authorizable_id | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
mysql> describe roles_users;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| user_id | int(11) | YES | MUL | NULL | |
| role_id | int(11) | YES | MUL | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
You guessed it, these are from an authorization plugin circa Rails 2.1ish. Now in use by an application that has been upgraded to Rails 2.3.5 at this point.
I've verified that the descriptions for the tables are the same on both hosts.
Next the data:
mysql> select * from roles where id = 1;
+----+------------+-------------------+-----------------+---------------------+---------------------+
| id | name | authorizable_type | authorizable_id | created_at | updated_at |
+----+------------+-------------------+-----------------+---------------------+---------------------+
| 1 | site_admin | Basket | 1 | 2009-05-27 00:14:22 | 2009-05-27 00:14:22 |
+----+------------+-------------------+-----------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from roles_users where role_id = 1 and user_id = 1;
+---------+---------+---------------------+---------------------+
| user_id | role_id | created_at | updated_at |
+---------+---------+---------------------+---------------------+
| 1 | 1 | 2009-05-27 00:14:22 | 2009-05-27 00:14:22 |
+---------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
Again, both hosts return the same result.
Here comes the query as expected to return (constructed by the authorization plugin to verify a user has the correct role on correct object in the system):
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 ;
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)
And here it is again, on the Debian Squeeze host, returning the empty set:
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)
Any ideas? Anyone else experience this?
Update for results requested from comment:
Here's without the LIMIT 1:
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 ));
Empty set (0.00 sec)
Here's dropping the roles_users.user_id = 1 from the where clause:
mysql> SELECT `roles`.id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles`.`id` = 1 ;
+----+
| id |
+----+
| 1 |
...
3 rows in set (0.00 sec)
Here's the above query, but showing the roles_users.user_id in the select:
mysql> SELECT `roles`.id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles`.`id` = 1 ;
+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
...
3 rows in set (0.00 sec
and finally here's dropping the roles.id from where clause, but keeping the roles_users.user_id:
mysql> SELECT `roles`.id, `roles_users`.user_id FROM `roles` INNER JOIN `roles_users` ON `roles`.id = `roles_users`.role_id WHERE `roles_users`.user_id = 1;
+----+---------+
| id | user_id |
+----+---------+
...
| 1 | 1 |
...
9 rows...