MERGE Bug with Filtered Indexes

re: MERGE Bug with Filtered Indexes

tobi — Mon, 10 Dec 2012 18:19:34 GMT

That is a nice (and nasty bug). And just another one in the merge statement query planner.

It seems like some automated, exhaustive testing with all possible data and schemas would be in order to finally free the merge statement from bugs.

re: MERGE Bug with Filtered Indexes

AlexK — Tue, 11 Dec 2012 22:43:17 GMT

Thank you for alerting us, Paul!

Apparently the old rule of waiting for the first service pack before using the product is not quite right any more. These two features were released quite a while ago, and they still do not work together.

As the product gets more complex, the amount of integration tests needed to ensure acceptable quality grows exponentially. The release of MERGE with all these bugs is a good example of why complexity kills.

So, next time someone suggests that "the optimizer should be smart enough to do something else", let us think a little bit before upvoting the suggestion. Do we really need a smarter, and more complex, optimizer, with more bugs? Don't we need a fully tested one instead?

re: MERGE Bug with Filtered Indexes

Paul White — Tue, 11 Dec 2012 23:49:24 GMT

Hi Alex,

I would agree with most of that. Update processing is already very complex (think about the update plan where there are instead of triggers on views on tables being modified by a merge with filtered indexes and query notifications and ...)

Combining 'relatively new' features in creative ways is a good way to encounter bugs. Makes for interesting blog material (I hope) but not at all fun in a production system.

Paul

re: MERGE Bug with Filtered Indexes

Gabe — Tue, 18 Dec 2012 16:46:54 GMT

What version(s) of SQL does this happen with? 2008 and/or 2012?

re: MERGE Bug with Filtered Indexes

Paul White — Wed, 19 Dec 2012 09:39:15 GMT

Gabe,

See the build list at the end of the post.