This is still an issue and was a major headache to fix on a Drupal + GatsbyJS project.
In my case, media files were missing AND getting duplicated.
Adding a sort to JSON API fixed the issue, but I also found something else.
Two identical unordered SQL queries return a result in different order depending on the presence of limit parameter.
This seems to be a bug with MySQL: LIMIT clause results in duplicate data across pages
People seem to disagree if this is a real bug or not. But long story short, MySQL does require an order to be specified for a consistent paginated result with no duplicates or missing records.
Here is my problem query without the limit parameter and result.
MySQL [db]> SELECT media.vid AS vid, media.mid AS mid FROM media media INNER JOIN media_field_data media_field_data ON media_field_data.mid = media.mid WHERE media_field_data.bundle = 'image' GROUP BY media.vid, media.mid;
+------+-----+
| vid | mid |
+------+-----+
| 159 | 1 |
| 2 | 2 |
| 160 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 161 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 19 | 19 |
| 20 | 20 |
| 21 | 21 |
| 22 | 22 |
| 151 | 23 |
...
82 rows in set (0.002 sec)And here is the same query with limit parameter and result.
MySQL [db]> SELECT media.vid AS vid, media.mid AS mid FROM media media INNER JOIN media_field_data media_field_data ON media_field_data.mid = media.mid WHERE media_field_data.bundle = 'image' GROUP BY media.vid, media.mid limit 82;
+------+-----+
| vid | mid |
+------+-----+
| 2 | 2 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 19 | 19 |
| 20 | 20 |
| 21 | 21 |
| 22 | 22 |
| 24 | 24 |
| 25 | 25 |
| 26 | 26 |
| 46 | 46 |
| 47 | 47 |
| 48 | 48 |
| 49 | 49 |
| 50 | 50 |
| 51 | 51 |
| 65 | 63 |
| 66 | 64 |
| 67 | 65 |
| 68 | 66 |
| 69 | 67 |
| 70 | 68 |
| 93 | 27 |
... ... ...
82 rows in set (0.002 sec)
You can see how the order of the results are different. I'm using MySQL 8.0.
When I paginate the results, just like how JSON API does, without a sort, I do see missing AND repeated items on subsequent queries. Here is page 1.
MySQL [db]> SELECT media.vid AS vid, media.mid AS mid FROM media media INNER JOIN media_field_data media_field_data ON media_field_data.mid = media.mid WHERE media_field_data.bundle = 'image' GROUP BY media.vid, media.mid LIMIT 51 OFFSET 0;
+------+-----+
| vid | mid |
+------+-----+
| 2 | 2 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 19 | 19 |
| 20 | 20 |
| 21 | 21 |
| 22 | 22 |
| 24 | 24 |
| 25 | 25 |
| 26 | 26 |
| 46 | 46 |
| 47 | 47 |
| 48 | 48 |
| 49 | 49 |
| 50 | 50 |
| 51 | 51 |
| 65 | 63 |
| 66 | 64 |
| 67 | 65 |
| 68 | 66 |
| 69 | 67 |
| 70 | 68 |
| 93 | 27 |
| 95 | 56 |
| 96 | 43 |
| 97 | 84 |
| 100 | 52 |
| 106 | 87 |
| 107 | 85 |
| 108 | 83 |
| 109 | 82 |
| 110 | 81 |
| 111 | 80 |
| 112 | 79 |
| 113 | 78 |
| 135 | 93 |
| 136 | 95 |
| 137 | 96 |
| 140 | 98 |
+------+-----+
51 rows in set (0.002 sec)
and here is page 2.
MySQL [db]> SELECT media.vid AS vid, media.mid AS mid FROM media media INNER JOIN media_field_data media_field_data ON media_field_data.mid = media.mid WHERE media_field_data.bundle = 'image' GROUP BY media.vid, media.mid LIMIT 51 OFFSET 50;
+------+-----+
| vid | mid |
+------+-----+
| 180 | 62 |
| 65 | 63 |
| 66 | 64 |
| 67 | 65 |
| 68 | 66 |
| 69 | 67 |
| 70 | 68 |
| 113 | 78 |
| 112 | 79 |
| 111 | 80 |
| 110 | 81 |
| 109 | 82 |
| 108 | 83 |
| 97 | 84 |
| 107 | 85 |
| 152 | 86 |
| 106 | 87 |
| 183 | 90 |
| 148 | 91 |
| 135 | 93 |
| 149 | 94 |
| 136 | 95 |
| 137 | 96 |
| 147 | 97 |
| 140 | 98 |
| 146 | 99 |
| 179 | 100 |
| 178 | 101 |
| 177 | 102 |
| 176 | 103 |
| 172 | 104 |
| 184 | 105 |
+------+-----+
32 rows in set (0.002 sec)
You can see that mid 3 is missing and mids 64-68 are duplicating on second page.
Upvoting for self-closing tags.
This is still an issue on Drupal 9.5.7, with PHP8.1.
I'm attaching a new patch.