Answers.Body: <p>I think this is the same problem as <a href="https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14" rel="nofollow noreferrer">https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-09-24T20:13:51.627Z
Answers.Text: Thanks for this - 47 minutes from reporting the problem and the fix is now installed!
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-09-24T20:14:21.603Z
Answers.Text: Great! Glad it's working.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-09-24T19:59:39.887Z
Answers.LastActivityDate: 2020-09-24T19:59:39.887Z
Answers.UserDisplayName: Demerit
Body: <p>Activity Search is failing every time for this install (Search/Find Activities) no matter what search parameters are used. The problem is the following SQL error:
  <br>
  <em>SELECT count( DISTINCT ( civicrm_activity.id ) ) as rowCount  FROM civicrm_contact contact_a   LEFT JOIN civicrm_activity_contact
  ON ( civicrm_activity_contact.contact_id = contact_a.id )  LEFT JOIN civicrm_activity
  ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
  AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) INNER JOIN civicrm_contact
  ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )  LEFT JOIN civicrm_activity_contact source_activity
  ON (source_activity.activity_id = civicrm_activity_contact.activity_id
  AND source_activity.record_type_id = 2)
  LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id)  WHERE  ( civicrm_activity.activity_type_id IN (&quot;110&quot;) AND civicrm_activity.status_id IN (&quot;1&quot;, &quot;2&quot;) AND civicrm_activity.is_test = 0 )  AND  (  ( civicrm_activity.activity_type_id IN (110,135,136,120,1,2,3,116,4,9,12,5,6,19,22,34,40,44,37,38,39,45,41,42,43,52,49,50,46,47,48,51,64,114,115,68,73,74,75,76,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,96,97,99,100,101,102,103,104,105,106,107,108,109,111,113,117,118,119,121,122,123,124,125,126,127,128,130,131,133,134,137,138,139,140,141,142,143) )  )  AND (contact_a.is_deleted = 0) AND (civicrm_activity.activity_type_id Array)
  <br>
  the error message is 'check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Array)' at line 8]</em>'
  <br>
  As you can see this install has a <em><strong>lot</strong></em> of activity types - could this be the reason? The last 'AND' statement seems to be the problem.  This is on 5.28.3 and activity search is working fine on other 5.28.3 systems. Also there seems to be no limit on the number of items in the 'IN' clause in MySQL.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-09-24T19:30:16.740Z
Comments.Text: Do you have some ACL or extension that restricts by types? It's not the amount - what's happening is there's supposed to be a string but it's getting a php array, so it's a coding error somewhere.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-09-24T19:35:53.250Z
Comments.Text: Yes indeed (my, that was a fast response!). We have the Activity ACL extension installed (ACL for Activity Types).  I didn't think it looked like that was relevant...wrong?
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-09-24T19:59:15.857Z
Comments.Text: See https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-09-24T20:02:49.253Z
Comments.Text: Many thanks, I'll test and will confirm if that's the answer. I looked at that site but it wasn't in the list of issues
Comments.UserDisplayName: Andy Clark
Tags: <activity><sql>
Title: Why is this Activity Search failing?
ThreadText: # Why is this Activity Search failing?
  
  (Asked by: username_0 on 2020-09-24)
  
  Activity Search is failing every time for this install (Search/Find Activities) no matter what search parameters are used. The problem is the following SQL error:
    
  *SELECT count( DISTINCT ( civicrm\_activity.id ) ) as rowCount FROM civicrm\_contact contact\_a LEFT JOIN civicrm\_activity\_contact
  ON ( civicrm\_activity\_contact.contact\_id = contact\_a.id ) LEFT JOIN civicrm\_activity
  ON ( civicrm\_activity.id = civicrm\_activity\_contact.activity\_id
  AND civicrm\_activity.is\_deleted = 0 AND civicrm\_activity.is\_current\_revision = 1 ) INNER JOIN civicrm\_contact
  ON ( civicrm\_activity\_contact.contact\_id = civicrm\_contact.id and civicrm\_contact.is\_deleted != 1 ) LEFT JOIN civicrm\_activity\_contact source\_activity
  ON (source\_activity.activity\_id = civicrm\_activity\_contact.activity\_id
  AND source\_activity.record\_type\_id = 2)
  LEFT JOIN civicrm\_contact source\_contact ON (source\_activity.contact\_id = source\_contact.id) WHERE ( civicrm\_activity.activity\_type\_id IN ("110") AND civicrm\_activity.status\_id IN ("1", "2") AND civicrm\_activity.is\_test = 0 ) AND ( ( civicrm\_activity.activity\_type\_id IN (110,135,136,120,1,2,3,116,4,9,12,5,6,19,22,34,40,44,37,38,39,45,41,42,43,52,49,50,46,47,48,51,64,114,115,68,73,74,75,76,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,96,97,99,100,101,102,103,104,105,106,107,108,109,111,113,117,118,119,121,122,123,124,125,126,127,128,130,131,133,134,137,138,139,140,141,142,143) ) ) AND (contact\_a.is\_deleted = 0) AND (civicrm\_activity.activity\_type\_id Array)
    
  the error message is 'check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Array)' at line 8]*'
    
  As you can see this install has a ***lot*** of activity types - could this be the reason? The last 'AND' statement seems to be the problem. This is on 5.28.3 and activity search is working fine on other 5.28.3 systems. Also there seems to be no limit on the number of items in the 'IN' clause in MySQL.
  
  **Tags:** activity, sql
  
  > **Comments:**
  > **username_1**: Do you have some ACL or extension that restricts by types? It's not the amount - what's happening is there's supposed to be a string but it's getting a php array, so it's a coding error somewhere.  
  > **username_0**: Yes indeed (my, that was a fast response!). We have the Activity ACL extension installed (ACL for Activity Types).  I didn't think it looked like that was relevant...wrong?  
  > **username_1**: See https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14  
  > **username_0**: Many thanks, I'll test and will confirm if that's the answer. I looked at that site but it wasn't in the list of issues  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2020-09-24. Score: 3 | Accepted answer
  
  I think this is the same problem as <https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14>
  
  > **Comments:**
  > **username_0**: Thanks for this - 47 minutes from reporting the problem and the fix is now installed!  
  > **username_1**: Great! Glad it's working.  
  
  ---
  
  