Defect #19544

Malformed SQL query with SQLServer when grouping issues

Added by Olivier Houdas about 3 years ago. Updated about 2 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Issues
Target version:3.0.2
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?).

issue_query.rb Magnifier (23.5 KB) Silvio Fernandes, 2016-04-26 20:38

Associated revisions

Revision 14165
Added by Jean-Philippe Lang about 3 years ago

SQL error with SQLServer when grouping+sorting issues (#19544).

Revision 14166
Added by Jean-Philippe Lang about 3 years ago

Fixed test failures (#19544).

History

#1 Updated by Jean-Philippe Lang about 3 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.

#2 Updated by Olivier Houdas about 3 years ago

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

#3 Updated by Jean-Philippe Lang about 3 years ago

  • Status changed from Resolved to Closed

#4 Updated by Silvio Fernandes about 2 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

...

#5 Updated by Toshi MARUYAMA about 2 years ago

Silvio Fernandes wrote:

Hi guys,
...

Please create new issue.

Also available in: Atom PDF