Testing ClickHouse

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/development/tests.md)

## Test types[​](#test-types "Direct link to Test types")

There are following tests in ClickHouse:

- [Functional tests](#functional-tests) - a set of queries and scripts which include the following intersecting subsets
  - [Fast test](#running-fast-tests) - the minimal subset
  - [Stateless tests](#running-stateless-tests) which do not require populating databases with data
  - Sequential tests which cannot be run in parallel
- [Integration tests](#integration-tests), run by `pytest` in a cluster
- [Unit tests](#unit-tests)
- [Performance tests](#performance-tests)
- [Build tests](#build-tests)
- [Sanitizers](#sanitizers)
- [Fuzzers](#fuzzing)
  and some others, see the sections below.

## Functional tests[​](#functional-tests "Direct link to Functional tests")

Functional tests are the most simple and convenient to use.
Most of ClickHouse features can be tested with functional tests and they are mandatory to use for every change in ClickHouse code that can be tested that way.

Each functional test sends one or multiple queries to the running ClickHouse server and compares the result with reference.

Tests are located in `./tests/queries` directory.

Each test can be one of two types: `.sql` and `.sh`.

- An `.sql` test is the simple SQL script that is piped to `clickhouse-client`.
- An `.sh` test is a script that is run by itself.

SQL tests are generally preferable to `.sh` tests.
You should use `.sh` tests only when you have to test some feature that cannot be exercised from pure SQL, such as piping some input data into `clickhouse-client` or testing `clickhouse-local`.

Note

A common mistake when testing data types `DateTime` and `DateTime64` is assuming that the server uses a specific time zone (e.g. "UTC"). This is not the case, time zones in CI test runs
are deliberately randomized. The easiest workaround is to specify the time zone for test values explicitly, e.g. `toDateTime64(val, 3, 'Europe/Amsterdam')`.

### Running a test locally[​](#running-a-test-locally "Direct link to Running a test locally")

Start the ClickHouse server locally, listening on the default port (9000).
To run, for example, the test `01428_hash_set_nan_key`, change to the repository folder and run the following command:

```
PATH=<path to clickhouse-client>:$PATH tests/clickhouse-test 01428_hash_set_nan_key
```

Test results (`stderr` and `stdout`) are written to files `01428_hash_set_nan_key.[stderr|stdout]` which are located next the test itself (for `queries/0_stateless/foo.sql`, the output will be in `queries/0_stateless/foo.stdout`).

See `tests/clickhouse-test --help` for all options of `clickhouse-test`.
You can run all tests or run subset of tests by providing a filter for test names: `./clickhouse-test substring`.
There are also options to run tests in parallel or in random order.

### Running fast tests[​](#running-fast-tests "Direct link to Running fast tests")

You may need a decently powerful machine to run a subset of tests (called "Fast test"). The following works on `t3.2xlarge` AWS amd64 Ubuntu instance with 100 GB storage.

1. Install prerequisites and re-login.

```
sudo apt-get update
sudo apt-get install docker.io
sudo usermod -aG docker "$USER"
```

2. Get the source code.

```
git clone --single-branch https://github.com/ClickHouse/ClickHouse
cd ClickHouse
```

3. Build code and run "fast tests".

```
python -m ci.praktika run fast
```

You should get

```
Failed: 0, Passed: 7394, Skipped: 1795
```

If you leave the run unattended, you may use `nohup` or `disown` to keep it running after the `ssh` connection is lost.

### Running stateless tests[​](#running-stateless-tests "Direct link to Running stateless tests")

You may need a decently powerful machine to run stateless tests. The following works on `m7i.8xlarge` AWS amd64 Ubuntu instance with 200 GB storage.

1. Install prerequisites and re-login.

```
sudo apt-get update
sudo apt-get install docker.io
sudo usermod -aG docker "$USER"
sudo tee /etc/docker/daemon.json <<'EOF'
{
  "ipv6": true,
  "ip6tables": true
}
EOF
sudo systemctl restart docker
```

2. Get the source code.

```
git clone --single-branch https://github.com/ClickHouse/ClickHouse
cd ClickHouse
```

3. Build the code.

```
python -m ci.praktika run build_debug
cp ci/tmp/build/programs/clickhouse ci/tmp
```

4. Run stateless tests which can be run in parallel.

```
python -m ci.praktika run functional
```

You should get

```
Failed: 0, Passed: 8497, Skipped: 103
```

Note. `python -m ci.praktika run` invocations run a specific continuous integration job, you can read more about ClickHouse CI [here](/docs/development/continuous-integration#running-stateless-tests).

### Adding a new test[​](#adding-a-new-test "Direct link to Adding a new test")

To add new test, first create a `.sql` or `.sh` file in `queries/0_stateless` directory.
Then generate the corresponding `.reference` file using `clickhouse-client < 12345_test.sql > 12345_test.reference` or `./12345_test.sh > ./12345_test.reference`.

Tests should only create, drop, select from, etc. tables in database `test` which is automatically created beforehand.
It is okay to use temporary tables.

To set up the same environment as in CI locally, install the test configurations (they will use a Zookeeper mock implementation and adjust some settings)

```
cd <repository>/tests/config
sudo ./install.sh
```

Note

Tests should be

- be minimal: only create the minimally needed tables, columns and, complexity,
- be fast: not take longer than a few seconds (better: sub-seconds),
- be correct and deterministic: fails if and only if the feature-under-test is not working,
- be isolated/stateless: don't rely on environment and timing
- be exhaustive: cover corner cases like zeros, nulls, empty sets, exceptions (negative tests, use syntax `-- { serverError xyz }` and `-- { clientError xyz }` for that),
- clean up tables at the end of the test (in case of leftovers),
- make sure the other tests don't test the same stuff (i.e. grep first).

### Restricting test runs[​](#restricting-test-runs "Direct link to Restricting test runs")

A test can have zero or more *tags* specifying restrictions in which contexts the test runs in CI.

For `.sql` tests tags are placed in the first line as a SQL comment:

```
-- Tags: no-fasttest, no-replicated-database
-- no-fasttest: <provide_a_reason_for_the_tag_here>
-- no-replicated-database: <provide_a_reason_here>

SELECT 1
```

For `.sh` tests tags are written as a comment on the second line:

```
#!/usr/bin/env bash
# Tags: no-fasttest, no-replicated-database
# - no-fasttest: <provide_a_reason_for_the_tag_here>
# - no-replicated-database: <provide_a_reason_here>
```

List of available tags: