Bug when filtering users by more than one role

I'm facing the same problem when I use 2 filters by role.

The bug is caused by a SQL architecture. When someone wants to filter by two roles (find users that belongs to both roles) Drupal generates this SQL query:

SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 AND ur.rid = 4 AND ur.rid = 5

while DISTINCT is used this query will NEVER return any result... We replace the role filters so the above query will look like this:

SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 AND u.uid IN (SELECT uid FROM users_roles WHERE rid = 4) AND u.uid IN (SELECT uid FROM users_roles WHERE rid = 5)

This fixes the issue.

Attached you can find a patch for this.

The UI says the roles will be ANDed, this would need to be fixed there too. We generally don't use subselects, queries with them can usually be rewritten to be faster and more clear.

@drumm

The UI says the roles will be ANDed, this would need to be fixed there too [...]

The new SQL statement is ANDed the roles... Check it!

We generally don't use subselects, queries with them can usually be rewritten to be faster and more clear

In the actual format of the user.module Drupal 5 the records must be obtained in a single SQL query. This is by design... This is the reason why I used sub-queries... There are no options here...

Saw that you switched to 7.x-dev? Why? I created the patch against Drupal 5.18.

Attached, the patch for D7...

This has been fixed in 7.x-dev in other place. Now, in D67, you can filter by more than one role.

The issue is still present in 6.x

Too late for this patch. It's fixed in the current version. 8.x will use Views.