Skip and take doesn't work with join #4742
Description
Issue type:
[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue
Database system/driver:
[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo
TypeORM version:
[ ] latest
[ ] @next
[x] 0.2.18 (or put your version here)
Hi
I have 7 row in my table and it's my first query.
First Query
Organization.find({
relations,
order: {
createdAt: "DESC"
},
skip: pagination.skip,
take: pagination.take
});First test for First query
It's my query log for just one request (my resolver dosn't have any additional middleware)
with skip: 1 and take: 2
query: SELECT DISTINCT "distinctAlias"."Organization_id" as "ids_Organization_id", "distinctAlias"."Organization_createdAt" FROM (SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId" LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE "Organization"."type" IN ($1)) "distinctAlias" ORDER BY "distinctAlias"."Organization_createdAt" DESC, "Organization_id" ASC LIMIT 2 OFFSET 1 -- PARAMETERS: ["Organization"]
query: SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId" LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE "Organization"."type" IN ($1) ORDER BY "Organization"."createdAt" DESC -- PARAMETERS: ["Organization"]but i got my whole 7 rows and it seem two query executed!!!
In above query log, in first log LIMIT and OFFSET clause exist but in the second query they don't exist.
Second test for First query
It's my second log for skip: 7 and take: 2
query: SELECT DISTINCT "distinctAlias"."Organization_id" as "ids_Organization_id", "distinctAlias"."Organization_createdAt" FROM (SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type", "Organization__university"."id" AS "Organization__university_id", "Organization__university"."createdAt" AS "Organization__university_createdAt", "Organization__university"."updatedAt" AS "Organization__university_updatedAt", "Organization__university"."name" AS "Organization__university_name", "Organization__university"."address" AS "Organization__university_address", "Organization__university"."location" AS "Organization__university_location", "Organization__university"."contact" AS "Organization__university_contact", "Organization__university"."type" AS "Organization__university_type", "Organization__units"."id" AS "Organization__units_id", "Organization__units"."createdAt" AS "Organization__units_createdAt", "Organization__units"."updatedAt" AS "Organization__units_updatedAt", "Organization__units"."name" AS "Organization__units_name", "Organization__units"."address" AS "Organization__units_address", "Organization__units"."location" AS "Organization__units_location", "Organization__units"."contact" AS "Organization__units_contact", "Organization__units"."allowedWareGroupsIds" AS "Organization__units_allowedWareGroupsIds", "Organization__units"."organizationId" AS "Organization__units_organizationId", "Organization__units"."type" AS "Organization__units_type" FROM "site" "Organization" LEFT JOIN "site" "Organization__university" ON "Organization__university"."id"="Organization"."universityId" LEFT JOIN "site" "Organization__units" ON "Organization__units"."organizationId"="Organization"."id" WHERE "Organization"."type" IN ($1)) "distinctAlias" ORDER BY "distinctAlias"."Organization_createdAt" DESC, "Organization_id" ASC LIMIT 2 OFFSET 7 -- PARAMETERS: ["Organization"]As i expected, result was empty and just one query executed (i have just 7 row in my table)
LIMIT and OFFSET clause exist in query log.
Second Query
I changed my query to just retrieve rows without any join (relations).
Organization.find({
order: {
createdAt: "DESC"
},
skip: pagination.skip,
take: pagination.take
});First test for Second query
It's my log with skip: 1 and take: 2
query: SELECT "Organization"."id" AS "Organization_id", "Organization"."createdAt" AS "Organization_createdAt", "Organization"."updatedAt" AS "Organization_updatedAt", "Organization"."name" AS "Organization_name", "Organization"."address" AS "Organization_address", "Organization"."location" AS "Organization_location", "Organization"."contact" AS "Organization_contact", "Organization"."universityId" AS "Organization_universityId", "Organization"."type" AS "Organization_type" FROM "site" "Organization" WHERE "Organization"."type" IN ($1) ORDER BY "Organization"."createdAt" DESC LIMIT 2 OFFSET 1 -- PARAMETERS: ["Organization"]Everything works properly and i got expected rows.
Conclusion
I can only say it's strange behavior.