I have different results from query for COUNT('e.id') or COUNT(e.id)

Question

I have following code:

def self.department_members(department)
  where(organization_id: department.organization_id)
    .joins("LEFT JOIN core_employments As e ON
      e.organization_id = #{department.organization_id} AND
      core_members.user_id = e.user_id")
    .group('core_members.id')
end

def self.can_automerged(department)
  department_members(department).having("COUNT('e.id') = 1")
  # department_members(department).having("COUNT(e.id) = 1")
end

def self.can_not_automerged(department)
  department_members(department).having("Count('e.id') > 1")
end

When I use

department_members(department).having("COUNT('e.id') = 1")

my test completes without errors. When I use

department_members(department).having("COUNT(e.id) = 1")

my test fails. I can't understand why. Can u explain why? I use Rails-4 and PostgreSQL.

schema:

  create_table "core_members", force: :cascade do |t|
    t.integer  "user_id",                                    null: false
    t.integer  "project_id",                                 null: false
    t.boolean  "owner",                      default: false
    t.string   "login"
    t.string   "project_access_state"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "organization_id"
    t.integer  "organization_department_id"
  end

  create_table "core_employments", force: :cascade do |t|
    t.integer  "user_id"
    t.integer  "organization_id"
    t.boolean  "primary"
    t.string   "state"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "organization_department_id"
  end

test:

module Core
  require "initial_create_helper"
  describe Member do
    describe "automerge" do
      before(:each) do
        @organization = create(:organization)
        @department1 = create(:organization_department,organization: @organization)
        @department2 = create(:organization_department,organization: @organization)

        @user = create(:user)
        @user_with_many_employments = create(:user)

        @department1.employments.create!(user: @user)
        @department1.employments.create!(organization: @organization, user: @user_with_many_employments)
        @department2.employments.create!(organization: @organization, user: @user_with_many_employments)
        @project = create_project
        @project.members.create!(user: @user,
                                organization: @organization)
        @project.members.create!(user: @user_with_many_employments,
                                organization: @organization)

      end

      it "::can_not_automerged" do
        expect(Member.can_not_automerged(@department1).to_a.map(&:user)).to match_array [@user_with_many_employments]
      end
      it "::can_automerged" do
        expect(Member.can_automerged(@department1).to_a.map(&:user)).to match_array [@user]
      end
    end
  end
end

I decided to count members without departments too with "coalesce" built-in function. Anyway your answer is complete and correct. Thank you. — SwipeZ, May 18 '18 at 04:37

Erwin Brandstetter · Accepted Answer · 2018-04-29T15:53:44.660

I have different results from query for COUNT('e.id') or COUNT(e.id)

'e.id' is a string constant, so COUNT('e.id') is just an awkward, misleading way of saying COUNT(*).

COUNT(e.id), on the other hand, counts all rows in the result where e.id IS NOT NULL - since count() does not count NULL values.

The manual about count():

count(*) ... number of input rows

count(expression) ... number of input rows for which the value of expression is not null

As you can see, there are even two separate functions internally. And it should be noted that count(*) is slightly faster. So use that unless you need the second variant. Related:

PostgreSQL: running count of rows for a query 'by minute'

You might counter with:
"But e.id is the PRIMARY KEY of core_employments, so it is defined NOT NULL!"

But that would overlook the conditional LEFT JOIN in your query that still introduces NULL values in your NOT NULL column, where the join conditions are not met. Related:

Query with LEFT JOIN not returning rows for count of 0

That said, LEFT [OUTER] JOIN is misleading, too. The later condition

having("COUNT(e.id) = 1")

forces it to act like a plain [INNER] JOIN. Once you have fixed that, you might as well simplify to:

having("COUNT(*) = 1")

And if all you care is that at least one related row exists in core_employments, translating to having("COUNT(*) >= 1"), the superior (clearer, faster) technique in simple cases would be an EXISTS semi-join:

WHERE EXISTS (SELECT FROM core_employments WHERE <conditions>)

I have different results from query for COUNT('e.id') or COUNT(e.id)

1 Answers1

Linked