Problems referencing associated query conditions that are not primary key fields

Problems referencing associated query conditions that are not primary key fields | BUG!

Bug Description

// Account table model
const Account = sequelize.define('account', {
    pk: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        comment: '主键'
    },
    userId: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        comment: '用户ID'
    }
}, {
    indexes: [{
        unique: true,
        fields: ['userId']
    }]
})

// Role table model
const Role = sequelize.define('account', {
    pk: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        comment: '主键'
    },
    roleId: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        comment: '角色ID'
    }
}, {
    indexes: [{
        unique: true,
        fields: ['roleId']
    }]
})

// Association table of accounts and roles
const RoleConfig = sequelize.define('roleConfig', {
    pk: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        comment: '主键'
    },
    roleId: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        comment: '角色ID'
    },
    userId: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        comment: '用户ID'
    }
})


 // The role table is associated with the account table using a join table
 RoleConfig.associate = function () {
    Account.belongsToMany(Role, {
        through: RoleConfig,
        sourceKey: 'userId',
        targetKey: 'roleId',
        foreignKey: 'userId'
    })
    Role.belongsToMany(Account, {
        through: RoleConfig,
        sourceKey: 'roleId',
        targetKey: 'userId',
        foreignKey: 'roleId'
    })
}



// Query the account table and associated role data, and filter the data in the primary table based on the search criteria of the role ID. required: true
const result = await Account.findAndCountAll({
    include: [
        {
            model: Role,
            required: true,
            through: {
                where: {
                    roleId: 1
                }
            }
        }
    ]
})


console.log('result', result);   // out {count: 4, rows: Array(0)}  The length of rows is equal to 0 

What do you expect to happen?

The query result is as follows: {count: 4, rows: Array(4)}

1. The length of rows is equal to the value of count.
2. count Indicates the statistics filtered from the primary table.
3. row Indicates the data filtered from the primary table.
4. As the primary table, you can filter the data of the account table based on the query criteria of the role table. The returned result is the data and statistics of the account table

What is actually happening?

The roleId=1 data in the role table exists and is associated with the account in the association table

Or the where: {} condition does not find data, and count is error.

Query result:

{count: 4, rows: Array(0)}

I can't find the data, but the statistics are correct.

Here is the full SQL statement:

 SELECT `account`.*, `company`.`pk` AS `company.pk`, `company`.`companyId` AS `company.companyId`, `company`.`companyName` AS `company.companyName`, `company`.`unifiedSocialCreditID` AS `company.unifiedSocialCreditID`, `company`.`enterpriseAddress` AS `company.enterpriseAddress`, `company`.`account` AS `company.account`, `company`.`password` AS `company.password`, `company`.`landlineNumber` AS `company.landlineNumber`, `company`.`menuStrictly` AS `company.menuStrictly`, `company`.`locale` AS `company.locale`, `company`.`expireDate` AS `company.expireDate`, `company`.`status` AS `company.status`, `company`.`remark` AS `company.remark`, `company`.`createdAt` AS `company.createdAt`, `company`.`updatedAt` AS `company.updatedAt`, `roles`.`pk` AS `roles.pk`, `roles`.`companyId` AS `roles.companyId`, `roles`.`roleId` AS `roles.roleId`, `roles`.`roleName` AS `roles.roleName`, `roles`.`authScope` AS `roles.authScope`, `roles`.`menuStrictly` AS `roles.menuStrictly`, `roles`.`orgStrictly` AS `roles.orgStrictly`, `roles`.`status` AS `roles.status`, `roles`.`remark` AS `roles.remark`, `roles`.`createdAt` AS `roles.createdAt`, `roles`.`updatedAt` AS `roles.updatedAt`, `roles->roleConfig`.`pk` AS `roles.roleConfig.pk`, `roles->roleConfig`.`companyId` AS `roles.roleConfig.companyId`, `roles->roleConfig`.`roleId` AS `roles.roleConfig.roleId`, `roles->roleConfig`.`userId` AS `roles.roleConfig.userId`, `roles->roleConfig`.`createdAt` AS `roles.roleConfig.createdAt`, `roles->roleConfig`.`updatedAt` AS `roles.roleConfig.updatedAt` FROM (SELECT `account`.`pk`, `account`.`companyId`, `account`.`organizationalId`, `account`.`userId`, `account`.`account`, `account`.`password`, `account`.`userName`, `account`.`mobilePhone`, `account`.`email`, `account`.`locale`, `account`.`status`, `account`.`remark`, `account`.`createdAt`, `account`.`updatedAt`, `organizational`.`pk` AS `organizational.pk`, `organizational`.`companyId` AS `organizational.companyId`, `organizational`.`parentId` AS `organizational.parentId`, `organizational`.`organizationalId` AS `organizational.organizationalId`, `organizational`.`organizationalName` AS `organizational.organizationalName`, `organizational`.`organizationalType` AS `organizational.organizationalType`, `organizational`.`remark` AS `organizational.remark`, `organizational`.`createdAt` AS `organizational.createdAt`, `organizational`.`updatedAt` AS `organizational.updatedAt` FROM `account` AS `account` INNER JOIN `organizational` AS `organizational` ON `account`.`organizationalId` = `organizational`.`organizationalId` WHERE `account`.`companyId` = 1 AND `account`.`userId` IN (1, 2) ORDER BY `account`.`updatedAt` DESC LIMIT 0, 50) AS `account` LEFT OUTER JOIN `company` AS `company` ON `account`.`companyId` = `company`.`companyId` LEFT OUTER JOIN ( `roleConfig` AS `roles->roleConfig` INNER JOIN `role` AS `roles` ON `roles`.`roleId` = `roles->roleConfig`.`roleId`) ON `account`.`userId` = `roles->roleConfig`.`userId` ORDER BY `account`.`updatedAt` DESC;

SELECT count(`account`.`pk`) AS `count` FROM `account` AS `account` LEFT OUTER JOIN `company` AS `company` ON `account`.`companyId` = `company`.`companyId` INNER JOIN `organizational` AS `organizational` ON `account`.`organizationalId` = `organizational`.`organizationalId` LEFT OUTER JOIN ( `roleConfig` AS `roles->roleConfig` INNER JOIN `role` AS `roles` ON `roles`.`roleId` = `roles->roleConfig`.`roleId`) ON `account`.`userId` = `roles->roleConfig`.`userId` WHERE `account`.`companyId` = 1 AND `account`.`userId` IN (1, 2);

Data cannot be queried if the following conditions occur:

AND ( SELECT roleConfig.pk FROM roleConfig AS roleConfig INNER JOIN role AS role ON roleConfig.roleId = role.pk WHERE (account.pk = roleConfig.userId) LIMIT 1 )

The pk of the account table is equal to the userId of the associated table. Why is this?
This condition exists even if constraints are cancelled with constraints: false

Is this a BUG?

Or is it my way of writing?

thank you!

Environment

• Sequelize version: -- egg-sequelize@6.0.0 -- sequelize@6.35.2
• Node.js version: v14.21.3
• Database & Version: mysql Server 5.7
• Connector library & Version: mysql2

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

Start with the Account.belongsToMany(Role) and Role.belongsToMany(Account) definitions and the Account.findAndCountAll call shown in the report. Reproduce the generated MySQL queries using the non-primary sourceKey and targetKey with through.where, then compare the rows query with the count query. Done means the associated filter returns the expected account rows and matching count, with a regression test covering the case.