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

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.


2
0
Sign in
to vote
ID 649688 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 3/5/2011 5:26:57 PM
Access Restriction Public

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.
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