Regression Testing

Regression testing protects against introducing bugs when refactoring code. In SQL, we refactor queries, either because the calling application code is changed and the query must change too, or because we’re hitting problems in production and a new optimized version of the query is being checked in to replace the previous erroneous version.

Regression testing registers the expected results from our queries and then checks actual results against the expected results. Typically, we would run the regression tests each time a query is changed.

Regression test suite in lieu of “tools for testing”

The PostgreSQL project includes many SQL tests to validate its query parser, optimizer, and executor. It uses a framework named the regression tests suite based on a very simple idea:

1. Run a SQL file containing our tests with psql
2. Capture its output to a text file that includes the queries and their results
3. Compare the output with the expected one that is maintained in the repository with the standard diff utility
4. Report any difference as a failure

We can have a look at the PostgreSQL repository to see how it’s done; as an example, we could pick src/test/regress/sql/aggregates.sql, and its matching ...