Unknown column in 'on clause' when using limit & offset

[abramz]

This may be the same root cause as #9157, but I am not totally sure. If it is, feel free to close and I'm sorry.

What are you doing?

const Promise = require('bluebird');
const Sequelize = require('sequelize');

const opts = { freezeTableName: true };
const db = new Sequelize(config.name, config.user, config.password, {
  host: config.host,
  port: config.port,
  dialect: config.dialect,
  logging: config.logging,
});

const User = db.define(
  'user',
  {
    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.UUIDV4,
    },
    email: {
      type: Sequelize.STRING,
      allowNull: false,
    },
    deleted: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  },
  opts
);
const Organization = db.define(
  'organization',
  {
    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.UUIDV4,
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false,
    },
    deleted: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  },
  opts
);
const Profile = db.define(
  'profile',
  {
    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.UUIDV4,
    },
    userId: {
      type: Sequelize.UUID,
      unique: 'userOrg',
    },
    organizationId: {
      type: Sequelize.UUID,
      unique: 'userOrg',
    },
    isCurrent: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    deleted: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  },
  opts
);

Organization.Profiles = Organization.hasMany(Profile, { as: 'profiles' });
Profile.Organization = Profile.belongsTo(Organization, {
  as: 'organization',
});
Profile.User = Profile.belongsTo(User, { as: 'user' });
User.Profiles = User.hasMany(Profile, { as: 'profiles' });

db
  .sync({ force: true })
  .then(() =>
    Promise.all([
      Organization.create({ name: 'my organization' }),
      User.create({ email: 'myemail@example.com' }),
      Profile.create({ isCurrent: true }),
    ])
  )
  .then(([organization, user, profile]) =>
    profile.update({ userId: user.id, organizationId: organization.id })
  )
  .then(() =>
    User.findAll({
      where: { deleted: false },
      include: [
        {
          model: Profile,
          as: 'profiles',
          where: { deleted: false },
          include: [
            {
              model: Organization,
              as: 'organization',
              where: { deleted: false },
            },
          ],
        },
      ],
      // comment these out & the query is successful  
      offset: 0,
      limit: 50,
    })
  )
  .then(users => console.log(users[0] ? users[0].toJSON() : 'nothing'))
  .catch(error => console.error(error))
  .finally(() => process.exit());

What do you expect to happen?

I expected to get an array w/ the single user that I created. The results should be exactly the same as the query below.

User.findAll({
      where: { deleted: false },
      include: [
        {
          model: Profile,
          as: 'profiles',
          where: { deleted: false },
          include: [
            {
              model: Organization,
              as: 'organization',
              where: { deleted: false },
            },
          ],
        },
      ],
    })

The result of the above query:

{
	"id": "42e039a6-241c-43cf-a8f0-0ca454f330da",
	"email": "myemail@example.com",
	"deleted": false,
	"createdAt": "2018-03-09T21:56:44.000Z",
	"updatedAt": "2018-03-09T21:56:44.000Z",
	"profiles": [{
		"id": "899f22ad-8277-43f2-94c7-4652aa501520",
		"userId": "42e039a6-241c-43cf-a8f0-0ca454f330da",
		"organizationId": "dd1c29f1-f0a7-4a05-b68a-d65e4f85995a",
		"isCurrent": true,
		"deleted": false,
		"createdAt": "2018-03-09T21:56:44.000Z",
		"updatedAt": "2018-03-09T21:56:44.000Z",
		"organization": {
			"id": "dd1c29f1-f0a7-4a05-b68a-d65e4f85995a",
			"name": "my organization",
			"deleted": false,
			"createdAt": "2018-03-09T21:56:44.000Z",
			"updatedAt": "2018-03-09T21:56:44.000Z"
		}
	}]
}

What is actually happening?

I get a ER_BAD_FIELD_ERROR exception. The SQL that is executed is also incredibly complicated given what we are actually trying to retrieve.

error:

Error: Unknown column 'profiles.organizationId' in 'on clause'
    at Packet.asError (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/packets/packet.js:713:13)
    at Query.Command.execute (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/commands/command.js:28:22)
    at Connection.handlePacket (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/connection.js:502:28)
    at PacketParser.onPacket (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/connection.js:81:16)
    at PacketParser.executeStart (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/packet_parser.js:77:14)
    at Socket.<anonymous> (/Users/andrew/source/sqlizeweird/node_modules/mysql2/lib/connection.js:89:29)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:594:20)

generated query:

SELECT `user`.*, 
       `profiles`.`id`             AS `profiles.id`, 
       `profiles`.`userid`         AS `profiles.userId`, 
       `profiles`.`organizationid` AS `profiles.organizationId`, 
       `profiles`.`iscurrent`      AS `profiles.isCurrent`, 
       `profiles`.`deleted`        AS `profiles.deleted`, 
       `profiles`.`createdat`      AS `profiles.createdAt`, 
       `profiles`.`updatedat`      AS `profiles.updatedAt` 
FROM   (SELECT `user`.`id`, 
               `user`.`email`, 
               `user`.`deleted`, 
               `user`.`createdat`, 
               `user`.`updatedat`, 
               `profiles->organization`.`id`        AS 
               `profiles.organization.id`, 
               `profiles->organization`.`name`      AS 
               `profiles.organization.name`, 
               `profiles->organization`.`deleted`   AS 
               `profiles.organization.deleted`, 
               `profiles->organization`.`createdat` AS 
                      `profiles.organization.createdAt`, 
               `profiles->organization`.`updatedat` AS 
                      `profiles.organization.updatedAt` 
        FROM   `user` AS `user` 
               INNER JOIN `organization` AS `profiles->organization` 
                       ON `profiles`.`organizationid` = 
                          `profiles->organization`.`id` 
                          AND `profiles->organization`.`deleted` = false 
        WHERE  `user`.`deleted` = false 
               AND (SELECT `userid` 
                    FROM   `profile` AS `profiles` 
                    WHERE  ( `profiles`.`userid` = `user`.`id` 
                             AND `profiles`.`deleted` = false ) 
                    LIMIT  1) IS NOT NULL 
        LIMIT  0, 50) AS `user` 
       INNER JOIN `profile` AS `profiles` 
               ON `user`.`id` = `profiles`.`userid` 
                  AND `profiles`.`deleted` = false; 

expected query:

SELECT *
    FROM user u
    JOIN profile p
    ON u.id = p.userId
    AND p.deleted IS NOT FALSE
    JOIN organization o
    ON p.organizationId = o.id
    AND o.deleted IS NOT FALSE
    WHERE u.deleted IS NOT FALSE
    LIMIT 0, 50;

__Dialect: mysql
__Dialect version: mysql2@1.5.2
__Database version: 5.7.21
__Sequelize version: 4.35.2
__Tested with latest release: Yes, 4.35.2

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

[VishalDiyora]

+1

[912187345]

+1

[tiagoajacobi]

+1

[manV]

@abramz issue #9157 was addressed in v4.37.4(PR #9188) so can you try reproducing this with latest version?

[abramz]

Yes, this is resolved. Thank you.

[jcnevess]

I've had this same issue and, as of v4.37.6, using subQuery: false did the trick for me, even though I don't know exactly how this option works. It does not appear to be in the official docs yet.

[ae94]

Still having this issue when setting required: true on at least one include.

[kernel-memory-dump]

@jcnsilva ty, subQuery: false did the trick for me as well

[sushantdhiman]

Moved to #9869

[goalia]

You save my day 🎉