Debugging SQL

I love SQL. Let me count the ways:

  1. It really fits the relational database model. You only have  to work with Hibernate for few days to appreciate the beautiful simplicity of SQL.
  2. SQL doesn’t have direct memory access. Which means that I don’t have all those freaky  bugs that C developers have, where you write random chunks of memory by mistake and get totally random results that are impossible to debug.
  3. Even more important – the concurrency model is relatively simple and as a bonus the DB will detect and resolve deadlocks for you.
  4. It is easy to read. There are no “obfuscated SQL” contests for a reason.
  5. Eye on Oracle got few more reasons, just in case you need convincing.

However, one of the things I really wish I had is a nice SQL debugger, because I’m tired of picking apart complicated SQL statements by hand just to find the bad join that causes duplicate rows or missing rows or whatever.

I realize that debugging SQL is not as trivial as it would be for C, because an SQL statement does not contain a list of well defined operations that one can step though one by one while watching key variables.  But there are a bunch of logical set operations that make up each query. Even if Oracle’s implementation of them can get complicated – an “exists in” is logically a nested loop, join is a set product which can then get filtered, etc.

So, why not build a tool that allows you to walk through this operation. The tool should allow you to see the data set after each operation. This way it should be much easier to find the join that breaks everything. After all, we go through a very similar process every time we try to debug a query. It is clear that we need a tool that will support the task that we do so many times a day.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s