Defect #11002

Duplicate field in query breaks Roadmap view

Added by Rick Mason over 5 years ago. Updated over 5 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Roadmap
Target version:-
Resolution:Invalid Affected version:2.0.0

Description

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] = @0 AND (users.status = 1)', N'@0 int', @0 = 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'


Related issues

Related to Redmine - Feature #12713: Microsoft SQLServer support Closed

History

#1 Updated by Mischa The Evil over 5 years ago

Looks to me that this describes the 'same' sorting issue as mentioned in #10930...

#2 Updated by Etienne Massip over 5 years ago

  • Status changed from New to Closed
  • Resolution set to Invalid

Yep, dupe of #10930.

And indeed, MSSQL is not supported.

#3 Updated by Rick Mason over 5 years ago

For anyone hitting this problem and reading this, it looks like a better fix is to change line 34 to:

@trackers = @project.trackers.find(:all, :order => "#{Tracker.table_name}.position")

Also available in: Atom PDF