{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/5465","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/5465/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/5465/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/5465/events","html_url":"https://github.com/sequelize/sequelize/issues/5465","id":135202031,"number":5465,"title":"Join table includes LIMIT on queries with IN","user":{"login":"jylinman","id":150944,"avatar_url":"https://avatars.githubusercontent.com/u/150944?v=3","gravatar_id":"","url":"https://api.github.com/users/jylinman","html_url":"https://github.com/jylinman","followers_url":"https://api.github.com/users/jylinman/followers","following_url":"https://api.github.com/users/jylinman/following{/other_user}","gists_url":"https://api.github.com/users/jylinman/gists{/gist_id}","starred_url":"https://api.github.com/users/jylinman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jylinman/subscriptions","organizations_url":"https://api.github.com/users/jylinman/orgs","repos_url":"https://api.github.com/users/jylinman/repos","events_url":"https://api.github.com/users/jylinman/events{/privacy}","received_events_url":"https://api.github.com/users/jylinman/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2016-02-21T12:47:11Z","updated_at":"2016-02-22T09:13:28Z","closed_at":null,"body":"I am having some issues joining some tables, here is the situation:\r\n\r\n**Mysql Tables**\r\ntickets\r\ntickets_pools (n:m)\r\npools\r\n\r\nWhen I do a join on the tickets_pools using the `include: [models.Pools]` it joins just fine if the `tickets_pools` table has less than 20 records of relations for a `pool` to `ticket`.  If it has more than 20, I can't do a { where: [IDS] } if the IDS are falling in the > 20 records.\r\n\r\nThe code:\r\n\r\n```javascript\r\n      # scope\r\n      pools: function(pools) {\r\n        return {\r\n          distinct: ['Ticket.id'],\r\n          include: [{\r\n            model: this.db.Pool,\r\n            through: this.db.TicketPool,\r\n            where: {\r\n              id: pools\r\n            }\r\n          }]\r\n        };\r\n      }\r\n\r\n      # query\r\n      query = query.scope({\r\n        method: ['pools', pools]\r\n      });\r\n```\r\n\r\nThe SQL generated by the simple association:\r\n\r\n```sql\r\nSELECT `Ticket`.*, `Pools`.`id` AS `Pools.id`, ..... \r\n    FROM (SELECT `Ticket`.`id`, `Ticket`.`parent_id` AS `parentId`, `Ticket`.`creator_id` AS `creatorId`, ....  \r\n    WHERE `Ticket`.`deleted_at` IS NULL AND ( SELECT `tickets_pools`.`ticket_id` \r\n    FROM `tickets_pools` AS `tickets_pools` \r\nINNER JOIN `pools` AS `Pool` ON `tickets_pools`.`pool_id` = `Pool`.`id` \r\n    WHERE (`Ticket`.`id` = `tickets_pools`.`ticket_id`) \r\n    LIMIT 1 ) IS NOT NULL LIMIT 20) AS `Ticket` \r\nINNER JOIN (`tickets_pools` AS `Pools.tickets_pools` \r\nINNER JOIN `pools` AS `Pools` \r\n    ON `Pools`.`id` = `Pools.tickets_pools`.`pool_id`) \r\n    ON `Ticket`.`id` = `Pools.tickets_pools`.`ticket_id` \r\n        AND (`Pools`.`deleted_at` IS NULL AND `Pools`.`id` IN (155, 156));\r\n```\r\n\r\nAs you can see above, the *IS NOT NULL LIMIT 20* is automatically added.  Is there a way to avoid this / do it better, or is it a bug?\r\n\r\nOn a second note, I can create a separate bug, but the distinct shows only in the COUNT() query and disappears from the actual content query.  Thoughts?"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/187082955","html_url":"https://github.com/sequelize/sequelize/issues/5465#issuecomment-187082955","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/5465","id":187082955,"user":{"login":"jylinman","id":150944,"avatar_url":"https://avatars.githubusercontent.com/u/150944?v=3","gravatar_id":"","url":"https://api.github.com/users/jylinman","html_url":"https://github.com/jylinman","followers_url":"https://api.github.com/users/jylinman/followers","following_url":"https://api.github.com/users/jylinman/following{/other_user}","gists_url":"https://api.github.com/users/jylinman/gists{/gist_id}","starred_url":"https://api.github.com/users/jylinman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jylinman/subscriptions","organizations_url":"https://api.github.com/users/jylinman/orgs","repos_url":"https://api.github.com/users/jylinman/repos","events_url":"https://api.github.com/users/jylinman/events{/privacy}","received_events_url":"https://api.github.com/users/jylinman/received_events","type":"User","site_admin":false},"created_at":"2016-02-22T09:13:28Z","updated_at":"2016-02-22T09:13:28Z","body":"@mickhansen Is there something I am missing to maybe figure out where its adding LIMIT to the subquery?"}}