Using :group with #count generating bad SQL on Postgres

[ahawkins]

Here is a call to scoped:

scoped({:select=>nil,
 :joins=>
  "JOIN taggings activities_taggings_4ce995f  ON activities_taggings_4ce995f.taggable_id = activities.id AND activities_taggings_4ce995f.taggable_type = 'Activity' AND activities_taggings_4ce995f.tag_id = 2 JOIN taggings activities_taggings_58b7315  ON activities_taggings_58b7315.taggable_id = activities.id AND activities_taggings_58b7315.taggable_type = 'Activity' AND activities_taggings_58b7315.tag_id = 3 LEFT OUTER JOIN taggings activities_taggings_group  ON activities_taggings_group.taggable_id = activities.id AND activities_taggings_group.taggable_type = 'Activity'",
 :group=>
  "activities.id, activities.reference_id, activities.reference_type, activities.account_id, activities.owner_id, activities.owner_type, activities.created_at, activities.updated_at, activities.timestamp HAVING COUNT(activities_taggings_group.taggable_id) = 2",
 :conditions=>"",
 :order=>nil,
 :readonly=>false})

Here it the error:

       PG::Error: ERROR:  syntax error at or near "AS"
       LINE 1: ... COUNT(activities_taggings_group.taggable_id) = 2 AS activit...
                                                                    ^
       : SELECT COUNT(*) AS count_all, activities.id, activities.reference_id, activities.reference_type, activities.account_id, activities.owner_id, activities.owner_type, activities.created_at, activities.updated_at, activities.timestamp HAVING COUNT(activities_taggings_group.taggable_id) = 2 AS activities_id_activities_reference_id_activities_reference_type FROM "activities" JOIN taggings activities_taggings_4ce995f  ON activities_taggings_4ce995f.taggable_id = activities.id AND activities_taggings_4ce995f.taggable_type = 'Activity' AND activities_taggings_4ce995f.tag_id = 2 JOIN taggings activities_taggings_58b7315  ON activities_taggings_58b7315.taggable_id = activities.id AND activities_taggings_58b7315.taggable_type = 'Activity' AND activities_taggings_58b7315.tag_id = 3 LEFT OUTER JOIN taggings activities_taggings_group  ON activities_taggings_group.taggable_id = activities.id AND activities_taggings_group.taggable_type = 'Activity' WHERE "activities"."reference_id" = 1 AND "activities"."reference_type" = 'CallList' GROUP BY activities.id, activities.reference_id, activities.reference_type, activities.account_id, activities.owner_id, activities.owner_type, activities.created_at, activities.updated_at, activities.timestamp HAVING COUNT(activities_taggings_group.taggable_id) = 2

Here is the query formatted nicely:

SELECT COUNT(*) 
  AS count_all, activities.id, activities.reference_id, 
    activities.reference_type, activities.account_id, 
    activities.owner_id, activities.owner_type, activities.created_at, 
    activities.updated_at, activities.timestamp 
HAVING COUNT(activities_taggings_group.taggable_id) = 2 AS activities_id_activities_reference_id_activities_reference_type 
FROM "activities" 
JOIN taggings activities_taggings_a00c683 
  ON activities_taggings_a00c683.taggable_id = activities.id
    AND activities_taggings_a00c683.taggable_type = 'Activity' 
    AND activities_taggings_a00c683.tag_id = 2 
JOIN taggings activities_taggings_0980a52 
  ON activities_taggings_0980a52.taggable_id = activities.id 
  AND activities_taggings_0980a52.taggable_type = 'Activity' 
  AND activities_taggings_0980a52.tag_id = 3 
LEFT OUTER JOIN taggings activities_taggings_group
  ON activities_taggings_group.taggable_id = activities.id
  AND activities_taggings_group.taggable_type = 'Activity' 
GROUP BY 
  activities.id, activities.reference_id, activities.reference_type, 
  activities.account_id, activities.owner_id, activities.owner_type, 
  activities.created_at, activities.updated_at, activities.timestamp 
HAVING COUNT(activities_taggings_group.taggable_id) = 2

TL;DR: rails adds HAVING COUNT to the select clause and postgres blows up.

[ahawkins]

I've added a basic failing test here: ahawkins@0ba4234

I will work on it more later.

[dmathieu]

Is this still broken ? Are you able to reproduce it in master ?

[take]

@twinturbo ur basic failing test link are out of date -> ahawkins@0ba4234

are there anyway to look at them? i'll happly write a patch for it :) thx

[senny]

Is this still a problem with 4.0.0.rc1? If so, can you write your test-case using this template. This will help us to reproduce and fix the problem.

[steveklabnik]

This issue has been open for a loooong time, and it seems we don't have a reproduction. I'm giving it a close. @ahawkins, if you want to keep up with this, you know what to do ❤️

[yawboakye]

Rails 4.0.0, Ruby 2.0

class Title < ActiveRecord::Base
  has_many :comments, dependent: :delete_all

  # validations

  def Title.order_by_number_of_comments_descending
    select('titles.*, count(comments.id) AS comments_count').
    joins(:comments).
    group('titles.id').
    order('comments_count DESC')
  end
end

results in this Postgres exception:

PG::Error: ERROR:  column "comments_count" does not exist
LINE 1: ..._id" = "titles"."id" GROUP BY titles.id  ORDER BY comments_c...

: SELECT COUNT(*) AS count_all, titles.id AS titles_id FROM "titles" INNER JOIN "comments" ON "comments"."title_id" = "titles"."id" GROUP BY titles.id ORDER BY comments_count DESC

PostgreSQL 9.2 and 9.1.5

[steveklabnik]

Thanks! Sorry I missed this.

[al2o3cr]

This ticket should really be split - I'm not 100% sure the original problem is even a bug (probably should be using having on the relation, not just wedging the clause into group), and the second problem is only related by being about count.

Related tickets (after a little digging):

• has_many through with uniq gives wrong count if condition given #1003 - count-clause weirdness with unique has_many associations
• Arel: #count does not work after #select("... AS ...") #2541 - very similar issue with AS
• #count doesn't honor distinct attributes in select clause #5554 - there appears to be a solution referenced at the end of this one
• subquery_for_count's count_column is overwritten if select scope is used [regression] #11824 - another issue with invalid count SQL
• Remove column restrictions for #count, let the database raise if the SQL is invalid. #10710 - back-and-forth with a possible solution. @yawboakye , can you try out the patch in this one?

[iantropov]

Can't reproduce this with following gist https://gist.github.com/iantropov/7394625

[robin850]

@iantropov : Yes, it looks like most of the issues quoted by @al2o3cr are resolved. Also the gist you provided is passing on my machine (with 4.0.0 and master) ; I first thought this was PostgreSQL specific but it passes as well with the pg adapter.

I'm going to give it a close.

@yawboakye : If you can provide en executable gist that reproduce the problem, let us know and we can reopen. Thank you!