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

smart groups based on postal code numeric ranges cause fatal

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: 4.4.5
    • Fix Version/s: 4.7
    • Component/s: None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code

      Description

      This error only occurs if there is text in the postal codes & I think it might depend on mysql tolerance to 'warnings'. Basically calling ROUND on a field with letters in it throws a warning - I'm seeing this render as a fatal - however, mysql considers it a warning & does return results

      query is

      CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1267 (SELECT 74 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE ( ( ROUND(civicrm_address.postal_code) >= '97501' ) AND ( ROUND(civicrm_address.postal_code) <= '97540' ) AND civicrm_address.country_id = 1228 ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
      SELECT contact_id FROM civicrm_group_contact
      WHERE civicrm_group_contact.status = 'Removed'
      AND civicrm_group_contact.group_id = 74 ) );

      Changing the query such that it renders more like

      CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1267008
      (
      SELECT 74 AS group_id, contact_a.id AS id
      FROM civicrm_contact contact_a
      LEFT JOIN civicrm_address ON (contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1)
      WHERE IF(civicrm_address.postal_code REGEXP '^[0-9]+$', CAST(civicrm_address.postal_code AS UNSIGNED), 0) >= 97501
      )

      ie. this appears to be working fine

      https://github.com/fuzionnz/civicrm-core/commit/6f4db1b71bcb96d55a5a8b50487fac44d3cfbcee

        Attachments

          Activity

            People

            • Assignee:
              eileen Eileen McNaughton
              Reporter:
              eileen Eileen McNaughton
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: