Regression: distinct option in scopes generates invalid SQL for findAndCountAll() queries (MySQL 5.5)

[adamjmurray]

What you are doing?

We are using scopes with the distinct option in combination with findAndCount()

var Sequelize = require('sequelize');
var sequelize = new Sequelize('mysql://root@localhost/sequelize_test');

var User = sequelize.define('user', {
  name: {
    type: Sequelize.STRING
  }
});

var Role = sequelize.define('role', {
  name: {
    type: Sequelize.STRING
  }
});

Role.belongsTo(User);
User.hasMany(Role);

User.addScope('withRoles', {
  distinct: true,
  include: [{model: Role}]
});

sequelize.sync({force:true})
  .then(function() {
    return User.scope('withRoles').findAndCountAll();
  });

What do you expect to happen?

In Sequelize 3.23.4 and earlier, this generated valid SQL (for MySQL):

SELECT count(DISTINCT(`user`.`id`)) AS `count` FROM `users` AS `user` LEFT OUTER JOIN `roles` AS `roles` ON `user`.`id` = `roles`.`userId`;

What is actually happening?

Starting in Sequelize 3.23.5, this generates invalid SQL (for MySQL):

SELECT count(DISTINCT(*)) AS `count` FROM `users` AS `user` LEFT OUTER JOIN `roles` AS `roles` ON `user`.`id` = `roles`.`userId`;

Unhandled rejection SequelizeDatabaseError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)) AS `count` FROM `users` AS `user`' at line 1

The only difference is count(DISTINCT(*)) instead of count(DISTINCT(user.id))

System info

Dialect: mysql
Database version: 5.5.46
Sequelize version: 3.23.5+

Other notes:

Interestingly, if you change the above code sample to do the same thing but without a scope, like this:

sequelize.sync({force:true})
  .then(function() {
    return User.findAndCountAll({
      distinct: true,
      include: [{model: Role}]
    });
  });

That did not work in Sequelize 3.23.4 and earlier, which is why we setup our scopes with distinct options. This approach started working in 3.23.5 at the same time scopes+distinct broke.

We use this scopes+distinct pattern throughout our codebase. Given the recent changes in behavior, I am hesitant to switch all our scopes to inline options, in case the behavior changes again in a future update.

Is it possible to make distinct + findAndCount work with both scopes and inline options, so we can write our code either way? If not, can a Sequelize contributor comment on whether one of these approaches is officially supported so I can update our codebase appropriately?

[sushantdhiman]

Thanks for reporting with short and easy to test example :) Not everyone does that.

In master #6114 will solve this issue. It allow you to pass col option in count queries.

So this should work

sequelize.sync({force:true})
  .then(function() {
    return User.scope('withRoles').findAndCountAll({ col: 'user.id' });
  })
  .then(console.log)
  .catch(console.error);

I am not sure if #6114 has been backported to v3, if not you can copy code and make a PR, we will release new version with fix.