Can't search for activity subjects starting with 1 - and many more caching issues
Overview
Due to how the query that fills the SQL cache is built, it fails under some very common circumstances. Some of those are hidden by the fact that Civi will fall back to an uncached search; some simply show no results.
Reproduction steps
- Create a new activity with a subject of
12345
. - On Advanced Search, search Activity Text for
12345
. - Observe no results found.
- Search Activity Text for
2345
. - Observe the the activity is found.
Why this happens
- The SQL created is something like
SELECT contact_a.id FROM civicrm_activity WHERE activity_subject like '%12345%'
. -
CRM_Contact_Selector
has this code snippet which replacesSELECT contact_a.id
with"SELECT DISTINCT %1, contact_a.id, contact_a.sort_name"
, which yieldsSELECT DISTINCT %1, contact_a.id, contact_a.sort_name FROM civicrm_activity WHERE activity_subject like '%12345%'
. - It then passes this SQL to a function that does variable substitution - but the
%1
in%12345%
gets substituted with the cache key!
This problem manifests with any Advanced Search field that prepends a wildcard (%
) to the form value. However, if you search for a contact named 12345
the search will still complete, because there's a fallback query in case of exceptions.
Additional Note(s)
I'm going to break my PR into two parts to make it easier to review. The first part will deal with the bug itself; I'll follow up with a cleanup PR to simplily the fillWithSQL
signature.
The fillWithSQL
method is only called in one other place in the code, in CiviCampaign. However, this query ALWAYS fails, because it puts FROM
twice in a row. So this will always fail to populate the cache.