Activity Detail report gives syntax error with force=1 if include case activities is on
Directly related to: #1552 (closed)
Summary from 1552: On the Activity Details report, when the filter "Include Case Activities" is set to "Yes" the report can generate an SQL syntax error as follows:
`[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civicrm_tmp_e_dflt_607d7436413c499f88a03a05edf1fd9c ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
FROM civicrm_activity activity_civireport
INNER JOIN civicrm_activity_contact activity_contact_civireport
ON activity_civireport.id = activity_contact_civireport.activity_id AND
activity_contact_civireport.record_type_id = 3
INNER JOIN civicrm_contact civicrm_contact_target
ON activity_contact_civireport.contact_id = civicrm_contact_target.id
LEFT JOIN civicrm_case_activity
ON .activity_id = activity_civireport.id
LEFT JOIN civicrm_address address_civireport
ON (civicrm_contact_target.id =
address_civireport.contact_id) AND
address_civireport.is_primary = 1
WHERE activity_civireport.is_test = 0 AND
activity_civireport.is_deleted = 0 AND
activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_date_time >= 20200401000000) AND ( activity_civireport.activity_date_time <= 20200430235959) AND ( activity_civireport.activity_type_id IN (66,27,20,19,37,29,18,16,53,26,15,33,35,36,48,52,51,6,42,49,43,3,50,5,41,54,14,12,45,62,55,25,64,34,56,1,8,17,7,58,23,13,4,46,32,2,30,10,11,22,24,47,40,21,44,60,28,9,39,38,67,68,31) ) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= activity_civireport.id
LEFT JOIN civicrm_address address_civi' at line 10]`
The exact same syntax error still exists in a demo install of 5.26.alpha1 but only when the direct "View Results" link is used without first viewing report criteria i.e. the report form is viewed with a param like "force=1" or "output=result" as it generally would be in a direct menu link to that report.
Further, it seems the following notices appear only when the Activity Detail report result are accessed directly. These notices to not appear when one views criteria first and clicks "view results":
Notice: Undefined index: civicrm_case_activity in CRM_Report_Form_Activity->buildFrom() (line 1157 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Form/Activity.php).
Notice: Undefined offset: 3 in CRM_Report_Utils_Report::getInstanceID() (line 307 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Utils/Report.php).
It appears {$this->_aliases['civicrm_case_activity']}
is only populated when the report criteria is accessed before results.