Error when trying to generate Activity Report after upgrading to 4.6.2

Question

UPDATE: I upgraded to 4.6.3 and the problem persists. I can see that it's related to our petition, but beyond that I'm stumped. I'm having problems with other reports timing out too now, and we've only got around 600 contacts.

The error below occurs when I go to Contacts > Contact Reports > Activities

"Sorry but we are not able to provide this at the moment. DB Error: no such field"

Before the upgade this worked fine. Everything else seems to be functioning okay. Tired it in demo and it works there. Does anyone have ideas?

Thanks!

Here is the full text of the error message I see when I'm in debugging mode:

Sorry but we are not able to provide this at the moment.
DB Error: no such field
Error Details
Database Error Code: Unknown column '1INNER' in 'on clause', 1054
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target 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, civicrm_email_target.email as civicrm_email_contact_target_email, 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, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        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_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target 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, civicrm_email_target.email as civicrm_email_contact_target_email, 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, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        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_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_activity_temp_target 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, civicrm_email_target.email as civicrm_email_contact_target_email, 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, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        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_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']"]
)

Answer 1

UPDATE: This is now submitted as CRM-16640, pull request to fix is here: https://github.com/civicrm/civicrm-core/pull/5971. This fix should be built in to 4.6.4+.

You've found a bug. I found a fix, but have to run out so don't have time at the moment to submit it upstream - I'll try to do so later this week.

For now - if you're able to edit your CiviCRM files, go to your civicrm plugin folder and find the file CRM/Report/Form/Activity.php. On line 521 you'll see this line:

    $this->_from .= "INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}

Add a space before the word "INNER" like so:

    $this->_from .= " INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}

Answer 2

If it's working on the demo site, then I would try generating a new activity report from the template. Go to (Administer menu > CiviReport > Create New Report from Template) and select "Activity Report". If it works from there, save the new report and delete the old one - and of course, come back here to report on whether it worked!