Account name search returns no records in 5.1

[fmtworldwidecom]

After installing 5.1, we've discovered that any search for account name in the Accounts module returns zero rows, even when there are definitely records in there. This is the case for both basic and advanced search, and also when we use the search facility on the top of each page it finds contacts for the relevant account, but not the account itself.

When turning on debug/info and logged queries the following query came back:

SELECT accounts.id ,accounts_cstm.account_id_c, accounts.name , accounts.billing_address_city , accounts.phone_office , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, accounts.assigned_user_id , jt1.name team_name , jt1.created_by team_name_owner , 'Teams' team_name_mod, accounts.team_id , jt2.name partner_id_c , accounts.created_by FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON
jt0.id= accounts.assigned_user_id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN teams jt1 ON
jt1.id= accounts.team_id AND jt1.deleted=0
AND jt1.deleted=0 LEFT JOIN accounts jt2 ON accounts_cstm.account_id_c = jt2.id AND jt2.deleted=0 where ((jt2.name like 'A%' ) ) AND accounts.deleted=0 ORDER BY accounts.name ASC

If I copy the query into phpmyadmin, no records are returned. If I change "jt2.name" to "accounts.name" in the where clause, the correct records are returned. This is obviously the problem, but after wading through the code I cannot figure out where or how this can be fixed.

Any help would be appreciated!

Sue

[fmtworldwidecom]

Okay, digging around I found the problem. It's specific to our case as we have accounts that may be attached to other accounts, so this was seen as referring to a custom link table rather than the actual table.

To fix the problem, I've inserted the following line at approx line number 3015 in the custom relate loop of function create_new_list_query in /data/SugarBean.php

if ($relateJoinInfo['rel_table'] != $this->table_name) // fix for custom references to this table
$where = preg_replace('/(^|[\s(])' . $field_name . '/' , '${1}' . $relateJoinInfo['name_field'], $where);

I hope this helps anyone with a similar setup!

Sue

[pkranz]

I've filled bugs for this issue, and its been fixed in some releases, only to be re-broken in the next release.. The bugs relating to this problem are #23291 and #25401