Defect #11160

SQL Error on time report if a custom field has multiple values for an entry

Added by Gibbet G over 5 years ago. Updated over 5 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Time tracking
Target version:2.0.3
Resolution:Fixed Affected version:1.4.1

Description

I get a 500 error when I am using a custom field that is not enabled for all projects... This is the error from production log:

ActiveRecord::StatementInvalid (Mysql::Error: Subquery returns more than 1 row: SELECT sum(`time_entries`.hours) AS sum_hours, time_entries.project_id AS time_entries_project_id, (SELECT c.value FROM custom_values c WHERE c.custom_field_id = 2 AND c.customized_type = 'Issue' AND c.customized_id = issues.id) AS select_c_value_from_custom_values_c_where_c_custom_field_id_2_and_c_customized_type_issue_and_c_customized_id_issues_id, 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 `issues` ON `issues`.id = `time_entries`.issue_id WHERE (((projects.id = 16 OR (projects.lft > 13 AND projects.rgt < 18))) AND (((projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='time_tracking')) AND (projects.is_public = 1 OR projects.id IN (1,15,16,17) OR projects.id IN (1))))) GROUP BY time_entries.project_id,(SELECT c.value FROM custom_values c WHERE c.custom_field_id = 2 AND c.customized_type = 'Issue' AND c.customized_id = issues.id),tyear,tmonth,tweek,spent_on ):
lib/redmine/helpers/time_report.rb:55:in `run'
lib/redmine/helpers/time_report.rb:36:in `initialize'
app/controllers/timelog_controller.rb:103:in `new'
app/controllers/timelog_controller.rb:103:in `report'
config/initializers/mongrel_cluster_with_rails_211_fix.rb:62:in `dispatch_cgi'

Rendering /opt/redmine-1.4.1-0/apps/redmine/public/500.html (500 Internal Server Error)ctiveRecord::StatementInvalid (Mysql::Error: Subquery returns more than 1 row: SELECT sum(`time_entries`.hours) AS sum_hours, time_entries.project_id AS time_entries_project_id, (SELECT c.value FROM custom_values c WHERE c.custom_field_id = 2 AND c.customized_type = 'Issue' AND c.customized_id = issues.id) AS select_c_value_from_custom_values_c_where_c_custom_field_id_2_and_c_customized_type_issue_and_c_customized_id_issues_id, 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 `issues` ON `issues`.id = `time_entries`.issue_id WHERE (((projects.id = 16 OR (projects.lft > 13 AND projects.rgt < 18))) AND (((projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='time_tracking')) AND (projects.is_public = 1 OR projects.id IN (1,15,16,17) OR projects.id IN (1))))) GROUP BY time_entries.project_id,(SELECT c.value FROM custom_values c WHERE c.custom_field_id = 2 AND c.customized_type = 'Issue' AND c.customized_id = issues.id),tyear,tmonth,tweek,spent_on ):
lib/redmine/helpers/time_report.rb:55:in `run'
lib/redmine/helpers/time_report.rb:36:in `initialize'
app/controllers/timelog_controller.rb:103:in `new'
app/controllers/timelog_controller.rb:103:in `report'
config/initializers/mongrel_cluster_with_rails_211_fix.rb:62:in `dispatch_cgi'

Rendering /opt/redmine-1.4.1-0/apps/redmine/public/500.html (500 Internal Server Error)

Associated revisions

Revision 9831
Added by Jean-Philippe Lang over 5 years ago

Fixed that time report raises a SQL error if there are multiple CustomValue for a time entry (#11160).

History

#1 Updated by Gibbet G over 5 years ago

I should also add that the field is a list that allows multiple select...

The field also has "required", "for all projects" and "searchable" unchecked... It has "Used as filter checked though...

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

  • Category set to Time tracking
  • Target version set to 2.0.3

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

  • Subject changed from Custom field time reporting to SQL Error on time report if a custom field has multiple values for an entry

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

  • Status changed from New to Resolved
  • Resolution set to Fixed

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

  • Status changed from Resolved to Closed

Merged in 1.4 and 2.0.

Also available in: Atom PDF