Actions
Defect #32737
closed
Duplicate sort keys for issue query cause SQL error with SQL Server
Start date:
Due date:
% Done:
0%
Estimated time:
Resolution:
Fixed
Affected version:
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
Files
Related issues
Updated by Go MAEDA over 5 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
Updated by Tobias Pitzer over 5 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?
Updated by Go MAEDA over 5 years ago
- Has duplicate Defect #32808: Gantt Query Error 500 added
Updated by Yuichi HARADA over 5 years ago
- File 32737_exclude_same_column_from_orderby.patch 32737_exclude_same_column_from_orderby.patch added
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.
Updated by Dean Yeh over 5 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. :)
Updated by Go MAEDA over 5 years ago
- File 32737-remove-duplicate-keys.patch added
Maybe also this patch will work.
Updated by Go MAEDA over 5 years ago
Updated by Go MAEDA over 5 years ago
- File deleted (
32737-remove-duplicate-keys.patch)
Updated by Go MAEDA over 5 years ago
- Related to Defect #30009: Empty sort criteria for issue query gives error added
Updated by Go MAEDA over 5 years 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
Updated by Yuichi HARADA over 5 years 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
Updated by Sascha Merz over 5 years 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
Updated by Marius BĂLTEANU over 5 years ago
- Has duplicate Defect #33224: SQL Server: A column has been specified more than once added
Updated by Pavel Rosický over 5 years ago
- File gantt.rb.patch gantt.rb.patch added
for reference #29581 caused the issue
patches 32737-remove-duplicate-keys.patch and gantt.rb.patch should be applied
Updated by Marius BĂLTEANU over 5 years ago
- Target version set to 4.1.1
Updated by Yuichi HARADA over 5 years 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
Updated by Go MAEDA over 5 years ago
- Related to Defect #29581: Issues in paginated views may be lost because sorting criteria are not unique added
Updated by Go MAEDA over 5 years 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.
Actions