Bad query and broken paging on admin/people

Our default user listing query at admin/people returns duplicate users. Those users get filtered upon display but we end up with crazy paging. Here is the current query:

SELECT u.uid AS uid, u.name AS name, u.status AS status, u.created AS created, u.access AS access FROM users u LEFT OUTER JOIN users_roles ur ON u.uid = ur.uid WHERE (u.uid <> 1) ORDER BY u.created DESC LIMIT 0, 50

To see the problem, use devel_generate to create 100 users with multiple roles (requires latest devel_generate). Then view admin/people. I recommend changing the limit in user_admin_account() from 50 to 5 so you can quickly see that at times, only 3 or 4 rows are on each page.

We have to be careful how we fix this. In D6 we always add a DISTINCT which murders performance for a site with many users (economist.com has 3M users). We should not join to roles table IMO when no role condition is present. We can user_load_multiple() on each row, IMO. When a role condition is present, we should join on role but still not DISTINCT since only one role can be selected at a time. See #604290: user admin page slow with lots of users for a D6 patch.