Answers.Body: <p>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. </p>
  
  <p>Now back to the bug mentioned here, here's my fix <a href="https://github.com/civicrm/civicrm-core/pull/6320" rel="nofollow">https://github.com/civicrm/civicrm-core/pull/6320</a>. In addition buildClause will also support previous format but will through a e-notice to notify the wrong value format.  </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-28T15:23:24.840Z
Answers.Text: I tried the fix.  It's still building the where clause without the WHERE.  See my comment above.  I'm guessing this is a different case.   Should I file a different question/issue?
Answers.UserDisplayName: Paul-Tahoe
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-28T16:43:19.013Z
Answers.Text: I was missing the steps to replicate the bug from UI, as you mentioned earlier, so the fix was an improvement. But if the bug still persist please file a issue with steps to replicate and if possible attach the screenshots of 46 demo site http://d46.demo.civicrm.org !!
Answers.UserDisplayName: Monish Deb
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-28T19:30:43.047Z
Answers.Text: I'll file a separate issue.  In the meantime, I get the error if I clear the civi cache and then go to the manage groups page.  It goes off and creates the cache for that page.  It churns for a while and then errors with syntax error.  I'm guessing I won't be able to reproduce in a demo database.  Some of the smart groups are based upon include exclude query which in the past has caused issues.
Answers.UserDisplayName: Paul-Tahoe
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-29T02:22:21.767Z
Answers.Text: Thanks Monish, that seems to make the smart groups work again.  (Though I haven't done full testing.)  Unfortunately, when I try to view a contact record, it breaks with the error "Expected one Contact but found 0"
Answers.UserDisplayName: Norris
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-29T12:20:33.860Z
Answers.Text: Could you provide links to the JIRA issue(s) please? Thanks.
Answers.UserDisplayName: davejenx
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-29T12:54:51.053Z
Answers.Text: Fixed under https://issues.civicrm.org/jira/browse/CRM-16483 and https://issues.civicrm.org/jira/browse/CRM-16922
Answers.UserDisplayName: Monish Deb
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-28T10:39:45.020Z
Answers.LastActivityDate: 2015-07-28T10:45:32.783Z
Answers.LastEditDate: 2015-07-28T10:45:32.783Z
Answers.UserDisplayName: Monish Deb
Body: <p>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.</p>
  
  <p>The database query that causes problems is:</p>
  
  <pre><code>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 )
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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!</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-07-28T03:16:14.843Z
Comments.Text: I too am seeing odd things with smart groups in 4.6.5.  I have a number of smart groups that are based upon membership.  It has lost this info so it pulls everyone.  Also see error if I clear caches and then go to manage groups gets syntax error the sql has AND where should have WHERE: CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache768 (SELECT 65 as group_id, contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a 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 = 65 ) )
Comments.UserDisplayName: Paul-Tahoe
Tags: <smartgroups><issues>
Title: Why do Smart Groups now give database errors?
ThreadText: # Why do Smart Groups now give database errors?
  
  (Asked by: username_0 on 2015-07-27)
  
  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!
  
  **Tags:** smartgroups, issues
  
  > **Comments:**
  > **username_1**: I too am seeing odd things with smart groups in 4.6.5.  I have a number of smart groups that are based upon membership.  It has lost this info so it pulls everyone.  Also see error if I clear caches and then go to manage groups gets syntax error the sql has AND where should have WHERE: CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache768 (SELECT 65 as group_id, contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a 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 = 65 ) )  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2015-07-28. Score: 4
  
  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.
  
  > **Comments:**
  > **username_1**: I tried the fix.  It's still building the where clause without the WHERE.  See my comment above.  I'm guessing this is a different case.   Should I file a different question/issue?  
  > **username_2**: I was missing the steps to replicate the bug from UI, as you mentioned earlier, so the fix was an improvement. But if the bug still persist please file a issue with steps to replicate and if possible attach the screenshots of 46 demo site http://d46.demo.civicrm.org !!  
  > **username_1**: I'll file a separate issue.  In the meantime, I get the error if I clear the civi cache and then go to the manage groups page.  It goes off and creates the cache for that page.  It churns for a while and then errors with syntax error.  I'm guessing I won't be able to reproduce in a demo database.  Some of the smart groups are based upon include exclude query which in the past has caused issues.  
  > **username_0**: Thanks Monish, that seems to make the smart groups work again.  (Though I haven't done full testing.)  Unfortunately, when I try to view a contact record, it breaks with the error "Expected one Contact but found 0"  
  > **username_3**: Could you provide links to the JIRA issue(s) please? Thanks.  
  > **username_2**: Fixed under https://issues.civicrm.org/jira/browse/CRM-16483 and https://issues.civicrm.org/jira/browse/CRM-16922  
  
  ---
  
  