Debugging SQLPosted: October 17, 2007
I love SQL. Let me count the ways:
- It really fits the relational database model. You only have to work with Hibernate for few days to appreciate the beautiful simplicity of SQL.
- 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.
- Even more important – the concurrency model is relatively simple and as a bonus the DB will detect and resolve deadlocks for you.
- It is easy to read. There are no “obfuscated SQL” contests for a reason.
- 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.