[PATCH] ActiveRecord::Base.count is broken with :include

Out of curiosity, what's the expected behavior of Person.count(:all, :include => :addresses) ? Is it to give the total number of people with addresses or the total number of addresses with people? Or is it to give the total people plus the total addresses?

I always assume the include is ambiguous and do something like Person.count(:id, :conditions => "addresses.person_id IS NOT NULL", :include => :addresses) to be sure.

That being said, something like the following also doesn't seem to be working:

Person.count(:id, :conditions => "addresses.foo > 10", :include => :addresses)

Which is a sort of important functionality, no?

The real error I'm getting is this:

>> c.entities.count :id, :conditions => Farm::SQL_SMALL, :include => :farm ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'farms.hectares' in 'where clause': SELECT count(id) AS count_id FROM entities WHERE (entities.cafe_application_id = 2 AND (farms.hectares < 12))

I would expect the :include argument to have added a join in there.

Replying to heisters:

I always assume the include is ambiguous and do something like Person.count(:id, :conditions => "addresses.person_id IS NOT NULL", :include => :addresses) to be sure. That being said, something like the following also doesn't seem to be working: Person.count(:id, :conditions => "addresses.foo > 10", :include => :addresses) Which is a sort of important functionality, no? The real error I'm getting is this:

c.entities.count :id, :conditions => Farm::SQL_SMALL, :include => :farm

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'farms.hectares' in 'where clause': SELECT count(id) AS count_id FROM entities WHERE (entities.cafe_application_id = 2 AND (farms.hectares < 12)) I would expect the :include argument to have added a join in there.

Sorry, my bad. Associations' interfaces aren't the same as the class, so I guess this doesn't matter. Apologies for wasting bandwidth and pixels.

Replying to danger:

Out of curiosity, what's the expected behavior of Person.count(:all, :include => :addresses) ? Is it to give the total number of people with addresses or the total number of addresses with people? Or is it to give the total people plus the total addresses?

I think this ticket should be closed unless there's a compelling reason for Model.count(:all, :include => :association) to be used.

Replying to danger:

Replying to danger:

Out of curiosity, what's the expected behavior of Person.count(:all, :include => :addresses) ? Is it to give the total number of people with addresses or the total number of addresses with people? Or is it to give the total people plus the total addresses?

I think this ticket should be closed unless there's a compelling reason for Model.count(:all, :include => :association) to be used.

The point isn't whether it should or should not be used. The point is that it shouldn't crash because of broken sql if it is used. The fact that it supports having an :include option, means that it should work with an supplied :include option.

Can you verify that this happens on trunk? The following is working fine for me:

class User < ActiveRecord::Base
  has_many :posts
end
class Post < ActiveRecord::Base
  belongs_to :user
end
# I've preloaded the database with 4 users - each with one or two posts
User.count # => 4
User.count(:id) # => 4
User.count(:all, :include => :posts) # => 4
User.count(:include => :posts) # => 4

And here's the SQL generated for the join count:

SELECT count(DISTINCT users.id) AS count_all FROM users LEFT OUTER JOIN posts ON posts.user_id = users.id

Is there something tricky going on with the association in your app? Is this a has_many :through or something? It seems to be working fine with really simple relationships.

Ok, scratch this. I'm not sure what exactly happened, but:

ActiveRecord::Base.connection.supports_count_distinct? # => false

I'm using the postgresql adapter (pure ruby). I cannot for the life of me find where this is switched from true (I've done numerous searches of all of my code and all of the plugins I use). The problem only exists when the connection adapter does not support doing count(DISTINCT ...). This might still be a problem for sqlite only (as this is the only database that doesn't or didn't?) support count distinct. Any advice on the supports_count_distinct switching to false?

I bumped into this issue as well using different code:

options = {:include => :dokter, :conditions => ..}
count_collection_for_pagination(User, options)

Strange thing is that on my development machine (a Mac with Postgres 8.1.5) this works fine. (No subquery is used in the sql)

But in production (Linux with Postgres 8.2.3) it breaks the sql by using a subquery without an alias.

Replying to nerdrew: It's like you said. With Postgres 8.1.5 a count (DISTINCT ) is used, but with Postgres 8.2.3, a subquery is used.

(I've tested it with postgres-pr and postgres drivers)

Replying to andrewf:

Replying to nerdrew: It's like you said. With Postgres 8.1.5 a count (DISTINCT ) is used, but with Postgres 8.2.3, a subquery is used. (I've tested it with postgres-pr and postgres drivers)

Yeah, my workaround is to over-write supports_count_distinct? for the instance of connection my Models use.

Strange, supports_count_distinct? is true in all my Models. When I use the stable 1.2.3 release, the bug isn't there. Only in edge (r6532)

Replying to andrewf:

Strange, supports_count_distinct? is true in all my Models. When I use the stable 1.2.3 release, the bug isn't there. Only in edge (r6532)

I hear you. Same exact thing for me. Running off the 1.2.3 gem works just fine. Edge rails does not though (at r6538 for me).

Seeing as how I just registered, I'm not about to change the status of this bug. I can, however, shed some light on it.

First, I think we're talking about two separate bugs here. Rails 1.2.3 exhibits the behavior shown in the original report when using an adapter that truely doesn't have select(distinct ...). I can confirm that using sqlite3. I don't know why the postgres adapters would trigger the workaround using 8.2. That seems to be a different issue.

Second, an attempt to fix this is in the repository. I can confirm it works with sqlite3.

Third, the argument about whether this is moot or not is silly. ActionController::Pagination::paginate() uses this code. For that matter, you may only want to count records with associated objects having certain attributes. For example, only employees whose cars are red. You'd do something like Employee.count(:include => :cars, :conditions => ['cars.color = ?', 'red']) -- assuming a one-to-one relationship. I can't say I've used this, but I'm sure somebody wants to.

Fourth, I'll get specific. The issue is in activerecord/lib/active_record/calculations.rb. In both versions (svn and 1.2.3), the method construct_calculation_sql() sets a flag to enable a workaround for missing count(DISTINCT...). In AR 1.15.3 (Rails 1.2.3), the distinct flag is set a few lines later if there are any includes, but does not update the workaround flag. In the trunk, the line setting the workaround is moved below this.

Rails 1.2.3:

def construct_calculation_sql(operation, column_name, options) #:nodoc:
...
          use_workaround  = !Base.connection.supports_count_distinct? && options[:distinct] && operation.to_s.downcase == 'count'
...

          if merged_includes.any? && operation.to_s.downcase == 'count'
            options[:distinct] = true
            column_name = options[:select] || [table_name, primary_key] * '.'
          end
...

trunk:

        def construct_calculation_sql(operation, column_name, options) #:nodoc:
...
          if operation == 'count'
            if merged_includes.any?
              options[:distinct] = true
              column_name = options[:select] || [table_name, primary_key] * '.'
            end

            if options[:distinct]
              use_workaround = !connection.supports_count_distinct?
            end
          end

If you want to fix this in your local installation (AR 1.15.3), just move line 166 (the one starting with use_workaround =) to line 173, after the last place options[:distinct] may be set.

Jeffy, can you verify (ideally with a test) that that one small change will fix the behavior?

Patch against Rails 1.2.3

Test of Patch against Rails 1.2.3

Wow, that was a pain to craft.

Apparently, newer versions of SQLite3 (i.e. ones that pass most of the standard tests) support SELECT count(DISTINCT ...) when the ... isn't *. The SQLite 2 adapter actually re-writes these queries as part of connection.execute.

Patch and test are attached.

Jeffy, thanks for putting that together - it looks solid.

The test still fails for me after the patch.

Replying to bitsweat:

The test still fails for me after the patch.

Could you share the error message?

I'm guessing Jeremy only patched with the second diff. Maybe you should unify the two patches in one.

The test is still failing for me as well, on both 1.2.3 and Edge. I've attached the unified patch.

#supports_count_distinct? is returning true for MySQL and that is what's causing the test failure for me. Executing these through script/console also fails with the same exception:

Firm.count(:select => "*", :include => "plain_clients")
ActiveRecord::Base.execute("SELECT (DISTINCT *) FROM plain_clients")
ActiveRecord::Base.execute("SELECT (DISTINCT name) FROM plain_clients")

But executing any of these works just fine:

Firm.count(:select => "name", :include => "plain_clients")
ActiveRecord::Base.execute("SELECT DISTINCT * FROM plain_clients")
ActiveRecord::Base.execute("SELECT DISTINCT name FROM plain_clients")

So it seems as if the parens around the DISTINCT are what's throwing off MySQL, at least in my case.

Replying to pelargir:

Firm.count(:select => "*", :include => "plain_clients") ActiveRecord::Base.execute("SELECT (DISTINCT *) FROM plain_clients") ActiveRecord::Base.execute("SELECT (DISTINCT name) FROM plain_clients")

I suspect those should be count(DISTINCT *). Is that SQL being generated?

Guys,

I'm New here so sorry if I don't know what I'm talking about... Or if this is not the place for my comment ...

I'm using Rails 1.2.4 core and I notice an issue (bug) with regards to using :include in .count function.

When I use it on an ActiveRecord::Base (Model) then it works just fine:

 Lead.count(:include=>:statuses, :conditions=>["lead_statuses.lead_type_status_value_id = ?", 1])

results in:

SELECT count(DISTINCT leads.id) AS count_all FROM leads LEFT OUTER JOIN lead_statuses ON lead_statuses.lead_id = leads.id WHERE (lead_statuses.lead_type_status_value_id = 1)

However, when I do it on an association, I get an error:

@lt = LeadType.find(1)
@lt.leads.count(:include=>:statuses, :conditions=>["lead_statuses.lead_type_status_value_id = ?", 1])

results in:

Mysql::Error: #42S22Unknown column 'lead_statuses.lead_type_status_value_id' in 'where clause': SELECT count(*) AS count_all FROM leads WHERE (leads.lead_type_id = 1 AND (lead_statuses.lead_type_status_value_id = 1))

Given that I'm a Rails BEGINNER. I have nothing else to add (yet) in terms of diagnosis. Sorry. But do help :)

Cheers Krum

Looks like this issue is described in Ticket 9167: http://dev.rubyonrails.org/ticket/9167

Not sure why I'm still seeing the issue in Rails core 1.2.4 ?