Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-19687

searches do not delimit custom field id properly - leading to incorrect results

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Important
    • Resolution: Cannot Reproduce
    • Affects Version/s: 4.6
    • Fix Version/s: None
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Core Team Contract
    • Verified?:
      No

      Description

      reproduced on Demo by:

      • create custom group (custom_group)
      • add field (custom_field) to custom_group (text: multiselect – 11 options will be enough (I numbered mine 1-11)
      • create user with custom_group -> custom_field set to 1
      • create user with custom_group -> custom_field set to 11
      • cunduct search for custom_field = 1
        Issue is that the search picks up ANY contacts with a '1' in the id, so includes both 1 & 11.

      The search query used in the search does not seem to delimit the id of the option properly. The query I picked up using debugging log was:

      ELECT 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`, civicrm_value_yp_programme_preference_13.id as civicrm_value_yp_programme_preference_13_id, civicrm_value_yp_programme_preference_13.ambitions_drop_down_215 as custom_215  FROM civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON contact_a.id = pnc.entity_id1 AND pnc.cacheKey = 'civicrm search a20200580d861f11dd91f607ce0eb1e0_2851'  
      	LEFT JOIN civicrm_value_yp_programme_preference_13 ON civicrm_value_yp_programme_preference_13.entity_id = `contact_a`.id  WHERE  (  ( civicrm_value_yp_programme_preference_13.ambitions_drop_down_215 RLIKE '[[:cntrl:]]*8[[:cntrl:]]*' )  )  AND (contact_a.is_deleted = 0)  GROUP BY contact_a.id  ORDER BY pnc.id
      

      This seems to be using:
      [code:mysql]
      WHERE ( ( civicrm_value_yp_programme_preference_13.ambitions_drop_down_215 RLIKE '[[:cntrl:]]8[[:cntrl:]]' )
      [code]
      whereas I need to CONCAT a CHAR(0) (or paste one) on each end of the id to delimit it and not get results from all id's containing a 1.

        Attachments

          Activity

            People

            • Assignee:
              monish.deb Monish Deb
              Reporter:
              marshCircle Martin Castle
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: