Postgres Error

[arsphl]

Hello,

On the Task and Run Charts tab I get an error that seems to be Postegres related. Then again it might be due to the fact that my Redmine installation was migrated from 1.3.4 to 2.1.0...

Here is the Task Error :

ActiveRecord::StatementInvalid (PG::Error: ERROR: column "issues.trackerid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT issues., sum(hours) as spent FROM "issues" left join... ^ : SELECT issues._, sum(hours) as spent FROM "issues" left join time_entries ON time_entries.issue_id = issues.id WHERE (issues.project_id = 17 and status_id = 1 and fixed_version_id = '12' and assigned_to_id = 1) GROUP BY issues.id ORDER BY case when issues.ir_position is null then 1 else 0 end ASC, case when issues.ir_position is NULL then issues.id else issues.ir_position end ASC):

Here is the Run Charts error :

ActiveRecord::StatementInvalid (PG::Error: ERROR: syntax error at or near "" LINE 1: ...op_key, DATE(journals.created_on) created_on fromjournals... ^ : select * from (select old_value as value, journalized_id as issueId, prop_key, DATE(journals.created_on) created_on fromjournalsinner join journal_details on (journals.id = journal_id) inner join issues on (issues.id = journalized_id) where journalized_type = 'Issue' and property = 'attr' and (prop_key = 'estimated_hours' or prop_key = 'done_ratio') and project_id = 17 order by journals.id desc) a group byissueId`, created_on, prop_key order by created_on desc):

I am runing redmine 2.1.0 on Centos 6.2 with Postgresql 9.0.7 I did a bundle install on the Redmine installation.

There was no error at the migrate. The same plugin works fine on bitnami redmine under windows. I have not tried it yet on Fedora.

Help ? ;-)

[Maltaman]

1+ ActiveRecord::StatementInvalid (PG::Error: FEHLER: Spalte »issues.tracker_id« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden LINE 1: SELECT issues.*, sum(hours) as spent FROM "issues" left join...

: SELECT issues.*, sum(hours) as spent FROM "issues" left join time_entries ON time_entries.issue_id = issues.id WHERE (issues.project_id = 30 and status_id = 1 and assigned_to_id = 6) GROUP BY issues.id ORDER BY case when issues.ir_position is null then 1 else 0 end ASC, case when issues.ir_position is NULL then issues.id else issues.ir_position end ASC):

Select with * (all_fields from issues) but in the group_by is only issues.id

[Leeto2]

I don't think it was your migration. I'm seeing the same thing when clicking on either the Run Charts or the Tasks tab. Redmine: 2.12 Postgres: 8.4 Agile Dwarf: 0.0.3 Centos 5.x Jruby: 1.6.8

---

Started GET "/adtasks/list?project_id=myproject" for 192.168.1.1 at Wed Nov 07 17:50:10 UTC 2012 Processing by AdtasksController#list as HTML Parameters: {"project_id"=>"myproject"} Current user: leeto2 (id=42) Completed 500 Internal Server Error in 282ms

ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: ERROR: column "issues.tracker_id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8: SELECT issues.*, sum(hours) as spent FROM "issues" left join time_entries ON time_entries.issue_id = issues.id WHERE (issues.project_id = 13 and status_id = 1 and fixed_version_id = '134' and assigned_to_id = 42) GROUP BY issues.id ORDER BY case when issues.ir_position is null then 1 else 0 end ASC, case when issues.ir_position is NULL then issues.id else issues.ir_position end ASC): arjdbc/jdbc/RubyJdbcConnection.java:191:in `execute'

---

I'm new to Ruby. How can I figure out what module has the query in it? (short of doing a search and grepping for it. Anything else I can do to help?

Thanks, Leeto2

[arsphl]

Hello,

I did not have time to look at this myself.

But from the error message I read that basically the Adtask Controller in the list command is miss behaving.

In SQL you have to have all fields upon which you apply calculus in the group by list of fields. You also have add the accompanying fields. So by stating issues.* next to a sum() I guess postgres want you you add all those fields to the group by definition.

I would need to look at the data base to be sure. Postgres is much more rigid about this sort of things than mysql.

You can have a look here to give you ideas :

http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql

http://practiceovertheory.com/blog/2009/09/23/postgresql-s-group-by/

Personally I would rewrite the query to conform with standard SQL and treat mysql as the exception. Otherwise if I connect the plugin to an other database like Oracle, you will end up with the same issue.

Cheers,

I don't think it was your migration. I'm seeing the same thing when clicking on either the Run Charts or the Tasks tab. Redmine: 2.12 Postgres: 8.4 Agile Dwarf: 0.0.3 Centos 5.x Jruby: 1.6.8

---

Started GET "/adtasks/list?project_id=myproject" for 192.168.1.1 at Wed Nov 07 17:50:10 UTC 2012 Processing by AdtasksController#list as HTML Parameters: {"project_id"=>"myproject"} Current user: leeto2 (id=42) Completed 500 Internal Server Error in 282ms

ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: ERROR: column "issues.tracker_id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8: SELECT issues.*, sum(hours) as spent FROM "issues" left join time_entries ON time_entries.issue_id = issues.id WHERE (issues.project_id = 13 and status_id = 1 and fixed_version_id = '134' and assigned_to_id = 42) GROUP BY issues.id ORDER BY case when issues.ir_position is null then 1 else 0 end ASC, case when issues.ir_position is NULL then issues.id else issues.ir_position end ASC): arjdbc/jdbc/RubyJdbcConnection.java:191:in `execute'

---

I'm new to Ruby. How can I figure out what module has the query in it? (short of doing a search and grepping for it. Anything else I can do to help?

Thanks, Leeto2

---

Reply to this email directly or view it on GitHub: https://github.com/iRessources/AgileDwarf/issues/44#issuecomment-10158380

Cordialement,

Philippe Laliberté philippel@lacitelp.com http://www.linkedin.com/in/plaliberte +1 418 478 2055

[Leeto2]

Fixed the Tasks issue. It's a hack but it works. Edit \AgileDwarf\app\models\sprints_tasks.rb
Find SprintsTasks.Find and look for group ==> change it to this: (note the double quotes has been changed to single tics.): :group => issues.id,issues.tracker_id,issues.project_id,issues.subject,issues.description,issues.due_date,issues.category_id,issues.status_id,issues.assigned_to_id,issues.priority_id,issues.fixed_version_id,issues.author_id,issues.lock_version,issues.created_on,issues.updated_on,issues.start_date,issues.done_ratio,issues.estimated_hours,issues.parent_id,issues.root_id,issues.lft,issues.rgt,issues.is_private,issues.ir_position',