DB Error when creating a smart group from the results of Advanced Search

[ken]

I tried with 4.2.0 to create a smart group from an Advanced Search where the criteria was contacts who had made a contribution in the last 12 months of a particular contribution type.

The problem is the query: the INSERT part expects a group id and a contact id, but the SELECT part only supplies the contact id.

Code: [Select]

INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ( SELECT DISTINCT(contact_a.id)  as contact_id  FROM
The following error is thrown ...

Aug 30 21:25:56  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [ 0 ] => CRM_Core_Error
            [ 1 ] => handle
        )

    [ code ] => -22
    [message] => DB Error: value count on row
    [mode] => 16
    [debug_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ( SELECT DISTINCT(contact_a.id)  as contact_id  FROM civicrm_contact contact_a INNER JOIN It_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableIt ON (contact_a.id = temptableIt.contact_id) LEFT JOIN Xg_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIt.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL 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 = 386 ) ); [nativecode=1136 ** Column count doesn't match value count at row 1]
    [type] => DB_Error
    [user_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ( SELECT DISTINCT(contact_a.id)  as contact_id  FROM civicrm_contact contact_a INNER JOIN It_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableIt ON (contact_a.id = temptableIt.contact_id) LEFT JOIN Xg_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIt.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL 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 = 386 ) ); [nativecode=1136 ** Column count doesn't match value count at row 1]
    [to_string] => [db_error: message="DB Error: value count on row" code=-22 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ( SELECT DISTINCT(contact_a.id)  as contact_id  FROM civicrm_contact contact_a INNER JOIN It_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableIt ON (contact_a.id = temptableIt.contact_id) LEFT JOIN Xg_civicrm_temp_custom_117b0d81079a893545b47413630bd049 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIt.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL 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 = 386 ) ); [nativecode=1136 ** Column count doesn't match value count at row 1]"]
)


Aug 30 21:25:56  [info] $backTrace = /sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 196
, handle,
/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2421
/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1613
/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 155
/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 888
/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php, executeQuery, 347
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, load, 2560
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, addGroupContactCache, 2527
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, savedSearch, 2504
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, group, 1336
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, whereClauseSingle, 1527
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, whereClause, 458
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, initialize, 419
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, __construct, 3631
/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php, apiQuery, 216
/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php, getMember, 367
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, load, 2560
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, addGroupContactCache, 2527
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, savedSearch, 2504
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, group, 1336
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, whereClauseSingle, 1527
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, whereClause, 458
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, initialize, 419
/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, __construct, 3631
/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php, apiQuery, 104
/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php, add, 414
/sites/all/modules/civicrm/CRM/Contact/Form/Task/SaveSearch.php, create, 215
/sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 246
/sites/all/modules/civicrm/CRM/Core/StateMachine.php, mainProcess, 159
/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 60
/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 289
/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 228
/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 500
, civicrm_invoke,
/includes/menu.inc, call_user_func_array, 516
/index.php, menu_execute_active_handler, 21

[ken]

I seem to be getting this error with 4.2.0 on various criteria for Smart Groups

[ken]

Line 190 of CRM/Contact/Form/Search/Custom/Group.php sets ...

Code: [Select]

    $sql = " SELECT $selectClause $from WHERE  $where ";
The space at the beginning of the string causes the following statement on line 326 of CRM/Contact/BAO/GroupContactCache.php to leave $sql unchanged

Code: [Select]

      $sql = preg_replace("/^SELECT/", "SELECT $groupID as group_id, ", $sql);
This means that the SQL doesn't select the group_id and the INSERT fails.

Changing the pattern to "/^\s*SELECT/" should fix the problem, except the query has the keyword 'DISTINCT' embedded in it, so there are further syntax issue.

More research needed.

[ken]

The patch should be ...

Code: [Select]

-      $sql = preg_replace("/^SELECT/", "SELECT $groupID as group_id, ", $sql);
+      $sql = preg_replace("/^\s*SELECT\s+(DISTINCT\s*)?/", "SELECT $1 $groupID as group_id, ", $sql);
This is a hack, and relies on the SQL having a certain format. The proper solution would be to change line 289 to be ...

Code: [Select]

-         $searchSQL = $customClass->contactIDs();
+       $searchSQL = $customClass->groupIDandContactIDs($groupID);
... and add the function groupIDandContactIDs() to all classes which might be instantiated as $customClass. This function would call all() in the same way contactIDs() does, but with an extra argument for the $groupID. This argument for all() would default to NULL, and if non-NULL, all() would add "$groupID as group_id," to the $selectClause.

It's a little beyond me to decide if the latter solution is appropriate, or what the scope of that change is. I'll create an issue and a patch for the hack solution.

[ken]

Created issue http://issues.civicrm.org/jira/browse/CRM-10753