Contribution Summary report throw DB error with custom data and soft credit fields (#479) · Issues · Development / Core

Contribution Summary report throw DB error with custom data and soft credit fields

Steps to replicate :

Go to Contribution Summary report, select column Soft Credit Amount Stats
Select filter criteria on any custom field, and search.

It throws DB error:

Database Error Code: Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where 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] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']"]
)

Steps to replicate :

1. Go to Contribution Summary report, select column Soft Credit Amount Stats
2. Select filter criteria on any custom field, and search.

It throws DB error:
```
Database Error Code: Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where 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] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, COUNT(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_count, SUM(contribution_soft_civireport.amount) as civicrm_contribution_soft_soft_amount_sum, ROUND(AVG(contribution_soft_civireport.amount),2) as civicrm_contribution_soft_soft_amount_avg   FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
              WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( ( value_constituent_information_1_civireport.most_important_issue_1 IN ( 'Edu') ) ) GROUP BY YEAR(contribution_civireport.receive_date),  MONTH(contribution_civireport.receive_date) WITH ROLLUP    LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_constituent_information_1_civireport.most_important_issue_1' in 'where clause']"]
)
```

To upload designs, you'll need to enable LFS. More information