Defect #32737
Duplicate sort keys for issue query cause SQL error with SQL Server
| Status: | Closed | Start date: | ||
|---|---|---|---|---|
| Priority: | Normal | Due 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)
32737_exclude_same_column_from_orderby.patch 
(2.41 KB)
32737-remove-duplicate-keys.patch
(1.51 KB)
gantt.rb.patch
(529 Bytes)
Related issues
Associated revisions
Remove duplicate keys from sort criteria (#32737).
Patch by Go MAEDA.
Fix that viewing gantt causes SQL error with SQL Server (#32737).
Patch by Pavel Rosický.
History
#1
Updated by Go MAEDA about 1 year 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 about 1 year 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 about 1 year ago
- Duplicated by Defect #32808: Gantt Query Error 500 added
#4
Updated by Yuichi HARADA about 1 year ago
- File 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.
#5
Updated by Dean Yeh about 1 year 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. :)
#7
Updated by Go MAEDA 12 months ago
- File 32737-remove-duplicate-keys.patch added
#9
Updated by Go MAEDA 12 months ago
- Related to Defect #30009: Empty sort criteria for issue query gives error added
#11
Updated by Yuichi HARADA 12 months 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 11 months 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 10 months ago
- Duplicated by Defect #33224: SQL Server: A column has been specified more than once added
#14
Updated by Pavel Rosický 10 months ago
- File gantt.rb.patch added
for reference #29581 caused the issue
patches 32737-remove-duplicate-keys.patch and gantt.rb.patch should be applied
#15
Updated by Marius BALTEANU 10 months ago
- Target version set to 4.1.1
#16
Updated by Marius BALTEANU 10 months ago
- Description updated (diff)
#17
Updated by Yuichi HARADA 10 months 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 10 months ago
- Related to Defect #29581: Issues in paginated views may be lost because sorting criteria are not unique added