#1336
  • Created on
    2012-09-30 14:53
  • Reported by
    zoombody
  • Status
    Fixed
  • Priority
    Normal (3)
  • Assigned to
    zoombody
  • Milestone
    2.2
  • Plan Level
    -
  • Due Date
    -

Attachments

Related Tickets

Add new subtask or bug

SQL is incompatible with Postgres

When testing the latest Tracks dev with the 'pg' gem and a PostgreSQL database, I get the following error in the server logs when logging in and trying to load the home page. (My production Tracks 2.1 runs fine on PostgreSQL.)

2012-09-30T18:34:40+00:00 heroku[router]: POST my-app-name.herokuapp.com/login dyno=web.1 queue=0 wait=0ms service=165ms status=302 bytes=107
2012-09-30T18:34:40+00:00 app[web.1]:
2012-09-30T18:34:40+00:00 app[web.1]: LINE 1: ..."."state" = 'active' GROUP BY project_id ORDER BY todos.comp...
2012-09-30T18:34:40+00:00 app[web.1]: ActiveRecord::StatementInvalid (PG::Error: ERROR: column "todos.completed_at" must appear in the GROUP BY clause or be used in an aggregate function
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:283:in `eval'
2012-09-30T18:34:40+00:00 heroku[router]: GET my-app-name.herokuapp.com/ dyno=web.1 queue=0 wait=0ms service=210ms status=500 bytes=752
2012-09-30T18:34:40+00:00 app[web.1]: ^
2012-09-30T18:34:40+00:00 app[web.1]: : SELECT COUNT(*) AS count_all, project_id AS project_id FROM "todos" WHERE "todos"."user_id" = 1 AND "todos"."state" = 'active' GROUP BY project_id ORDER BY todos.completed_at DESC, todos.created_at DESC):
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/todos_controller.rb:16:in `index'
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:283:in `eval'
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:275:in `init_data_for_sidebar'
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:282:in `each'
2012-09-30T18:34:40+00:00 app[web.1]:
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:282:in `init_not_done_counts'
2012-09-30T18:34:40+00:00 app[web.1]: Completed 500 Internal Server Error in 197ms
2012-09-30T18:34:40+00:00 app[web.1]: app/controllers/application_controller.rb:283:in `block in init_not_done_counts'
2012-09-30T18:34:40+00:00 app[web.1]: Processing by TodosController#index as HTML
User picture
  on 2012-09-30 17:33 *
By zoombody
application_controller.rb:283 contains this Active Record query:
current_user.todos.active.group('#{parent}_id').count

which generates the following SQL:
SELECT COUNT(*) AS count_all, project_id AS project_id FROM "todos"  WHERE "todos"."user_id" = 1 AND "todos"."state" = 'active' GROUP BY project_id ORDER BY todos.completed_at DESC, todos.created_at DESC

PostgreSQL is stricter than MySQL or SQLite about columns used in ORDER BY having to appear in GROUP BY. It's true in this case that the ORDER BY makes no sense because those columns aren't being returned.

The ORDER BY is coming from models/user.rb:83.

One opaque solution is to change the query to override the order with something like this:
current_user.todos.reorder('').active.group('#{parent}_id').count

which generates a cleaner query:
SELECT COUNT(*) AS count_all, project_id AS project_id FROM "todos" WHERE "todos"."user_id" = 1 AND "todos"."state" = 'active' GROUP BY project_id


The better approach would be to use DISTINCT instead of GROUP BY. I'll see if I can get that working.

There are two other offending queries in application_controller.rb on lines 284 and 290, both of which can be fixed in the same way.
User picture
  on 2012-09-30 18:10 *
By zoombody
My brain is dead at the moment. I think DISTINCT was a wild goose chase.

I think this might be a (slightly) clearer way of doing the reorder because it's more obviously negating something:
current_user.todos.except(:order).active.group('#{parent}_id').count

I'm definitely down in the weeds with this one. There might be a higher-level solution than modifying these three queries, but if so, I don't have a good enough sense of the overall application flow to be able to identify it.
User picture
  on 2012-09-30 18:47 *
By zoombody
(Comment removed)
User picture
  on 2012-09-30 22:07 *
By zoombody
Description changed from When testing deployment of ... to When testing the latest Tra...
Description changed from When testing deployment of ... to When testing the latest Tra...
User picture
  on 2012-10-01 16:45 *
By zoombody
Here's a great overview of the problem and some possible solutions: http://weblog.jamisbuck.org/2007/1/18/activerecord-association-scoping-pitfalls
User picture
  on 2012-10-02 04:22 *
By lrbalt
The order is the default for current_user.todos. So removing the order for this query is fine with me.
User picture
  on 2012-10-02 04:22 *
By lrbalt
(Comment removed)
User picture
  on 2012-10-02 04:24 *
By lrbalt
Component changed from None to Functionality (app)
Milestone set to 2.2
Component changed from None to Functionality (app)
Milestone set to 2.2
User picture
  on 2012-10-04 00:58 *
By zoombody
Assigned to set to zoombody
Assigned to set to zoombody
I have a patch ready, pending comment from Reinier in a private email thread.
User picture
  on 2012-10-05 16:12 *
By zoombody
Patch proposed in https://github.com/TracksApp/tracks/pull/112
User picture
  on 2012-10-05 16:13 *
By zoombody
Status changed from New to Test
Status changed from New to Test
User picture
  on 2012-10-05 16:13 *
By zoombody
Status changed from Test to Accepted
Status changed from Test to Accepted
User picture
  on 2012-10-08 00:02 *
By Anonymous
Status changed from Accepted to Fixed
Status changed from Accepted to Fixed
(In tracks-tickets:5b6cbf566aa3b6f577e77be9c5868a4c81808efa) Don't sort todos when counting them

Grouping isn't as lax in PostgreSQL as it is in MySQL or SQLite. All
sort fields also need to be in the GROUP BY, or be aggregated. The order
isn't relevant when counting, so simply don't order in that case.

Fix #1336

Branch: master