Redmine

The SQL for the roadmap view includes a query which ends with "ORDER BY trackers.position ASC, position ASC", ie: sorting by the same field twice. MySQL silently ignores the duplicate but MS SQL Server returns an error which causes the Roadmap page to fail with a 500 Internal Server Error.

I realise MS SQL Server is not a supported database, but nonetheless this is an error in the SQL syntax generated by Redmine. The fix is to change line 34 of app/controllers/versions_controller.rb from:

@trackers = @project.trackers.find(:all, :order => 'position')

to:

@trackers = @project.trackers.find(:all)

This changes the generated SQL from:

EXEC sp_executesql N'SELECT [trackers].* FROM [trackers] INNER JOIN [projects_trackers] ON [trackers].[id] = [projects_trackers].[tracker_id] WHERE [projects_trackers].[project_id] = 27 ORDER BY trackers.position ASC, position ASC'

to:

EXEC sp_executesql N'SELECT [trackers].* FROM [trackers] INNER JOIN [projects_trackers] ON [trackers].[id] = [projects_trackers].[tracker_id] WHERE [projects_trackers].[project_id] = 27 ORDER BY trackers.position ASC'

In other words it removes the error, but the view is still sorted by position as required.

Environment:
Redmine version 2.0.0.stable
Ruby version 1.8.7 (i386-mingw32)
Rails version 3.2.3
Environment production
Database adapter SQLServer
Redmine plugins:
no plugin installed

Database is SQL Server 2005 SP3. The OS is Windows Server 2003. Redmine is running on Thin. The request details when it fails is as follows. The error text "A column has been specified more than once in the order by list. Columns in the order by list must be unique." is a SQL Server error.

Started GET "/projects/websiterefresh/roadmap" for 127.0.0.1 at Wed May 23 16:16
:32 +0100 2012
Creating scope :open. Overwriting existing method Version.open.
Creating scope :active. Overwriting existing method User.active.
DEPRECATION WARNING: The InstanceMethods module inside ActiveSupport::Concern wi
ll be no longer included automatically. Please define instance methods directly
in CollectiveIdea::Acts::NestedSet::Model instead. (called from include at C:/ap
plications/redmine2/redmine-2.0.0/lib/plugins/awesome_nested_set/lib/awesome_nes
ted_set/awesome_nested_set.rb:58)
DEPRECATION WARNING: The InstanceMethods module inside ActiveSupport::Concern wi
ll be no longer included automatically. Please define instance methods directly
in CollectiveIdea::Acts::NestedSet::Model instead. (called from include at C:/ap
plications/redmine2/redmine-2.0.0/lib/plugins/awesome_nested_set/lib/awesome_nes
ted_set/awesome_nested_set.rb:58)
Creating scope :open. Overwriting existing method Issue.open.
Processing by VersionsController#index as HTML
Parameters: {"project_id"=>"websiterefresh"}
←[1m←[36m (0.0ms)←[0m ←[1mEXEC sp_executesql N'SELECT MAX([settings].[updated
_on]) AS max_id FROM [settings]'←[0m
←[1m←[35mUser Load (0.0ms)←[0m EXEC sp_executesql N'SELECT TOP (1) [users].*
FROM [users] WHERE [users].[type] IN (N''User'', N''AnonymousUser'') AND [users]
.[id] = Rangicaadmice Rangicaadmice AND (users.status = 1)', N'@0 int', Rangicaadmice Rangicaadmice = 19 "id", 19
←[1m←[36mProject Load (0.0ms)←[0m ←[1mEXEC sp_executesql N'SELECT TOP (1) [pr
ojects].* FROM [projects] WHERE [projects].[identifier] = N''websiterefresh'''←[
0m
←[1m←[35mEnabledModule Load (0.0ms)←[0m EXEC sp_executesql N'SELECT name FROM
[enabled_modules] WHERE [enabled_modules].[project_id] = 27'
←[1m←[36mTracker Load (0.0ms)←[0m ←[1mEXEC sp_executesql N'SELECT [trackers].

• FROM [trackers] INNER JOIN [projects_trackers] ON [trackers].[id] = [projects_
  trackers].[tracker_id] WHERE [projects_trackers].[project_id] = 27 ORDER BY trac
  kers.position ASC, position ASC'←[0m
  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 [trac
  kers].* FROM [trackers] INNER JOIN [projects_trackers] ON [trackers].[id] = [pro
  jects_trackers].[tracker_id] WHERE [projects_trackers].[project_id] = 27 ORDER B
  Y trackers.position ASC, position ASC'
  Completed 500 Internal Server Error in 140ms

ActiveRecord::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.: E
XEC sp_executesql N'SELECT [trackers].* FROM [trackers] INNER JOIN [projects_tra
ckers] ON [trackers].[id] = [projects_trackers].[tracker_id] WHERE [projects_tra
ckers].[project_id] = 27 ORDER BY trackers.position ASC, position ASC'):
app/controllers/versions_controller.rb:34:in `index'
app/controllers/versions_controller.rb:32:in `index'