Answers.Body: <p>The issue is locked at <a href="https://lab.civicrm.org/dev/core/issues/325" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/issues/325</a> and its fix is at <a href="https://github.com/civicrm/civicrm-core/pull/12660" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/12660</a></p>
  
  <p>You can either apply <a href="https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/12660.patch" rel="nofollow noreferrer">patch</a> or wait for next release.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-30T18:17:08.497Z
Answers.LastActivityDate: 2018-08-30T18:17:08.497Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>After CiviCRM upgrade from v4.7.14. to v5.4.1. on Drupal 7 some reports that use AGE column are broken, e.g. <strong>Instance 8: Contribution Details.</strong></p>
  
  <p>The report works fine, if AGE column is disabled (checkbox unticked), but produce the error listed below, if AGE column is enabled.</p>
  
  <p>The error appears specifically with the upgrade to <strong>v4.7.30</strong>, but does not disappear all they way up to v5.4.1.</p>
  
  <pre><code>Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
  DB Error: no such field
  Error Details
  Database Error Code: Unknown column 'contact.birth_date' in 'field list', 1054
  Additional Details:
  Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; handle
          )
  
      [code] =&gt; -19
      [message] =&gt; DB Error: no such field
      [mode] =&gt; 16
      [debug_info] =&gt; CREATE TEMPORARY TABLE civireport_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']
      [type] =&gt; DB_Error
      [user_info] =&gt; CREATE TEMPORARY TABLE civireport_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']
      [to_string] =&gt; [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']"]
  )
  Return to home page.
  </code></pre>
  
Tags: <issues>
Title: Report causes DB errors when AGE column is enabled
ThreadText: # Report causes DB errors when AGE column is enabled
  
  (Asked by: username_0 on 2018-08-30)
  
  After CiviCRM upgrade from v4.7.14. to v5.4.1. on Drupal 7 some reports that use AGE column are broken, e.g. Instance 8: Contribution Details.
  The report works fine, if AGE column is disabled (checkbox unticked), but produce the error listed below, if AGE column is enabled.
  The error appears specifically with the upgrade to v4.7.30, but does not disappear all they way up to v5.4.1.
  Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
  DB Error: no such field
  Error Details
  Database Error Code: Unknown column 'contact.birth_date' in 'field list', 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_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']
      [type] => DB_Error
      [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']
      [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_contribution_detail_temp1  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, TIMESTAMPDIFF(YEAR, contact.birth_date, CURDATE()) as civicrm_contact_age, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.payment_instrument_id as civicrm_contribution_payment_instrument_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_civireport.total_amount as civicrm_contribution_total_amount, 'Contribution' as civicrm_contribution_contribution_or_soft, note_civireport.note as civicrm_note_contribution_note, value_member_since_1_civireport.membership_type_11 as civicrm_value_member_since_1_custom_11   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
        LEFT JOIN civicrm_phone phone_civireport
               ON contact_civireport.id = phone_civireport.contact_id AND
                  phone_civireport.is_primary = 1
  
                   LEFT JOIN civicrm_address address_civireport
                             ON (contact_civireport.id =
                                 address_civireport.contact_id) AND
                                 address_civireport.is_primary = 1
  
              LEFT JOIN  civicrm_email email_civireport
                     ON (contact_civireport.id = email_civireport.contact_id AND
                         email_civireport.is_primary = 1) 
              LEFT JOIN civicrm_note note_civireport
                        ON ( note_civireport.entity_table = 'civicrm_contribution' AND
                             contribution_civireport.id = note_civireport.entity_id )
  LEFT JOIN civicrm_value_member_since_1 value_member_since_1_civireport ON value_member_since_1_civireport.entity_id = contact_civireport.id WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) ) AND (`note_civireport`.`id` IS NULL OR (`note_civireport`.`id` IN (SELECT id FROM `civicrm_note` WHERE ((entity_table = 'civicrm_relationship')) OR ((entity_table = 'civicrm_contact')) OR ((entity_table = 'civicrm_participant')) OR ((entity_table = 'civicrm_contribution')))))   GROUP BY contact_civireport.id, contribution_civireport.id  ORDER BY contribution_civireport.receive_date DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'contact.birth_date' in 'field list']"]
  )
  Return to home page.
  
  **Tags:** issues
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-08-30. Score: 1 | Accepted answer
  
  The issue is locked at <https://lab.civicrm.org/dev/core/issues/325> and its fix is at <https://github.com/civicrm/civicrm-core/pull/12660>
  
  You can either apply [patch](https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/12660.patch) or wait for next release.
  
  
  ---
  
  