I-Rex: An Interactive Relational Query Explainer for SQL

We demonstrate I-REX1, a system designed to help users understand SQL query evaluation and debug SQL queries. I-REX lets users interactively “trace” the evaluation of complex SQL queries, including those with correlated subqueries. I-REX also explains why a query returns an incorrect answer with respect to a reference query over a test database instance—a common use case in education and software regression testing. To avoid the cognitive overload caused by debugging over a large database instance, I-REX lets users focus on smaller instances contained in the large one (which we call “counterexamples”) that still distinguish the two queries. Supporting these features for SQL queries poses two key challenges. First, unlike debugging for procedural languages, it is not clear how to trace a declarative SQL query, because its execution plan often differs from how it was originally written. I-REX offers a novel interface for tracing SQL query evaluation in a way faithful to how queries are written syntactically, even for complex queries involving multiple levels of nesting and correlation. Second, we need a method for finding small counterexamples that handles the complexity of practical SQL. I-REX extends provenance support for SQL in non-trivial ways to work with various query constructs. This demonstration walks through use cases in which I-REX helps users understand and debug SQL queries.

Duke Scholars

Author Sudeepa Roy Computer Science

Author Jun Yang Computer Science