Home Dashboard Directory Help
Search

SQL Server does not reorder left joins even if cost-reducing by xor88


Status: 

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


2
0
Sign in
to vote
Type: Bug
ID: 649688
Opened: 3/5/2011 5:26:57 PM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

It seems there is a bug which prevents the query optimizer under very general conditions from reordering left joins at all. Here are three equivalent queries for the AdventureWorks database:


SELECT c.CustomerID
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID
LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID
LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID


SELECT c.CustomerID
FROM Sales.Customer c
LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID
LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID
LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID


SELECT c.CustomerID
FROM Sales.Customer c
LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID
LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID
LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID


They produce different plans with the exact same join order of the query preserved. The plan costs are different so the optimal plan was not found by SQL Server in at least 2 cases. Given that there are only 4 tables involved and that the query is kind of expensive I would have expected the same (optimal) plan 3 times.

First reported in the comments at http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-left-outer-join-reordering.aspx . I have found this to be true over and over again in production, not only in this contrived query.

Notice that inner joins or full outer joins are unaffected. This is additional evidence that a bug is present.
Details
Sign in to post a comment.
Posted by Microsoft on 1/31/2012 at 9:52 AM
Thank you for submitting this suggestion, but given its priority relative to the many other enhancements we are considering, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by Microsoft on 3/7/2011 at 9:38 AM
Hi,

Thanks for the feedback. We'll consider this for a future release.

Best regards,
Eric Hanson
Program Manger
SQL Server Query Processing
Sign in to post a workaround.