Redmine

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.