advanced search using relationship and smart group produces backtrace

If you search for a contacts related to contacts in a smart group, you get a back trace (see below).

I traced the problem to the fix in https://issues.civicrm.org/jira/browse/CRM-15662. If I revert that fix, the problem goes away.

You can re-create the problem by:

• Create a smart group (in the demo site I made a smart group of everyone)
• Do an advanced search, expand relationships, choose employee of and then enter your newly created smart group in the Target Contact(s) in Group field.

Dec 22 12:06:06 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache `civicrm_relationship_group_contact_
cache` ON contact_b.id = `civicrm_relationship_group_contact_cache`.contact_id LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact
contact_b ON (civicrm_relationship.contact_id_b = contact_b.id ) LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact
_b.id AND civicrm_relationship_group_contact.status = 'Added' WHERE ( ( ( civicrm_relationship_group_contact.group_id IN (583) ) OR ( `civicrm_relationship_group_contact_cache`.group_id IN
(583) ) ) AND (
civicrm_relationship.is_active = 1 AND
( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20141222 ) AND
( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20141222 )
) AND civicrm_relationship.relationship_type_id = 4 ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'contact_b.id' in
'on clause']
[type] => DB_Error
[user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache `civicrm_relationship_group_contact_c
ache` ON contact_b.id = `civicrm_relationship_group_contact_cache`.contact_id LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact
contact_b ON (civicrm_relationship.contact_id_b = contact_b.id ) LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_
b.id AND civicrm_relationship_group_contact.status = 'Added' WHERE ( ( ( civicrm_relationship_group_contact.group_id IN (583) ) OR ( `civicrm_relationship_group_contact_cache`.group_id IN
(583) ) ) AND (
civicrm_relationship.is_active = 1 AND
( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20141222 ) AND
( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20141222 )
) AND civicrm_relationship.relationship_type_id = 4 ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'contact_b.id' in
'on clause']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as so
rt_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache `civicrm_relationship_group_contact_cache` ON contact_b.id = `civicrm_relationship_group_contact_cache`.contact_i
d LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id ) LEFT JOIN
civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id AND civicrm_relationship_group_contact.status = 'Added' WHERE ( ( ( c
ivicrm_relationship_group_contact.group_id IN (583) ) OR ( `civicrm_relationship_group_contact_cache`.group_id IN (583) ) ) AND (
civicrm_relationship.is_active = 1 AND
( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20141222 ) AND
( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20141222 )
) AND civicrm_relationship.relationship_type_id = 4 ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown column 'contact_b.id' in
'on clause']"]
)

Dec 22 12:06:06 [info] $backTrace = #0 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Error.php(239): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB.php(975): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#4 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#5 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...", "DB_Error", TRUE)
#6 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/mysql.php(899): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'contact_b.id' in 'on clause'")
#7 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#9 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/DataObject.php(2442): DB_common->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#10 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#11 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/DAO.php(325): DB_DataObject->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#12 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/DAO.php(1116): CRM_Core_DAO->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...", TRUE)
#13 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(4637): CRM_Core_DAO::executeQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicr...")
#14 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Contact/Selector.php(1184): CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE)
#15 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(86): CRM_Contact_Selector->alphabetQuery()
#16 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(113): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(55): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Contact/Form/Search.php(845): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Contact/Form/Search/Advanced.php(328): CRM_Contact_Form_Search->postProcess()
#20 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Form.php(328): CRM_Contact_Form_Search_Advanced->postProcess()
#21 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#23 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#24 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Controller.php(356): HTML_QuickForm_Page->handle("refresh")
#25 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Invoke.php(331): CRM_Core_Controller->run((Array:4), (Array:0))
#26 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Invoke.php(75): CRM_Core_Invoke::runItem((Array:13))
#27 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#28 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/sites/all/modules/civicrm/drupal/civicrm.module(457): CRM_Core_Invoke::invoke((Array:4))
#29 [internal function](): civicrm_invoke("contact", "search", "advanced")
#30 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:3))
#31 /var/aegir/platforms/master-ourpowerbase-d7-c4.5/index.php(21): menu_execute_active_handler()
#32