| Project: | Drupal core |
| Version: | 6.x-dev |
| Component: | user.module |
| Category: | bug report |
| Priority: | normal |
| Assigned: | claudiu.cristea |
| Status: | needs work |
| Issue tags: | admin user |
Issue Summary
Hi there,
I have a Drupal 5 website with +/- 300 user accounts. I noticed recently that the last 2 pages of the "Users List" (Administer > User management > Users) are empty and display a "No users available" message. All the users are being displayed in the first 5 pages, so it looks like the pager is adding 2 extra empty pages (page 6 and 7). When a filter is applied to the list, the pager is listing the page correctly and no additional empty pages are added at the end of the list. One of the filters used for the test is outputting close to 300 users (5 pages).
Just wondering if there is a way to fix this issue.
Any help would be really appreciated. Thanks.
--------------------------------------------------
Drupal 5.3
Core Modules Enabled:
Color, Comment, Help, Menu, Path, Profile, Search, Statistics, Taxonomy, Tracker, Upload
Contrib Modules Installed:
CCK 5.x-1.6-1, Views 5.x-1.6, Content Templates 5.x-1.8, Date API 5.x-1.8, Backup and Migrate 5.x-1.1, Diff 5.x-1.1, Google Analytics 5.x-1.3, Login Destination 5.x-1.0, Pathauto 5.x-2.0, Revision Moderation 5.x-1.0, Token 5.x-1.9, Update Status 5.x-2.0
Contrib Modules Installed (User-related):
Account Types 5.x-1.4, Organic Groups 5.x-4.1, OG Subgroups 5.x-3.0-dev, Account Expiry 5.x-1.x-dev, Site User List 5.x-1.x-dev
Server Specs:
Apache 2.0.54, PHP 5.2.6, MySQL 4.1.25
--------------------------------------------------
Comments
#1
I'm facing the same problem when I use 2 filters by role.
#2
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 = 5while
DISTINCTis 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.
#3
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.
#4
@drumm
The new SQL statement is ANDed the roles... Check it!
In the actual format of the
user.moduleDrupal 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.
#5
Attached, the patch for D7...
#6
The last submitted patch failed testing.
#7
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