`limit` with `subQuery: false` return wrong result

What are you doing?

Let’s say I have Transaction model which have many-to-many relation with Good model. And also belongs to User model. Here’s my model and relation definition.

module.exports = (sequelize, DataTypes) => {
  const Transaction = sequelize.define('Transaction', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false,
      autoIncrement: false,
    },
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
      foreignKey: true,
      references: {
        model: 'users',
        key: 'id',
      },
    },
    status: DataTypes.ENUM('verified', 'waiting', 'empty', 'rejected'),
    channel: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
  }, {});

  Transaction.associate = (models) => {
    Transaction.belongsTo(models.User, { as: 'user', foreignKey: 'userId' });
  };

  return Transaction;
};

module.exports = (sequelize, DataTypes) => {
  const Good = sequelize.define('Good', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false,
      autoIncrement: false,
    },
    rphAset: {
      type: DataTypes.BIGINT,
      allowNull: false,
      defaultValue: 0,
    },
    urUpb: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  }, {});

  return Good;
};

db.Good.belongsToMany(db.Transaction, { as: 'transactions', through: db.TransactionGood, foreignKey: 'goodId' });
db.Transaction.belongsToMany(db.Good, { as: 'goods', through: db.TransactionGood, foreignKey: 'transactionId' });

I already seed my database with data so user with id 30e1c140-d4d0-4edb-980a-3d7b9a47a958 has 7 transaction. I want to get transaction for that user with all goods in it. Here’s my code.

const x = await models.Transaction.findAll({
    subQuery: false,
    distinct: true,
    where: {
      userId: '30e1c140-d4d0-4edb-980a-3d7b9a47a958',
    },
    include: [
      {
        model: models.User,
        as: 'user',
        attributes: ['id','name','createdAt','updatedAt'],
      },
      {
        model: models.Good,
        as: 'goods',
      },
    ],
    order: [['createdAt','ASC',]],
    limit: 10,
  });

What do you expect to happen?

It will output array with 7 transcations data (all records in database).

What is actually happening?

It returns array with only 3 data. Here’s generated SQL

SELECT `Transaction`.`id`,
       `Transaction`.`userId`,
       `Transaction`.`status`,
       `Transaction`.`channel`,
       `Transaction`.`createdAt`,
       `Transaction`.`updatedAt`,
       `user`.`id` AS `user.id`,
       `user`.`name` AS `user.name`,
       `user`.`createdAt` AS `user.createdAt`,
       `user`.`updatedAt` AS `user.updatedAt`,
       `goods`.`id` AS `goods.id`,
       `goods`.`rphAset` AS `goods.rphAset`,
       `goods`.`urUpb` AS `goods.urUpb`,
       `goods`.`createdAt` AS `goods.createdAt`,
       `goods`.`updatedAt` AS `goods.updatedAt`,
       `goods->TransactionGood`.`id` AS `goods.TransactionGood.id`,
       `goods->TransactionGood`.`createdAt` AS `goods.TransactionGood.createdAt`,
       `goods->TransactionGood`.`updatedAt` AS `goods.TransactionGood.updatedAt`,
       `goods->TransactionGood`.`goodId` AS `goods.TransactionGood.goodId`,
       `goods->TransactionGood`.`transactionId` AS `goods.TransactionGood.transactionId`
FROM `Transactions` AS `Transaction`
LEFT OUTER JOIN `Users` AS `user`ON `Transaction`.`userId` = `user`.`id`
LEFT OUTER JOIN (`TransactionGoods` AS `goods->TransactionGood`
                 INNER JOIN`Goods` AS `goods` ON `goods`.`id` = `goods->TransactionGood`.`goodId`) ON `Transaction`.`id` = `goods->TransactionGood`.`transactionId`
WHERE `Transaction`.`userId` = '30e1c140-d4d0-4edb-980a-3d7b9a47a958'
ORDER BY `Transaction`.`createdAt` ASC
LIMIT 10;

But when I remove subQuery: false option it will return 7 data. Here’s generated sql without subQuery: false option.

SELECT `Transaction`.*,
       `user`.`id` AS `user.id`,
       `user`.`name` AS `user.name`,
       `user`.`createdAt` AS `user.createdAt`,
       `user`.`updatedAt` AS `user.updatedAt`,
       `goods`.`id` AS `goods.id`,
       `goods`.`rphAset` AS `goods.rphAset`,
       `goods`.`urUpb` AS `goods.urUpb`,
       `goods`.`createdAt` AS `goods.createdAt`,
       `goods`.`updatedAt` AS `goods.updatedAt`,
       `goods->TransactionGood`.`id` AS `goods.TransactionGood.id`,
       `goods->TransactionGood`.`createdAt` AS `goods.TransactionGood.createdAt`,
       `goods->TransactionGood`.`updatedAt` AS `goods.TransactionGood.updatedAt`,
       `goods->TransactionGood`.`goodId` AS `goods.TransactionGood.goodId`,
       `goods->TransactionGood`.`transactionId` AS `goods.TransactionGood.transactionId`
FROM
  (SELECT `Transaction`.`id`,
          `Transaction`.`userId`,
          `Transaction`.`status`,
          `Transaction`.`channel`,
          `Transaction`.`createdAt`,
          `Transaction`.`updatedAt`
   FROM `Transactions` AS `Transaction`
   WHERE `Transaction`.`userId` = '30e1c140-d4d0-4edb-980a-3d7b9a47a958'
   ORDER BY `Transaction`.`createdAt` ASC
   LIMIT 10) AS `Transaction`
LEFT OUTER JOIN `Users` AS `user` ON `Transaction`.`userId` = `user`.`id`
LEFT OUTER JOIN (`TransactionGoods` AS `goods->TransactionGood`
                 INNER JOIN `Goods` AS `goods` ON `goods`.`id` = `goods->TransactionGood`.`goodId`) ON `Transaction`.`id` = `goods->TransactionGood`.`transactionId`
ORDER BY `Transaction`.`createdAt` ASC;

Dialect: mysql Database version: 5.7.18 Sequelize version: 4.38.0 Tested with latest release: Yes

Note : Your issue may be ignored OR closed by maintainers if it’s not tested against latest version OR does not follow issue template.