Contribution_type_id still needs to be migrated to financial_type_id for smart groups and some reports

1. Smart groups which include contribution_type_id in their saved queries need to be upgraded to reference financial_type_id instead.

Upgrade should check for contribution_type_id occurrences in civicrm_saved_search table - form_values and where_clause columns.
(e.g. SELECT *
FROM `civicrm_saved_search`
WHERE form_values LIKE '%contribution_type_id%' etc.)

• For civicrm_saved_search.form_values: De-serialize, replace, re-serialize
• For civicrm_saved_search.where_clause: string replace

2. Report instances which include contribution_type_id* in civicrm_report_instance.form_values need to be migrated.
2.1 xml/templates/civicrm_navigation.tpl includes several references to contribution_type_id, contribution_type_id_op and contribution_type_id_value. Please find and replace in serialized data.

2.2 Upgrade needs to find existing report_instance rows with the above references and deserialize, replace, re-serialize the form_values column.

3. Reports display issues
3.1 Financial Type in report columns displays ID instead of label for these 2 reports

• Constituent Detail (./Report/Form/Contact/Detail.php)
• Pledged but not Paid (./Report/Form/Pledge/Pbnp.php)

3.2 This report has references to contribution_type_id - but seems to display properly. Please investigate:

• Repeat Contributions (./Report/Form/Contribute/Repeat.php)

2.
— Original Post ----
When viewing a contact record, the groups tab gives a DB error. I tested it and it occurs with a new contact record (individual, household or organization) as well as historical contact record(s). The following is the stack trace:

NOTE: this environment was an upgrade from CiviCRM4.2.7 (current version 4.3.beta2)

#0 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(308): CRM_Core_Error::backtrace()
#1 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/ProximityQuery.php(300): CRM_Core_Error::fatal("Proximity searching requires you to set a valid geocoding provider")
#2 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1554): CRM_Contact_BAO_ProximityQuery::process(Object(CRM_Contact_BAO_Query), (Array:5))
#3 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1603): CRM_Contact_BAO_Query->whereClauseSingle((Array:5))
#4 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(481): CRM_Contact_BAO_Query->whereClause()
#5 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(442): CRM_Contact_BAO_Query->initialize()
#6 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(359): CRM_Contact_BAO_Query->__construct((Array:9), (Array:0), NULL, FALSE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND")
#7 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2623): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#8 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2590): CRM_Contact_BAO_Query->addGroupContactCache((Array:1))
#9 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2556): CRM_Contact_BAO_Query->savedSearch((Array:5))
#10 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1388): CRM_Contact_BAO_Query->group((Array:5))
#11 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1603): CRM_Contact_BAO_Query->whereClauseSingle((Array:5))
#12 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(481): CRM_Contact_BAO_Query->whereClause()
#13 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(442): CRM_Contact_BAO_Query->initialize()
#14 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3960): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE)
#15 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(156): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
#16 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(140): CRM_Contact_BAO_GroupContactCache::add((Array:36))
#17 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(461): CRM_Contact_BAO_GroupContactCache::loadAll()
#18 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/Page/View/GroupContact.php(65): CRM_Contact_BAO_GroupContactCache::contactGroup("1")
#19 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Contact/Page/View/GroupContact.php(164): CRM_Contact_Page_View_GroupContact->browse()
#20 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(292): CRM_Contact_Page_View_GroupContact->run((Array:4), NULL)
#21 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(70): CRM_Core_Invoke::runItem((Array:14))
#22 /home/dev17/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#23 /home/dev17/public_html/sites/all/modules/civicrm/drupal/civicrm.module(382): CRM_Core_Invoke::invoke((Array:4))
#24 [internal function](): civicrm_invoke("contact", "view", "group")
#25 /home/dev17/public_html/includes/menu.inc(348): call_user_func_array("civicrm_invoke", (Array:3))
#26 /home/dev17/public_html/index.php(18): menu_execute_active_handler()
#27

{main}

_______________

Below are the SQL details:

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

[code] => a515ac9c2796ca0e23adbe92c68fc9fc
[message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
[mode] => 16
[debug_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 92 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( LOWER() = '279' AND civicrm_contribution.contribution_status_id = 2 ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed'
AND civicrm_group_contact.group_id = 92 ) ); [nativecode=1582 ** Incorrect parameter count in the call to native function 'LOWER']
[type] => DB_Error
[user_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 92 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( LOWER() = '279' AND civicrm_contribution.contribution_status_id = 2 ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed'
AND civicrm_group_contact.group_id = 92 ) ); [nativecode=1582 ** Incorrect parameter count in the call to native function 'LOWER']
[to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 92 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( LOWER() = '279' AND civicrm_contribution.contribution_status_id = 2 ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed'
AND civicrm_group_contact.group_id = 92 ) ); [nativecode=1582 ** Incorrect parameter count in the call to native function 'LOWER']"]
)