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.


4
0
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

Description

For the query

SELECT x.*
FROM   T1 org
       CROSS APPLY (SELECT TOP (50) PERCENT *
                    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 Microsoft 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?

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