Wrong query result with include association

[ALjean]

What are you doing?

Hello.
After last updates sequelize. Tests for my application broke. I have got wrong query result.

The method that produces the wrong result

 async getApplication(product_id) {
            return sequelize.models.Application.findOne({
                include: [{
                    association: sequelize.models.Application.associations.products,
                    where: {id:product_id}
                }]
            });
        }

I have next data in the database:
application_product table data
| id | app_id |
| 14 | 1 |
| 15 | 2 |

application table data
| id | name |
| 1 | test_1 |
| 2 | test_2 |

Models

class Application extends sequelize.Model {
    static associate(models) {
        Application.hasMany(models.ApplicationProduct, {foreignKey: 'app_id', as: 'products'});
    }
}

Application.init({
    id: {
        type: DataTypes.INTEGER.UNSIGNED,
        primaryKey: true,
        autoIncrement: true
    },
//....
});

class ApplicationProduct extends sequelize.Model {

    static associate(models) {
        ApplicationProduct.belongsTo(models.Application, {foreignKey: 'app_id', as: 'application'});
    }
}

ApplicationProduct.init({

    id: {
        type: DataTypes.INTEGER.UNSIGNED,
        primaryKey: true,
        autoIncrement: true
    },
    app_id: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false
    },

    //....
});

What do you expect to happen?

I wanted get the same result when i set different args.

When i use "sequelize": "=4.36.0" I get a working query.

SELECT `Application`.*, `products`.`id` AS `products.id`, `products`.`app_id` AS `products.app_id`,  FROM `applications` AS `Application` WHERE ( SELECT `app_id` FROM `application_products` AS `products` WHERE (`products`.`app_id` = `Application`.`id` AND `products`.`id` = 15) LIMIT 1 ) IS NOT NULL LIMIT 1) AS `Application` INNER JOIN `application_products` AS `products` ON `Application`.`id` = `products`.`app_id` AND `products`.`id` = 15;

(products.app_id=Application.idANDproducts.id = 15) We have diff

What is actually happening?

I execute query with two different args But I get different results
If product_id = 14 I get the application. But if I try send 15 I don't get any result

SELECT `Application`.*, `products`.`id` AS `products.id`, `products`.`app_id` AS `products.app_id`,  FROM `applications` AS `Application` WHERE ( SELECT `app_id` FROM `application_products` AS `products` WHERE (`products`.`app_id` = `Application`.`id`) LIMIT 1 ) IS NOT NULL LIMIT 1) AS `Application` INNER JOIN `application_products` AS `products` ON `Application`.`id` = `products`.`app_id` AND `products`.`id` = 14

SELECT `Application`.*, `products`.`id` AS `products.id`, `products`.`app_id` AS `products.app_id`,  FROM `applications` AS `Application` WHERE ( SELECT `app_id` FROM `application_products` AS `products` WHERE (`products`.`app_id` = `Application`.`id`) LIMIT 1 ) IS NOT NULL LIMIT 1) AS `Application` INNER JOIN `application_products` AS `products` ON `Application`.`id` = `products`.`app_id` AND `products`.`id` = 15

In first query application will be found but the following calls will give an empty result. Data in the database exists.

Dialect: mysql
Database version: 5.6
Sequelize version: 4.37.4
Tested with latest release: Yes

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

[jorrit]

I think #9188 broke it, because v4.37.3 works and v4.37.4 doesn't.

Query from v4.37.3:

SELECT
  `User`.*,
  `logins`.`name`            AS `logins.name`,
  `logins`.`key`             AS `logins.key`,
  `logins`.`createdAt`       AS `logins.createdAt`,
  `logins`.`updatedAt`       AS `logins.updatedAt`,
  `logins`.`userid`          AS `logins.userid`,
  `profile`.`userid`         AS `profile.userid`,
  `profile`.`slug`           AS `profile.slug`,
  `profile`.`profileimageid` AS `profile.profileimageid`,
  `profile`.`displayname`    AS `profile.displayname`,
  `profile`.`picture`        AS `profile.picture`,
  `profile`.`gender`         AS `profile.gender`,
  `profile`.`location`       AS `profile.location`,
  `profile`.`website`        AS `profile.website`,
  `profile`.`createdAt`      AS `profile.createdAt`,
  `profile`.`updatedAt`      AS `profile.updatedAt`
FROM (SELECT
        `User`.`id`,
        `User`.`email`,
        `User`.`emailconfirmed`,
        `User`.`newemail`,
        `User`.`newemailsecret`,
        `User`.`lostpasswordsecret`,
        `User`.`password`,
        `User`.`salt`,
        `User`.`newsletter`,
        `User`.`createdAt`,
        `User`.`updatedAt`
      FROM `users` AS `User`
      WHERE (SELECT `userid`
             FROM `userlogins` AS `logins`
             WHERE (`logins`.`userid` = `User`.`id` AND (`logins`.`name` = 'facebook' AND `logins`.`key` = '10208711861426488'))
             LIMIT 1) IS NOT NULL
      LIMIT 1) AS `User` INNER JOIN `userlogins` AS `logins` ON `User`.`id` = `logins`.`userid` AND `logins`.`name` = 'facebook' AND `logins`.`key` = 'xxxx'
  LEFT OUTER JOIN `userprofiles` AS `profile` ON `User`.`id` = `profile`.`userid`

Query from v4.37.4:

SELECT
  `User`.*,
  `logins`.`name`            AS `logins.name`,
  `logins`.`key`             AS `logins.key`,
  `logins`.`createdAt`       AS `logins.createdAt`,
  `logins`.`updatedAt`       AS `logins.updatedAt`,
  `logins`.`userid`          AS `logins.userid`,
  `profile`.`userid`         AS `profile.userid`,
  `profile`.`slug`           AS `profile.slug`,
  `profile`.`profileimageid` AS `profile.profileimageid`,
  `profile`.`displayname`    AS `profile.displayname`,
  `profile`.`picture`        AS `profile.picture`,
  `profile`.`gender`         AS `profile.gender`,
  `profile`.`location`       AS `profile.location`,
  `profile`.`website`        AS `profile.website`,
  `profile`.`createdAt`      AS `profile.createdAt`,
  `profile`.`updatedAt`      AS `profile.updatedAt`
FROM (SELECT
        `User`.`id`,
        `User`.`email`,
        `User`.`emailconfirmed`,
        `User`.`newemail`,
        `User`.`newemailsecret`,
        `User`.`lostpasswordsecret`,
        `User`.`password`,
        `User`.`salt`,
        `User`.`newsletter`,
        `User`.`createdAt`,
        `User`.`updatedAt`
      FROM `users` AS `User`
      WHERE (SELECT `userid`
             FROM `userlogins` AS `logins`
             WHERE (`logins`.`userid` = `User`.`id`)
             LIMIT 1) IS NOT NULL
      LIMIT 1) AS `User` INNER JOIN `userlogins` AS `logins` ON `User`.`id` = `logins`.`userid` AND `logins`.`name` = 'facebook' AND `logins`.`key` = '10208711861426488'
  LEFT OUTER JOIN `userprofiles` AS `profile` ON `User`.`id` = `profile`.`userid`;

Personally I think both queries are unnecessarily complex. What is the point of the inner-most inner query when there is an INNER JOIN to the same table, making the same check as the inner query?

Code:

  return User.findOne({
    include: [{
      model: UserLogin,
      as: 'logins',
      where: { name: loginName, key: loginKey },
    }, {
      model: UserProfile,
      as: 'profile',
    }],
  });

[rusekr]

Oh, found related to last update bug too. findOne with filter by hasMany assocation`s id broken, but findAll with same query works.

[rusekr]

But that fix #9188 is very important. Try to replace with findAll and get [0] result as workaround before someone'll fix broken "findOne" (:

[jharting]

Apologies for the regression. I am working on a fix.

[jharting]

@ALjean @jorrit @rusekr #9247 should fix the problem. Can you verify?

[sushantdhiman]

v4.37.5