Different order by on same relationship field results in incorrect result

[jondavis9898]

When more than one rollup targeting the same parent/child relationship (different target fields) are defined and each contains a different order by clause, the results of the rollup do not correspond to the specified rollup on each target field.

DLRS is using the same context for these currently and because of the default behavior of LREngine, a single SOQL is executed with a "Then By" approach for ordering.

The default behavior of LREngine is to apply order by as follows:

1. LookupField then by
2. For each RollupSummaryField in context (in the order specified in the context)
   a) if detailOrderBy is specified use detailOrderBy.getName()
   b) else use detail.getName()

Since LREngine supports multiple rollupsummary fields with their own OrderBy, there is something to be said for resolving this issue within LREngine instead of DLRS. This comes in to play when no order by is specified as well as one is specified. Order by should be non-deterministic in that case and it will be, but LREngine will apply an order by and it will change depending on the list of RollupSummaryFields passed in the context. It's still non-deterministic but it can vary and an unsuspecting admin can start seeing unexpected results when something "appeared" to be working originally but after adding a new rollup, now its not. The fact that they didn't have an order by to begin with resulted in it being non-determinstic and working as expected but the admin wouldn't understand what's occurring under the covers since default order by behavior is not in the docs.

Example: Two rollups defined on Account from Opportunity, one on StageName and one on Name. The resulting order by generated by LREngine will be AccountId + the order of RollupSummaryFields "detail field" passed in from DLRS. As new rollups on defined on Account, the list of fields passed in will change and hence the outcome will change.

The "simple" solution here is to ensure that DLRS uses the orderby in the ContextKey to ensure each unique order by receives its own SOQL. I think the better solution is to modify LREngine so that it doesn't apply an order by (except for relationshipfield) by default.

While technically a fix, either of these approaches presents a potential regression as current functionality could be impacted to an unsuspecting user/admin/developer since the current approach for all rollups defined on same parent/child relationship is "Then By" in non-deterministic order.

[afawcett]

Fixed in v1.23