Defect #32737

Duplicate sort keys for issue query cause SQL error with SQL Server

Added by Sascha Merz almost 2 years ago. Updated over 1 year ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Issues
Target version:4.1.1
Resolution:Fixed Affected version:4.1.0

Description

Installed Redmine 4.1.0.stable from scratch following RedmineInstall guide.
Created a new project / tracker / issues. Have done the configuration.

Now having a Redmine issue with Gantt. Directly after installation, the Gantt Tab is showing the following.
"500 - An error occurred while executing the query and has been logged. Please report this error to your Redmine administrator."
Looking for a workaround to get the Gantt Tab working.

Redmine:

4.1.0.stable
Ruby:
2.5.7-p206 (2019-10-01) [x64-mingw32]
Rails:
5.2.4.1
Database:
MS SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition
Webserver:
MS IIS Reverse Proxy -> Puma
OS:
VM Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Redmine plugins:
  redhopper                      2.0.0

production.log:

Started GET "/projects/projektmanagement-it/issues/gantt" for 127.0.0.1 at 2019-12-30 23:07:36 +0100
Processing by GanttsController#show as HTML
  Parameters: {"project_id"=>"projektmanagement-it"}
  Current user: amerzsa (id=5)
  Rendering gantts/show.html.erb within layouts/base
  Rendered queries/_filters.html.erb (10.7ms)
  Rendered queries/_columns.html.erb (3.4ms)
  Rendered gantts/show.html.erb within layouts/base (65.9ms)
Query::StatementInvalid: TinyTds::Error: A column has been specified more than once in the order by list. Columns in the order by list must be unique.: EXEC sp_executesql N'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, [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, [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, [trackers].[description] AS t4_r7, [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 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 (N''IssuePriority'') LEFT OUTER JOIN [issue_categories] ON [issue_categories].[id] = [issues].[category_id] LEFT OUTER JOIN [versions] ON [versions].[id] = [issues].[fixed_version_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.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 1)  ORDER BY projects.lft ASC, issues.id ASC, issues.id DESC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 500
  Rendering common/error.html.erb within layouts/base
  Rendered common/error.html.erb within layouts/base (0.1ms)
Completed 500 Internal Server Error in 152ms (Views: 18.0ms | ActiveRecord: 20.2ms)

SQL Statement with issue in ORDER BY:

EXEC sp_executesql N'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,
        [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,
        [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,
        [trackers].[description] AS t4_r7,
        [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
  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 (N''IssuePriority'') LEFT OUTER JOIN [issue_categories] ON [issue_categories].[id] = [issues].[category_id] LEFT OUTER JOIN [versions] ON [versions].[id] = [issues].[fixed_version_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.status_id IN (SELECT id
  FROM issue_statuses
WHERE is_closed=0))
  AND projects.id = 1)
ORDER BY projects.lft ASC,
        issues.id ASC,
        issues.id DESC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY',
        N'@0 int',
        @0 = 500

production.log (27.2 KB) Sascha Merz, 2020-01-02 10:46

32737_exclude_same_column_from_orderby.patch Magnifier (2.41 KB) Yuichi HARADA, 2020-01-22 09:40

32737-remove-duplicate-keys.patch Magnifier (1.51 KB) Go MAEDA, 2020-01-27 12:58

gantt.rb.patch Magnifier (529 Bytes) Pavel Rosický, 2020-03-31 18:25

Related issues

Related to Redmine - Defect #30009: Empty sort criteria for issue query gives error Closed
Related to Redmine - Defect #29581: Issues in paginated views may be lost because sorting cri... Closed
Duplicated by Redmine - Defect #32808: Gantt Query Error 500 Closed
Duplicated by Redmine - Defect #33224: SQL Server: A column has been specified more than once Closed

Associated revisions

Revision 19636
Added by Go MAEDA over 1 year ago

Remove duplicate keys from sort criteria (#32737).

Patch by Go MAEDA.

Revision 19637
Added by Go MAEDA over 1 year ago

Fix that viewing gantt causes SQL error with SQL Server (#32737).

Patch by Pavel Rosický.

Revision 19638
Added by Go MAEDA over 1 year ago

Merged r19636 from trunk to 4.1-stable (#32737).

Revision 19639
Added by Go MAEDA over 1 year ago

Merged r19637 from trunk to 4.1-stable (#32737).

History

#1 Updated by Go MAEDA almost 2 years ago

I have confirmed that the query edit form can set the same field more than once as sort criteria and the same column appears multiple times in the ORDER BY clause as a result.

I think the following checks (at least the first one) should be added.

  • Don't add the same column to ORDER BY clause more than once
  • Reject specifying the same field more than twice for sort criteria in the query edit form

#2 Updated by Tobias Pitzer almost 2 years ago

Go MAEDA wrote:

I have confirmed that the query edit form can set the same field more than once as sort criteria and the same column appears multiple times in the ORDER BY clause as a result.

I think the following checks (at least the first one) should be added.

  • Don't add the same column to ORDER BY clause more than once
  • Reject specifying the same field more than twice for sort criteria in the query edit form

Is there any workaround on this?

#3 Updated by Go MAEDA almost 2 years ago

#4 Updated by Yuichi HARADA almost 2 years ago

Go MAEDA wrote:

I think the following checks (at least the first one) should be added.

  • Don't add the same column to ORDER BY clause more than once
  • Reject specifying the same field more than twice for sort criteria in the query edit form

Exclude the same column from the ORDER BY clause.
I attached a patch.

#5 Updated by Dean Yeh almost 2 years ago

Yuichi HARADA wrote:

Go MAEDA wrote:

I think the following checks (at least the first one) should be added.

  • Don't add the same column to ORDER BY clause more than once
  • Reject specifying the same field more than twice for sort criteria in the query edit form

Exclude the same column from the ORDER BY clause.
I attached a patch.

Thank you, Yuichi HARADA.
It works on my Redmine. :)

#6 Updated by Go MAEDA over 1 year ago

  • File 32737-remove-duplicate-keys.patch added

Maybe also this patch will work.

#8 Updated by Go MAEDA over 1 year ago

  • File deleted (32737-remove-duplicate-keys.patch)

#9 Updated by Go MAEDA over 1 year ago

  • Related to Defect #30009: Empty sort criteria for issue query gives error added

#10 Updated by Go MAEDA over 1 year ago

  • Subject changed from Gantt Tab - 500 - An error occurred while executing the query - A column has been specified more than once in the order by list to Duplicate sort keys for issue query cause SQL error with SQL Server
  • Category changed from Gantt to Issues

#11 Updated by Yuichi HARADA over 1 year ago

Go MAEDA wrote:

Maybe also this patch will work.

Execute tests on SQL Server, 12 test failures (500: Internal Server Error. I think duplicate ORDER BY clauses are involved) and 16 execution errors (duplicate ORDER BY clause) occurred.

  • test/unit/lib/redmine/helpers/gantt_test.rb
  • test/functional/gantts_controller_test.rb

#12 Updated by Sascha Merz over 1 year ago

Will this issue be solved in 4.1.1?
We as a team are using Redmine as our project management solution on a daily basis. To have Gantt is a key feature for us.
Thank you. Sascha

#13 Updated by Marius BALTEANU over 1 year ago

  • Duplicated by Defect #33224: SQL Server: A column has been specified more than once added

#14 Updated by Pavel Rosický over 1 year ago

for reference #29581 caused the issue

patches 32737-remove-duplicate-keys.patch and gantt.rb.patch should be applied

#15 Updated by Marius BALTEANU over 1 year ago

  • Target version set to 4.1.1

We should fix this in 4.1.1 together with #30285.

#16 Updated by Marius BALTEANU over 1 year ago

  • Description updated (diff)

#17 Updated by Yuichi HARADA over 1 year ago

Pavel Rosický wrote:

for reference #29581 caused the issue

patches 32737-remove-duplicate-keys.patch and gantt.rb.patch should be applied

That's great. All tests completed successfully by combining 32737-remove-duplicate-keys.patch and gantt.rb.patch.
However, since the relative path of gantt.rb was unknown, I was rebuilt gantt.rb.patch.

diff --git a/lib/redmine/helpers/gantt.rb b/lib/redmine/helpers/gantt.rb
index 16f180090..206cdea24 100644
--- a/lib/redmine/helpers/gantt.rb
+++ b/lib/redmine/helpers/gantt.rb
@@ -151,7 +151,7 @@ module Redmine
       def issues
         @issues ||= @query.issues(
           :include => [:assigned_to, :tracker, :priority, :category, :fixed_version],
-          :order => "#{Project.table_name}.lft ASC, #{Issue.table_name}.id ASC",
+          :order => ["#{Project.table_name}.lft ASC", "#{Issue.table_name}.id ASC"],
           :limit => @max_rows
         )
       end

#18 Updated by Go MAEDA over 1 year ago

  • Related to Defect #29581: Issues in paginated views may be lost because sorting criteria are not unique added

#19 Updated by Go MAEDA over 1 year ago

  • Status changed from New to Closed
  • Assignee set to Go MAEDA
  • Resolution set to Fixed

Committed the patch. Thank you to everyone involved in testing and fixing the issue.

Also available in: Atom PDF