Project

General

Profile

Actions
Copy link

Defect #19544

closed

Malformed SQL query with SQLServer when grouping issues

Added by Olivier Houdas over 9 years ago. Updated over 8 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Jean-Philippe Lang
Category:
Issues
Target version:
3.0.2
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:
3.0.1

Description

Environment: CentOS, Redmine 3.0.1, using Rails 4.2 and MS SQL database 2012 (through activerecord-sqlserver-adapter-4.2.4)

On a project, filter issues and group them by category.
When trying that on our test deployment of Redmine 3.0.1, I got an Error 500, with the following message:
TinyTds::Error: A column has been specified more than once in the order by list. Columns in the order by list must be unique. SELECT [issues].[id] AS t0_r0 (...) ORDER BY issue_categories.name ASC, (CASE WHEN versions.effective_date IS NULL THEN 1 ELSE 0 END), versions.effective_date, versions.name, versions.id, enumerations.position DESC, issue_categories.name OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

It seems that group_by_sort_order from app/models/query.rb adds the first issue_categories.name ASC, but issue_categories.name is also contained in the order_by options when merged in app/models/issue_query.rb:

def issues(options={})
    order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)

which results in duplicated columns. It did not cause any error when using Rails 3.2, and Redmine 2.6.1.

Can you confirm you reproduce?
I fixed it by adding 

dup_index = order_option.rindex{|s| s[/#{group_by_sort_order.split(" ")[0]}/]}
    if dup_index > 0
      order_option.delete_at(dup_index)
    end

in the issues() function after merging order options, but I'm not sure this is the right way to do it (maybe it should not be present in options[:order] in the first place?).

Files

issue_query.rb (23.5 KB) issue_query.rb Silvio Fernandes, 2016-04-26 20:38
Actions
Copy link
 #1

Updated by Jean-Philippe Lang over 9 years ago

  • Subject changed from Malformed SQL query with MS SQL and Rails 4.2 when grouping issues to Malformed SQL query with SQLServer when grouping issues
  • Status changed from New to Resolved
  • Assignee set to Jean-Philippe Lang
  • Target version set to 3.0.2
  • Resolution set to Fixed

That should be fixed by r14165.

Actions
Copy link
 #2

Updated by Olivier Houdas over 9 years ago

I removed my changes and applied r14165, and the filtering now works fine.

Actions
Copy link
 #3

Updated by Jean-Philippe Lang over 9 years ago

  • Status changed from Resolved to Closed
Actions
Copy link
 #4

Updated by Silvio Fernandes over 8 years ago

Hi guys,

I had the same problem of this issue, but my was with the fixed version column, in version 3.2.1 with SQL Server 2012, and this review 14207 did not solve my problem.

making a debug in the code, I found that the part where it should make a flatten between two Array, the variable group_by_sort_order comes as String, so I did a treatment to make this String into Array, and solved my problem.

code modified in /app/models/issue_query.rb

  # Returns the issues
  # Valid options are :order, :offset, :limit, :include, :conditions
  def issues(options={})
    if group_by_sort_order.is_a?(String)
      # convert String in Array
      order_option = [group_by_sort_order.split(','), options[:order]].flatten.reject(&:blank?)
    else
      order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
    end

...

Actions
Copy link
 #5

Updated by Toshi MARUYAMA over 8 years ago

Silvio Fernandes wrote:

Hi guys,
...

Please create new issue.

Actions
Copy link

Also available in: Atom PDF