Incorrect Results with Indexed Views

re: Incorrect Results with Indexed Views

jamiet — Tue, 05 Feb 2013 22:32:31 GMT

Paul, you are on another planet. Awesome post - great detective work!

re: Incorrect Results with Indexed Views

Rob Farley — Tue, 05 Feb 2013 22:35:24 GMT

I keep meaning to find the time to update that Join Simplification post. It's been a lot of years since I wrote about it, and I don't even tend to give that talk any more either.

re: Incorrect Results with Indexed Views

Paul White — Tue, 05 Feb 2013 22:41:24 GMT

@Jamie You're welcome, thanks for tweeting about the problem - that got me interested!

@Rob I always think of your post whenever this simplification thing comes up. One of my all-time favourites, as you know.

re: Incorrect Results with Indexed Views

Boris Hristov — Wed, 06 Feb 2013 05:34:17 GMT

Is it only me that can see this:

"Updates the value column of parent row 1 from Apple to Kiwi Fruit

Adds a new parent row 4 for Dragon Fruit"

in the first screenshot of what data is inside the table dbo.Parents. I cannot see anywhere a record for Apple.

Paul, am I correct?

re: Incorrect Results with Indexed Views

Paul White — Wed, 06 Feb 2013 06:31:07 GMT

Hi Boris,

No it wasn't just you. The T-SQL script was correct, but I must have pasted the wrong screenshot at some stage. Corrected now, thanks for pointing that out.

Paul

NOEXPAND query hint returns wrong results – CU fix now available

SSIS Junkie — Wed, 06 Feb 2013 09:01:18 GMT

Three days ago at my current gig we stumbled across a problem where use of the NOEXPAND query hint was

re: Incorrect Results with Indexed Views

tobi — Wed, 06 Feb 2013 21:53:22 GMT

The MERGE statement strikes again.

re: Incorrect Results with Indexed Views

AlexK — Thu, 07 Feb 2013 17:46:31 GMT

This is awesome research, Paul!

I guess you could do your research in a fraction of the time if the source code was available, so that you could just debug right through. If that were the case, we might have fixed this problem already. Peer reviews are very good for product quality.

I'm using MERGE less and less. It has too many problems. I am not sure we know them all. This does not smell good. I do not want my users to discover yet another bug in MERGE, which will ruin their day and my weekend or vacation. In some cases a DELETE followed by an INSERT even performs better.

re: Incorrect Results with Indexed Views

Paul White — Thu, 07 Feb 2013 18:18:10 GMT

Tobi and Alex,

Yes, it's all rather sad. Every time I revisit MERGE and begin to warm to it, another problem comes up. Many of the past bugs have been edge cases that not too many people would encounter, but this one requires only foreign keys and an indexed view - not an uncommon combination at all!

Paul

re: Incorrect Results with Indexed Views

AlexK — Thu, 07 Feb 2013 22:53:44 GMT

Paul,

I am not with you on the following assessment: "edge cases that not too many people would encounter". These problems constitute a smell: they indicate that there might be something wrong with the overall quality of the feature.

If we observed similar problems in an open source system, where we could access the code and automated tests, quite likely we would find that the full matrix of possible automated test cases is either missing altogether or incomplete.

We could build a complete matrix of test cases, fix the exposed problems, and use the fixed feature with confidence.

Beta testers that cannot read source code cannot ensure high quality, even if there are millions of them. For example, if the code does not work on the last day of a leap year, and we are beta-testing now, in 2013, we will not notice that defect no matter how many people test the black box. The bug would manifest itself on Dec 31st, 2016.

On the other hand, developers can and do find such problems, and peer reviews can and do expose such things by just reading the code. In many cases this is just so very much more efficient than testing, or blindly trusting, a black box.

re: Incorrect Results with Indexed Views

Ian Yates — Fri, 08 Feb 2013 11:33:51 GMT

Out of curiosity, if I have a corrupted table - and am unaware of it - and then apply this patch, does the corruption get fixed for me or do I still need to run the DBCC repair statement?

re: Incorrect Results with Indexed Views

Adam Machanic — Fri, 08 Feb 2013 17:03:04 GMT

I don't think indexed views are very common. They're so limiting, and cause so many side issues, that I think they drive a lot of people away.

That said, agreed wholeheartedly with Alex, this is an indicator that the entire feature was badly designed. And it really makes no sense from an outsider perspective.

If you imagine how you might implement an index update, there would be some central method (overloaded, naturally), and all the updates would go through there. It would control things like subsequent updates to indexed views, etc. Enforcement of constraints, perhaps. There would be no way around these things, because it would all be centrally handled. There would be no choice.

But that's clearly not how this feature has been implemented, and it's scary to imagine the spaghetti code that must exist in core parts of the engine. What other bugs are lying in wait?

re: Incorrect Results with Indexed Views

Paul White — Fri, 08 Feb 2013 17:37:50 GMT

Hi Ian,

That's a great question! I haven't tried it myself, yet, but my expectation is that the fix only prevents future corruptions caused by an incorrect update plan. I would be amazed if it did more than that (but I have been wrong before). That's another detail missing from the KB :)

The safest thing to do (it seems to me) is to test and apply the hotfix, and then either rebuild all indexed views manually or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option - something which could take quite a long time, incidentally.

Paul

re: Incorrect Results with Indexed Views

tobi — Sat, 09 Feb 2013 12:58:32 GMT

Adam,

I wish indexed views had less limitations (like LEFT JOINs being unsupported) and could be stacked. That would be very powerful in OLTP. Joins without runtime cost, just disk, buffer pool and DML cost.

re: Incorrect Results with Indexed Views

Paul White — Sun, 10 Feb 2013 20:23:26 GMT

I doubt we will ever see indexed views that can be stacked, or which allow outer joins, despite the fact that Microsoft Research have published papers showing that they have built code for this and tested it in SQL Server.

There might be other reasons, but I suspect Microsoft see more general technologies like column-store (for DW) and Hekaton (for OLTP) as being better solutions, in that they are more general and perhaps easier to implement. There's a bit of text about Hekaton here: http://research.microsoft.com/en-us/news/features/hekaton-122012.aspx

re: Incorrect Results with Indexed Views

tobi — Sun, 10 Feb 2013 22:21:16 GMT

The public link to the paper is broken. Here is an alternate one I discovered: http://research.microsoft.com/pubs/170384/bw-tree.pdf

re: Incorrect Results with Indexed Views

Paul White — Sat, 16 Feb 2013 09:46:13 GMT

Thanks, Tobi!