Plan Regression with Sequence Projections - by Paul White NZ

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<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 727419 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/29/2012 8:41:51 PM
Access Restriction Public

Description

In SQL Server 2008, the optimizer is able to push suitable predicates below a sequence project.  In 2008 R2, pushing selections is broken for sequence projections that require a segment apply, namely NTILE and windowed aggregates.  (This regression does not appear, or is fixed, in SQL Server 2012).

(tested on 2008 build 5768 and R2 build 2806)

USE tempdb;
GO
CREATE TABLE dbo.T (x int PRIMARY KEY, y int)
GO
-- Predicate successfully pushed in both 2008 and R2
SELECT z.* FROM 
(
    SELECT 
        t.x,
        t.y,
        rn = ROW_NUMBER() OVER(
            PARTITION BY t.x
            ORDER BY t.y)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO
-- Predicate pushed to a seek on 2008
-- Not pushed on 2008 R2
SELECT z.* FROM 
(
    SELECT 
        t.x,
        t.y,
        rn = MAX(t.y) OVER(
            PARTITION BY t.x)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO
-- Pushed to a seek on 2008
-- No seek and COUNT(*) computed *twice* on 2008 R2
SELECT z.* FROM 
(
    SELECT 
        t.x,
        t.y,
        rn = NTILE(4) OVER(
            PARTITION BY t.x
            ORDER BY t.y)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO


Sign in to post a comment.
Posted by Microsoft on 7/22/2013 at 9:32 AM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we have decided that we will not fix this bug in SQL Server 2008, since the issue does not occur in SQL Server 2012 or the latest version. Thanks again for reporting the product issue and continued support in improving our product.

Sincerely,
Manbeen