In our database systems lectures, we regularly teach how SQL can be used to express algorithms such as transitive closure, shortest paths, and graph traversals. While SQL is expressive enough to formulate these algorithms, doing so often lacks ergonimics and requires complex query patterns or workarounds—especially when recursive Common Table Expressions (CTEs) are involved. In case of recursive CTEs, execution is done in multiple iterations, with intermediate results being generated and consumed in each step. However, SQL does not provide a straightforward way to observe these intermediate results. This is a significant limitation for educational purposes, as understanding the step-by-step execution is crucial for debugging and understanding how the SQL queries work.

The goal of this thesis is to implement a mechanism that allows users to inspect intermediate results of arbitrary (sub-)queries. Inspired by the UNIX tee command, which duplicates a data stream while allowing it to continue unchanged, we propose the so-called TEE operator for SQL. This operator will enable users to “tap into” arbitrary points in their SQL queries, including recursive CTEs, to observe intermediate results without altering the final outcome of the computation. The intermediate results can be logged to the console, written to a file, or stored in a temporary table for further analysis.

This functionality can be implemented as a DuckDB extension, and we plan to publish it as an open-source community extension for general availability.

Contact