Vote #76255
完了Malformed SQL query with SQLServer when grouping issues
0%
説明
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?).
journals
That should be fixed by r14165.
--------------------------------------------------------------------------------
I removed my changes and applied r14165, and the filtering now works fine.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
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
<pre><code class="ruby">
# 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
...
</code></pre>
--------------------------------------------------------------------------------
Silvio Fernandes wrote:
> Hi guys,
> ...
Please create new issue.
--------------------------------------------------------------------------------