Inconsistency between count and actual query results size when using join
Summary
When setting a table A with two LEFT OUTER using a table B it returns inconsistent sizes when running a count and checking the length of the criteria. Moreover, the pagination returns wrong sizes or duplicates on every interaction.
Example
Database setup
// Table PARTICIPANTS contains 499 entries
table = dbClient.db[PARTICIPANTS].join({
[generalJoin]: {
type: 'LEFT OUTER',
relation: PARTICIPANTS_STATUS,
on: {
participant_id: 'id',
current: true,
status: ['open', 'prospecting', 'interviewing', 'offer_made', 'rejected'],
},
},
[hiredJoin]: {
type: 'LEFT OUTER',
relation: PARTICIPANTS_STATUS,
on: {
participant_id: 'id',
current: true,
status: 'hired',
},
},
});
Code demonstrating the behavior
If I have, for example, two current status as 'prospecting', this happens:
console.log((await table.find(criteria)).length); // prints 499
console.log(await table.count(criteria)); // prints 500 (wrong)
Expected behavior
Both console.log should return 499
Actual behavior
The count returns +1 for each duplicated statuses
Additional context
If I use a regular pagination (offset, limit) the result of each page also brings me inconsistent page sizes and items appearing again in other pages.