MySQL Forums
Forum List  »  Quality Assurance

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
Posted by: Walter McGinnis
Date: August 22, 2011 11:42PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
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
3752
August 22, 2011 11:42PM


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.