Eager load and limit does not produce valid sql, regression from sequelize 3

[linpekka]

What are you doing?

When upgrading from sequelize 3, i noticed that some lookups does no longer produce valid sql. This is related to #9869, and has been tested against PR #9900, but is not affected by the pull request.

'use strict';

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

let A, B, C;

async function test() {

  const db = new Sequelize('test', 'user', 'user', {
    host: '127.0.0.1',
    dialect: 'postgresql',
    "quoteIdentifiers": true, 
    "underscored": true
  });

  A = db.define('A', { str: Sequelize.STRING }, {});
  B = db.define('B', { a_id : Sequelize.INTEGER, c_id : Sequelize.INTEGER }, {});
  C = db.define('C', { str: Sequelize.STRING }, {});

  B.belongsTo(A, {foreignKey: 'a_id'});
  A.hasMany(B, {foreignKey: 'a_id'});

  B.belongsTo(C, {foreignKey: 'c_id'});
  C.hasMany(B, {foreignKey: 'c_id'});

  await db.sync({ force: true });

  await populateDb();

  console.log('Find that does not reproduce the issue')
  const t1 = await A.findAll({
    where: {str:'a'}, 
    include:[
    { model: B, required:true, include:{model:C, where:{str:'a'}} }
  ]});

  console.log('Find that reproduces the issue, only limit differs')
  const t2 = await A.findAll({
    where: {str:'a'}, 
    limit: 6,
    include:[
    { model: B, required:true, include:{model:C, where:{str:'a'}} }
  ]});

  await db.close();
}


async function populateDb() {
  const c = await C.create({ str:'a' });
  const a = await A.create({ str:'a' });
  const b = await B.create({ c_id:c.id, a_id:a.id });
}

async function main() {
  try {
    console.log('Bad sql');
    await test();
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
}

main();

What do you expect to happen?

I want the findAll to generate valid sql

What is actually happening?

Got an error - error: missing FROM-clause entry for table "Bs"

Output, either JSON or SQL

SELECT "A".*, "Bs"."id" AS "Bs.id", "Bs"."a_id" AS "Bs.a_id", "Bs"."c_id" AS "Bs.c_id", "Bs"."createdAt" AS "Bs.createdAt", "Bs"."updatedAt" AS "Bs.updatedAt" FROM (SELECT "A"."id", "A"."str", "A"."createdAt", "A"."updatedAt", "Bs->C"."id" AS "Bs.C.id", "Bs->C"."str" AS "Bs.C.str", "Bs->C"."createdAt" AS "Bs.C.createdAt", "Bs->C"."updatedAt" AS "Bs.C.updatedAt" FROM "As" AS "A" INNER JOIN "Cs" AS "Bs->C" ON "Bs"."c_id" = "Bs->C"."id" AND "Bs->C"."str" = 'a' WHERE "A"."str" = 'a' AND ( SELECT "Bs"."a_id" FROM "Bs" AS "Bs" INNER JOIN "Cs" AS "C" ON "Bs"."c_id" = "C"."id" AND "C"."str" = 'a' WHERE ("Bs"."a_id" = "A"."id") LIMIT 1 ) IS NOT NULL LIMIT 6) AS "A" INNER JOIN "Bs" AS "Bs" ON "A"."id" = "Bs"."a_id";

__Dialect:postgres
__Dialect version:(PostgreSQL) 9.6.5
__Database version:(PostgreSQL) 9.6.5
__Sequelize version:4.38.0
__Tested with latest release:4.38.0

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

[sushantdhiman]

Please keep discussion to same issue #9869 which is focusing on this regression. PR can always be improved to handle different cases

[linpekka]

The steps to reproduce is different, and the pull request does not affect this one, so it seems like a separate issue from a users perspective, but I can add this as a comment to the other one if you desire

[sushantdhiman]

#9869 is regression related to sub queries which can be triggered by limit in various forms, including this one, PR is just an attempt not a fix all solution