Answers.Body: <p>Check Drupal permissions at <code>http://site/admin/people/permissions</code>. It might be "CiviReport: save Report Criteria".</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:27:39.733Z
Answers.LastActivityDate: 2019-04-24T23:27:39.733Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>Although it's not ultimately an answer to why the code is blowing up, the simplest solution to my problem was found in the report's settings. Under the <code>Sorting</code> tab, the report was set to sort on <code>Contribution Status</code>. This is what caused the blow up. If I set it to sort on another column, like <code>Contact Name</code>, the report runs fine.</p>
  
  <p>So now I have another, hopefully much simpler, problem: I cannot resave the report with the new sort setting. Really? You can't change the options in a report definition and then save them? I looked in the (apparently old) docs, which <a href="https://docs.civicrm.org/user/en/latest/reporting/set-up/#editing-or-copying-an-existing-report" rel="nofollow noreferrer">show clearly</a> an <code>Update Report</code> button, which no longer exists. I also found <a href="https://civicrm.stackexchange.com/questions/15191/cant-save-reports">this</a>, which presumably was correct at the time, but didn't remain in the UI long. I don't have a <code>Save</code> item under my Actions menu. This is, at best, terrible UX. Can someone enlighten me? Or is the answer implicit in the above comment: reports are write-only, and if you need to make a change, you redo it from scratch?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-06-04T15:07:02.763Z
Answers.Text: I've just run into this as well - it seems to have arrived with 5.7.6 update which is surprising. Specifically, the contribution/detail report now gives the error noted above when sorting by contribution status. So aside from the specific advice of changing the sort order, this would seem to me a bug that should be reported as an issue.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:04:53.513Z
Answers.Text: He he that isn't what I meant in my comment I've just found sometimes that's the cleanest. As to why there is no Save action under the Actions menu I'm not sure - it seems like there should be. I can see it on dmaster.demo.civicrm.org.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:18:02.953Z
Answers.Text: Hey thanks for the pointer to dmaster... I was not aware of that, I now I can see that the Save option is there - for some of the people, some of the time ;=P I went looking for some sort of global setting saying "don't allow modification of reports" or such, and didn't find it. And I tried **Create New Report From Template**. I can set up the report options the same as my broken report, and _even sort on Contribution Status_, but... I can't save it.
Answers.UserDisplayName: Kotodharma
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:19:37.810Z
Answers.Text: Check drupal permissions in the drupal admin pages - I can't remember off the top if there's one that controls this.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:21:20.167Z
Answers.Text: It might be "CiviReport: save Report Criteria", at http://SITE/admin/people/permissions
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:26:23.900Z
Answers.Text: That was the setting... damn, so complex.. that setting must have been introduced at some point and left unset, and I am too lazy to go comb through all the myriad mind-numbing settings every time I update this thing to see what might be new. So I'm good now. Please post that as an answer, and I'll upvote it.
Answers.UserDisplayName: Kotodharma
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T23:27:53.077Z
Answers.Text: Great! . . . . .. .
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-24T22:28:22.543Z
Answers.LastActivityDate: 2019-04-24T22:45:30.893Z
Answers.LastEditDate: 2019-04-24T22:45:30.893Z
Answers.UserDisplayName: Kotodharma
Body: <p>EDIT: This question turned out to be, in practical terms, something different than I originally thought I was asking. I am changing the title so that others will find the answer more easily in the future.</p>
  
  <p>I've got CiviCRM 5.10.4 running on Drupal 7.66, and one particular function ("Subscriber Donor Report") is blowing up on an underlying SQL error. I turned on debugging, and following is what I see. Looks like a hard-core internals problem that I could never deal with on my own. Incidentally, this problem has persisted across several updates of Civi (since 5.4 maybe?) and also Drupal (at least since 7.60). Is there any hope? Could this possibly be caused by some weird data in the db? I wouldn't normally think so. Thanks.</p>
  
  <pre><code>Database Error Code: The used SELECT statements have a different number of columns, 1222
  Additional Details:
  Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; handle
          )
  
      [code] =&gt; -1
      [message] =&gt; DB Error: unknown error
      [mode] =&gt; 16
      [debug_info] =&gt; CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
      [type] =&gt; DB_Error
      [user_info] =&gt; CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
      [to_string] =&gt; [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]"]
  )
  </code></pre>
  
  <pre><code>#0 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
  #1 &lt;path&gt;/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
  #2 &lt;path&gt;/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error-&gt;__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #3 &lt;path&gt;/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error-&gt;__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #4 &lt;path&gt;/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR-&gt;_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "DB_Error", TRUE)
  #5 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR-&gt;__call("raiseError", (Array:7))
  #6 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common-&gt;raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "1222 ** The used SELECT statements have a different number of columns")
  #7 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli-&gt;mysqliRaiseError()
  #8 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli-&gt;simpleQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #9 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common-&gt;query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #10 &lt;path&gt;/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject-&gt;_query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #11 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/DAO.php(438): DB_DataObject-&gt;query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #12 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO-&gt;query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", TRUE)
  #13 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Form.php(3710): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", (Array:0))
  #14 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Form/Contribute/Detail.php(568): CRM_Report_Form-&gt;executeReportQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #15 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Form.php(2816): CRM_Report_Form_Contribute_Detail-&gt;beginPostProcessCommon()
  #16 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Form.php(3473): CRM_Report_Form-&gt;beginPostProcess()
  #17 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Form.php(886): CRM_Report_Form-&gt;postProcess()
  #18 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Form.php(585): CRM_Report_Form-&gt;preProcess()
  #19 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form-&gt;buildForm()
  #20 &lt;path&gt;/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display-&gt;perform(Object(CRM_Report_Form_Contribute_Detail), "display")
  #21 &lt;path&gt;/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller-&gt;handle(Object(CRM_Report_Form_Contribute_Detail), "display")
  #22 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page-&gt;handle("display")
  #23 &lt;path&gt;/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller-&gt;run()
  #24 &lt;path&gt;/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(89): CRM_Utils_Wrapper-&gt;run("CRM_Report_Form_Contribute_Detail", NULL, (Array:0))
  #25 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Report_Page_Instance-&gt;run((Array:4), NULL)
  #26 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
  #27 &lt;path&gt;/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
  #28 &lt;path&gt;/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:4))
  #29 &lt;path&gt;/includes/menu.inc(527): civicrm_invoke("report", "instance", "4")
  #30 &lt;path&gt;/index.php(21): menu_execute_active_handler()
  #31 {main}
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-04-17T00:00:36.390Z
Comments.Text: First thing I'd try is recreating that contribution report from scratch and see if that fixes it.
Comments.UserDisplayName: Demerit
Tags: <drupal><mysql><sql>
Title: Trouble saving changes to a report
ThreadText: # Trouble saving changes to a report
  
  (Asked by: username_0 on 2019-04-16)
  
  EDIT: This question turned out to be, in practical terms, something different than I originally thought I was asking. I am changing the title so that others will find the answer more easily in the future.
  
  I've got CiviCRM 5.10.4 running on Drupal 7.66, and one particular function ("Subscriber Donor Report") is blowing up on an underlying SQL error. I turned on debugging, and following is what I see. Looks like a hard-core internals problem that I could never deal with on my own. Incidentally, this problem has persisted across several updates of Civi (since 5.4 maybe?) and also Drupal (at least since 7.60). Is there any hope? Could this possibly be caused by some weird data in the db? I wouldn't normally think so. Thanks.
  
  ```
  Database Error Code: The used SELECT statements have a different number of columns, 1222
  Additional Details:
  Array
  (
      [callback] => Array
          (
              [0] => CRM_Core_Error
              [1] => handle
          )
  
      [code] => -1
      [message] => DB Error: unknown error
      [mode] => 16
      [debug_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
      [type] => DB_Error
      [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
      [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
  (SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
  UNION ALL
  (SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]"]
  )
  
  ```
  
  ```
  #0 <path>/sites/all/modules/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
  #1 <path>/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
  #2 <path>/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #3 <path>/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #4 <path>/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "DB_Error", TRUE)
  #5 <path>/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
  #6 <path>/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "1222 ** The used SELECT statements have a different number of columns")
  #7 <path>/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
  #8 <path>/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #9 <path>/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #10 <path>/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #11 <path>/sites/all/modules/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #12 <path>/sites/all/modules/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", TRUE)
  #13 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(3710): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", (Array:0))
  #14 <path>/sites/all/modules/civicrm/CRM/Report/Form/Contribute/Detail.php(568): CRM_Report_Form->executeReportQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
  #15 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(2816): CRM_Report_Form_Contribute_Detail->beginPostProcessCommon()
  #16 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(3473): CRM_Report_Form->beginPostProcess()
  #17 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(886): CRM_Report_Form->postProcess()
  #18 <path>/sites/all/modules/civicrm/CRM/Core/Form.php(585): CRM_Report_Form->preProcess()
  #19 <path>/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form->buildForm()
  #20 <path>/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display->perform(Object(CRM_Report_Form_Contribute_Detail), "display")
  #21 <path>/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Contribute_Detail), "display")
  #22 <path>/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("display")
  #23 <path>/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
  #24 <path>/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(89): CRM_Utils_Wrapper->run("CRM_Report_Form_Contribute_Detail", NULL, (Array:0))
  #25 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Report_Page_Instance->run((Array:4), NULL)
  #26 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
  #27 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
  #28 <path>/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:4))
  #29 <path>/includes/menu.inc(527): civicrm_invoke("report", "instance", "4")
  #30 <path>/index.php(21): menu_execute_active_handler()
  #31 {main}
  
  ```
  
  **Tags:** drupal, mysql, sql
  
  > **Comments:**
  > **username_1**: First thing I'd try is recreating that contribution report from scratch and see if that fixes it.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-04-24. Score: 3
  
  Check Drupal permissions at `http://site/admin/people/permissions`. It might be "CiviReport: save Report Criteria".
  
  
  ---
  
  ### Answer by username_0 on 2019-04-24. Score: 3
  
  Although it's not ultimately an answer to why the code is blowing up, the simplest solution to my problem was found in the report's settings. Under the `Sorting` tab, the report was set to sort on `Contribution Status`. This is what caused the blow up. If I set it to sort on another column, like `Contact Name`, the report runs fine.
  
  So now I have another, hopefully much simpler, problem: I cannot resave the report with the new sort setting. Really? You can't change the options in a report definition and then save them? I looked in the (apparently old) docs, which [show clearly](https://docs.civicrm.org/user/en/latest/reporting/set-up/#editing-or-copying-an-existing-report) an `Update Report` button, which no longer exists. I also found [this](https://civicrm.stackexchange.com/questions/15191/cant-save-reports), which presumably was correct at the time, but didn't remain in the UI long. I don't have a `Save` item under my Actions menu. This is, at best, terrible UX. Can someone enlighten me? Or is the answer implicit in the above comment: reports are write-only, and if you need to make a change, you redo it from scratch?
  
  > **Comments:**
  > **username_1**: He he that isn't what I meant in my comment I've just found sometimes that's the cleanest. As to why there is no Save action under the Actions menu I'm not sure - it seems like there should be. I can see it on dmaster.demo.civicrm.org.  
  > **username_0**: Hey thanks for the pointer to dmaster... I was not aware of that, I now I can see that the Save option is there - for some of the people, some of the time ;=P I went looking for some sort of global setting saying "don't allow modification of reports" or such, and didn't find it. And I tried **Create New Report From Template**. I can set up the report options the same as my broken report, and _even sort on Contribution Status_, but... I can't save it.  
  > **username_1**: Check drupal permissions in the drupal admin pages - I can't remember off the top if there's one that controls this.  
  > **username_1**: It might be "CiviReport: save Report Criteria", at http://SITE/admin/people/permissions  
  > **username_0**: That was the setting... damn, so complex.. that setting must have been introduced at some point and left unset, and I am too lazy to go comb through all the myriad mind-numbing settings every time I update this thing to see what might be new. So I'm good now. Please post that as an answer, and I'll upvote it.  
  > **username_1**: Great! . . . . .. .  
  > **username_2**: I've just run into this as well - it seems to have arrived with 5.7.6 update which is surprising. Specifically, the contribution/detail report now gives the error noted above when sorting by contribution status. So aside from the specific advice of changing the sort order, this would seem to me a bug that should be reported as an issue.  
  
  ---
  
  