limit with subQuery: false return wrong result

[husnulhamidiah]

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.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[chetanmenge]

@husnulhamidiah Were you able to figure out the issue ? I'm also observing similar issue so looking for root cause and approach to handle it

[eldadgiladi]

@husnulhamidiah @chetanmenge , did any of you figure out the issue?

[Afinetri]

sorry, but i have the same issue using Limit with subQuery : false; can any one figured it out who to solve similar issue?

[husnulhamidiah]

@chetanmenge @eldadgiladi @Afinetri No solution and workaround for this, the only option is using bare SQL with knex or bookshelf.

[davidadas]

Has there been any progress whatsoever? This is a big deal.

[AlexBykovMinsk]

Same problem. The issue is actual for now

[papb]

Hello, can anyone please convert OP's SSCCE into a failing test case?

• Fork the sequelize repository
• Add one commit on top of master (or create another branch if you prefer) that adds a failing test
• Create a PR just adding the failing test (so I can clearly see it) - you can name the PR 'failing test for 9605'
• Ping me there and link to this very comment

Thanks!!

[AlexBykovMinsk]

@papb FYI: In my case the issue takes place, when I include models with @ HasMany relations.
I solved my issue by setting "separate" flag to true

include: [
    {
        model: Offer,
        as: "offers",
        required: false,
        separate: true
    },
    {
        model: DealFund,
        as: "fund",
        include: [
            {
                model: DealFundCharge,
                as: "charges",
                required: false,
                separate: true
            }
        ]
    }
]

Alex

[agam-99]

Did anyone found any solution to this?

[dahui4dev]

Did anyone found any solution to this?

[RobinxiangbinNie]

any solution? I need add "subQuery:false" to avoid the error "RangeError [ERR_INVALID_OPT_VALUE]: The value "2147483648" is invalid for option "size"". While if I add "subQury:false", the "limit" results is not correct. I have 6 rows, while it only queried 1 row.

[Adam-Burke]

There is like 9 versions of this issue isn't there?

[aaditya]

Facing the same issue

[mostafaebrahimi]

same issue - I need to set subQuery:false but when I want to use it I expect limit to filter the first table but it's limiting all query

[ben-walters]

Same issue here, but when I use subquery: false I get all manner of incorrect results and counts... please someone fix this!

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[fkursun]

is there any update on this?

[ephys]

I don't think it's possible to make LIMIT return the desired number of items from the "main" table without using a sub-query when a JOIN is involved. (unless you use separate: true)

I think it's better to look into why you need to disable subQuery instead and fix that issue

[fkursun]

I don't think it's possible to make LIMIT return the desired number of items from the "main" table without using a sub-query when a JOIN is involved. (unless you use separate: true)

I think it's better to look into why you need to disable subQuery instead and fix that issue

well the reason I disable the subquery is that I get an error(Unknown column 'user.first_name' in 'order clause) when I don't disable it. Basically I want to order by associate model's property but it does not allow me to do it unless I disable subquery. And it returns missing data when I disable

[ephys]

@fkursun Could you open an issue (if you don't already have one) with what your query looks like, what the generated SQL is, and what you'd expect it to be?

Related PR to clarify how limit works: #13985

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[Taqiarzoo]

Did anyone find the solution
Subquery:false allow us doing deeply nested filter
I want to apply like operator on nested modal but without Subquery:false sequelize give bad field error
Also with Subquery:false on modal involving has may relationship give wrong data when apply limit

separate: true solve the Limited data problem and give all the Data applied limit

But in that case top level where clause fail
'$stones.material.name$: { [Op.like]: $'{q}'

This kind of syntax failed and give error unknown column stones.material.name

If I apply like query on each nested modals individually then also a problem because in that case like query not apply with Or operator and return zero result

If anyone have a solution to this problem plz

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[ephys]

I'll close this issue as I've now documented that limit needs subQuery to be true to work properly

Issues related to $nested.attribute.syntax$ being broken with subQuery: true will be sorted out in this thread

[IT1Furindo]

Facing same issue, if i delete subQuery: false, i got error Unknown column '$someIncludeTable.someColumn$' in 'where clause'