{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/2886","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/2886/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/2886/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/2886/events","html_url":"https://github.com/sequelize/sequelize/issues/2886","id":53951203,"number":2886,"title":"Limit doesn't work as I expect","user":{"login":"milushov","id":424326,"avatar_url":"https://avatars.githubusercontent.com/u/424326?v=3","gravatar_id":"","url":"https://api.github.com/users/milushov","html_url":"https://github.com/milushov","followers_url":"https://api.github.com/users/milushov/followers","following_url":"https://api.github.com/users/milushov/following{/other_user}","gists_url":"https://api.github.com/users/milushov/gists{/gist_id}","starred_url":"https://api.github.com/users/milushov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/milushov/subscriptions","organizations_url":"https://api.github.com/users/milushov/orgs","repos_url":"https://api.github.com/users/milushov/repos","events_url":"https://api.github.com/users/milushov/events{/privacy}","received_events_url":"https://api.github.com/users/milushov/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":14,"created_at":"2015-01-10T08:24:00Z","updated_at":"2015-10-14T16:20:16Z","closed_at":null,"body":"Hello, thanks for sequelize, but I stuck with problem:\r\n\r\nHere's my code:\r\n\r\n```coffeescript\r\nmodels.Product.belongsToMany(models.Category, through: 'CategoriesProducts')\r\nmodels.Category.belongsToMany(models.Product, through: 'CategoriesProducts')\r\n\r\n...\r\n     \r\ndb.Product.findAll(\r\n  order: 'Product.id asc'\r\n\r\n  limit: params.limit\r\n  offset: params.offset\r\n\r\n  where:\r\n    merchantId: params.merchants,\r\n    price:\r\n      gte: params.price.min\r\n      lte: params.price.max\r\n\r\n  include:\r\n    model: db.Category\r\n    where:\r\n      id: params.categories\r\n).then (items) ->\r\n  ...\r\n```\r\n\r\nand it's build next query:\r\n\r\n```sql\r\nSELECT `Product`.*, \r\n       `categories`.`id`                            AS `Categories.id`, \r\n       ...\r\n       `Categories.CategoriesProducts`.`createdat`  AS \r\n       `Categories.CategoriesProducts.createdAt`, \r\n       ...\r\nFROM   (SELECT `Product`.`id`, \r\n               `Product`.`image`, \r\n                ...\r\n        FROM   `products` AS `Product` \r\n        WHERE  `Product`.`merchantid` IN ( '1', '2' ) \r\n               AND `Product`.`price` >= '0' \r\n               AND `Product`.`price` <= '10000' \r\n               AND (SELECT `categoriesproducts`.`productid` \r\n                    FROM   `categoriesproducts` AS `CategoriesProducts` \r\n                           INNER JOIN `categories` AS `Category` \r\n                                   ON `categoriesproducts`.`categoryid` = \r\n                                      `Category`.`id` \r\n                    WHERE  `Product`.`id` = `categoriesproducts`.`productid` \r\n                    LIMIT  1) IS NOT NULL \r\n        LIMIT  0, 12) AS `Product` \r\n       INNER JOIN `categoriesproducts` AS `Categories.CategoriesProducts` \r\n               ON `Product`.`id` = `Categories.CategoriesProducts`.`productid` \r\n       INNER JOIN `categories` AS `Categories` \r\n               ON `categories`.`id` = \r\n`Categories.CategoriesProducts`.`categoryid` \r\n                  AND `categories`.`id` IN ( '1' ) \r\nORDER  BY product.id ASC; \r\n```\r\n\r\nit's mean that I often get less than 12 rows, but I expect something like that:\r\n```sql\r\nSELECT `Product`.`id`, \r\n      ...\r\n       `categories`.`id`                            AS `Categories.id`,     \r\n      ...\r\n       `Categories.CategoriesProducts`.`createdat`  AS   `Categories.CategoriesProducts.createdAt`, \r\n      ...\r\nFROM   `products` AS `Product` \r\n       INNER JOIN `categoriesproducts` AS `Categories.CategoriesProducts` \r\n               ON `Product`.`id` = `Categories.CategoriesProducts`.`productid` \r\n       INNER JOIN `categories` AS `Categories` \r\n               ON `categories`.`id` = `Categories.CategoriesProducts`.`categoryid` \r\n                  AND `categories`.`id` IN ( '1' ) \r\nWHERE  `Product`.`merchantid` IN ( '1', '2' ) \r\n       AND `Product`.`price` >= '0' \r\n       AND `Product`.`price` <= '10000' \r\nORDER  BY product.id ASC \r\nLIMIT 12 OFFSET 0; \r\n```\r\n\r\nI get this built query just by deleting\r\n ```\r\nlimit: params.limit\r\noffset: params.offset\r\n```\r\nfrom parameters object and write ``` LIMIT 12 OFFSET 0; ``` manually. \r\n\r\nQuestion: how to get what I expect (returning 12 rows) or how to get built query for manually changing it?"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/148104819","html_url":"https://github.com/sequelize/sequelize/issues/2886#issuecomment-148104819","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/2886","id":148104819,"user":{"login":"abossard","id":86611,"avatar_url":"https://avatars.githubusercontent.com/u/86611?v=3","gravatar_id":"","url":"https://api.github.com/users/abossard","html_url":"https://github.com/abossard","followers_url":"https://api.github.com/users/abossard/followers","following_url":"https://api.github.com/users/abossard/following{/other_user}","gists_url":"https://api.github.com/users/abossard/gists{/gist_id}","starred_url":"https://api.github.com/users/abossard/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/abossard/subscriptions","organizations_url":"https://api.github.com/users/abossard/orgs","repos_url":"https://api.github.com/users/abossard/repos","events_url":"https://api.github.com/users/abossard/events{/privacy}","received_events_url":"https://api.github.com/users/abossard/received_events","type":"User","site_admin":false},"created_at":"2015-10-14T16:20:16Z","updated_at":"2015-10-14T16:20:16Z","body":"If I raise the limit from 68 to e.g. 999 I get all the 42 results! The sweet spot is around limit 661, from there I get all the rows.\r\n\r\nHere is the full SQL (I omitted some of the classes before):\r\n```sql\r\nSELECT \"Product\".*,\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"id\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.id\",\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"language\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.language\",\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"manufacturer_profile\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.manufacturer_profile\",\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"created_at\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.created_at\",\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"updated_at\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.updated_at\",\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"product_manufacturer_id\"\r\n       AS\r\n       \"ProductManufacturer.ProductManufacturerLabels.product_manufacturer_id\",\r\n       \"ProductLabels\".\"id\"\r\n       AS \"ProductLabels.id\",\r\n       \"ProductLabels\".\"language\"\r\n       AS \"ProductLabels.language\",\r\n       \"ProductLabels\".\"product_id\"\r\n       AS \"ProductLabels.product_id\",\r\n       \"ProductLabels\".\"product_title\"\r\n       AS \"ProductLabels.product_title\",\r\n       \"ProductLabels\".\"product_description\"\r\n       AS \"ProductLabels.product_description\",\r\n       \"ProductLabels\".\"additional_product_info\"\r\n       AS\r\n       \"ProductLabels.additional_product_info\",\r\n       \"ProductLabels\".\"delivery_time\"\r\n       AS \"ProductLabels.delivery_time\",\r\n       \"ProductLabels\".\"created_at\"\r\n       AS \"ProductLabels.created_at\",\r\n       \"ProductLabels\".\"updated_at\"\r\n       AS \"ProductLabels.updated_at\",\r\n       \"ProductCategories\".\"id\"\r\n       AS \"ProductCategories.id\",\r\n       \"ProductCategories\".\"slug\"\r\n       AS \"ProductCategories.slug\",\r\n       \"ProductCategories\".\"created_at\"\r\n       AS \"ProductCategories.created_at\",\r\n       \"ProductCategories\".\"updated_at\"\r\n       AS \"ProductCategories.updated_at\",\r\n       \"ProductCategories\".\"hierarchylevel\"\r\n       AS \"ProductCategories.hierarchyLevel\",\r\n       \"ProductCategories\".\"parentid\"\r\n       AS \"ProductCategories.parentId\",\r\n       \"ProductCategories.ProductToProductCategory\".\"created_at\"\r\n       AS\r\n       \"ProductCategories.ProductToProductCategory.created_at\",\r\n       \"ProductCategories.ProductToProductCategory\".\"updated_at\"\r\n       AS\r\n       \"ProductCategories.ProductToProductCategory.updated_at\",\r\n       \"ProductCategories.ProductToProductCategory\".\"product_id\"\r\n       AS\r\n       \"ProductCategories.ProductToProductCategory.product_id\",\r\n       \"ProductCategories.ProductToProductCategory\".\"product_category_id\"\r\n       AS\r\n       \"ProductCategories.ProductToProductCategory.product_category_id\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"id\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.id\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"language\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.language\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"title\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.title\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"created_at\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.created_at\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"updated_at\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.updated_at\",\r\n       \"ProductCategories.ProductCategoryLabels\".\"product_category_id\"\r\n       AS\r\n       \"ProductCategories.ProductCategoryLabels.product_category_id\",\r\n       \"ProductImages\".\"id\"\r\n       AS \"ProductImages.id\",\r\n       \"ProductImages\".\"product_id\"\r\n       AS \"ProductImages.product_id\",\r\n       \"ProductImages\".\"url\"\r\n       AS \"ProductImages.url\",\r\n       \"ProductImages\".\"created_at\"\r\n       AS \"ProductImages.created_at\",\r\n       \"ProductImages\".\"updated_at\"\r\n       AS \"ProductImages.updated_at\",\r\n       \"ProductPrices\".\"id\"\r\n       AS \"ProductPrices.id\",\r\n       \"ProductPrices\".\"product_id\"\r\n       AS \"ProductPrices.product_id\",\r\n       \"ProductPrices\".\"currency\"\r\n       AS \"ProductPrices.currency\",\r\n       \"ProductPrices\".\"amount\"\r\n       AS \"ProductPrices.amount\",\r\n       \"ProductPrices\".\"created_at\"\r\n       AS \"ProductPrices.created_at\",\r\n       \"ProductPrices\".\"updated_at\"\r\n       AS \"ProductPrices.updated_at\",\r\n       \"ProductColors\".\"id\"\r\n       AS \"ProductColors.id\",\r\n       \"ProductColors\".\"slug\"\r\n       AS \"ProductColors.slug\",\r\n       \"ProductColors\".\"created_at\"\r\n       AS \"ProductColors.created_at\",\r\n       \"ProductColors\".\"updated_at\"\r\n       AS \"ProductColors.updated_at\",\r\n       \"ProductColors.ProductToProductColor\".\"created_at\"\r\n       AS\r\n       \"ProductColors.ProductToProductColor.created_at\",\r\n       \"ProductColors.ProductToProductColor\".\"updated_at\"\r\n       AS\r\n       \"ProductColors.ProductToProductColor.updated_at\",\r\n       \"ProductColors.ProductToProductColor\".\"product_id\"\r\n       AS\r\n       \"ProductColors.ProductToProductColor.product_id\",\r\n       \"ProductColors.ProductToProductColor\".\"product_color_id\"\r\n       AS\r\n       \"ProductColors.ProductToProductColor.product_color_id\",\r\n       \"ProductColors.ProductColorLabels\".\"id\"\r\n       AS \"ProductColors.ProductColorLabels.id\",\r\n       \"ProductColors.ProductColorLabels\".\"language\"\r\n       AS\r\n       \"ProductColors.ProductColorLabels.language\",\r\n       \"ProductColors.ProductColorLabels\".\"color_name\"\r\n       AS\r\n       \"ProductColors.ProductColorLabels.color_name\",\r\n       \"ProductColors.ProductColorLabels\".\"created_at\"\r\n       AS\r\n       \"ProductColors.ProductColorLabels.created_at\",\r\n       \"ProductColors.ProductColorLabels\".\"updated_at\"\r\n       AS\r\n       \"ProductColors.ProductColorLabels.updated_at\",\r\n       \"ProductColors.ProductColorLabels\".\"product_color_id\"\r\n       AS\r\n       \"ProductColors.ProductColorLabels.product_color_id\"\r\nFROM   (SELECT \"product\".\"id\",\r\n               \"product\".\"uuid\",\r\n               \"product\".\"architonic_product_id\",\r\n               \"product\".\"architonic_family_id\",\r\n               \"product\".\"manufacturer_product_code\",\r\n               \"product\".\"designer_name\",\r\n               \"product\".\"created_at\",\r\n               \"product\".\"updated_at\",\r\n               \"product\".\"product_manufacturer_id\",\r\n               \"productmanufacturer\".\"id\"                 AS\r\n               \"ProductManufacturer.id\",\r\n               \"productmanufacturer\".\"manufacturer_brand\" AS\r\n               \"ProductManufacturer.manufacturer_brand\",\r\n               \"productmanufacturer\".\"manufacturer_logo\"  AS\r\n               \"ProductManufacturer.manufacturer_logo\",\r\n               \"productmanufacturer\".\"created_at\"         AS\r\n                      \"ProductManufacturer.created_at\",\r\n               \"productmanufacturer\".\"updated_at\"         AS\r\n                      \"ProductManufacturer.updated_at\"\r\n        FROM   \"product\" AS \"Product\"\r\n               INNER JOIN \"productmanufacturer\" AS \"ProductManufacturer\"\r\n                       ON \"product\".\"product_manufacturer_id\" =\r\n                          \"productmanufacturer\".\"id\"\r\n                          AND \"productmanufacturer\".\"id\" IN ( '1' )\r\n        WHERE  (SELECT \"producttoproductcategory\".\"product_id\"\r\n                FROM   \"producttoproductcategory\" AS \"ProductToProductCategory\"\r\n                       INNER JOIN \"productcategory\" AS \"ProductCategory\"\r\n                               ON\r\n                       \"producttoproductcategory\".\"product_category_id\" =\r\n                       \"productcategory\".\"id\"\r\n                WHERE  ( \"product\".\"id\" =\r\n                         \"producttoproductcategory\".\"product_id\" )\r\n                LIMIT  1) IS NOT NULL\r\n               AND (SELECT \"producttoproductcolor\".\"product_id\"\r\n                    FROM   \"producttoproductcolor\" AS \"ProductToProductColor\"\r\n                           INNER JOIN \"productcolor\" AS \"ProductColor\"\r\n                                   ON \"producttoproductcolor\".\"product_color_id\"\r\n                                      =\r\n                                      \"productcolor\".\"id\"\r\n                    WHERE  ( \"product\".\"id\" =\r\n                             \"producttoproductcolor\".\"product_id\" )\r\n                    LIMIT  1) IS NOT NULL\r\n        LIMIT  661) AS \"Product\"\r\n       LEFT OUTER JOIN \"productmanufacturerlabel\" AS\r\n                       \"ProductManufacturer.ProductManufacturerLabels\"\r\n                    ON \"productmanufacturer.id\" =\r\n       \"ProductManufacturer.ProductManufacturerLabels\".\"product_manufacturer_id\"\r\n       LEFT OUTER JOIN \"productlabel\" AS \"ProductLabels\"\r\n                    ON \"Product\".\"id\" = \"ProductLabels\".\"product_id\"\r\n       INNER JOIN (\"producttoproductcategory\" AS\r\n                  \"ProductCategories.ProductToProductCategory\"\r\n                   INNER JOIN \"productcategory\" AS \"ProductCategories\"\r\n                           ON \"ProductCategories\".\"id\" =\r\n\"ProductCategories.ProductToProductCategory\".\"product_category_id\")\r\nON \"Product\".\"id\" =\r\n\"ProductCategories.ProductToProductCategory\".\"product_id\"\r\nLEFT OUTER JOIN \"productcategorylabel\" AS\r\n    \"ProductCategories.ProductCategoryLabels\"\r\n ON \"ProductCategories\".\"id\" =\r\n\"ProductCategories.ProductCategoryLabels\".\"product_category_id\"\r\nLEFT OUTER JOIN \"productimage\" AS \"ProductImages\"\r\n ON \"Product\".\"id\" = \"ProductImages\".\"product_id\"\r\nLEFT OUTER JOIN \"productprice\" AS \"ProductPrices\"\r\n ON \"Product\".\"id\" = \"ProductPrices\".\"product_id\"\r\nINNER JOIN (\"producttoproductcolor\" AS \"ProductColors.ProductToProductColor\"\r\nINNER JOIN \"productcolor\" AS \"ProductColors\"\r\n        ON \"ProductColors\".\"id\" =\r\n\"ProductColors.ProductToProductColor\".\"product_color_id\")\r\nON \"Product\".\"id\" = \"ProductColors.ProductToProductColor\".\"product_id\"\r\nAND \"ProductColors\".\"id\" IN ( '3' )\r\nLEFT OUTER JOIN \"productcolorlabel\" AS \"ProductColors.ProductColorLabels\"\r\n ON \"ProductColors\".\"id\" =\r\n\"ProductColors.ProductColorLabels\".\"product_color_id\"\r\nORDER  BY \"ProductLabels\".\"product_title\" ASC; \r\n```"}}