SQL Server 2008 bug with outer join and index

Question

Hello! We found a problem when migrating from SQL Server 2005 to SQL Server 2008. SQL-statements like

 

SELECT <table1>.<fields>, <table2>.<fields>, <table3>.<fields>

FROM <table1>

JOIN <table2>

ON <table2>.<field> = <table1>.<field>

LEFT JOIN <table3>

ON <table3>.<field> = <table1>.<field>

WHERE (<table2>.<field> = 2 AND <table2>.<another_field> = 'some_text'

OR <table3>.<field> = 2 AND <table3>.<another_field> = 'some_text')

 

give different results on 2005 / 2008. On 2008 I get lots of exessive rows which have NULLs instead of the values required in the WHERE-term.

Changing the Compability Level does nothing. It also doesn't matter from where the query is executed (direct via ODBC, in a view or from the Enterprise Manager).

2005/2008 also use different execution plans. (Btw: how can I make an screenshot of a EP larger than the screen?)

 

After some hours I drilled it down to the existence of a index! By deleting or deactivating the index 2008 gives the correct results like the 2005 did, after activating the index I get these exessive NULL-rows again!

 

Anyone else ran into this problem? Any tips (dropping all indexes in a 25+ GB DB is NOT an option :-) ?

 

Thanks in advance!

Holger Wellenkötter

MCTS SQL 2005

Answer 1

Unless you can provide an example that reproduces the problem the forums aren't going to be much help with this.  If you can provide how to reproduce the issue, first post that on a Connect Feedback and get it into the Microsoft Product Support Services Bug tracking.  If you can't build a reproduceable version of the problem in another database, then it might be something with your statistics being inaccurate or out of date that allows it to pick an index that is not correct for the query, resulting in the null values?  I am afraid I can only make uneducated guesses without a reproduction or set of execution plans for this.

 

Having NULLS show up for table3 wouldn't be unexpected/problemattic based on the example query your provided.

Answer 2

I have managed to reproduce this behaviour in a new database where I created the three involved tables, copied the data and created this one index.

I wasn't aware of that feedback-feature and the guy at the Microsoft-Hotline didn't mention it either. THX for the tip! I already searched in the feedback topics, but it seems I am the first one who discovered this bug. I post my feedback there and close this topic.

 

P.S. somehow I knew there would be some kind of a NULL-value-related comment...I won my bet :-) No, really, never ever, under NO circumstances may an index alter the results (number of rows) of a query, only the performance! Also, I expect the same query on the same data to deliver the same rows, no matter on what version of the SQL-Server it's executed.

 

Thanks again!

Holger Wellenkötter

MCTS SQL 2005

Answer 3

Holger Wellenkötter wrote:

P.S. somehow I knew there would be some kind of a NULL-value-related comment...I won my bet :-) No, really, never ever, under NO circumstances may an index alter the results (number of rows) of a query, only the performance! Also, I expect the same query on the same data to deliver the same rows, no matter on what version of the SQL-Server it's executed.

 

I am not arguing this point.  The output should be consistent, but a LEFT JOIN should have NULL returned for non-matching rows in the right table which in this case is table3, so if that is where the NULL occurs, I would expect that.  This is all I was trying to say.  With or without the index, you should have the same exact output.

 

Post the link to your feedback item, and I will post it on the private MVP Newsgroup so that it can be validated and voted on which improves its visibility to the development team.  Microsoft developers also watch those groups and can see the item as well.  This is not saying it will get any more attention or faster responses, but if it is a true bug, then the more people who can validate it, the faster it can be fixed. 

Answer 4

I just sent my feedback: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387586 

All things to reproduced this behaviour are included: a ZIP of the backup of a demo-DB and the SQL-Statement.

 

Thank you for your help!

Answer 5

Holger,

 

One of the other MVP's suggested that this bug was fixed in Cumulative Update 2 for SQL Server 2008.  You might try downloading and applying that.

 

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=958186&kbln=en-us

Answer 6

Jon,
sorry I forgot to mention that this Hotfix is already installed. It is listed in the Installed Updates-section and I found no error-logs, so AFAIK it is installed correctly.

Yes, it covers some index-related issues, but our problem still exists.

Answer 7

Holger,

You nailed it down well. I am experiencing the same exact problem. I am in the process of installing the cummulative update 2 and i will let you know if that fixed it for me.

BTW, I also upgraded from SQL 2005 to SQL 2008 and seems to be connected to that.

---

Shafat

Answer 8

Holger,

Good News! After applying the cummulative update 2 hotfix, the problem was resolved. Link to download hotfix:

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=958186&kbln=en-us

Shafat

---

Shafat

Answer 9

Check instead http://support.microsoft.com/kb/2083921/en-us

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog