Duplicated and missing items when using GraphQL API with sorting and pagination.

[ipg0]

Bug report

Warning: this bug is NOT a duplicate of #11892, though, it might be related to the same issue. It might also be a bug in Postgres.

Fix: This bug can be worked around by adding "id" or another unique attribute to the end of the sorting args array.

For example: sort: "name" should be changed to sort: ["name", "id"]. This fixes the problem. More details at the end of the report.

Required System information

• Node.js version: 18.18.2
• NPM version: 10.1.0 (I'm using Yarn 4.0.1 instead)
• Strapi version: 4.15
• Database: Postgres (client: 15.4, server: 14.7)
• Operating system: Ubuntu 23.10 x86_64 on Linux 6.5.0-10-generic
• Is your project Javascript or Typescript: Typescript

Describe the bug

When using pagination with sorting via the GraphQL API, one row appears on two pages, while another row doesn't appear at all.

Steps to reproduce the behavior

1. Create 23 rows of one collection in via the Admin UI.
2. Query all pages consequently via GraphQL API while using sorting:

items(pagination: {page: 1, pageSize: 8}, sort: "name") {
# ...
# ...
}

3. Accumulate the response data (either programmaticaly or manually), find two objects with the same id and one id missing.

Expected behavior

Accumulated results from requesting all pages via GraphQL should be a list containing every row once.

Code snippets

Sorry for my absolutely random test data, I have censored out the swear words.

JSON response of all items when requested as a single page:

{
  "data": {
    "items": {
      "data": [
        {
          "id": "16",
          "attributes": {
            "name": "12341234",
            "price": null
          }
        },
        {
          "id": "10",
          "attributes": {
            "name": "123412341",
            "price": 12342
          }
        },
        {
          "id": "11",
          "attributes": {
            "name": "123412341234",
            "price": 12341234
          }
        },
        {
          "id": "22",
          "attributes": {
            "name": "123412341234",
            "price": 1234123
          }
        },
        {
          "id": "19",
          "attributes": {
            "name": "123412341234",
            "price": 23423
          }
        },
        {
          "id": "13",
          "attributes": {
            "name": "123412341234",
            "price": 1234432
          }
        },
        {
          "id": "9",
          "attributes": {
            "name": "123412341234",
            "price": 12341234
          }
        },
        {
          "id": "20",
          "attributes": {
            "name": "1234123412341234",
            "price": 43212344
          }
        },
        {
          "id": "12",
          "attributes": {
            "name": "1234123412341234",
            "price": 12341234
          }
        },
        {
          "id": "21",
          "attributes": {
            "name": "1234132123434",
            "price": 434212
          }
        },
        {
          "id": "18",
          "attributes": {
            "name": "12344321",
            "price": 12344
          }
        },
        {
          "id": "15",
          "attributes": {
            "name": "123443211234",
            "price": 1234
          }
        },
        {
          "id": "23",
          "attributes": {
            "name": "234123",
            "price": 432
          }
        },
        {
          "id": "17",
          "attributes": {
            "name": "43211234",
            "price": 43212134
          }
        },
        {
          "id": "14",
          "attributes": {
            "name": "43211234",
            "price": 4321123
          }
        },
        {
          "id": "7",
          "attributes": {
            "name": "asdfasdf",
            "price": 12
          }
        },
        {
          "id": "3",
          "attributes": {
            "name": "asdfdd",
            "price": 12
          }
        },
        {
          "id": "5",
          "attributes": {
            "name": "asdffdsa",
            "price": 12312
          }
        },
        {
          "id": "6",
          "attributes": {
            "name": "fasdfasdfasdf",
            "price": 122222
          }
        },
        {
          "id": "4",
          "attributes": {
            "name": "fdsaf",
            "price": 123
          }
        },
        {
          "id": "8",
          "attributes": {
            "name": "gfasdfg",
            "price": 123
          }
        },
        {
          "id": "2",
          "attributes": {
            "name": "not p****",
            "price": 399999
          }
        },
        {
          "id": "1",
          "attributes": {
            "name": "p****",
            "price": 299
          }
        }
      ],
      "meta": {
        "pagination": {
          "page": 1,
          "pageSize": 30,
          "pageCount": 1,
          "total": 23
        }
      }
    }
  }
}

Accumulated response data from 3 requests with page size = 8:

[
  {
    "id": "16",
    "attributes": {
      "name": "12341234",
      "price": null
    }
  },
  {
    "id": "10",
    "attributes": {
      "name": "123412341",
      "price": 12342
    }
  },
  {
    "id": "13",
    "attributes": {
      "name": "123412341234",
      "price": 1234432
    }
  },
  {
    "id": "19",
    "attributes": {
      "name": "123412341234",
      "price": 23423
    }
  },
  {
    "id": "9",
    "attributes": {
      "name": "123412341234",
      "price": 12341234
    }
  },
  {
    "id": "11",
    "attributes": {
      "name": "123412341234",
      "price": 12341234
    }
  },
  {
    "id": "22",
    "attributes": {
      "name": "123412341234",
      "price": 1234123
    }
  },
  {
    "id": "12",
    "attributes": {
      "name": "1234123412341234",
      "price": 12341234
    }
  },
  {
    "id": "12",
    "attributes": {
      "name": "1234123412341234",
      "price": 12341234
    }
  },
  {
    "id": "21",
    "attributes": {
      "name": "1234132123434",
      "price": 434212
    }
  },
  {
    "id": "18",
    "attributes": {
      "name": "12344321",
      "price": 12344
    }
  },
  {
    "id": "15",
    "attributes": {
      "name": "123443211234",
      "price": 1234
    }
  },
  {
    "id": "23",
    "attributes": {
      "name": "234123",
      "price": 432
    }
  },
  {
    "id": "17",
    "attributes": {
      "name": "43211234",
      "price": 43212134
    }
  },
  {
    "id": "14",
    "attributes": {
      "name": "43211234",
      "price": 4321123
    }
  },
  {
    "id": "7",
    "attributes": {
      "name": "asdfasdf",
      "price": 12
    }
  },
  {
    "id": "3",
    "attributes": {
      "name": "asdfdd",
      "price": 12
    }
  },
  {
    "id": "5",
    "attributes": {
      "name": "asdffdsa",
      "price": 12312
    }
  },
  {
    "id": "6",
    "attributes": {
      "name": "fasdfasdfasdf",
      "price": 122222
    }
  },
  {
    "id": "4",
    "attributes": {
      "name": "fdsaf",
      "price": 123
    }
  },
  {
    "id": "8",
    "attributes": {
      "name": "gfasdfg",
      "price": 123
    }
  },
  {
    "id": "2",
    "attributes": {
      "name": "not p****",
      "price": 399999
    }
  },
  {
    "id": "1",
    "attributes": {
      "name": "p****",
      "price": 299
    }
  }
]

The bug only happens when the sorting order is ambiguous across the end of a page. This occurs when the last element on one page and the first element on another have the same value of the sorting arg, (in this case, the "name" field). Adding at least one unique attribute to the sorting arg fixes the problem.

[derrickmehaffy]

K own issue with PostgreSQL as it doesn't automatically add I'd to the sort like MySQL or MariaDB does. 🤔 Honestly not sure if we should try to make all of act the same or not.