Project

General

Profile

Actions

Defect #6413

closed

Error in SQL

Added by Juan G over 13 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Normal
Category:
Code cleanup/refactoring
Target version:
Start date:
2010-09-16
Due date:
% Done:

100%

Estimated time:
Resolution:
Fixed
Affected version:

Description

Hi all!

I am using redmine and having an SQL error when trying to get the issues resume inside a project.

Environment:

Ruby 1.8.7 pathlevel 249)
Rails 2.3.5
Oracle (yes, I know it's not supported but the solution below seems so easy and won't cause regression I think)

The error is in the SQL executed in the file app/models/issue.rb, line 828:
Where now is
def self.count_and_group_by(options)
    project = options.delete(:project)
    select_field = options.delete(:field)
    joins = options.delete(:joins)

    where = "i.#{select_field}=j.id" 

    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
                                                s.is_closed as closed, 
                                                j.id as #{select_field},
                                                count(i.id) as total 
                                              from 
                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{joins} as j
                                              where 
                                                i.status_id=s.id 
                                                and #{where}
                                                and i.project_id=#{project.id}
                                              group by s.id, s.is_closed, j.id")
  end

it should be
def self.count_and_group_by(options)
    project = options.delete(:project)
    select_field = options.delete(:field)
    joins = options.delete(:joins)

    where = "i.#{select_field}=j.id" 

    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
                                                s.is_closed as closed, 
                                                j.id as #{select_field},
                                                count(i.id) as total 
                                              from 
                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{joins}  j
                                              where 
                                                i.status_id=s.id 
                                                and #{where}
                                                and i.project_id=#{project.id}
                                              group by s.id, s.is_closed, j.id")
  end

The "as j" in the SQL query is wrong and causes SQL error. Deleting "as" makes it working ok.

Actions #1

Updated by Felix Schäfer over 13 years ago

  • Category changed from Projects to Code cleanup/refactoring

Eric, JB: seems ok to me, anyone of you could commit this?

Actions #2

Updated by Jean-Baptiste Barth over 13 years ago

  • Status changed from New to Resolved
  • Assignee set to Jean-Baptiste Barth
  • Target version set to 1.0.2
  • % Done changed from 0 to 100
  • Resolution set to Fixed

It was introduced with r3365 : before this refactoring, there was no "as" word in "from" clauses. So we can be sure there won't be any regression, thanks for pointing this out (and reviewing!). Committed in r4091

Actions #3

Updated by Eric Davis over 13 years ago

  • Status changed from Resolved to Closed

Merged into 1.0-stable for release in 1.0.2

Actions

Also available in: Atom PDF