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

The Best Bang for Your Bu(ck)g — When SQL Debugging and Data Provenance Go Hand in Hand

Benjamin DietrichTobias MüllerTorsten Grust

Proceedings of the 19th Int’l Conference on Extending Database Technology (EDBT 2016), Bordeaux, France, March 2016.

A SQL Debugger Built from Spare Parts — Turning a SQL:1999 Database System into Its Own Debugger.

Benjamin DietrichTorsten Grust

Proceedings of the 34th ACM SIGMOD Int’l Conference on the Management of Data (SIGMOD 2015), Melbourne, Australia, June 2015.

Observing SQL Queries in their Natural Habitat.

Torsten GrustJan Rittinger

ACM Transactions on Database Systems (TODS), 38(1), March 2013.

True Language-Level SQL Debugging.

Fabian KliebhanTorsten GrustJan RittingerTom Schreiber

Proceedings of the 14th International Conference on Extending Database Technology (EDBT 2011), Uppsala, Sweden, March 2011.