Habitat

True Language-Level SQL Debugging

We present Habitat, a declarative observational debugger for SQL. Habitat facilitates true language-level (not: plan-level) debugging of, probably flawed, SQL queries that yield unexpected results. Users may mark arbitrary SQL subexpressions-ranging from literals, over fragments of predicates, to entire subquery blocks-to observe whether these evaluate as expected. The SQL query is transformed to additionally record the values of marked subexpressions during the process of evaluation. The notion of SQL subexpressions as functions of free variables allows to locate the observed values in the query evaluation process, in order to merge multiple observations into a single (nested) tabular display. This way a user of Habitat can explore the complex relationship of various observations. Some additional features furthermore ease the interpretation of large results. Currently we are working on a prototype of Habitat on top of the open source DBMS PostgreSQL. A first release of this prototype will soon be available for download on this website.

The Basic Idea: Mark & Observe

We built the observational SQL debugger Habitat that helps users to identify errors, or “bugs”, buried in queries. With Habitat, we pursue language-level debugging of logical flaws-that lead SQL queries to yield unexpected results-and do not consider query engine or performance debugging. Habitat enables users to mark arbitrary suspect (or interesting) SQL subexpressions of a buggy query. Given such markings, Habitat transforms the SQL query, so the target DBMS additionally logs all intermediate values of the suspect subexpression. Users now can observe and correlate these values, then narrow or widen their markings to hunt down the bug in an iterative, interactive process.

Publications