Opensource internals

ClickHouse Fuzzes Itself with Oracles: Two Runs, One MD5

14 min read Jay Hubbert

BuzzHouse pairs every random query with a second, equivalent query and compares the two result files by MD5, so the fuzzer catches wrong answers and not just crashes.

TL;DR: BuzzHouse generates a random query, but never trusts its answer. It generates a second query — same data, different route — writes both results to files, and compares the two files with an MD5 digest. Different digests mean a wrong answer, and the fuzzer stops with BUZZHOUSE_ORACLE.

A fuzzer’s job is usually to find crashes. You mutate bytes, you run the program, and if the program dies with a signal you have found a bug. That model works fine for a parser, a deserializer, or a network stack.

It does not work for a database. The most damaging bugs in a query engine are not crashes — they are wrong answers. SELECT sum(x) returns a number. The number is a UInt64. Nothing crashes. The query is fast, the log is clean, and the customer quietly gets 7 % off their revenue because a LowCardinality dictionary was read with the wrong granularity.

So how do you fuzz a database for wrong answers? You need a reference implementation. The obvious reference is “the same query on the old version of the server” — but the fuzzer does not have the old version, and building one per commit is a maintenance nightmare.

ClickHouse’s answer, implemented in src/Client/BuzzHouse/, is the oracle: a second query that is semantically equivalent by construction but executes a different code path. If the two disagree, one of them is wrong, and you do not need to know which.

The shape of an oracle

The generic shape is four statements, executed in order, with the results of the first and the last compared:

text 
    sq1:  <the query under test>            --> writes /var/lib/clickhouse/user_files/query.data
    sq2:  <intermediate perturbation>        (optional: a SET, an OPTIMIZE, a DETACH)
    sq3:  <the second, equivalent query>    --> writes the same file
                                                 (or a peer's file)

    md5(sq1's file) == md5(sq3's file) ?  fine : BUZZHOUSE_ORACLE

The driver lives in programs/client/FuzzLoop.cpp, and the comparison is deliberately tiny — two functions on QueryOracle:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:2018
void QueryOracle::processFirstOracleQueryResult(const int errcode, ExternalIntegrations & ei)
{
    if (can_test_oracle_result)
    {
        if (!errcode)
        {
            if (measure_performance)
            {
                other_steps_success &= ei.getPerformanceMetricsForLastQuery(PeerTableDatabase::None, this->res1);
            }
            else
            {
                md5_hash1.hashFile(qcfile.generic_string(), first_digest);
            }
        }
        first_errcode = errcode;
    }
}

and the second half, which is where the bug is reported:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:2037
void QueryOracle::processSecondOracleQueryResult(const int errcode, ExternalIntegrations & ei, const String & oracle_name)
{
    if (other_steps_success && can_test_oracle_result)
    {
        if (can_test_success && ((first_errcode && !errcode) || (!first_errcode && errcode))
            && !fc.oracle_ignore_error_codes.contains(static_cast<uint32_t>(first_errcode ? first_errcode : errcode)))
        {
            throw DB::Exception(
                DB::ErrorCodes::BUZZHOUSE_ORACLE,
                "{}: failed with different success results: {} vs {}",
                oracle_name,
                DB::ErrorCodes::getName(first_errcode),
                DB::ErrorCodes::getName(errcode));
        }
        if (!first_errcode && !errcode)
        {
            if (measure_performance)
            {
                if (ei.getPerformanceMetricsForLastQuery(PeerTableDatabase::ClickHouse, this->res2))
                {
                    fc.comparePerformanceResults(oracle_name, this->res1, this->res2);
                }
            }
            else
            {
                md5_hash2.hashFile((peer_query == PeerQuery::ClickHouseOnly ? qfile_peer : qcfile).generic_string(), second_digest);
                if (first_digest != second_digest)
                {
                    throw DB::Exception(DB::ErrorCodes::BUZZHOUSE_ORACLE, "{}: failed with different result sets", oracle_name);
                }
            }
        }
    }
}

Two things to notice.

First, the comparison is on digests of files, not on rows in memory. The oracle query is not SELECT ... — it is SELECT ... INTO OUTFILE, so the result is materialised in one of three text formats and then hashed:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:17
const DB::Strings QueryOracle::oracleFormats = {"CSV", "TabSeparated", "Values"};

The point of writing to a file and hashing it is that the comparison is format-level: two results that differ in a single byte, in a single column, in a single row, produce a different digest. There is no “compare the rows field by field” logic that could itself be buggy.

Second, the digest is computed by a hand-rolled MD5 over the file in chunks, not by loading the file into memory:

cpp 
// src/Client/BuzzHouse/Utils/MD5Impl.cpp:17
void MD5Impl::hashFile(const String & filePath, Poco::DigestEngine::Digest & res)
{
    std::ifstream file(filePath, std::ios::binary);

    if (!file)
    {
        throw DB::Exception(DB::ErrorCodes::BUZZHOUSE, "Could not open file: {}", filePath);
    }
    while (file.read(reinterpret_cast<char *>(inputBuffer), inputBufferSize) || file.gcount() > 0)
    {
        ctx.update(reinterpret_cast<const uint8_t *>(inputBuffer), file.gcount());
    }
    res = ctx.digest();
}

Oracle #1: the same query, two settings

The simplest oracle needs no second query at all. Run the same query twice, with one setting changed, and the answer must not change.

The driver generates the settings first, then the query, then the second set of settings, then the query again:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:1521
bool QueryOracle::generateFirstSetting(RandomGenerator & rg, SQLQuery & sq1)
{
    const bool use_settings = rg.nextMediumNumber() < 86;

    /// Most of the times use SET command, other times SYSTEM
    if (use_settings)
    {
        std::uniform_int_distribution<uint32_t> settings_range(1, 40);
        const uint32_t nsets = settings_range(rg.generator);
        SettingValues * sv = sq1.mutable_single_query()->mutable_explain()->mutable_inner_query()->mutable_setting_values();

        nsettings.clear();
        for (uint32_t i = 0; i < nsets; i++)
        {
            const auto & toPickFrom = (hotSettings.empty() || rg.nextMediumNumber() < 94) ? queryOracleSettings : hotSettings;
            const String & setting = rg.pickRandomly(toPickFrom);
            const CHSetting & chs = queryOracleSettings.at(setting);
            SetValue * setv = i == 0 ? sv->mutable_set_value() : sv->add_other_values();

            setv->set_property(setting);
            if (chs.oracle_values.size() == 2)
            {
                if (rg.nextBool())
                {
                    setv->set_value(*chs.oracle_values.begin());
                    nsettings.push_back(*std::next(chs.oracle_values.begin(), 1));
                }
                else
                {
                    setv->set_value(*std::next(chs.oracle_values.begin(), 1));
                    nsettings.push_back(*(chs.oracle_values.begin()));
                }
            }
            else
            {
                const String & fvalue = rg.pickRandomly(chs.oracle_values);
                String svalue = rg.pickRandomly(chs.oracle_values);

                for (uint32_t j = 0; j < 4 && fvalue == svalue; j++)
                {
                    /// Pick another value until they are different
                    svalue = rg.pickRandomly(chs.oracle_values);
                }
                setv->set_value(fvalue);
                nsettings.push_back(svalue);
            }
            can_test_oracle_result &= !chs.changes_behavior;
        }
    }
    return use_settings;
}

The interesting line is the last one: can_test_oracle_result &= !chs.changes_behavior;.

Every setting in the fuzzer’s catalogue carries a flag saying whether toggling it is allowed to change the meaning of the query. max_threads does not:

cpp 
// src/Client/BuzzHouse/Generator/RandomSettings.h:58
const auto threadSetting = CHSetting(
    [](RandomGenerator & rg, FuzzConfig &) { return std::to_string(rg.randomInt<uint32_t>(0, std::thread::hardware_concurrency())); },
    {"0", "1", "2", std::to_string(std::thread::hardware_concurrency())},
    false);

but allow_lossy_numeric_supertype obviously does — it is explicitly marked:

cpp 
// src/Client/BuzzHouse/Generator/SessionSettings.cpp:664
    {"allow_lossy_numeric_supertype", CHSetting(trueOrFalse, {"0", "1"}, true)},

(the third argument of CHSetting is changes_behavior). If a setting that changes behaviour is used, the whole result comparison is voided rather than allowed to produce a false positive. This is the difference between a fuzzer people trust and a fuzzer people mute.

The two settings are guaranteed to differ — the for (uint32_t j = 0; j < 4 && fvalue == svalue; j++) loop above picks a second, different value for the second run, because an oracle that runs the same thing twice proves nothing.

Oracle #2: the predicate rewrite

The second oracle is the classic metamorphic test: rewrite the query into a form that must return the same number but goes through a completely different execution path.

BuzzHouse implements three variants, chosen at random:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:194
/// Correctness query oracle
/// WHERE_ONLY:       SELECT count() FROM T WHERE pred = TRUE
/// HAVING_ONLY:      SELECT ifNull(SUM(cnt),0) FROM (SELECT count() AS cnt FROM T GROUP BY g HAVING pred(g) = TRUE) sub
/// WHERE_AND_HAVING: SELECT ifNull(SUM(cnt),0) FROM (SELECT count() AS cnt FROM T WHERE pred1 = TRUE GROUP BY g HAVING pred2(g) = TRUE) sub

and the “flat” rewrite that the second query uses:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:328
/// WHERE_ONLY:       SELECT ifNull(SUM(pred = TRUE), 0) FROM T
/// HAVING_ONLY:      SELECT ifNull(SUM(having_pred = TRUE), 0) FROM T
/// WHERE_AND_HAVING: SELECT ifNull(SUM(having_pred = TRUE), 0) FROM T WHERE where_pred = TRUE

The HAVING variant is the interesting one. In the first query the predicate runs after the aggregation, on groups; in the second query the same predicate is evaluated per row and then summed. WHERE filtering, GROUP BY, the HAVING filter and the aggregate all get a second implementation of the same arithmetic. If the aggregate engine and the filter disagree about a single row, the counts differ.

Concretely, for a predicate pred:

sql 
-- sq1: the predicate filters rows, count() counts them
SELECT count() FROM t WHERE ((a + b) > 10) = TRUE;

-- sq2: the same predicate, evaluated per row and summed
SELECT ifNull(SUM(((a + b) > 10) = TRUE), 0) FROM t;

The subtlety is that the two queries do not have the same column footprint, and the fuzzer knows it:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:234
    /// In the GROUP BY/HAVING combinations the first query references the GROUP BY
    /// key columns while the second (flat) rewrite references only the predicate
    /// columns. So a column that fails analysis only in the first query (e.g. a
    /// group key that cannot be resolved) makes the two queries differ in success
    /// without any result divergence - a false positive. Only the result is
    /// comparable here, not the success status. WHERE_ONLY keeps the same column
    /// footprint in both queries, so its success comparison stays meaningful.
    can_test_success &= oracle_combination == OracleCombination::WHERE_ONLY;

This is the kind of detail that separates a fuzzer that finds real bugs from one that files a bug every ten minutes. The oracle narrows its own claim when the two forms are not comparable.

Oracle #3: the roundtrip

Some functions are supposed to be invertible. hex/unhex, bin/unbin, base64Encode/base64Decode — if the roundtrip does not return the original value, the encoder or the decoder is broken.

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.h:118
    /// Roundtrip oracle: verifies that encoding/encryption functions compose correctly.
    /// Query 1: SELECT count() FROM <from_clause> WHERE col IS NOT NULL  (baseline)
    /// Query 2: SELECT count() FROM <from_clause> WHERE roundtrip(col) = col
    /// Both queries must return the same value, catching any roundtrip failures.
    void generateRoundtripOracleQueries(RandomGenerator & rg, StatementGenerator & gen, SQLQuery & sq1, SQLQuery & sq2);

The predicate is built as a string, deliberately, so that the same random expression appears on both sides:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.cpp:446
        case 0:
            /// hex/unhex
            roundtrip_pred = fmt::format("unhex(hex({0})) = {0}", val);
            break;

Note the trick that makes this work on Nullable columns without a special case: NULL = x evaluates to NULL, not TRUE, so a NULL row is excluded from the second query’s count by the same predicate that excludes it from the first query’s IS NOT NULL filter. The oracle is correct by construction rather than by special-casing.

Oracle #4: dump, perturb, read back

The most aggressive oracle attacks the storage engine rather than the query engine. It:

  1. dumps a table to a file (sq1),
  2. perturbs the table — OPTIMIZE, DETACH/ATTACH, BACKUP/RESTORE, ALTER UPDATE, FREEZE, MOVE PARTITION, REPLACE PARTITION, RENAME, TRUNCATE + re-insert (13 strategies in total),
  3. reads the table back (sq2),
  4. compares the two files.
cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.h:10
enum class DumpOracleStrategy
{
    DO_NOTHING = 0,
    REINSERT_TABLE = 1,
    OPTIMIZE = 2,
    REATTACH = 3,
    BACKUP_RESTORE = 4,
    ALTER_UPDATE = 5,
    INSERT_COUNT = 6,
    RENAME_BACK = 7,
    FREEZE_UNFREEZE = 8,
    MOVE_PARTITION = 9,
    REPLACE_PARTITION = 10,
    ALTER_COLUMN = 11,
    ALTER_TABLE = 12,
    TRUNCATE_COUNT = 13
};

This is the oracle that finds the bugs users actually hit: a merge that loses rows, a REPLACE PARTITION that keeps the old parts, an ALTER UPDATE that mutates the wrong rows, a detach/attach cycle that changes the data.

The driver picks the strategy with weights that depend on the table’s engine — FREEZE only makes sense on a MergeTree, BACKUP_RESTORE only if backups are enabled:

cpp 
// programs/client/FuzzLoop.cpp:950
            rg.pickWeighted(
                {{15 * static_cast<uint32_t>(test_content && tbl.get().can_run_merges),
                  [&]() { strategy = BuzzHouse::DumpOracleStrategy::OPTIMIZE; }},
                 {25 * static_cast<uint32_t>(test_content), [&]() { strategy = BuzzHouse::DumpOracleStrategy::REATTACH; }},
                 {10 * static_cast<uint32_t>(fuzz_config->enable_backups && test_content),
                  [&]() { strategy = BuzzHouse::DumpOracleStrategy::BACKUP_RESTORE; }},
                 {40 * static_cast<uint32_t>(test_content), [&]() { strategy = BuzzHouse::DumpOracleStrategy::ALTER_TABLE; }},
                 {20 * static_cast<uint32_t>(test_content), [&]() { strategy = BuzzHouse::DumpOracleStrategy::ALTER_UPDATE; }},

Oracle #5: a second server

The last oracle is the one people expect: run the query on a peer database and compare. BuzzHouse supports MySQL, PostgreSQL, SQLite, MongoDB, Redis, MinIO, Azurite, an HTTP server, and — most usefully — a second ClickHouse server:

cpp 
// src/Client/BuzzHouse/Generator/ExternalIntegrations.h:444
    bool hasClickHouseExtraServerConnection() const { return clickhouse != nullptr; }

The peer comparison is not a digest comparison (the two databases will not agree on formats); it is a comparison of success, plus a comparison of performance:

cpp 
// programs/client/FuzzLoop.cpp:454
        if (can_compare)
        {
            /// Always run query on peer server
            fmt::print(stdout, "Running query on peer server\n");
            peer_success &= !external_integrations->performQuery(BuzzHouse::PeerTableDatabase::ClickHouse, query_to_execute);
        }
        if (can_compare && fuzz_config->compare_success_results && peer_success != !have_error)
        {
            exitOnOracleMismatch(query_to_execute, "AST Fuzzer: The peer server got a different success result");
        }

and the performance comparison is a threshold, not equality — a peer server that is 30 % slower is a finding, a peer that is 0.1 % slower is noise:

cpp 
// src/Client/BuzzHouse/Generator/FuzzConfig.cpp:1458
    if (this->measure_performance)
    {
        for (const auto & [key, val] : metrics)
        {
            if (val.enabled)
            {
                if (val.minimum < server.metrics.at(key)
                    && server.metrics.at(key) > static_cast<uint64_t>(
                           static_cast<double>(peer.metrics.at(key)) * (1 + (static_cast<double>(val.threshold) / 100.0))))
                {
                    throw DB::Exception(
                        DB::ErrorCodes::BUZZHOUSE_ORACLE,
                        "{}: ClickHouse peer server {}: {} was less than the target server: {}",
                        oracle_name,
                        key,
                        peer.result_strings.at(key),
                        server.result_strings.at(key));
                }
            }
        }
    }

The bookkeeping that keeps it honest

Every oracle shares the same state machine on QueryOracle, and the flags are the whole discipline:

cpp 
// src/Client/BuzzHouse/Generator/QueryOracle.h:60
    PeerQuery peer_query = PeerQuery::AllPeers;
    int first_errcode = 0;
    uint64_t nrows = 0;
    std::uniform_int_distribution<uint64_t> rows_dist;
    bool other_steps_success = true;
    OracleCombination oracle_combination = OracleCombination::WHERE_ONLY;
    bool can_test_oracle_result;
    bool can_test_success;
    bool measure_performance;
    bool compare_explain = false;
  • can_test_oracle_result — is the result comparable at all? Turned off by behaviour-changing settings, by non-deterministic functions, by engine UDFs.
  • can_test_success — is the success status comparable? Turned off for the HAVING variants, and for the roundtrip oracle (can_test_success = false; /// Don't compare query success, queries are different).
  • other_steps_success — did the intermediate steps (the SET, the OPTIMIZE, the ATTACH) succeed? If an intermediate step failed, the comparison is meaningless and is skipped entirely.

And on the client side, the loop is written so that a failure is fatal and immediate, not a warning in a log file nobody reads:

cpp 
// programs/client/FuzzLoop.cpp:66
/// An AST fuzzer oracle mismatch is a finding: print the reproducer to stderr (fuzzer.log in CI) and exit at once
/// with the low byte of `AST_FUZZER_ORACLE_MISMATCH`, so the run fails fast instead of ending as a clean timeout
[[noreturn]] static void exitOnOracleMismatch(const String & query, const String & details)
{
    fmt::print(
        stderr,
        "\n\n"
        "=== AST FUZZER ORACLE MISMATCH (fatal) ===\n"
        "Client-side query (a server-side oracle may have mutated it further):\n"
        "  {}\n"
        "{}\n"
        "==========================================\n",
        query,
        details);
    (void)std::fflush(stderr);
    (void)std::fflush(stdout);
    _exit(ErrorCodes::AST_FUZZER_ORACLE_MISMATCH & 0xFF);
}

Note the deliberate choice of a non-LOGICAL_ERROR code, explained in the comment right above the call site: in a sanitizer build, LOGICAL_ERROR aborts inside the exception constructor, before the message can propagate back to the client.

Reproducibility

An oracle finding is worthless if you cannot reproduce it. Two mechanisms make it reproducible.

The first is the seed. The whole fuzzer is driven by one pcg64_fast:

cpp 
// src/Client/BuzzHouse/Generator/RandomGenerator.h:128
    pcg64_fast generator;

    RandomGenerator(const uint64_t in_seed, const uint32_t min_string_length, const uint32_t max_string_length, const bool limited)
        : seed(in_seed ? in_seed : randomSeed())

and the seed is written into the transcript as the very first line:

cpp 
// programs/client/FuzzLoop.cpp:762
        fuzz_config->outf << "--Session seed: " << rg.getSeed() << std::endl;

The second is the transcript. Every statement the fuzzer runs is echoed into out.sql as it runs, not reconstructed afterwards:

cpp 
// programs/client/FuzzLoop.cpp:876
                    {20 * static_cast<uint32_t>(fuzz_config->allow_query_oracles),
                     [&]()
                     {
                         qo.resetOracleValues();
                         qo.generateCorrectnessTestFirstQuery(rg, gen, sq1);
                         BuzzHouse::SQLQueryToString(full_query, sq1);
                         fuzz_config->outf << full_query << std::endl;
                         server_up &= processBuzzHouseQuery(full_query);
                         qo.processFirstOracleQueryResult(error_code, *external_integrations);

That transcript is also a replayable script: the --Reconnecting client, --External table <hex> and --External command with seed <n> markers are recognised on replay, so the exact external side effect (a Dolor catalog call, a table creation in another engine) can be re-triggered with the same seed.

Trade-offs

What the oracle buys. Wrong answers become detectable without a reference implementation, without a golden file, and without a human. The metamorphic rewrites (HAVING → flat SUM, dump → perturb → read back) are all self-evident equivalences, so the oracle needs no domain knowledge to decide what “correct” means. And because the comparison is an MD5 of a file, it is total: there is no field-by-field comparison logic that could hide a difference in one row out of a million.

What it costs.

  • The oracle is only as good as its equivalence. A rewrite that is not actually equivalent produces false positives. This is why the code is full of can_test_success &= ... narrowing, and why oracle_ignore_error_codes exists.
  • Everything is materialised to disk. Each oracle run writes its result to a file and hashes it. The fuzzer is I/O-bound, not CPU-bound, and it needs a writable user_files directory on the server.
  • Half the oracles are disabled by default. compare_success_results defaults to false in FuzzConfig, and the dump oracle needs use_dump_table_oracle > 0. The cheap oracles (settings, correctness) run always; the expensive ones are opt-in.
  • The digest hides the difference. When the MD5s differ, the fuzzer says “different result sets” and stops. It does not tell you which row differed. There is a trade-off here: a row-level diff would give better diagnostics but would need the two result sets in memory at once, and would need its own comparison logic — which is exactly the kind of code that has its own bugs.
  • The peer oracle needs a second server. It is the most convincing oracle and the most expensive to set up, so it is optional and off by default.

The one thing to remember: the fuzzer does not check whether the answer is correct; it checks whether two different routes to the answer agree. That is a weaker claim — it cannot catch a bug that affects both routes equally — but it is a claim that can be made automatically, thousands of times a day, without a human reading any output.

The part people get wrong: an oracle mismatch is not automatically a bug in the new code. It is a disagreement, and half the time the “new” path is right and the oracle’s rewrite is subtly invalid. This is why the fuzzer throws BUZZHOUSE_ORACLE (an exception with a message) rather than LOGICAL_ERROR, and why a human still has to look at the transcript.

What to go do about it: run the fuzzer with compare_success_results enabled against a branch that changes query semantics, and read the out.sql transcript next to the exception message. The transcript is the reproducer; the message tells you which oracle fired.

ClickhouseTestingFuzzingInternals