Testing ClickHouse

A test can specify minimum and maximum allowed values for settings that can be randomized during test run.

For `.sh` tests limits are written as a comment on the line next to tags or on the second line if no tags are specified:

```
#!/usr/bin/env bash
# Tags: no-fasttest
# Random settings limits: max_block_size=(1000, 10000); index_granularity=(100, None)
```

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

```
-- Tags: no-fasttest
-- Random settings limits: max_block_size=(1000, 10000); index_granularity=(100, None)
SELECT 1
```

If you need to specify only one limit, you can use `None` for another one.

### Choosing the test name[​](#choosing-the-test-name "Direct link to Choosing the test name")

The name of the test starts with a five-digit prefix followed by a descriptive name, such as `00422_hash_function_constexpr.sql`.
To choose the prefix, find the largest prefix already present in the directory, and increment it by one.

```
ls tests/queries/0_stateless/[0-9]*.reference | tail -n 1
```

In the meantime, some other tests might be added with the same numeric prefix, but this is OK and does not lead to any problems, you don't have to change it later.

### Checking for an error that must occur[​](#checking-for-an-error-that-must-occur "Direct link to Checking for an error that must occur")

Sometimes you want to test that a server error occurs for an incorrect query. We support special annotations for this in SQL tests, in the following form:

```
SELECT x; -- { serverError 49 }
```

This test ensures that the server returns an error with code 49 about unknown column `x`.
If there is no error, or the error is different, the test will fail.
If you want to ensure that an error occurs on the client side, use `clientError` annotation instead.

Do not check for a particular wording of error message, it may change in the future, and the test will needlessly break.
Check only the error code.
If the existing error code is not precise enough for your needs, consider adding a new one.

### Testing a distributed query[​](#testing-a-distributed-query "Direct link to Testing a distributed query")

If you want to use distributed queries in functional tests, you can leverage `remote` table function with `127.0.0.{1..2}` addresses for the server to query itself; or you can use predefined test clusters in server configuration file like `test_shard_localhost`.
Remember to add the words `shard` or `distributed` to the test name, so that it is run in CI in correct configurations, where the server is configured to support distributed queries.

### Working with temporary files[​](#working-with-temporary-files "Direct link to Working with temporary files")

Sometimes in a shell test you may need to create a file on the fly to work with.
Keep in mind that some CI checks run tests in parallel, so if you are creating or removing a temporary file in your script without a unique name this can cause some of the CI checks, such as Flaky, to fail.
To get around this you should use environment variable `$CLICKHOUSE_TEST_UNIQUE_NAME` to give temporary files a name unique to the test that is running.
That way you can be sure that the file you are creating during setup or removing during cleanup is the file only in use by that test and not some other test which is running in parallel.

## Known bugs[​](#known-bugs "Direct link to Known bugs")

If we know some bugs that can be easily reproduced by functional tests, we place prepared functional tests in `tests/queries/bugs` directory.
These tests will be moved to `tests/queries/0_stateless` when bugs are fixed.

## Integration tests[​](#integration-tests "Direct link to Integration tests")

Integration tests allow testing ClickHouse in clustered configuration and ClickHouse interaction with other servers like MySQL, Postgres, MongoDB.
They are useful to emulate network splits, packet drops, etc.
These tests are run under Docker and create multiple containers with various software.

See `tests/integration/README.md` on how to run these tests.

Note that integration of ClickHouse with third-party drivers is not tested.
Also, we currently do not have integration tests with our JDBC and ODBC drivers.

## Unit tests[​](#unit-tests "Direct link to Unit tests")

Unit tests are useful when you want to test not the ClickHouse as a whole, but a single isolated library or class.
You can enable or disable build of tests with `ENABLE_TESTS` CMake option.
Unit tests (and other test programs) are located in `tests` subdirectories across the code.
To run unit tests, type `ninja test`.
Some tests use `gtest`, but some are just programs that return non-zero exit code on test failure.

It's not necessary to have unit tests if the code is already covered by functional tests (and functional tests are usually much more simple to use).

You can run individual gtest checks by calling the executable directly, for example:

```
$ ./src/unit_tests_dbms --gtest_filter=LocalAddress*
```

## Performance tests[​](#performance-tests "Direct link to Performance tests")

Performance tests allow to measure and compare performance of some isolated part of ClickHouse on synthetic queries.
Performance tests are located at `tests/performance/`.
Each test is represented by an `.xml` file with a description of the test case.
Tests are run with `docker/test/performance-comparison` tool . See the readme file for invocation.

Each test run one or multiple queries (possibly with combinations of parameters) in a loop.

If you want to improve performance of ClickHouse in some scenario, and if improvements can be observed on simple queries, it is highly recommended to write a performance test.
Also, it is recommended to write performance tests when you add or modify SQL functions which are relatively isolated and not too obscure.
It always makes sense to use `perf top` or other `perf` tools during your tests.

## Test tools and scripts[​](#test-tools-and-scripts "Direct link to Test tools and scripts")

Some programs in `tests` directory are not prepared tests, but are test tools.
For example, for `Lexer` there is a tool `src/Parsers/tests/lexer` that just do tokenization of stdin and writes colorized result to stdout.
You can use these kind of tools as a code examples and for exploration and manual testing.

## Miscellaneous tests[​](#miscellaneous-tests "Direct link to Miscellaneous tests")

There are tests for machine learned models in `tests/external_models`.
These tests are not updated and must be transferred to integration tests.

There is a separate test for quorum inserts.
This test runs a ClickHouse cluster on separate servers and emulates various failure cases: network split, packet drop (between ClickHouse nodes, between ClickHouse and ZooKeeper, between ClickHouse server and client, etc.), `kill -9`, `kill -STOP` and `kill -CONT`, like [Jepsen](https://aphyr.com/tags/Jepsen). Then the test checks that all acknowledged inserts were written and all rejected inserts were not.

## Manual Testing[​](#manual-testing "Direct link to Manual Testing")

When you develop a new feature, it is reasonable to also test it manually.
You can do it with the following steps:

Build ClickHouse. Run ClickHouse from the terminal: change directory to `programs/clickhouse-server` and run it with `./clickhouse-server`. It will use configuration (`config.xml`, `users.xml` and files within `config.d` and `users.d` directories) from the current directory by default. To connect to ClickHouse server, run `programs/clickhouse-client/clickhouse-client`.

Note that all clickhouse tools (server, client, etc) are just symlinks to a single binary named `clickhouse`.
You can find this binary at `programs/clickhouse`.
All tools can also be invoked as `clickhouse tool` instead of `clickhouse-tool`.

Alternatively you can install ClickHouse package: either stable release from ClickHouse repository or you can build package for yourself with `./release` in ClickHouse sources root.
Then start the server with `sudo clickhouse start` (or stop to stop the server).
Look for logs at `/etc/clickhouse-server/clickhouse-server.log`.