Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.6
-
Fix Version/s: 4.6.9
-
Component/s: CiviReport
-
Labels:None
-
Documentation Required?:None
-
Funding Source:Contributed Code
Description
The problem is that two ORDER BY clauses are added.
If you choose the grouping feature, you get:
Sep 09 19:00:55 [info] $backTrace = #0 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Error.php(235): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#4 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#5 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "DB_Error", TRUE)
#6 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#9 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/DataObject.php(2442): DB_common->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#10 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#11 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/DAO.php(325): DB_DataObject->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#12 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/DAO.php(1192): CRM_Core_DAO->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", TRUE)
#13 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Report/Form.php(2813): CRM_Core_DAO::executeQuery("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#14 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Report/Form/Event/ParticipantListCount.php(505): CRM_Report_Form->buildRows("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", NULL)
#15 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Form.php(345): CRM_Report_Form_Event_ParticipantListCount->postProcess()
#16 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php(74): CRM_Core_Form->mainProcess()
#17 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Event_ParticipantListCount), "submit")
#18 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Event_ParticipantListCount), "submit")
#19 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("submit")
#20 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(115): CRM_Core_Controller->run()
#21 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Report/Page/Report.php(76): CRM_Utils_Wrapper->run("CRM_Report_Form_Event_ParticipantListCount", NULL, NULL)
#22 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Invoke.php(312): CRM_Report_Page_Report->run((Array:4), NULL)
#23 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:14))
#24 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:4))
#25 /var/www/civicrm/civicrm-buildkit/build/mycivi/sites/all/modules/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:4))
#26 [internal function](): civicrm_invoke("report", "event", "participantlist")
#27 /var/www/civicrm/civicrm-buildkit/build/mycivi/includes/menu.inc(519): call_user_func_array("civicrm_invoke", (Array:3))
#28 /var/www/civicrm/civicrm-buildkit/build/mycivi/index.php(21): menu_execute_active_handler()
#29
The SQL:
Sep 09 19:00:55 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, employer_civireport.id as civicrm_employe
r_id, employer_civireport.organization_name as civicrm_employer_organization_name, phone_civireport.phone as civicrm_phone_phone, participant_civireport.id as civicrm_participant_participant_i
d, participant_civireport.event_id as civicrm_participant_event_id, participant_civireport.role_id as civicrm_participant_role_id, participant_civireport.status_id as civicrm_participant_statu
s_id, event_civireport.event_type_id as civicrm_event_event_type_id, SUM(line_item_civireport.line_total) as civicrm_line_item_line_total_sum, SUM(line_item_civireport.participant_count) as ci
vicrm_line_item_participant_count_sum
FROM civicrm_participant participant_civireport
LEFT JOIN civicrm_event event_civireport
ON (event_civireport.id = participant_civireport.event_id ) AND event_civireport.is_template = 0
LEFT JOIN civicrm_contact contact_civireport
ON (participant_civireport.contact_id = contact_civireport.id )
LEFT JOIN civicrm_contact employer_civireport
ON (employer_civireport.id = contact_civireport.employer_id )
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_phone phone_civireport
ON contact_civireport.id = phone_civireport.contact_id AND
phone_civireport.is_primary = 1
LEFT JOIN civicrm_line_item line_item_civireport
ON line_item_civireport.entity_table = 'civicrm_participant' AND participant_civireport.id =line_item_civireport.entity_id WHERE participant_civireport.is_test = 0 AND contact_ci
vireport.is_deleted = 0 GROUP BY participant_civireport.id ORDER BY participant_civireport.event_id, event_civireport.event_type_id, contact_civireport.sort_name ORDER BY contact_civireport.
sort_name ASC LIMIT 0, 50 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY c
ontact_civireport.sort_name ASC LIMIT 0, 50' at line 20