This post was originally published in 2018 by former CockroachDB engineer Matt Jibson, who owns goats and makes his own soap. It’s one of the most popular blogs we’ve ever published so we wanted to get it back up on the site for a new crop of blog readers.

Randomized testing is a way for programmers to automate the discovery of interesting test cases that would be difficult or overly time consuming to come up with by hand. CockroachDB uses randomized testing in many parts of its code. I previously wrote about generating random, valid SQL. Since then we’ve added an improved SQL generator to our suite called SQLsmith, inspired by a C compiler tester called Csmith. It improves on the previous tool by generating type and column-aware SQL that usually passes semantic checking and tests the execution logic of the database. It has found over 40 new bugs in just a few months that the previous tool was unable to produce. Here I’ll discuss the evolution of our randomized SQL testing, how the new SQLsmith tool works, and some thoughts on the future of targeted randomized testing.

We think CockroachDB is a great database for many people, and want them to try us out. Not just for new applications, but for existing, large applications as well. The first problem that users with an existing database will hit when trying us out for the first time is getting their data into CockroachDB. For the 1.1 release, we built a new feature that performs high-speed, bulk data import. It works by transforming CSV files into our backup/restore format, then is able to quickly ingest the results.

Rounding in Go is hard to do correctly. That is, given a float64, truncate the fractional part (anything right of the decimal point), and add one to the truncated value if the fractional part was >= 0.5. This problem doesn't come up often, but it does enough that as of this writing, the second hit on Google for golang round is a closed issue from the Go project, which declined to add a Round function to the math package. That issue also includes many community contributions about ways to round.

With the release of CockroachDB beta-20170223, we’d like to announce a new arbitrary-precision decimal package for Go: apd. This package replaces the underlying implementation of the DECIMAL type in CockroachDB and is available for anyone to fork and use. Here we will describe why we made apd, some of its features, and how it improved CockroachDB.

Some months ago I started work on a way to test random SQL statements with CockroachDB. This is important to expose unintended behavior in our server. For example, we want to prevent valid SQL statements from unexpectedly crashing a server or using all of its CPU or memory. We have already performed some small-scale fuzz testing, but fuzz testing often produces un-parseable input since it modifies bytes (although some fuzzers like AFL do attempt to produce clean input). The goal here was to produce valid SQL statements that the parser would accept and the system would then execute. These statements would essentially attempt to try various combinations of valid SQL to panic or otherwise render the system unusable (like consuming all CPU).

In our most recent beta, we added a new feature: time-travel queries. These are `SELECT` queries where you can specify a timestamp, and the data returned will be the data as it was at that time. This has various uses including backups, undo, and historical reporting. The SQL:2011 standard describes this feature, and a few SQL databases (Oracle, MSSQL) have implemented it, in addition to various non-SQL DBs (Datomic). I’d like to introduce this feature: what it is, why we built it, and details about how it works for those interested in CockroachDB’s lower layers.

As CockroachDB approaches beta, user documentation has become increasingly important, and one of the meatiest requirements is documentation of our SQL implementation. For inspiration, I researched how other databases have documented SQL. The most effective example I found was SQLite’s grammar diagrams.

The goal of CockroachDB is to “make data easy,” and while it seems like a stretch now, we eventually want CockroachDB to be able to act as the entire state layer for web applications. We are currently addressing the SQL layer, and a full-text search like ElasticSearch is somewhere ahead on the product horizon. Since Cockroach Labs offered a flexible policy for work on experimental projects, I decided to use mine to experiment with implementing the Redis protocol on top of CockroachDB, attempting to answer the question: Could CockroachDB ever replace Redis?