Home Dashboard Directory Help
Search

Plan Regression with Sequence Projections by Paul White NZ


Status: 

Closed
 as Won't Fix Help for as Won't Fix


4
0
Sign in
to vote
Type: Bug
ID: 727419
Opened: 2/29/2012 8:41:51 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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


Details
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
Sign in to post a workaround.