Subquery Stumper

Monday Apr 16th 2001 by Steve Jones

Share:

A problem and stumper involving a subquery

Introduction

I do not use subqueries that often, but they are definitely a tool in my T-SQL drawer. I understand
(I think) how subqueries work and can read them to determine what the purpose of the query is.
There are times that a subquery will fit a situation better than any join that I can come up with.

That being said, I am not a guru or an expert on subqueries. There are better resources for very complicated and elegant subquery
solutions to problems. However, I came across an issue recently where a subquery was behaving strangely and
thought it would make interesting reading once I determined the problem. As an FYI, I did not solve this problem
in the first half hour or so when I looked at it. I was busy and had to let it go for a couple days and when
I came back to it, another good 30-60 minutes was spent determining the cause of the problem and verifying that
I had solved it.

The Problem

A developer recently sent me an email where he was asking what the difference was between the following
two queries:

This worked fine. I then verified that the Atttr table has an entry with 53 and it contains
a value of 'Item'. All good so far.

At this point, I was definitely a little stumped. Why would one query work fine and
the other one fail. They both are looking to achieve the same goal. It was then that
I decided to check the query plans. I have been a writing a book for the SQL Server 2000
certification exam and one of the chapters I had just finished was on performance. So I
examined the execution plans for both queries. The query with the error (the first one)
did not produce an execution plan, so I settled for getting the plan for the second query.
Here it is:

In this query, the first thing that happens is the OrdLineDtl table is scanned for all rows with
a value of 53. This result is then filtered based on the AttrVal = 100. Next the join
occurs and the aggregate is computed, etc. So I started to think about why the first
query would not follow the same plan.

After a cup of coffee, a few walks around the office, and a lot of staring at the screen,
it finally hit me. I changed the query slightly to test my hypothesis to the following:

The problem that I was encountering was that the AttrVal field. I had initially
assumed (incorrectly) that the difference in the two queries (the subquery) was the problem.
In fact, the issue occurred because of the query plan that SQL Server was choosing. For the
query above, SQL Server will first evaluate the subquery and execute an ASSERT in the
query plan which results in 53. At this point in the execution plan, the OrdLineDtl
table is scanned for rows which match the @this_id value. A number of rows match
this qualification, though not all of them are numerical values. As this matching occurs, an
implicit conversion from the AttrVal varchar values to numeric values occurs. For the
rows that cannot be converted, an error occurs and the query ends.

We fixed this query by using a stored procedure to calculate the 53 value and then include that in
the query as a variable. This way, we still have a dynamic system that can handle
the Attr table changing ids (such as a delete of the value and then an insert).

Conclusions

I have never encountered this situation before and it took me more time than I would have
thought to find the problem. It was interesting to me because the problem was not the
difference between the two queries (the obvious choice) and instead was because of the
query plans chosen by SQL Server.

As always, I welcome feedback and hope that this may help someone in the future
when they have a problem with a subquery.