Missing search results when searching by group due to incorrect SQL query

[davej]

Hi,

A client reported strange results on a 3.1.3 site when searching for members of a particular group. Basic search on members of group X -> "29 Results" but results only show 6 contacts (first 6 alphabetically by surname). A-Z links allow other contacts to show, but even clicking 'All' just shows 6. Even as uid 1, so not ACL issue. This is not a multi-site. Have truncated cache tables, no change. Upgraded to 3.1.5, no change.

Set up query logging & found the offending query:

Code: [Select]

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.home_URL  as `home_URL`, contact_a.image_URL  as `image_URL`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) AS organization_name, contact_a.sic_code  as `sic_code`, gender.value as gender_id, gender.label as gender, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) AS current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, `civicrm_group_contact-165`.id as group_contact_id, `civicrm_group_contact-165`.status as status
FROM civicrm_contact contact_a
LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id 
LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)
LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)
LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
LEFT JOIN civicrm_group_contact `civicrm_group_contact-165` ON contact_a.id = `civicrm_group_contact-165`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_165` ON contact_a.id = `civicrm_group_contact_cache_165`.contact_id
LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')
LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)
WHERE  ( ( ( `civicrm_group_contact-165`.group_id IN ( 165 ) AND `civicrm_group_contact-165`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_165`.group_id = 165 ) ) )  AND  ( 1 )
ORDER BY contact_a.sort_name asc  LIMIT 0, 50 ;

Running this manually, I found it returned one row per contact per entry for that contact in civicrm_group_contact. E.g. one contact had 14 entries in group_contact & got 14 rows from the above query. So the LIMIT 50 cut in when only 6 contacts had been returned.

I think the problem is this bit of the WHERE clause:

Code: [Select]

( ( ( `civicrm_group_contact-165`.group_id IN ( 165 ) AND `civicrm_group_contact-165`.status IN ("Added") )
 OR ( `civicrm_group_contact_cache_165`.group_id = 165 ) ) )

- The 'OR' subclause permits rows where the conditions on civicrm_group_contact-165 do not apply.

Have to rush off now...

Dave J

[davej]

Hi,

Further developments...

Replicated on demo
 - try a basic search on group "Test Group Search". Search results page says "117 Results" / "Contact 1-50 of 117" but only actually lists 9. How I set this up:

(1) Created group "Test Group Search" & added all contacts as members.
(2) Created group "Test Group Search child group" as child of "Test Group Search" (didn't add any members). *
(3) Added & removed some of the members to/from other groups: removed all the households from "Test Group Search", added all of the "Test Group Search" members beginning with "A" to several other groups.
(4) Basic search on group "Test Group Search".

* The group where we saw problems is the only one on the site that has a child group and I believe this is relevant as it causes where_clause/where_tables to include civicrm_group_contact_cache.

Fixing the query
I've found a couple of ways of modifying the original query so that it returns the desired one row per contact:

(a) change the WHERE clause to:

Code: [Select]

WHERE  ( ( ( `civicrm_group_contact-165`.group_id IN ( 165 ) AND `civicrm_group_contact-165`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_165`.group_id = 165 AND `civicrm_group_contact-165`.id IS NULL ) ) )  AND  ( 1 )
- i.e. added "AND `civicrm_group_contact-165`.id IS NULL" to the OR subclause for civicrm_group_contact_cache.
My rationale for this was that it stops the where clause matching on the problematic rows where:
`civicrm_group_contact_cache_165`.group_id = 165 and `civicrm_group_contact-165`.group_id not in ( 165 )
- the thought was: we want contact ids where either group_contact.group_id is 165 or, if there's no group_contact entry for this group_id, where group_contact_cache.group_id is 165. Does that seem right?

(b) alternative approach, shifting the conditions into the JOIN clauses - change the FROM/WHERE etc to:

Code: [Select]

FROM civicrm_contact contact_a
LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id 
LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)
LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)
LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
LEFT JOIN civicrm_group_contact `civicrm_group_contact-165` ON (contact_a.id = `civicrm_group_contact-165`.contact_id AND `civicrm_group_contact-165`.group_id IN (165) AND `civicrm_group_contact-165`.status IN ("Added"))
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_165` ON (contact_a.id = `civicrm_group_contact_cache_165`.contact_id AND `civicrm_group_contact_cache_165`.group_id = 165)
LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')
LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)
WHERE ( `civicrm_group_contact_cache_165`.id IS NOT NULL OR `civicrm_group_contact-165`.id IS NOT NULL ) AND ( 1 )
ORDER BY contact_a.sort_name asc  LIMIT 0, 50
Rationale: we only join rows from group_contact and group_contact_cache if they relate to the desired group. In the WHERE clause, we then just need to specify that we have a row from either group_contact or group_contact_cache.

I think (a), if the team think it's semantically correct, is the easier approach to take given what I know of how Civi generates the queries. I tried changing the where_clause for our group 165 from:

Code: [Select]

( ( ( `civicrm_group_contact-165`.group_id IN ( 165 ) AND `civicrm_group_contact-165`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_165`.group_id = 165 ) ) )to:

Code: [Select]

( ( ( `civicrm_group_contact-165`.group_id IN ( 165 ) AND `civicrm_group_contact-165`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_165`.group_id = 165 AND `civicrm_group_contact-165`.id IS NULL ) ) )
With this change, so far I have been getting correct results from the search UI. (Edit 16th June 2010:) Sadly just changing where_clause in civicrm_group doesn't appear sufficient to change the query that the search UI generates. I may have been fooled into thinking this had fixed the problem because in the meantime civicrm_group_contact_cache had been cleared. But the problem subsequently recurred; I enabled query logging & found that the query still had the old WHERE clause.

Created issue: http://issues.civicrm.org/jira/browse/CRM-6355

Dave J