Defect #6413

Error in SQL

Added by Juan G about 7 years ago. Updated about 7 years ago.

Status:ClosedStart date:2010-09-16
Priority:NormalDue date:
Assignee:Jean-Baptiste Barth% Done:

100%

Category:Code cleanup/refactoring
Target version:1.0.2
Resolution:Fixed Affected version:1.0.1

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.

Associated revisions

Revision 4091
Added by Jean-Baptiste Barth about 7 years ago

Fixed non standard SQL syntax. #6413

Contributed by Juan G

History

#1 Updated by Felix Schäfer about 7 years ago

  • Category changed from Projects to Code cleanup/refactoring

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

#2 Updated by Jean-Baptiste Barth about 7 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

#3 Updated by Eric Davis about 7 years ago

  • Status changed from Resolved to Closed

Merged into 1.0-stable for release in 1.0.2

Also available in: Atom PDF