{"action":"created","issue":{"url":"https://api.github.com/repos/rails/rails/issues/16791","labels_url":"https://api.github.com/repos/rails/rails/issues/16791/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/16791/comments","events_url":"https://api.github.com/repos/rails/rails/issues/16791/events","html_url":"https://github.com/rails/rails/issues/16791","id":41826117,"number":16791,"title":"ActiveRecord Sum (and other calculation operators) don't honor distinct attributes","user":{"login":"SimonKaluza","id":648386,"avatar_url":"https://avatars.githubusercontent.com/u/648386?v=3","gravatar_id":"","url":"https://api.github.com/users/SimonKaluza","html_url":"https://github.com/SimonKaluza","followers_url":"https://api.github.com/users/SimonKaluza/followers","following_url":"https://api.github.com/users/SimonKaluza/following{/other_user}","gists_url":"https://api.github.com/users/SimonKaluza/gists{/gist_id}","starred_url":"https://api.github.com/users/SimonKaluza/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SimonKaluza/subscriptions","organizations_url":"https://api.github.com/users/SimonKaluza/orgs","repos_url":"https://api.github.com/users/SimonKaluza/repos","events_url":"https://api.github.com/users/SimonKaluza/events{/privacy}","received_events_url":"https://api.github.com/users/SimonKaluza/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/activerecord","name":"activerecord","color":"0b02e1"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2014-09-03T15:00:27Z","updated_at":"2015-01-08T01:27:43Z","closed_at":null,"body":"When using the ActiveRecord \"sum\" calculation with the Postgres adapter (and I believe the others though I haven't tested them), any attributes added in a \"distinct\" clause are not used to filter/trim the dataset.  Though \"DISTINCT\" is added to the SQL, it is incorrectly added _outside_ the SUM operation, which effectively ignores the relevant distinct attributes.\r\n\r\nThis seems to be directly related to issue https://github.com/rails/rails/issues/5554 and issue https://github.com/rails/rails/pull/6970.  Though the pull request related to those issues seemed to fix the undesirable behavior for the \"count\" ActiveRecord::Calculations.\r\n\r\nI've written a short example script to display this behavior (there's also some Rails setup code included so it could be run outside of a Rails project, like in an IRB console easily):\r\n\r\n```\r\nrequire 'active_record'\r\nrequire 'pg'\r\n\r\n\r\n####    SETUP MIGRATIONS/DATABASE (Moved out of canonical Rails structure and into one file for demonstration)    ####\r\nPG_SPEC = {\r\n    :adapter  => 'postgresql',\r\n    :host     => 'localhost',\r\n    :database => 'my_test_db',\r\n    :username => 'my_db_super_user',\r\n    :encoding => 'utf8'\r\n}\r\n\r\nActiveRecord::Base.establish_connection(PG_SPEC.merge('database' => 'postgres', 'schema_search_path' => 'public'))\r\nActiveRecord::Base.connection.drop_database PG_SPEC[:database] rescue nil\r\nActiveRecord::Base.connection.create_database(PG_SPEC[:database])\r\nActiveRecord::Base.establish_connection(PG_SPEC)\r\n\r\nclass CreateMyModels < ActiveRecord::Migration\r\n  def self.change\r\n    create_table :users do |t|\r\n      t.integer :summable_attr\r\n      t.timestamps\r\n    end\r\n\r\n    create_table :user_groups do |t|\r\n      t.integer :user_id\r\n      t.integer :group_id\r\n      t.timestamps\r\n    end\r\n\r\n    create_table :groups do |t|\r\n      t.timestamps\r\n      t.string :status\r\n    end\r\n  end\r\nend\r\n\r\nCreateMyModels.change\r\n\r\n####    SETUP ACTIVERECORD, POPULATE DATABASE, AND DEMONSTRATE ODD QUERY BEHAVIOR    ####\r\n\r\nclass User < ActiveRecord::Base\r\n  has_many :user_groups\r\n  has_many :groups, through: :user_groups\r\n\r\n  # Should only return users that are active\r\n  def self.all_active_users\r\n    User.joins(:user_groups).joins(:groups).distinct(true).where('groups.status' => 'active')\r\n  end\r\nend\r\n\r\nclass Group < ActiveRecord::Base\r\n  has_many :user_groups\r\n  has_many :users, through: :user_groups\r\nend\r\n\r\nclass UserGroup < ActiveRecord::Base\r\n  belongs_to :group\r\n  belongs_to :user\r\nend\r\n\r\ng1 = Group.create(status: 'active')\r\ng2 = Group.create(status: 'inactive')\r\n\r\nu1 = User.create(summable_attr: 1 )\r\nu2 = User.create(summable_attr: 2 )\r\nu3 = User.create(summable_attr: 3 )\r\nu4 = User.create(summable_attr: 4 )\r\n\r\nUserGroup.create(group_id: g1.id, user_id: u1.id)\r\nUserGroup.create(group_id: g1.id, user_id: u2.id)\r\nUserGroup.create(group_id: g1.id, user_id: u3.id)\r\nUserGroup.create(group_id: g1.id, user_id: u4.id)\r\n\r\n\r\nUserGroup.create(group_id: g2.id, user_id: u1.id)\r\nUserGroup.create(group_id: g2.id, user_id: u2.id)\r\nUserGroup.create(group_id: g2.id, user_id: u3.id)\r\nUserGroup.create(group_id: g2.id, user_id: u4.id)\r\n\r\n# Perform the sum using a Postgres AR query gives an incorrect result.\r\n# Outputs \"Total Sum: 20\"\r\n# Uses SQL: SELECT DISTINCT SUM(\"users\".\"summable_attr\") AS sum_id FROM \"users\" INNER JOIN \"user_groups\" ON \"user_groups\".\"user_id\" = \"users\".\"id\" INNER JOIN \"user_groups\" \"user_groups_users_join\" ON \"user_groups_users_join\".\"user_id\" = \"users\".\"id\" INNER JOIN \"groups\" ON \"groups\".\"id\" = \"user_groups_users_join\".\"group_id\" WHERE \"groups\".\"status\" = 'active'\r\nputs \"Total Sum: #{User.all_active_users.sum('summable_attr')}\"\r\n\r\n# The correct summation SQL should be something like:\r\n# Outputs \"Total Sum: 10\"\r\nputs \"Total Sum: #{ActiveRecord::Base.connection.execute(%q[SELECT DISTINCT SUM(DISTINCT \"users\".\"id\") FROM \"users\" INNER JOIN \"user_groups\" ON \"user_groups\".\"user_id\" = \"users\".\"id\" INNER JOIN \"user_groups\" \"user_groups_users_join\" ON \"user_groups_users_join\".\"user_id\" = \"users\".\"id\" INNER JOIN \"groups\" ON \"groups\".\"id\" = \"user_groups_users_join\".\"group_id\" WHERE \"groups\".\"status\" = 'active'])[0][\"sum\"]}\"\r\n\r\n# Perform the sum using a Ruby inject operator gives us the correct result (at quite a heavy performance/memory cost)\r\n# Outputs \"Total Sum: 10\"\r\n# Uses SQL: SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"user_groups\" ON \"user_groups\".\"user_id\" = \"users\".\"id\" INNER JOIN \"user_groups\" \"user_groups_users_join\" ON \"user_groups_users_join\".\"user_id\" = \"users\".\"id\" INNER JOIN \"groups\" ON \"groups\".\"id\" = \"user_groups_users_join\".\"group_id\" WHERE \"groups\".\"status\" = 'active'\r\nputs \"Total Sum: #{User.all_active_users.map {|u| u.summable_attr}.inject(:+)}\"\r\n\r\n# However, the \"count\" from ActiveRecord::Calculations seems to work correctly:\r\n# Outputs \"Total Count: 4\"\r\n# Uses SQL: SELECT DISTINCT COUNT(DISTINCT \"users\".\"id\") FROM \"users\" INNER JOIN \"user_groups\" ON \"user_groups\".\"user_id\" = \"users\".\"id\" INNER JOIN \"user_groups\" \"user_groups_users_join\" ON \"user_groups_users_join\".\"user_id\" = \"users\".\"id\" INNER JOIN \"groups\" ON \"groups\".\"id\" = \"user_groups_users_join\".\"group_id\" WHERE \"groups\".\"status\" = 'active'\r\nputs \"Total Count: #{User.all_active_users.count}\"\r\n\r\n```"},"comment":{"url":"https://api.github.com/repos/rails/rails/issues/comments/69122656","html_url":"https://github.com/rails/rails/issues/16791#issuecomment-69122656","issue_url":"https://api.github.com/repos/rails/rails/issues/16791","id":69122656,"user":{"login":"ntdb","id":2095463,"avatar_url":"https://avatars.githubusercontent.com/u/2095463?v=3","gravatar_id":"","url":"https://api.github.com/users/ntdb","html_url":"https://github.com/ntdb","followers_url":"https://api.github.com/users/ntdb/followers","following_url":"https://api.github.com/users/ntdb/following{/other_user}","gists_url":"https://api.github.com/users/ntdb/gists{/gist_id}","starred_url":"https://api.github.com/users/ntdb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ntdb/subscriptions","organizations_url":"https://api.github.com/users/ntdb/orgs","repos_url":"https://api.github.com/users/ntdb/repos","events_url":"https://api.github.com/users/ntdb/events{/privacy}","received_events_url":"https://api.github.com/users/ntdb/received_events","type":"User","site_admin":false},"created_at":"2015-01-08T01:27:43Z","updated_at":"2015-01-08T01:27:43Z","body":"+1\r\n\r\nThanks for the temporary fix, @SimonKaluza. In my case I'm caching the calculated values to offset the performance hit."}}