Incorrect / Inconsistent results for CROSS APPLY (SELECT TOP X PERCENT ... ) - by Martin Smith

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 778200 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/1/2013 4:45:34 AM
Access Restriction Public


For the query

FROM   T1 org
                    FROM   T2
                    WHERE  OrganizationID = org.OrganizationID) x; 

the results differ dependant upon whether the plan has a table spool or an index spool.

The table T2 has 4 rows matching OrganizationID = 123 and 100 matching OrganizationID = 456

When the execution plan shows an Index spool the results have 4 rows for OrganizationID = 123 and 52 matching OrganizationID = 456 indicating that it is doing `TOP (0.5 * total row count)`

When the execution plan shows a Table spool the results have 2 rows for OrganizationID = 123 and 50 matching OrganizationID = 456  so seems to be doing `TOP (0.5 * filtered row count)`

The second behaviour is the one that seems correct to me.
Sign in to post a comment.
Posted by Martin Smith on 2/13/2013 at 1:36 PM
Or at least it doesn't change anything when doing "ORDER BY OrganizationID".

Doing "ORDER BY CustomerID" happens to change both plans so it doesn't use any spools at all and returns the 2 rows / 50 rows as per the table spool.

The non deterministic aspect of TOP (N) without an ORDER BY clause surely refers to the N rows returned not the value of N itself.
Posted by Martin Smith on 2/13/2013 at 1:29 PM
Not sure why you couldn't have tried adding an ORDER BY clause yourself but no it doesn't change anything.
Posted by Vishal [MSFT] on 2/13/2013 at 12:18 PM
Martin- A TOP without an ORDERBY Clause is nondeterministic. Can you use an Order by clause and see if that helps?

Posted by Vishal [MSFT] on 2/13/2013 at 12:16 PM
Thanks, we are looking into this request