U4-8831 - Paging logic is flawed when ordering by a potentially non-unique value

Created by Shannon Deminick 09 Aug 2016, 10:47:04 Updated by Claus Jensen 10 Aug 2016, 11:14:09

Tags: Backport Unscheduled

Relates to: U4-8833

When paging on a non unique value, there is no guarantee the results that are returned for a given page, we absolutely must also order by a unique value.

This can result in duplicate property data being entered and misleading paged results along with poor performance when querying for a page of values.

This seems to affect SQL Server more than SQL CE, but for example if you had a lot of members and this query is executed (which is the same query used for paging):

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY umbracoNode.text) peta_rn, cmsMember.nodeId
FROM [cmsMember]
INNER JOIN [cmsContentVersion]
ON [cmsContentVersion].[ContentId] = [cmsMember].[nodeId]
INNER JOIN [cmsContent]
ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
INNER JOIN [cmsContentType]
ON [cmsContentType].[nodeId] = [cmsContent].[contentType]
INNER JOIN [umbracoNode]
ON [cmsContent].[nodeId] = [umbracoNode].[id]
WHERE (([umbracoNode].[nodeObjectType] = '39eb0f98-b348-42a1-8662-e7eb18487560'))
) peta_paged WHERE peta_rn>0 AND peta_rn<=10

but you have multiple rows in the umbracoNode table for members with empty or the same value in the umbracoNode.text column then when this query is executed multiple times you will see a difference in the results because SQL Server doesn't know how to order something that is not unique.

If this query is changed to:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY umbracoNode.text, umbracoNode.id) peta_rn, cmsMember.nodeId
FROM [cmsMember]
INNER JOIN [cmsContentVersion]
ON [cmsContentVersion].[ContentId] = [cmsMember].[nodeId]
INNER JOIN [cmsContent]
ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
INNER JOIN [cmsContentType]
ON [cmsContentType].[nodeId] = [cmsContent].[contentType]
INNER JOIN [umbracoNode]
ON [cmsContent].[nodeId] = [umbracoNode].[id]
WHERE (([umbracoNode].[nodeObjectType] = '39eb0f98-b348-42a1-8662-e7eb18487560'))
) peta_paged WHERE peta_rn>0 AND peta_rn<=10

The it will always work as expected because the combination of umbracoNode.text, umbracoNode.id is unique.

Comments

Shannon Deminick 09 Aug 2016, 11:59:09

fixed in rev: 1b85163a7f39898f93d71fab787ce8eef23a2850


Shannon Deminick 09 Aug 2016, 12:14:08

This ensures that the sorting is always done by the field specified AND umbracoNode.id to ensure that the result is always a unique result and we don't end up with misleading paging data.


Priority: Major

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.5.0

Sprint: Sprint 39

Story Points:

Cycle: