Filtering with nested include

[magp3]

What are you doing?

Trying to filter on an associated field.

const A = Connection.define('a');

const B = Connection.define('b');

A.belongsTo(B);
B.hasMany(A);

const C = Connection.define('c',
{
    myField:Sequelize.STRING
});


B.belongsTo(C);


const main = async()=>
{
    await Connection.sync({force:true});

    const c= await C.create({myField:"123"});
    const b = await B.create({cId:c.id});
    const a = await A.create({bId:b.id});

    const resA = await A.findAll({
        logging:console.log,
        include:[{model:B,include:[{model:C,where:{myField:"dontCare"}}]}]
    });
    const resB = await A.findAll({
        logging:console.log,
        include:[{model:B,where:{id:{[Op.gt]:0}},include:[{model:C,where:{myField:"dontCare"}}]}]
    });
    console.log(resA.map(a=>a.get({plain:true})));
    console.log(resB.map(a=>a.get({plain:true})));
};

main();

To Reproduce
Run the code above.

What do you expect to happen?

I'm not entirely sure what the correct way of doing this kind of filtering is. But
I did not expect these 2 queries to produce a different result like this.

What is actually happening?

The first query runs the nested where on a different level from the second query. Seems inconsistent.
The inner where clause is shifted out when I include the useless filtering
where:{id:{[Op.gt]:0}}

Output:

SELECT `a`.`id`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`bId`, `b`.`id` AS `b.id`, `b`.`createdAt` AS 
`b.createdAt`, `b`.`updatedAt` AS `b.updatedAt`, `b`.`cId` AS `b.cId`, `b->c`.`id` AS
 `b.c.id`, `b->c`.`myField` AS `b.c.myField`, `b->c`.`createdAt` AS `b.c.createdAt`, `b->c`.`updatedAt` AS 
`b.c.updatedAt` FROM `as` AS `a` LEFT OUTER JOIN ( `bs` AS `b` INNER JOIN `cs` AS `b->c` ON
 `b`.`cId` = `b->c`.`id` AND `b->c`.`myField` = 'dontCare' ) ON `a`.`bId` = `b`.`id`;

SELECT `a`.`id`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`bId`, `b`.`id` AS `b.id`, `b`.`createdAt` AS 
`b.createdAt`, `b`.`updatedAt` AS `b.updatedAt`, `b`.`cId` AS
 `b.cId`, `b->c`.`id` AS `b.c.id`, `b->c`.`myField` AS `b.c.myField`, `b->c`.`createdAt` AS
 `b.c.createdAt`, `b->c`.`updatedAt` AS `b.c.updatedAt` FROM `as` AS `a` INNER JOIN 
`bs` AS `b` ON `a`.`bId` = `b`.`id` AND `b`.`id` > 0 INNER JOIN `cs` AS `b->c` ON 
`b`.`cId` = `b->c`.`id` AND `b->c`.`myField` = 'dontCare';

Environment

Dialect:

• [X ] mysql
  Dialect library version: 1.6.5
  Database version: XXX
  Sequelize version: 5.8.7

[papb]

Can you please post the outputs of the following lines:

console.log(resA.map(a=>a.get({plain:true})));
console.log(resB.map(a=>a.get({plain:true})));

[magp3]

Thanks for the response papb!

Here is the output.

[ { id: 1,
    createdAt: 2019-07-29T08:35:45.000Z,
    updatedAt: 2019-07-29T08:35:45.000Z,
    bId: 1,
    b: null } ]
[]

[papb]

I was able to reproduce. Thank you very much.

By the way, replacing "dontCare" by "123" oddly produces the same result!! (i.e. the bug does not happen)

[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. 🙂