Undeterministic sorting

If we add no sorting to an AQ_AggregateList and we retrieve its content, a query is sent to the database and the database specifies a default order in which the elements get returned. The default order can be different for different database vendors, versions or even subsequent queries. This means that loading a page twice with an AQ_AggregateList with the same parameters can yield different results each time.

This in itself is not that problematic as if the results of the query fit on one page but it could be problematic if the list is paginated.

Let's say for example we have the following records:

objectId | aggregateId | version | type | createdByUsername
-----------------------------------------------------------
8        | 8           | 1       | Zaak | Aanvrager
10       | 10          | 1       | Zaak | Aanvrager
12       | 12          | 1       | Zaak | Aanvrager

If we don't add any sorting or add a sort on type or createdByUsername, the results could be returned in a different order each time. The second time we query could result in the following:

objectId | aggregateId | version | type | createdByUsername
-----------------------------------------------------------
12       | 12          | 1       | Zaak | Aanvrager
10       | 10          | 1       | Zaak | Aanvrager
8        | 8           | 1       | Zaak | Aanvrager

If we now paginate this result with a limit of two records, the first time it will return those pages:

objectId | aggregateId | version | type | createdByUsername
-----------------------------------------------------------
8        | 8           | 1       | Zaak | Aanvrager         <- page 1 of the aggregate list
10       | 10          | 1       | Zaak | Aanvrager
-----------------------------------------------------------
12       | 12          | 1       | Zaak | Aanvrager         <- page 2 of the aggregate list

And the second time (after transitioning to page two, it will query the database for page two) it could return this result:

objectId | aggregateId | version | type | createdByUsername
-----------------------------------------------------------
12       | 12          | 1       | Zaak | Aanvrager         <- page 1 of the aggregate list
10       | 10          | 1       | Zaak | Aanvrager
-----------------------------------------------------------
8        | 8           | 1       | Zaak | Aanvrager         <- page 2 of the aggregate list

Switching from page one to page two now shows the same results twice, aggregate 8 and 10 on page one and aggregate 8 on page two. Also aggregate 12 is not shown at all.

Solution

To fix this problem (from 15.7, 14.11.14 and 13.13.34 onwards), we've added an ORDER BY statement to the query on objectId, ascending. This does not override any sorting present in the model as a sort on objectId can't be modelled in Studio. It's also always the last sort item so every sort item that has been created in Studio will take precedence over this. Any sorting added by the user will also take precedence over this. If two or more items still have non-unique values in the ORDER BY part of the query, the objectId will make the sort deterministic.