Model id added to an included separate query with group by that ends up breaking the query on MYSQL

[moda20]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

When running a query with a nested separate query for an included model which has a groupBy clause but not on the included model id column, sequelize forcibly adds the model id in the final SQL query and breaks the group by query on group by full mode enabled databases.

Reproducible Example

Here are a minimum version of models and the query:

sequelize.define('item', {
        name: {
            type: DataTypes.STRING(256),
            allowNull: true
        },
        product_id: {
            type: DataTypes.STRING(256),
            allowNull: true
        },
        id: {
            autoIncrement: true,
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        }
    }, {
        sequelize,
        tableName: 'item',
        timestamps: true,
        indexes: [
            {
                name: "PRIMARY",
                unique: true,
                using: "BTREE",
                fields: [
                    { name: "id" },
                ]
            },
            {
                name: "SKU_PRIMARY",
                unique: true,
                using: "BTREE",
                fields: [
                    { name: "sku" },
                ]
            },
            {
                name: "product_id__index",
                using: "BTREE",
                fields: [
                    { name: "product_id" },
                ]
            },
            {
                name: "product_id__price_index",
                using: "BTREE",
                fields: [
                    { name: "product_id" },
                ]
            },
        ]
    });

price model :

sequelize.define(
        "price",
        {
            product_id: {
                type: DataTypes.TEXT,
                allowNull: true,
            },
            id: {
                autoIncrement: true,
                type: DataTypes.INTEGER,
                allowNull: false,
                primaryKey: true,
            },
            availability: {
                type: DataTypes.BOOLEAN,
                allowNull: true,
            }
        },
        {
            sequelize,
            tableName: "price",
            timestamps: true,
            indexes: [
                {
                    name: "PRIMARY",
                    unique: true,
                    using: "BTREE",
                    fields: [{ name: "id" }],
                },
                {
                    name: "product_id__index",
                    using: "BTREE",
                    fields: [{ name: "product_id" }],
                },
            ],
        }
    )

        return item
            .findAndCountAll({
                include: [
                    {
                        model: price,
                        as: "prices",
                        attributes: [
                         
                        ],
                        separate: true,
                        group: [ "product_id"],
                    },
                ],
            })

What do you expect to happen?

i expect the nested query to look like thisk :

SELECT `product_id`, `availability` FROM `price` AS `price` WHERE `price`.`product_id` IN ('24730', '34977', '35587', '35522', '34586', '35518', '30132', '30133', '35526', '35040') GROUP BY `product_id`;

What is actually happening?

the nested query actually has the id in the selected columns which is not present in the groupBy clause thus creating an SQL issue:

SELECT `id`, `product_id`, `availability` FROM `price` AS `price` WHERE `price`.`product_id` IN ('30133', '35526', '35040') GROUP BY `product_id`;

Environment

• Sequelize version: 6.21.2
• Node.js version: v14.19.3
• If TypeScript related: TypeScript version:
• Database & Version: MYSQL 8.0.27
• Connector library & Version: mysql2 2.3.3

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

[laurelgr]

Please add Models and Associations before the query itself in your reproducible example. Creating some data before the query would be great too.

Actually, I would also recommend replacing your current example with a simplified and more generic one (no need for CONCAT or AVG COALESCE if you can just COUNT, 2 groupBy when you could do only 1, or such a specific table model).

I'm not familiar with the use of separate though so I cannot tell if this is an issue with sequelize or a missed option somewhere.

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

[moda20]

@laurelgr The separate is needed to use the group-by inside of the association call. if not used, sequelize will ignore the groupBy component of the query.

I still have this issue, and i have updated the issue with more data about my setup.

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