Defect #16519

Generating a spent time report on a list type custom field with multiple values causes an invalid SQL error

Added by Chris Cardwell almost 4 years ago. Updated over 3 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Custom fields
Target version:2.5.2
Resolution:Fixed Affected version:2.5.1

Description

When on the Spent Time report page (example: http://localhost/redmine/projects/<project name>/time_entries/report), trying to generate a report by adding a custom field of list type, that has the ability to select multiple values, will cause an internal error.

The error from the production log is:

ActiveRecord::StatementInvalid (Mysql2::Error: 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 'AS , tyear AS tyear, tmonth AS tmonth, tweek AS tweek, spent_on AS spent_on FROM' at line 1: SELECT SUM(`time_entries`.`hours`) AS sum_hours, AS , tyear AS tyear, tmonth AS tmonth, tweek AS tweek, spent_on AS spent_on FROM `time_entries` LEFT OUTER JOIN `projects` ON `projects`.`id` = `time_entries`.`project_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `time_entries`.`activity_id` AND `enumerations`.`type` IN ('TimeEntryActivity') LEFT OUTER JOIN `issues` ON `issues`.`id` = `time_entries`.`issue_id` LEFT OUTER JOIN custom_values cf_41 ON cf_41.customized_type = 'Issue' AND cf_41.customized_id = issues.id AND cf_41.custom_field_id = 41 AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 41)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = 1 AND ifa.id = 41) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 41)))) AND cf_41.value <> '' AND cf_41.id = (SELECT max(cf_41_2.id) FROM custom_values cf_41_2 WHERE cf_41_2.customized_type = cf_41.customized_type AND cf_41_2.customized_id = cf_41.customized_id AND cf_41_2.custom_field_id = cf_41.custom_field_id) WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='time_tracking')) AND ((time_entries.spent_on IS NOT NULL) AND projects.id = 4) GROUP BY tyear, tmonth, tweek, spent_on):
lib/redmine/helpers/time_report.rb:48:in `run'
lib/redmine/helpers/time_report.rb:35:in `initialize'
app/controllers/timelog_controller.rb:83:in `new'
app/controllers/timelog_controller.rb:83:in `report'

The error seems to come from the SELECT statement, where the second column to be selected is " AS ,". When looking at the query of a report that was generated correctly, that spot instead has something like "COALESCE(cf_41.value, '') AS coalesce_cf_41_value," where 41 is the id of the custom field in the custom_fields table.

We noticed that in the custom_field.rb file, located at C:\Bitnami\redmine-2.5.0-0\apps\redmine\htdocs\app\models\, there is this code:

def order_statement
return nil if multiple?
format.order_statement(self)
end

# Returns a GROUP BY clause that can used to group by custom value
# Returns nil if the custom field can not be used for grouping.
def group_statement
return nil if multiple?
format.group_statement(self)
end

Commenting out both instances of return nil if multiple? has resolved the internal error, however the generated report will show time spent in the bottom most selected value of the custom field list (if multiple values are selected).

A possible solution would be to not allow multiple value list custom fields to be added to reports at all. Another solution could be creating a row for each combination of selected values from the list (if that combination has time spent on it), however for large lists with many values, this could potentially become chaotic. Realistically, any solution that doesn't result in the internal error page would be preferred.


Related issues

Related to Redmine - Defect #16963: Multi select custom field is broken in the report New
Duplicated by Redmine - Defect #17091: Spent time report Closed

Associated revisions

Revision 13055
Added by Jean-Philippe Lang almost 4 years ago

Exclude custom fields with multiple values from time report criteria (#16519).

History

#1 Updated by Chris Cardwell almost 4 years ago

Sorry, this is for 2.5.0, not 2.5.1

#2 Updated by Jean-Philippe Lang almost 4 years ago

  • Status changed from New to Resolved
  • Assignee set to Jean-Philippe Lang
  • Target version set to 2.5.2
  • Resolution set to Fixed

Fixed in r13055, these custom fields are no longer available as time report criteria.

#3 Updated by Toshi MARUYAMA over 3 years ago

  • Related to Defect #16963: Multi select custom field is broken in the report added

#4 Updated by Jean-Philippe Lang over 3 years ago

  • Status changed from Resolved to Closed

Merged.

#5 Updated by Jean-Philippe Lang over 3 years ago

Also available in: Atom PDF