Principle:ClickHouse ClickHouse Stateless Test Execution
| Knowledge Sources | |
|---|---|
| Domains | Testing, Quality_Assurance |
| Last Updated | 2026-02-08 00:00 GMT |
Overview
Reference-based functional testing that validates database engine correctness by comparing query output against known-good reference files.
Description
ClickHouse stateless tests verify SQL correctness at the behavioral level. Each test consists of a test file and a corresponding .reference file. The test file contains queries or commands to execute; the reference file contains the exact expected output. The test runner executes the test, captures its standard output, and compares it byte-for-byte against the reference. A mismatch means the test has failed.
Tests reside in tests/queries/0_stateless/ and support multiple formats:
.sql-- Plain SQL statements fed toclickhouse-clientline by line..sql.j2-- Jinja2-templated SQL that is rendered before execution, enabling parameterized tests..sh-- Shell scripts that can orchestrate multiple client invocations, manipulate files, or test edge cases requiring process-level control..py-- Python scripts for tests that need programmatic logic or library access..expect-- Expect scripts for testing interactive terminal behavior.
Each test file is paired with a .reference file of the same base name (e.g., 01234_my_test.sql and 01234_my_test.reference). The reference file is the single source of truth for what constitutes correct output.
Database name normalization is an important aspect of the comparison. The test runner creates a randomly named database for each test to avoid collisions during parallel execution. Before comparing output to the reference file, the random database name is replaced with default, so reference files can be written in a stable, deterministic form.
Usage
Use stateless test execution whenever you need to validate that a change to the ClickHouse codebase does not alter the observable behavior of SQL queries. This applies to:
- Adding new SQL functions, table engines, or syntax.
- Modifying the query optimizer or execution engine.
- Fixing bugs -- the reference file captures the corrected behavior.
- Refactoring internals -- tests confirm no regressions.
Theoretical Basis
Reference-based testing (also called golden-file testing or snapshot testing) is a form of oracle-based testing where the oracle is a previously recorded correct output. It is especially effective for database engines because:
- Output is deterministic: SQL queries over fixed data produce deterministic results (assuming controlled ordering), making byte-level comparison reliable.
- Low maintenance for stable interfaces: once a reference file is created it only changes when the intended behavior changes.
- Broad coverage with minimal effort: a single test can exercise parsing, optimization, execution, and serialization simply by issuing a query and checking the output.
The multi-format support (SQL, shell, Python, Expect) recognizes that not all behaviors can be tested with pure SQL. Shell tests can verify error handling, client behavior, and server restarts. Python tests can drive complex scenarios. Expect tests cover interactive terminal features.
The database name normalization technique solves the tension between isolation (each test needs its own namespace to run safely in parallel) and determinism (reference files must be stable). By generating a random database name at runtime and then normalizing it to default in the output, the runner achieves both properties simultaneously.