CB

In frontend users lists which have a single-select in 1.7.0 candidate we hit this double bug:
This is bug 1 of 2.

JDatabaseMySQL::query: 1054 - Unknown column 'u.id' in 'on clause' SQL=SELECT COUNT(DISTINCT u.id) FROM jos_comprofiler ue, jos_users u, jos_comprofiler t1 JOIN jos_user_usergroup_map g ON g.`user_id` = u.`id` WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND g.group_id IN ( 2, 3, 4, 5, 6, 7, 8) AND u.`id` = ue.`id` AND u.`id` = t1.`id`

SELECT COUNT(DISTINCT u.id) FROM jos_comprofiler ue, jos_users u, jos_comprofiler t1 JOIN jos_user_usergroup_map g ON g.`user_id` = u.`id` WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND g.group_id IN ( 2, 3, 4, 5, 6, 7, 8) AND u.`id` = ue.`id` AND u.`id` = t1.`id`

does not work

query is mixing implicit joins and explicit ones, and that is hitting a MySQL bug.

strangely without the single-select field listed in users-list, this query works fine:

SELECT COUNT(DISTINCT u.id) 
 FROM jos_comprofiler ue, jos_users u JOIN jos_user_usergroup_map g 
 ON g.`user_id` = u.`id` 
 WHERE u.block = 0 
 AND ue.approved = 1 
 AND ue.confirmed = 1 
 AND ue.banned = 0 
 AND g.group_id IN ( 2, 3, 4, 5, 6, 7, 8) 
 AND u.`id` = ue.`id`

but this one does not (swapping simply order of listed rows of implicit join does not take table aliases, except for last one, so that's a MySQL bug:

SELECT COUNT(DISTINCT u.id) 
 FROM jos_users u, jos_comprofiler ue JOIN jos_user_usergroup_map g 
 ON g.`user_id` = u.`id` 
 WHERE u.block = 0 
 AND ue.approved = 1 
 AND ue.confirmed = 1 
 AND ue.banned = 0 
 AND g.group_id IN ( 2, 3, 4, 5, 6, 7, 8) 
 AND u.`id` = ue.`id`

To work around, this one works:

SELECT COUNT(DISTINCT u.id) FROM jos_comprofiler AS ue
JOIN jos_users AS u ON u.`id` = ue.`id`
JOIN jos_comprofiler AS t1 ON u.`id` = t1.`id`
JOIN jos_user_usergroup_map g ON g.`user_id` = u.`id`
WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND g.group_id IN ( 2, 3, 4, 5, 6, 7, 8)