GitLab

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.