Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Important
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.7.21, 4.7.22, 4.7.23, 4.7.27
-
Fix Version/s: 4.7.31
-
Component/s: CiviCRM Search
-
Labels:
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Contributed Code
-
Verified?:No
Description
"Unknown column" errors occur when the Search Builder is used to create a search that includes an address field (City, State and Country tested so far) from a location type other than Primary, then attempts to add all search results to a group.
Steps to reproduce:
- Create a search using the Search Builder on a CiviCRM 4.7.x site.
- Include a State,
City,or Country field in the search. - Set the Location Type for that field to anything other than Primary (Home, Billing, Main, Other or Work)
- Select all records returned by the search.
- Choose "Group - add contacts" from the Actions menu.
The debug_info from an example of such an error:
SELECT contact_a.id as contact_id FROM civicrm_contact contact_a WHERE ( ( `Home-address`.state_province_id = '1031' ) ) AND (contact_a.is_deleted = 0) ORDER BY `contact_a`.`sort_name` asc, `contact_a`.`id` [nativecode=1054 ** Unknown column 'Home-address.state_province_id' in 'where clause']
The error doesn't occur if Primary is used as the Location Type, or if the user selects all records displayed on one page of results (no matter how many rows are included) rather than selecting all records.
Attachments
Issue Links
Activity
Thanks for the quick response, Jon! I hadn't thought to test this on a sandbox site.
I was able to replicate this error on dmaster.demo.civicrm.org. Steps I took after logging into the site:
- Used the Search Builder (http://dmaster.demo.civicrm.org/civicrm/contact/search/builder?reset=1) to create a search that includes contacts where State (Home) = California.
- Clicked the "All 16 records" radio button.
- Selected "Group - add contacts" from the Actions menu.
The "DB Error: no such field" screen appears directly after clicking or selecting "Group - add contacts."
I replicated your search (City (Work) ≠ 'Brooklyn') and the error did NOT happen when I tried to add those contacts to a group! There weren't any records that matched the search using = instead of ≠ so I couldn't test the converse.
Further testing reveals that perhaps this isn't actually a problem for the city field, only for the State and Country fields? (I haven't any other address fields yet!)
- City (Work) = 'Asheville' - no error
- City (Work) ≠ 'Asheville' - no error
- State (Work) = North Carolina - error
- State (Work) ≠ North Carolina - error
- Country (Work) = United States - error
- Country (Work) ≠ Tonga - error
Noting here that this problem still exists after updating to CiviCRM 4.7.27; the error occurs in both the Drupal and Backdrop versions.
I wasn't able to replicate this on http://dmaster.demo.civicrm.org. See screenshot. I wonder if this was fixed as a side effect of
CRM-21130? Please confirm if you're able to replicate this error on one of the sandbox sites.