Internal Server Error sorting by enumerated fields

[lindhor]

Hi, Thanks for a great plugin!

An annoying problem is that if I create a query in the issue list view and try to use it in the pivot view I very often run into "Internal Server Error" with the below in my apache error_log (nothing in redmine log). This is very irritating since there is no way for users to see which queries are made for pivot and not. Also a selected query in the issue list is inherited when going to the pivot view, often causing these problems.

I have troubleshooted a bit and noticed that it seems to have something to do with the sort order, enumerated fields and the way the SQL is made.

• If my query sorts on Parent Task, Category and Priority, I do get the problem. Removing Priority from the sort order it works fine also in Pivot-view. Adding Priority again and the problem is back.
• Using Parent Task with any of the fields Priority and Status works fine as long as I select only one of them. Using all three causes the problem
• Using Parent Task with only Category does not work.
• It does not seem to matter if I have Parent Task first in the sort order or not. Using only Status and Priority also causes the problem.
• Having Priority first and Assignee as second sort order causes the problem. Using Priority and issue id (#) works. Using all three Priority, Issue id(#) and Assignee also works. ...

The error log says stuff like below, with different tables depending on the sort order used. App 21734 stderr: Query::StatementInvalid (PG::DuplicateAlias: ERROR: table name "issue_categories" specified more than once

App 21734 stderr: Started GET "/redmine/projects/dou/issues?query_id=7" for 10.0.2.2 at 2018-02-23 12:45:39 +0100
App 21734 stderr: Processing by IssuesController#index as HTML
App 21734 stderr:   Parameters: {"query_id"=>"7", "project_id"=>"dou"}
App 21734 stderr:   Current user: RLI (id=11)
App 21734 stderr:   Rendered queries/_filters.html.erb (19.0ms)
App 21734 stderr:   Rendered queries/_columns.html.erb (3.2ms)
App 21734 stderr:   Rendered queries/_query_form.html.erb (26.0ms)
App 21734 stderr:   Rendered issues/_list.html.erb (24.6ms)
App 21734 stderr:   Rendered plugins/redmine_default_custom_query/app/views/issues/_sidebar_issues_bottom.html.erb (1.8ms)
App 21734 stderr:   Rendered plugins/redmine_issue_templates/app/views/issue_templates/_issue_template_link.html.erb (1.2ms)
App 21734 stderr:   Rendered issues/_sidebar.html.erb (11.0ms)
App 21734 stderr:   Rendered issues/index.html.erb within layouts/base (74.5ms)
App 21734 stderr:   Rendered plugins/quick_view/app/views/hooks/_quick_view_base_head.html.erb (0.2ms)
App 21734 stderr:   Rendered plugins/redmine_code_review/app/views/code_review/_html_header.html.erb (0.4ms)
App 21734 stderr:   Rendered plugins/scrum/app/views/scrum_hooks/_head.html.erb (0.4ms)
App 21734 stderr:   Rendered plugins/clipboard_image_paste/app/views/clipboard_image_paste/_headers.html.erb (0.5ms)
App 21734 stderr:   Rendered plugins/scrum/app/views/scrum_hooks/_scrum_tips.html.erb (6.4ms)
App 21734 stderr:   Rendered plugins/quick_view/app/views/hooks/_quick_view_base_bottom_tooltip.html.erb (0.6ms)
App 21734 stderr:   Rendered plugins/quick_view/app/views/hooks/_quick_view_base_bottom.html.erb (4.0ms)
App 21734 stderr:   Rendered plugins/quick_view/app/views/hooks/_quick_view_base_bottom_worktime_ext.html.erb (1.5ms)
App 21734 stderr:   Rendered plugins/redmine_code_review/app/views/code_review/_body_bottom.html.erb (1.0ms)
App 21734 stderr:   Rendered plugins/redmine_xlsx_format_issue_exporter/app/views/hooks/_insert_xlsx_link_for_dialog.erb (1.1ms)
App 21734 stderr:   Rendered plugins/redmine_xlsx_format_issue_exporter/app/views/hooks/_xlsx_export_dialog_on_issues_index.erb (6.0ms)
App 21734 stderr:   Rendered plugins/clipboard_image_paste/app/views/clipboard_image_paste/_add_form.html.erb (3.6ms)
App 21734 stderr: Completed 200 OK in 195ms (Views: 127.2ms | ActiveRecord: 27.4ms)
App 21734 stderr: Started GET "/redmine/issue_badge" for 10.0.2.2 at 2018-02-23 12:45:39 +0100
App 21734 stderr: Processing by IssueBadgeController#index as */*
App 21734 stderr:   Current user: RLI (id=11)
App 21734 stderr:   Rendered plugins/redmine_issue_badge/app/views/issue_badge/_issue_badge.html.erb (1.0ms)
App 21734 stderr: Completed 200 OK in 18ms (Views: 1.9ms | ActiveRecord: 4.8ms)
App 21734 stderr: Started GET "/redmine/issue_badge/issues_count" for 10.0.2.2 at 2018-02-23 12:45:39 +0100
App 21734 stderr: Processing by IssueBadgeController#issues_count as JSON
App 21734 stderr:   Current user: RLI (id=11)
App 21734 stderr:   Rendered text template (0.2ms)
App 21734 stderr: Completed 200 OK in 28ms (Views: 0.9ms | ActiveRecord: 6.8ms)
App 21734 stderr: Started GET "/redmine/projects/dou/pivottables" for 10.0.2.2 at 2018-02-23 12:45:43 +0100
App 21734 stderr: Processing by PivottablesController#index as HTML
App 21734 stderr:   Parameters: {"project_id"=>"dou"}
App 21734 stderr:   Current user: RLI (id=11)
App 21734 stderr: Completed 500 Internal Server Error in 49ms (ActiveRecord: 19.0ms)
App 21734 stderr: 
App 21734 stderr: Query::StatementInvalid (PG::DuplicateAlias: ERROR:  table name "issue_categories" specified more than once
App 21734 stderr: : SELECT  "issues"."id" AS t0_r0, "issues"."tracker_id" AS t0_r1, "issues"."project_id" AS t0_r2, "issues"."subject" AS t0_r3, "issues"."description" AS t0_r4, "issues"."due_date" AS t0_r5, "issues"."category_id" AS t0_r6, "issues"."status_id" AS t0_r7, "issues"."assigned_to_id" AS t0_r8, "issues"."priority_id" AS t0_r9, "issues"."fixed_version_id" AS t0_r10, "issues"."author_id" AS t0_r11, "issues"."lock_version" AS t0_r12, "issues"."created_on" AS t0_r13, "issues"."updated_on" AS t0_r14, "issues"."start_date" AS t0_r15, "issues"."done_ratio" AS t0_r16, "issues"."estimated_hours" AS t0_r17, "issues"."parent_id" AS t0_r18, "issues"."root_id" AS t0_r19, "issues"."lft" AS t0_r20, "issues"."rgt" AS t0_r21, "issues"."is_private" AS t0_r22, "issues"."closed_on" AS t0_r23, "issues"."sprint_id" AS t0_r24, "issues"."position" AS t0_r25, "issue_statuses"."id" AS t1_r0, "issue_statuses"."name" AS t1_r1, "issue_statuses"."is_closed" AS t1_r2, "issue_statuses"."position" AS t1_r3, "issue_statuses"."default_done_ratio" AS t1_r4, "projects"."id" AS t2_r0, "projects"."name" AS t2_r1, "projects"."description" AS t2_r2, "projects"."homepage" AS t2_r3, "projects"."is_public" AS t2_r4, "projects"."parent_id" AS t2_r5, "projects"."created_on" AS t2_r6, "projects"."updated_on" AS t2_r7, "projects"."identifier" AS t2_r8, "projects"."status" AS t2_r9, "projects"."lft" AS t2_r10, "projects"."rgt" AS t2_r11, "projects"."inherit_members" AS t2_r12, "projects"."default_version_id" AS t2_r13, "projects"."default_assigned_to_id" AS t2_r14, "projects"."product_backlog_id" AS t2_r15, "users"."id" AS t3_r0, "users"."login" AS t3_r1, "users"."hashed_password" AS t3_r2, "users"."firstname" AS t3_r3, "users"."lastname" AS t3_r4, "users"."admin" AS t3_r5, "users"."status" AS t3_r6, "users"."last_login_on" AS t3_r7, "users"."language" AS t3_r8, "users"."auth_source_id" AS t3_r9, "users"."created_on" AS t3_r10, "users"."updated_on" AS t3_r11, "users"."type" AS t3_r12, "users"."identity_url" AS t3_r13, "users"."mail_notification" AS t3_r14, "users"."salt" AS t3_r15, "users"."must_change_passwd" AS t3_r16, "users"."passwd_changed_on" AS t3_r17, "trackers"."id" AS t4_r0, "trackers"."name" AS t4_r1, "trackers"."is_in_chlog" AS t4_r2, "trackers"."position" AS t4_r3, "trackers"."is_in_roadmap" AS t4_r4, "trackers"."fields_bits" AS t4_r5, "trackers"."default_status_id" AS t4_r6, "enumerations"."id" AS t5_r0, "enumerations"."name" AS t5_r1, "enumerations"."position" AS t5_r2, "enumerations"."is_default" AS t5_r3, "enumerations"."type" AS t5_r4, "enumerations"."active" AS t5_r5, "enumerations"."project_id" AS t5_r6, "enumerations"."parent_id" AS t5_r7, "enumerations"."position_name" AS t5_r8, "issue_categories"."id" AS t6_r0, "issue_categories"."project_id" AS t6_r1, "issue_categories"."name" AS t6_r2, "issue_categories"."assigned_to_id" AS t6_r3, "versions"."id" AS t7_r0, "versions"."project_id" AS t7_r1, "versions"."name" AS t7_r2, "versions"."description" AS t7_r3, "versions"."effective_date" AS t7_r4, "versions"."created_on" AS t7_r5, "versions"."updated_on" AS t7_r6, "versions"."wiki_page_title" AS t7_r7, "versions"."status" AS t7_r8, "versions"."sharing" AS t7_r9, "sprints"."id" AS t8_r0, "sprints"."name" AS t8_r1, "sprints"."description" AS t8_r2, "sprints"."sprint_start_date" AS t8_r3, "sprints"."sprint_end_date" AS t8_r4, "sprints"."user_id" AS t8_r5, "sprints"."project_id" AS t8_r6, "sprints"."created_on" AS t8_r7, "sprints"."updated_on" AS t8_r8, "sprints"."is_product_backlog" AS t8_r9, "sprints"."status" AS t8_r10, "sprints"."shared" AS t8_r11 FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" LEFT OUTER JOIN "users" ON "users"."id" = "issues"."assigned_to_id" LEFT OUTER JOIN "trackers" ON "trackers"."id" = "issues"."tracker_id" LEFT OUTER JOIN "enumerations" ON "enumerations"."id" = "issues"."priority_id" AND "enumerations"."type" IN ('IssuePriority') LEFT OUTER JOIN "issue_categories" ON "issue_categories"."id" = "issues"."category_id" LEFT OUTER JOIN "versions" ON "versions"."id" = "issues"."fixed_version_id" LEFT OUTER JOIN "sprints" ON "sprints"."id" = "issues"."sprint_id" LEFT OUTER JOIN issue_categories ON issue_categories.id = issues.category_id LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id WHERE (projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((issues.tracker_id IN ('5')) AND projects.lft >= 1 AND projects.rgt <= 24)  ORDER BY projects.name ASC, issues.root_id ASC, issues.lft ASC, issue_categories.name ASC, enumerations.position ASC LIMIT 1000 OFFSET 0):
App 21734 stderr:   app/models/issue_query.rb:312:in `rescue in issues'
App 21734 stderr:   app/models/issue_query.rb:275:in `issues'
App 21734 stderr:   plugins/scrum/lib/scrum/issue_query_patch.rb:41:in `issues_with_scrum'
App 21734 stderr:   plugins/redmine_pivot_table/app/controllers/pivottables_controller.rb:65:in `index'
App 21734 stderr:   lib/redmine/sudo_mode.rb:63:in `sudo_mode'
App 21734 stderr: 
App 21734 stderr: 

[deecay]

Thank you for the report.

This should be fixed with #58 and #59.