Why do Smart Groups now give database errors?

Question

I just updated from 4.6.3 to 4.6.5, on Joomla. I have a number of Smart Groups which worked fine before, but now inconsistently fail to load contacts or settings, giving a database error instead. The error seems related to building the cache, so I assume the inconsistency comes from whether or not it's refreshing the cache entry.

The database query that causes problems is:

SELECT id, cache_date, saved_search_id, children
FROM   civicrm_group
WHERE  id 0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    children IS NOT NULL )

Clearly "WHERE id 0" is invalid syntax. The problem seems to be with changes to the CRM/Contact/BAO/Query.php code. The function addGroupContactCache used to create a clause within the function, something like "WHERE ID IN (4, 7, 10)". Now it calls self::buildClause.

From some debugging I did, buildClause is usually called with "IN" as the $op value, in which case it generates a valid clause. But sometimes the $op value is "=", and it returns that "id 0" clause which breaks things.

The code changes between 4.6.3 and 4.6.5 are complicated enough that I'm not sure how to quick-fix it. Any suggestions to get our smart groups working again are very welcome!

Answer 1

To standardize Get.api and Search form underlying where clause(s), buildClause() fn expects value(s) to be in array(operator => array(values)) format [I call it "Operator as Key" - OK format :p ]. Earlier in prior version most of the Get.api aren't working because the buildClause() was designed according to Search formValues format [array(values)], so there is conflict in how the values got retrieved and eventually result into Fatal Error/Empty Value for get.Apis. Whereas in some worst-case scenario the get.Api was only working proper with '=' operator as the buildClause didn't got the flexibility to work with any other operator other then '='. But recent changes ( 4.6 onward ) in buildClause expects values atleast for n-nary operators IN/NOT IN in the desired OK format. On the otherhand recent changes has ALSO ensured that the Search Form formvalues processed first into OK format before passing it to build-Clause.

Now back to the bug mentioned here, here's my fix https://github.com/civicrm/civicrm-core/pull/6320. In addition buildClause will also support previous format but will through a e-notice to notify the wrong value format.