Problem with MSSQL and soap get entry list

[dhavaldarji]

Hi,
Using sugarcrm 6.1 with mssql server.

During soap operation (actually riva integration) when it tries to fetch records it get error like "Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY"

Here is some portion of sugar log.

Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (calls).
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship name: calls_users
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Table name: 
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Key name: 
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_table_name: users
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_key_name: id
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship record found.
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (meetings).
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship name: meetings_users
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Table name: 
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, Key name: 
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_table_name: users
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, _bean_key_name: id
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Link Constructor, relationship record found.
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Loading relationship (modified_user_link).
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] SugarBean.load_relationships, Error Loading relationship (modified_user_link).
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] process_list_query:  SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
 LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
 LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
 AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Array
(
    [0] =>  SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
 LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
 LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
 AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
    [1] => 0
    [2] => 21
    [3] => 1
    [4] => Error retrieving User list: 
)

Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Limit Query:  SELECT  TOP 21  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
 LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
 LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
 AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][INFO] Query: SELECT  TOP 21  users.* , ISNULL(users.first_name,'') + N' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + N' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
 LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
 LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
 AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE N'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE N'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][FATAL] SQL Error : Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Including generic hook file
Mon Feb 11 03:57:06 2013 [31100][50ee1355-2e15-1474-366b-50db333bf953][DEBUG] Calling DBManager::disconnect()

Any help on this?
Thanks.

[dhavaldarji]

Some one using SugarCRM with mssql and found this type of failed query?

SELECT  users.* , ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as full_name, ISNULL(users.first_name,'') + ' ' + ISNULL(users.last_name,'') as name, jtl0.call_id call_id, jtl0.id accept_status_id, jtl0.accept_status accept_status_name, jtl1.meeting_id meeting_id FROM users   LEFT JOIN  calls_users jtl0 ON users.id=jtl0.user_id  AND jtl0.deleted=0
 LEFT JOIN  calls jt0 ON jt0.id=jtl0.call_id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  meetings_users jtl1 ON users.id=jtl1.user_id  AND jtl1.deleted=0
 LEFT JOIN  meetings jt1 ON jt1.id=jtl1.meeting_id AND jt1.deleted=0
 AND jt1.deleted=0 where (( users.id IN (SELECT rel.bean_id FROM email_addr_bean_rel rel INNER JOIN email_addresses ea ON rel.email_address_id = ea.id AND rel.bean_module LIKE 'Users' AND rel.deleted = 0 AND ea.deleted = 0 WHERE (LTRIM(RTRIM(ea.email_address)) LIKE 'abc@test.com')))) AND users.deleted=0 Group By users.id  ORDER BY users.date_modified

Error :

SQL Error : Column 'users.user_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is working fine on mysql (except i remove the isnull - as it is not supported!)

How do i now change this core code to create correct query?

[jmertic]

Can you add a bug for this at http://bugs.sugarcrm.com?

[dhavaldarji]

Hi John,

Actually upgrading to SugarCE - 6.5.9 has resolved the issue. but now i am getting another error

Tue Feb 19 02:30:58 2013 [25795][50ee1355-2e15-1474-366b-50db333bf953][FATAL] The column 'account_id' was specified multiple times for 'a'.: SELECT TOP 21 * FROM
                                (
                                     SELECT  project.*  ,project_cstm.end_date_c,project_cstm.project_code_c,project_cstm.billable_after_hours_c,project_cstm.esp_incident_c,project_cstm.value_c,project_cstm.actual_after_hours_c,project_cstm.sales_order_billable_hours_c,project_cstm.estimated_hours2_c,project_cstm.user_id_c as usrid,project_cstm.billable_hours_c,project_cstm.total_hours_c,project_cstm.date_worked_c,project_cstm.fixed_billable_amount_c,project_cstm.currency_id , LTRIM(RTRIM(ISNULL(jt0.first_name,'')+N' '+ISNULL(jt0.last_name,''))) modified_by_name , jt0.created_by modified_by_name_owner  , N'Users' modified_by_name_mod , LTRIM(RTRIM(ISNULL(jt1.first_name,'')+N' '+ISNULL(jt1.last_name,''))) created_by_name , jt1.created_by created_by_name_owner  , N'Users' created_by_name_mod , LTRIM(RTRIM(ISNULL(jt2.first_name,'')+N' '+ISNULL(jt2.last_name,''))) assigned_user_name , jt2.created_by assigned_user_name_owner  , N'Users' assigned_user_name_mod , accounts.name account_name, jtl3.acc_id account_id, project_cstm.account_id_c as pc_acc_id, jt4.name account_id_c , project_cstm.user_id_c, LTRIM(RTRIM(ISNULL(jt5.first_name,'')+N' '+ISNULL(jt5.last_name,''))) last_worked_by_c  , LTRIM(RTRIM(ISNULL(jt6.first_name,'')+N' '+ISNULL(jt6.last_name,''))) modified_user_name , jt6.created_by modified_user_name_owner  , N'Users' modified_user_name_mod , ROW_NUMBER()
                                    OVER (ORDER BY project.date_modified) AS row_number
                                    FROM project   LEFT JOIN project_cstm ON project.id = project_cstm.id_c   LEFT JOIN  users jt0 ON project.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON project.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON project.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0  LEFT JOIN  projects_accounts jtl3 ON project.id=jtl3.project_id AND jtl3.deleted=0

 LEFT JOIN  accounts accounts ON accounts.id=jtl3.account_id AND accounts.deleted=0
 AND accounts.deleted=0 LEFT JOIN accounts jt4 ON project_cstm.account_id_c = jt4.id AND jt4.deleted=0  LEFT JOIN users jt5 ON project_cstm.user_id_c = jt5.id AND jt5.deleted=0   LEFT JOIN  users jt6 ON project.modified_user_id=jt6.id AND jt6.deleted=0

 AND jt6.deleted=0 where ((project.date_modified >= N'2013-02-07 12:20:37' AND project.date_modified <= N'2013-02-20 10:30:56') AND (project.assigned_user_id = N'50ee1355-2e15-1474-366b-50db333bf953')) AND project.deleted=0 
                                ) AS a
                                WHERE row_number > 0

when i rename and put alias for account_id it gives another error for user_id_c, you note here project_cstm.user_id_c is also being put multiple times in select list.

Any Idea or i should add a new bug?

[jmertic]

Sounds like this is from a customization; could you back it out and see if that fixes the problem?