Issue with Result Counting in PostgreSQL

[sgonyea]

For reference, I am using Rails 3.1.0rc4 with arel 2.1.1, and pg 0.11.0

My tests seemed to have exposed this bug. Take the following model:

class Location < ActiveRecord::Base
  belongs_to  :user

  scope :distinct_by_user,      select('DISTINCT ON(user_id) *')
  scope :from_sorted_locations, from('(SELECT locations.* FROM "locations" ORDER BY locations.id DESC) AS sorted_locations')
  scope :recent_user_locations, distinct_by_user.from_sorted_locations
end

The SQL Query that's generated is below the ruby code:

Location.recent_user_locations
# SELECT DISTINCT ON(user_id) * FROM (SELECT locations.* FROM "locations" ORDER BY locations.id DESC) AS sorted_locations

Location.recent_user_locations.size
# SELECT COUNT(*) FROM (SELECT locations.* FROM "locations" ORDER BY locations.id DESC) AS sorted_locations

Basically, calling "size" / "count" will go against my own "from" query, but disregard my select query. Once the query is executed, #size / #count return the correct record count.

Thank you!

[njakobsen]

I just ran into this problem while trying to work around the issue of calling count on a relations with group causing the return value to be an ordered hash instead of an integer. Instead of grouping to remove duplicates, I tried using the slower "SELECT DISTINCT" method, but this was overridden by calling .count() on the relation.

[njakobsen]

It looks like you can pass :distinct to the count option to ensure the count is distinct. It doesn't get applied automagically, but it should let you count distinct on user_id.

[matthewd]

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)