Sequelize count returns wrong count when association models included

Sequelize count returns wrong count when association models included, in my case, via the model's defaultScope.

What are you doing?

I have a Post model.
The Post model has attachments (oneToMany)

I'm trying to count posts [with attachments] using Sequelize's count

// Post model
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define(
    'Post',
    {
      id: {
        primaryKey: true,
        type: DataTypes.UUID
      },
      title: DataTypes.STRING
    }
  )

  Post.associate = models => {
    Post.hasMany(models.PostAttachment, { as: 'attachments' })

    Post.addScope('defaultScope', {
      include: [
        { model: models.PostAttachment, as: 'attachments' }
      ],
      order: [
        ['id', 'ASC']
      ]
    }, {
     override: true
    })
  }

  return Post
}

// PostAttachment model
module.exports = (sequelize, DataTypes) => {
  const PostAttachment = sequelize.define(
    'PostAttachment',
    {
      id: {
        primaryKey: true,
        type: DataTypes.UUID
      },
      key: DataTypes.STRING
    }
  )

  PostAttachment.associate = models => {
    PostAttachment.belongsTo(models.Post)
  }

  return PostAttachment
}

Sequelize query

const posts = await Post.findAll()
const total = await Post.count()

return { posts, total }

Scenario 1: Single Post, multiple PostAttachments

Posts table

id	title	createdAt	updatedAt
f8d68fc0-48b1-4e90-af73-c9a4dc577461	Title1	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

PostAttachments table

id	key	PostId	createdAt	updatedAt
410819f6-d1c4-44f4-9a52-edbac765e714	Key1	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a24	Key2	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a25	Key3	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

What do you expect to happen?

I'm expecting to get 1 post and total sum 1:

{
  "posts": [{...}],
  "total": 1
}

What is actually happening?

I'm actually getting 1 post and total sum 3:

{
  "posts": [{...}],
  "total": 3
}

Scenario 2: Multiple Posts, multiple PostAttachments

Posts table

id	title	createdAt	updatedAt
f8d68fc0-48b1-4e90-af73-c9a4dc577461	Title1	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
01a528d2-8696-465f-8739-2b32bc52ceca	Title2	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
01a528d2-8696-465f-8739-2b32bc52cecb	Title3	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

PostAttachments table

id	key	PostId	createdAt	updatedAt
410819f6-d1c4-44f4-9a52-edbac765e714	Key1	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a24	Key2	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a25	Key3	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a26	Key4	01a528d2-8696-465f-8739-2b32bc52ceca	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a27	Key5	01a528d2-8696-465f-8739-2b32bc52ceca	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

What do you expect to happen?

I'm expecting to get 3 posts and total sum 3:

{
  "posts": [{...}, {...}, {...}],
  "total": 3
}

What is actually happening?

I'm actually getting 3 posts and total sum 6:

{
  "posts": [{...}, {...}, {...}],
  "total": 6
}

I ran a few configurations through. It appears the count calculations are based on each post multiplied by the maximum between 1 and the number of attachments per post, so:

• PostA * Math.max(1, 3 attachments) = 3
• PostB * Math.max(1, 2 attachments) = 2
• PostC * Math.max(1, 0 attachments) = 1

---

Dialect: postgres
Dialect version: XXX
Database version: 11.1
Sequelize version: 4.43.0
Tested with latest release: No (If yes, specify that version)

Related: #1517, #9481, #9669, #10239

Start by reproducing Post.count() with the defaultScope association and PostgreSQL data matching the one-post and multiple-post scenarios. Trace the count query generated for the included attachments and add regression coverage for distinct posts. Done means count returns the number of posts rather than joined attachment rows.