Project

General

Profile

Bug #2951

CB Userlists : when additional joins are needed, we are hitting a MySQL bug Unknown column 'u.id' in 'on clause'

Added by beat almost 9 years ago. Updated almost 9 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Target version:
Start date:
02 November 2011
Due date:
% Done:

100%

Estimated time:
1:00 h

Description

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)

History

#1 Updated by beat almost 9 years ago

  • Status changed from New to Closed
  • % Done changed from 0 to 100

r1628 fixes this first part of the double bug

#2 Updated by beat almost 9 years ago

Second part was only affecting me, and it's fixed too.

Also available in: Atom PDF