When a logged in user has permission to access contacts via ACL role - rather than Drupal "view all contacts" permission... Find Contacts (search/basic) throws a fatal error due to a malformed query:
Database Error Code: Operand should contain 1 column(s), 1241
SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id LEFT JOIN civicrm_group_contact `civicrm_group_contact-3` ON contact_a.id = `civicrm_group_contact-3`.contact_id WHERE ( `civicrm_group_contact-2`.group_id IN (2) AND `civicrm_group_contact-2`.status IN ("Added") ) AND ( ( `civicrm_group_contact-2`.group_id = (2,2) AND `civicrm_group_contact-2`.status IN ("Added") ) OR ( `civicrm_group_contact-3`.group_id = (3,3) AND `civicrm_group_contact-3`.status IN ("Added") ) ) [nativecode=1241 ** Operand should contain 1 column(s)]
You can recreate this on sandbox as follows:
- Login as user=acltest pw=demo
- Go to Find Contacts - If you click the "in" (groups) select. you'll see that this user has been granted access to only 2 groups (via ACLs) - Newsletter Subscribers and Summer Volunteers
- Click the Search button (with or without any specific search string) - you get the fatal error.
The setup for this user is:
- They are a member of the "Volunteers ACL Group"
- That group is assigned to the "Volunteers" ACL Role
- That role is granted Edit rights on the two Mailing List groups listed above (Newsletters, Summer Volunteers)
- That user has just the basic "authenticated" Drupal role - which currently does NOT have the "view all contacts" permission. (My understanding is that this is the correct way to configure the Drupal role if you want to limit access to CiviCRM contact groups using ACLs. If you grant this user "view all contacts" via Drupal access control - then they indeed get to see everyone - which is not the case we're testing here.)