FindAll Lookup With where, include, and limit on hasMany does not produce valid sql

[cass-m-g]

What are you doing?

The lookup for a findall on a hasMany relationship is not producing the correct sql. The inner select statement does not contain all the necessary joins.

It looks like there is already a PR that is attempting to fix this issue (#9900), however pulling in a patch from that PR doesn't address this problem either.

'use strict';

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

let Author, Book;

/**
 * @param {boolean} useEnum
 */
async function test(useEnum) {
  const db = new Sequelize('test', 'postgres', '', {
    host: 'localhost',
    dialect: 'postgresql',
    "logging": true
  });


  Author = db.define(
    "Author",
    {
      id: { type: Sequelize.DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
      firstName: Sequelize.DataTypes.STRING,
      lastName: Sequelize.DataTypes.STRING
    },
    {}
  );
  Book = db.define(
    "Book",
    {
      id: { type: Sequelize.DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
      title: {type: Sequelize.DataTypes.STRING, unique: true},
      authorId: { type: Sequelize.DataTypes.INTEGER, allowNull: false, unique: false }
    },
    {}
  );
  Book.belongsTo(Author, { foreignKey: "authorId" });
  Author.hasMany(Book, { foreignKey: "authorId" });

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

  await populateDb();

  console.log('Find that reproduces the issue')
  const t1 = await Author.findAll(
      { where: { '$Books.title$': { '$iLike': '%book%' } },
        include:
          [ { association: 'Books',
            required: false,
            include: []} ],
        limit: 1,
        order: [ [ 'id', 'ASC' ] ]
      });

  await db.close();
}

async function populateDb() {
  const tom = await Author.create({
    firstName: "tom",
    lastName: "smith"
  });
  const bob = await Author.create({
    firstName: "bob",
    lastName: "john"
  });
  const debby = await Author.create({
    firstName: "debby",
    lastName: "pen"
  });
  const book1 = await Book.create({
    title: "book 1",
    authorId: tom.id
  });
  const book2 = await Book.create({
    title: "title 2",
    authorId: tom.id
  });
  const book3 = await Book.create({
    title: "book 3",
    authorId: bob.id
  });
  const book4 = await Book.create({
    title: "title 4",
    authorId: debby.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 was expecting this query to produce the following json output:

[
  {
    "id": 1,
    "firstName": "tom",
    "lastName": "smith",
    "Books": [
      {
        "id": 1,
        "title": "book 1"
      },
      {
        "id": 2,
        "title": "title 2"
      }
    ]
  },
  {
    "id": 2,
    "firstName": "bob",
    "lastName": "john",
    "Books": [
      {
        "id": 1,
        "title": "book 3"
      }
    ]
  }
]

What is actually happening?

It is producing an error: missing FROM-clause entry for table "Books"

Output in SQL:

SELECT
  "Author".*,
  "Books"."id"        AS "Books.id",
  "Books"."title"     AS "Books.title",
  "Books"."authorId"  AS "Books.authorId",
  "Books"."createdAt" AS "Books.createdAt",
  "Books"."updatedAt" AS "Books.updatedAt"
FROM (SELECT
        "Author"."id",
        "Author"."firstName",
        "Author"."lastName",
        "Author"."createdAt",
        "Author"."updatedAt"
      FROM "Authors" AS "Author"
      WHERE "Books"."title" ILIKE '%book%'
      ORDER BY "Author"."id" ASC
      LIMIT 1) AS "Author" LEFT OUTER JOIN "Books" AS "Books" ON "Author"."id" = "Books"."authorId"
ORDER BY "Author"."id" ASC;

The above sql is missing a join on the inner select statement, causing the error.

Dialect: postgres
Dialect version: 2.1.4
Database version: 2.1.4
Sequelize version: 4.23.2
Tested with latest release: Yes 4.39.0 (If yes, specify that version)

[sushantdhiman]

Duplicate of #9869