Details
-
Type: Bug
-
Status: Reopened
-
Priority: Trivial
-
Resolution: Unresolved
-
Affects Version/s: 4.4.5
-
Fix Version/s: 4.7
-
Component/s: None
-
Labels:
-
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