How long does it take to fix a bug

In the development cycle, it is needless to less we all in some part of our carrier, have injected  bugs in different scale. However, when we were told it is a bug, I am sure most of you try to fix it or prioritize it to fix it.

But can you imagine, if there is a bug in SQL Server for more than 5 years, ranging three SQL Server Products (SQL Server 2005, SQL Server 2008 and R2).

This bug is in CTE.

Now CTE was introduced primarily to support recursive queries.

Let’s say I have a table and data like this.

So I have a hierarchy as following.

Let’s assume that, you need to retrieve, only two levels starting from Richard, here is the query you should use.

so number of levels are handled by , MAXRECURSION = 2 option. In this of course, you will get the result but also and ERROR!!!,

yes, error says,

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

You can say that since it giving the correct result, what is the big deal about this. But the problem here is, since it is a Level 16 error, if you are using this inside of your code, your code will throw an exception.

You have a workaround which I have given in my sample code at the end of this post.

Now this error first appeared in SQL Server 2005 and Microsoft has make sure that they don’t fix this so you can see the same bug in SQL Server 2008 and in R2. I got to know that this was initially reported in 2002 when SQL Server 2005 CTP was released. So it is nine years now.

To confirm the stuff, I checked this in Denali CTP1. Guess what, still this issue in the backlog.

Download sample code from here