Can't search for activity subjects starting with 1 - and many more caching issues
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.
- Create a new activity with a subject of
- On Advanced Search, search Activity Text for
- Observe no results found.
- Search Activity Text for
- 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_Selectorhas this code snippet which replaces
"SELECT DISTINCT %1, contact_a.id, contact_a.sort_name", which yields
SELECT 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
%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.
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 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.