Defect #32737: Duplicate sort keys for issue query cause SQL error with SQL Server - Redmine

Actions

#1

Updated by Go MAEDA over 4 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

Actions

Copy link

#2

Updated by Tobias Pitzer over 4 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?

Actions

Copy link

#3

Updated by Go MAEDA over 4 years ago

Has duplicate Defect #32808: Gantt Query Error 500 added

Actions

Copy link

#4

Updated by Yuichi HARADA over 4 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.

Actions

Copy link

#5

Updated by Dean Yeh over 4 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. :)

Actions

Copy link

#6

Updated by Go MAEDA about 4 years ago

File 32737-remove-duplicate-keys.patch added

Maybe also this patch will work.

Actions

Copy link

#7

Updated by Go MAEDA about 4 years ago

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

Actions

Copy link

#8

Updated by Go MAEDA about 4 years ago

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

Actions

Copy link

#9

Updated by Go MAEDA about 4 years ago

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

Actions

Copy link

#10

Updated by Go MAEDA about 4 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

Actions

Copy link

#11

Updated by Yuichi HARADA about 4 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

Test results...Test results...

dev-trunk $ RAILS_ENV=test bundle exec rake test

(snip)

...E

Error:
Redmine::Helpers::GanttHelperTest#test_#subjects_version_should_be_rendered:
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 = 162)  ORDER BY projects.lft ASC, issues.id ASC, issues.id DESC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 500
    app/models/issue_query.rb:360:in `rescue in issues'
    app/models/issue_query.rb:318:in `issues'
    lib/redmine/helpers/gantt.rb:152:in `issues'
    lib/redmine/helpers/gantt.rb:176:in `projects'
    lib/redmine/helpers/gantt.rb:216:in `render'
    lib/redmine/helpers/gantt.rb:134:in `subjects'
    test/unit/lib/redmine/helpers/gantt_test.rb:141:in `block in <class:GanttHelperTest>'

bin/rails test test/unit/lib/redmine/helpers/gantt_test.rb:139

E

Error:
Redmine::Helpers::GanttHelperTest#test_#number_of_rows_should_not_exceed_max_rows_option:
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 = 169)  ORDER BY projects.lft ASC, issues.id ASC, issues.id DESC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 500
    app/models/issue_query.rb:360:in `rescue in issues'
    app/models/issue_query.rb:318:in `issues'
    lib/redmine/helpers/gantt.rb:152:in `issues'
    lib/redmine/helpers/gantt.rb:176:in `projects'
    lib/redmine/helpers/gantt.rb:216:in `render'
    test/unit/lib/redmine/helpers/gantt_test.rb:85:in `block in <class:GanttHelperTest>'

bin/rails test test/unit/lib/redmine/helpers/gantt_test.rb:79

(snip)

Failure:
GanttsControllerTest#test_gantt_should_work [/Users/harada/_work_/redmine/git/dev-trunk/test/functional/gantts_controller_test.rb:41]:
Expected response to be a <2XX: success>, but was a <500: Internal Server Error>

bin/rails test test/functional/gantts_controller_test.rb:33

F

Failure:
GanttsControllerTest#test_gantt_should_not_disclose_private_projects [/Users/harada/_work_/redmine/git/dev-trunk/test/functional/gantts_controller_test.rb:117]:
Expected response to be a <2XX: success>, but was a <500: Internal Server Error>

bin/rails test test/functional/gantts_controller_test.rb:115

(snip)

Finished in 924.631252s, 5.3632 runs/s, 24.4162 assertions/s.
4959 runs, 22576 assertions, 12 failures, 16 errors, 4 skips

You have skipped tests. Run with --verbose for details.
dev-trunk $

Actions

Copy link

#12

Updated by Sascha Merz about 4 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

Actions

Copy link

#13

Updated by Marius BĂLTEANU about 4 years ago

Has duplicate Defect #33224: SQL Server: A column has been specified more than once added

Actions

Copy link

#14

Updated by Pavel Rosický about 4 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

Actions

Copy link

#15

Updated by Marius BĂLTEANU about 4 years ago

Target version set to 4.1.1

We should fix this in 4.1.1 together with #30285.

Actions

Copy link

#16

Updated by Marius BĂLTEANU about 4 years ago

Description updated (diff)

Actions

Copy link

#17

Updated by Yuichi HARADA about 4 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

Actions

Copy link

#18

Updated by Go MAEDA about 4 years ago

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

Actions

Copy link

#19

Updated by Go MAEDA about 4 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.

production.log (27.2 KB) production.log		Sascha Merz, 2020-01-02 10:46
32737_exclude_same_column_from_orderby.patch (2.41 KB) 32737_exclude_same_column_from_orderby.patch		Yuichi HARADA, 2020-01-22 09:40
32737-remove-duplicate-keys.patch (1.51 KB) 32737-remove-duplicate-keys.patch		Go MAEDA, 2020-01-27 12:58
gantt.rb.patch (529 Bytes) gantt.rb.patch		Pavel Rosický, 2020-03-31 18:25

Project

General

Profile

Redmine

Custom queries

Defect #32737

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

Updated by Go MAEDA over 4 years ago

Updated by Tobias Pitzer over 4 years ago

Updated by Go MAEDA over 4 years ago

Updated by Yuichi HARADA over 4 years ago

Updated by Dean Yeh over 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Yuichi HARADA about 4 years ago

Updated by Sascha Merz about 4 years ago

Updated by Marius BĂLTEANU about 4 years ago

Updated by Pavel Rosický about 4 years ago

Updated by Marius BĂLTEANU about 4 years ago

Updated by Marius BĂLTEANU about 4 years ago

Updated by Yuichi HARADA about 4 years ago

Updated by Go MAEDA about 4 years ago

Updated by Go MAEDA about 4 years ago