Fix missing/inconsitent records without order

[felixbuenemann]

If the relation query has no order, it's possible, that different records are selected for the ids and filter CTE temp tables, because when no ORDER BY is given, postgres record order is non-deterministic.

This leads to inconsistencies where embedded ids or records do not match up.

The solution is to constrain the filter CTE by joining the ids CTE, if there is no ORDER BY and the relation query has a LIMIT or OFFSET.

The most likely scenario for this problem is if you use pagination without specifying an order on the serialized relation. The easiest fix is to just add an order(:id) to the query, but we shouldn't break if no order is used.

TODO:

• [x] Add tests with pagination that exercise this code

[felixbuenemann]

Adding a test for this is tricky, because we don't know which order postgres will return the records in and they could have the same order by coincidence.

[felixbuenemann]

Instead of adding a test that checks for random record order, we could at least check it is working as expected, when we do use limit/offset without order.

[felixbuenemann]

I've managed to write a test that reliably fails without this fix and rebased on master.

[felixbuenemann]

Btw. this could be rewritten as a subselect instead of a join which could potentially be faster, but require more logic.