ClickHouseのアップグレード

この記事は[スタンバイ Advent Calendar 2021](https://qiita.com/advent-calendar/2021/stanby)の2日目の記事です。
昨日は@kencom2400さんの[MySQLで変数を便利に使ってみる](https://qiita.com/kencom2400/items/3cdf982abeb5161436d8)でした。

---

ClickHouseのアップグレードは容易です。　　
以下のドキュメントにある通り最新版をインストールしてサーバを再起動すれば完了です。

https://clickhouse.com/docs/en/operations/update/

基本的に下位互換は保たれますし、変更や無くなる機能も数バージョンは`非推奨`で残り、警告も出してくれるのでその間に対応すれば問題ありません。

そう、きちんとメンテしていれば問題ありませんが数年経った場合はどうでしょうか？　　
ここでは`1.1.54343`から`21.10`のアップグレードを考えます。Dockerを利用しローカルで確認する手順を含め見ていきます。
4年が経ち変更がかなりありそうです。きちんと動いてくれるでしょうか。

## ローカル検証

DockerでローカルにClickhouseを立てて検証します。クラスタの検証は長くなっていしまうのでここでは単一サーバで検証していきます。
そしてアップグレードで確認する動作は以下とします。

- 作成したユーザがそのまま使用できること
- 作成したDB、テーブル、データが参照できること
- 作成したテーブルにデータが登録できること

### 旧バージョンの構築

 Dockerhubにあるので、Dockerで簡単に作成できます。

```bash
> mkdir $HOME/clickhouse_upgrade
> docker run -d --name clickhouse-upgrade-server \
  --ulimit nofile=262144:262144 \
  --volume=$HOME/clickhouse_upgrade:/var/lib/clickhouse \
  yandex/clickhouse-server:1.1.54343
```

#### ユーザの作成、サーバの設定を変更する

旧バージョンでは`CREATE USER`などのユーザ操作コマンドは存在しません。ユーザの作成や、サーバのグローバル設定を変更するには設定ファイルを書き換え、サーバの再起動が必要になります。

ローカルで行うにはDockerで立ち上げたサーバの中に入ってデフォルトの設定ファイルをローカルに持ってきてしまうのが簡単です。サーバが起動できたら、サーバの中に入り設定ファイルをマウントしたディレクトリにコピーします。これで設定を変更できるようになります。

```bash
> docker exec -it clickhouse-upgrade-server bash
> cp -p /etc/clickhouse-server/config.xml /var/lib/clickhouse/.
> cp -p /etc/clickhouse-server/users.xml /var/lib/clickhouse/.
> exit
```

ここではユーザの追加のみ行います。`profiles`と`quotas`はデフォルトのものを使い回します。

- ID: `test`
- PW: `test`

```xml:$HOME/clickhouse_upgrade/users.xml
<yandex>
  <profiles></profiles>
  <users>
    <default></default>
    <test>
      <password_sha256_hex>9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08</password_sha256_hex>
      <networks incl="networks" replace="replace">
        <ip>::/0</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
    </test>
  </users>
  <quotas></quotas>
</yandex>
```

設定ファイルを読み込ませるためDockerを立ち上げ直します。

```bash
> docker rm -f clickhouse-upgrade-server
> docker run -d --name clickhouse-upgrade-server \
  --ulimit nofile=262144:262144 \
  --volume=$HOME/clickhouse_upgrade:/var/lib/clickhouse \
  --volume=$HOME/clickhouse_upgrade/config.xml:/etc/clickhouse-server/config.xml \
  --volume=$HOME/clickhouse_upgrade/users.xml:/etc/clickhouse-server/users.xml \
  yandex/clickhouse-server:1.1.54343
```

作成したユーザでクライアントにログインを行うには以下の通りログインします。

```bash
> docker run -it --rm --link clickhouse-upgrade-server:clickhouse-server \
  yandex/clickhouse-client:1.1.54343 \
  --host clickhouse-server \
  --user test \
  --password test
```

ユーザの作成ができたので、データを登録していきます。

#### テスト用のデータ登録


```sql
:) CREATE DATABASE sample
:) USE sample
:) CREATE TABLE sample_table_20211201(id Int32, val String, d Date, dt DateTime)
  ENGINE = MergeTree(d, (id, d), 8192)
:) CREATE TABLE sample_table_20211202(id Int32, val String, d Date, dt DateTime)
  ENGINE = MergeTree(d, (id, d), 8192)
:) CREATE TABLE sample_table AS sample_table_20211201 ENGINE = Merge(sample, '^sample_table_')
:) INSERT INTO sample_table_20211201 VALUES 
  (1, 'sample1', '2021-12-01', '2021-12-01T00:00:00')
  ,(2, 'sample2', '2021-12-01', '2021-12-01T00:00:00')
  ,(3, 'sample3', '2021-12-01', '2021-12-01T00:00:00')
  ,(4, 'sample4', '2021-12-01', '2021-12-01T00:00:00')
  ,(5, 'sample5', '2021-12-01', '2021-12-01T00:00:00')
:) INSERT INTO sample_table_20211202 VALUES 
  (6, 'sample6', '2021-12-02', '2021-12-02T00:00:00')
  ,(7, 'sample7', '2021-12-02', '2021-12-02T00:00:00')
  ,(8, 'sample8', '2021-12-02', '2021-12-02T00:00:00')
  ,(9, 'sample9', '2021-12-02', '2021-12-02T00:00:00')
  ,(10, 'sample10', '2021-12-02', '2021-12-02T00:00:00')
:) SELECT * FROM sample_table 

┌─id─┬─val─────┬──────────d─┬──────────────────dt─┐
│  1 │ sample1 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  2 │ sample2 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  3 │ sample3 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  4 │ sample4 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  5 │ sample5 │ 2021-12-01 │ 2021-12-01 00:00:00 │
└────┴─────────┴────────────┴─────────────────────┘
┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│  6 │ sample6  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  7 │ sample7  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  8 │ sample8  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  9 │ sample9  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 10 │ sample10 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.019 sec. 
```

### ClickHouseアップグレード

Dockerを停止し、21.10を立ち上げます。

```bash
> docker rm -f clickhouse-upgrade-server
> docker run -d --name clickhouse-upgrade-server \
  --ulimit nofile=262144:262144 \
  --volume=$HOME/clickhouse_upgrade:/var/lib/clickhouse \
  --volume=$HOME/clickhouse_upgrade/config.xml:/etc/clickhouse-server/config.xml \
  --volume=$HOME/clickhouse_upgrade/users.xml:/etc/clickhouse-server/users.xml \
  clickhouse/clickhouse-server:21.10
```

#### 作成したユーザがそのまま使用できること

サーバが立ち上がったら作成したユーザで接続ができることを確認します。

```bash
> docker run -it --rm --link clickhouse-upgrade-server:clickhouse-server \
  clickhouse/clickhouse-client:21.10 \
  --host clickhouse-server \
  --user test \
  --password test
ClickHouse client version 21.10.2.15 (official build).
Connecting to clickhouse-server:9000 as user test.
Connected to ClickHouse server version 21.10.2 revision 54449.

:)
```

#### 作成したDB、テーブル、データが参照できること

古いバージョンで作成したテーブルを検索できることを確認します。

```sql
:) USE sample
:) SELECT * FROM sample_table

┌─id─┬─val─────┬──────────d─┬──────────────────dt─┐
│  1 │ sample1 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  2 │ sample2 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  3 │ sample3 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  4 │ sample4 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  5 │ sample5 │ 2021-12-01 │ 2021-12-01 00:00:00 │
└────┴─────────┴────────────┴─────────────────────┘
┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│  6 │ sample6  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  7 │ sample7  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  8 │ sample8  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  9 │ sample9  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 10 │ sample10 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.022 sec. 
```

#### 作成したテーブルにデータが登録できること

データの参照もできました。
最後にデータの登録を確認します。また、Mergeテーブルに加わるテーブルを追加し新旧のテーブルが混合できることも確認します。

```sql
:) INSERT INTO sample_table_20211202 VALUES 
  (11, 'sample11', '2021-12-02', '2021-12-02T00:00:00')
  ,(12, 'sample12', '2021-12-02', '2021-12-02T00:00:00')
  ,(13, 'sample13', '2021-12-02', '2021-12-02T00:00:00')
  ,(14, 'sample14', '2021-12-02', '2021-12-02T00:00:00')
  ,(15, 'sample15', '2021-12-02', '2021-12-02T00:00:00')
:) SELECT * FROM sample_table_20211202

┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│  6 │ sample6  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  7 │ sample7  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  8 │ sample8  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  9 │ sample9  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 10 │ sample10 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘
┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│ 11 │ sample11 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 12 │ sample12 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 13 │ sample13 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 14 │ sample14 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 15 │ sample15 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.013 sec. 
```

データの登録はできました。続いてテーブルを作成します。

```sql
:) CREATE TABLE sample_table_20211203(id Int32, val String, d Date, dt DateTime) ENGINE = MergeTree(d, (id, d), 8192)
:) INSERT INTO sample_table_20211203 VALUES 
  (16, 'sample16', '2021-12-03', '2021-12-03T00:00:00')
  ,(17, 'sample17', '2021-12-03', '2021-12-03T00:00:00')
  ,(18, 'sample18', '2021-12-03', '2021-12-03T00:00:00')
  ,(19, 'sample19', '2021-12-03', '2021-12-03T00:00:00')
  ,(20, 'sample20', '2021-12-03', '2021-12-03T00:00:00')
:) SELECT * FROM sample_table

┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│ 11 │ sample11 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 12 │ sample12 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 13 │ sample13 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 14 │ sample14 │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 15 │ sample15 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘
┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│ 16 │ sample16 │ 2021-12-03 │ 2021-12-03 00:00:00 │
│ 17 │ sample17 │ 2021-12-03 │ 2021-12-03 00:00:00 │
│ 18 │ sample18 │ 2021-12-03 │ 2021-12-03 00:00:00 │
│ 19 │ sample19 │ 2021-12-03 │ 2021-12-03 00:00:00 │
│ 20 │ sample20 │ 2021-12-03 │ 2021-12-03 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘
┌─id─┬─val──────┬──────────d─┬──────────────────dt─┐
│  6 │ sample6  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  7 │ sample7  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  8 │ sample8  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│  9 │ sample9  │ 2021-12-02 │ 2021-12-02 00:00:00 │
│ 10 │ sample10 │ 2021-12-02 │ 2021-12-02 00:00:00 │
└────┴──────────┴────────────┴─────────────────────┘
┌─id─┬─val─────┬──────────d─┬──────────────────dt─┐
│  1 │ sample1 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  2 │ sample2 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  3 │ sample3 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  4 │ sample4 │ 2021-12-01 │ 2021-12-01 00:00:00 │
│  5 │ sample5 │ 2021-12-01 │ 2021-12-01 00:00:00 │
└────┴─────────┴────────────┴─────────────────────┘

20 rows in set. Elapsed: 0.023 sec. 
```

データの追加は問題なくできました。古いCREATE TABLE構文でのテーブル作成も可能でした。しかし数年前から非推奨になっている構文なので修正したほうがいいでしょう。

見てきた通りMergeTreeエンジンで単純に利用していれば`1.1.54343`から`21.10`へのアップグレードは問題なくできそうです。

## Backward Incompatible Change, Upgrade Notes

ここからは公式ドキュメントから後方互換性のない変更のみを抜き出し列挙します。
いちいち大量のChangelogから確認したくない私用のメモです。

https://clickhouse.com/docs/category/changelog

<details><summary>2025年</summary><div>

### 25.4, 2025-04-22

- Check if all columns in a materialized view match the target table when allow_materialized_view_with_bad_select is false. [#74481](https://github.com/ClickHouse/ClickHouse/pull/74481) (Christoph Wurm).
- Fix cases where dateTrunc is used with negative Date/DateTime arguments. [#77622](https://github.com/ClickHouse/ClickHouse/pull/77622) (Yarik Briukhovetskyi).
- The legacy MongoDB integration has been removed. Server setting use_legacy_mongodb_integration became obsolete and now does nothing. [#77895](https://github.com/ClickHouse/ClickHouse/pull/77895) (Robert Schulze).
- Enhance SummingMergeTree validation to skip aggregation for columns used in partition or sort keys. [#78022](https://github.com/ClickHouse/ClickHouse/pull/78022) (Pervakov Grigorii).


### 25.3 LTS, 2025-03-20

- Disallow truncating replicated databases. [#76651](https://github.com/ClickHouse/ClickHouse/pull/76651) (Bharat Nallan).
- Skipping index cache is reverted. [#77447](https://github.com/ClickHouse/ClickHouse/pull/77447) (Nikita Mikhaylov).

### 25.2, 2025-02-27

- Completely enable async_load_databases by default (even for those installations that do not upgrade config.xml). [#74772](https://github.com/ClickHouse/ClickHouse/pull/74772) (Azat Khuzhin).
- Add JSONCompactEachRowWithProgress and JSONCompactStringsEachRowWithProgress formats. Continuation of [#69989](https://github.com/ClickHouse/ClickHouse/pull/69989). The JSONCompactWithNames and JSONCompactWithNamesAndTypes no longer output "totals" - apparently, it was a mistake in the implementation. [#75037](https://github.com/ClickHouse/ClickHouse/pull/75037) (Alexey Milovidov).
- Change the format_alter_operations_with_parentheses default to true to disambiguate the ALTER commands list (see https://github.com/ClickHouse/ClickHouse/pull/59532). This breaks replication with clusters prior to 24.3. If you are upgrading a cluster using older releases turn off the setting in the server config or upgrade to 24.3 first. [#75302](https://github.com/ClickHouse/ClickHouse/pull/75302) (Raúl Marín).
- Remove the possibility of filtering log messages using regular expressions. The implementation introduced a data race, so it has to be removed. [#75577](https://github.com/ClickHouse/ClickHouse/pull/75577) (János Benjamin Antal).
- The setting min_chunk_bytes_for_parallel_parsing cannot be zero anymore. This fixes: [#71110](https://github.com/ClickHouse/ClickHouse/issues/71110). [#75239](https://github.com/ClickHouse/ClickHouse/pull/75239) (Nikita Mikhaylov).
- Validate settings in the cache configuration. Non existing settings were ignored before, now they will throw an error and they should be removed. [#75452](https://github.com/ClickHouse/ClickHouse/pull/75452) (Kseniia Sumarokova).

### 25.1, 2025-01-28

- JSONEachRowWithProgress will write the progress whenever the progress happens. In previous versions, the progress was shown only after each block of the result, which made it useless. Change the way how the progress is displayed: it will not show zero values. This closes [#70800](https://github.com/ClickHouse/ClickHouse/issues/70800). [#73834](https://github.com/ClickHouse/ClickHouse/pull/73834) (Alexey Milovidov).
- Merge tables will unify the structure of underlying tables by using a union of their columns and deriving common types. This closes [#64864](https://github.com/ClickHouse/ClickHouse/issues/64864). In certain cases, this change could be backward incompatible. One example is when there is no common type between tables, but conversion to the type of the first table is still possible, such as in the case of UInt64 and Int64 or any numeric type and String. If you want to return to the old behavior, set merge_table_max_tables_to_look_for_schema_inference to 1 or set compatibility to 24.12 or earlier. [#73956](https://github.com/ClickHouse/ClickHouse/pull/73956) (Alexey Milovidov).
- Parquet output format converts Date and DateTime columns to date/time types supported by Parquet, instead of writing them as raw numbers. DateTime becomes DateTime64(3) (was: UInt32); setting output_format_parquet_datetime_as_uint32 brings back the old behavior. Date becomes Date32 (was: UInt16). [#70950](https://github.com/ClickHouse/ClickHouse/pull/70950) (Michael Kolupaev).
- Don't allow not comparable types (like JSON/Object/AggregateFunction) in ORDER BY and comparison functions less/greater/equal/etc by default. [#73276](https://github.com/ClickHouse/ClickHouse/pull/73276) (Pavel Kruglov).
- The obsolete MaterializedMySQL database engine has been removed and is no longer available. [#73879](https://github.com/ClickHouse/ClickHouse/pull/73879) (Alexey Milovidov).
- The mysql dictionary source no longer does SHOW TABLE STATUS query, because it does not provide any value for InnoDB tables, as long as for any recent MySQL versions. This closes [#72636](https://github.com/ClickHouse/ClickHouse/issues/72636). This change is backward compatible, but put in this category so you have a chance to notice it. #[73914](https://github.com/ClickHouse/ClickHouse/pull/73914) (Alexey Milovidov).
- CHECK TABLE queries now require a separate, CHECK grant. In previous versions, it was enough to have SHOW TABLES grant to run these queries. But a CHECK TABLE query can be heavy, and usual query complexity limits for SELECT queries don't apply to it. It led to the potential of DoS. [#74471](https://github.com/ClickHouse/ClickHouse/pull/74471) (Alexey Milovidov).
- Function h3ToGeo() now returns the results in the order (lat, lon) (which is the standard order for geometric functions). Users who wish to retain the legacy result order (lon, lat) can set setting h3togeo_lon_lat_result_order = true. [#74719](https://github.com/ClickHouse/ClickHouse/pull/74719) (Manish Gill).
- A new MongoDB driver is now the default. Users who like to continue using the legacy driver can set server setting use_legacy_mongodb_integration to true. [#73359](https://github.com/ClickHouse/ClickHouse/pull/73359) (Robert Schulze).

</div></details>
<details><summary>2024年</summary><div>

### 24.12, 2024-12-19

- Functions greatest and least now ignore NULL input values, whereas they previously returned NULL if one of the arguments was NULL. For example, SELECT greatest(1, 2, NULL) now returns 2. This makes the behavior compatible with PostgreSQL, but at the same time it breaks the compatibility with MySQL which returns NULL. To retain the previous behavior, set setting least_greatest_legacy_null_behavior (default: false) to true. [#65519](https://github.com/ClickHouse/ClickHouse/pull/65519) [#73344](https://github.com/ClickHouse/ClickHouse/pull/73344) (kevinyhzou).
- A new MongoDB integration is now the default. Users who like to use the legacy MongoDB driver (based on the Poco driver) can enable server setting use_legacy_mongodb_integration. [#73359](https://github.com/ClickHouse/ClickHouse/pull/73359) (Kirill Nikiforov.

### 24.11, 2024-11-26

Remove system tables generate_series and generateSeries. They were added by mistake here: [#59390](https://github.com/ClickHouse/ClickHouse/pull/59390). [#71091](https://github.com/ClickHouse/ClickHouse/pull/71091) (Alexey Milovidov).
Remove StorageExternalDistributed. Closes [#70600](https://github.com/ClickHouse/ClickHouse/issues/70600).[#71176](https://github.com/ClickHouse/ClickHouse/pull/71176) (flynn).
The table engines Kafka, NATS and RabbitMQ are now covered by their own grants in the SOURCES hierarchy. Add grants to any non-default database users that create tables with these engine types. [#71250](https://github.com/ClickHouse/ClickHouse/pull/71250) (Christoph Wurm).
Check the full mutation query before executing it (including subqueries). This prevents accidentally running an invalid query and building up dead mutations that block valid mutations. [#71300](https://github.com/ClickHouse/ClickHouse/pull/71300) (Christoph Wurm).
Rename filesystem cache setting skip_download_if_exceeds_query_cache to filesystem_cache_skip_download_if_exceeds_per_query_cache_write_limit. [#71578](https://github.com/ClickHouse/ClickHouse/pull/71578) (Kseniia Sumarokova).
Remove support for Enum as well as UInt128 and UInt256 arguments in deltaSumTimestamp. Remove support for Int8, UInt8, Int16, and UInt16 of the second ("timestamp") argument of deltaSumTimestamp. [#71790](https://github.com/ClickHouse/ClickHouse/pull/71790) (Alexey Milovidov).
When retrieving data directly from a dictionary using Dictionary storage, dictionary table function, or direct SELECT from the dictionary itself, it is now enough to have SELECT permission or dictGet permission for the dictionary. This aligns with previous attempts to prevent ACL bypasses: [#57362](https://github.com/ClickHouse/ClickHouse/pull/57362) and [#65359](https://github.com/ClickHouse/ClickHouse/pull/65359). It also makes the latter one backward compatible. [#72051](https://github.com/ClickHouse/ClickHouse/pull/72051) (Nikita Mikhaylov).

### 24.10, 2024-10-31

- Allow to write SETTINGS before FORMAT in a chain of queries with UNION when subqueries are inside parentheses. This closes [#39712](https://github.com/ClickHouse/ClickHouse/issues/39712). Change the behavior when a query has the SETTINGS clause specified twice in a sequence. The closest SETTINGS clause will have a preference for the corresponding subquery. In the previous versions, the outermost SETTINGS clause could take a preference over the inner one. [#68614](https://github.com/ClickHouse/ClickHouse/pull/68614) (Alexey Milovidov).
- Reordering of filter conditions from [PRE]WHERE clause is now allowed by default. It could be disabled by setting allow_reorder_prewhere_conditions to false. [#70657](https://github.com/ClickHouse/ClickHouse/pull/70657) (Nikita Taranov).
- Remove the idxd-config library, which has an incompatible license. This also removes the experimental Intel DeflateQPL codec. [#70987](https://github.com/ClickHouse/ClickHouse/pull/70987) (Alexey Milovidov).

### 24.9, 2024-09-26

- Expressions like a[b].c are supported for named tuples, as well as named subscripts from arbitrary expressions, e.g., expr().name. This is useful for processing JSON. This closes [#54965](https://github.com/ClickHouse/ClickHouse/issues/54965). In previous versions, an expression of form expr().name was parsed as tupleElement(expr(), name), and the query analyzer was searching for a column name rather than for the corresponding tuple element; while in the new version, it is changed to tupleElement(expr(), 'name'). In most cases, the previous version was not working, but it is possible to imagine a very unusual scenario when this change could lead to incompatibility: if you stored names of tuple elements in a column or an alias, that was named differently than the tuple element's name: SELECT 'b' AS a, CAST([tuple(123)] AS 'Array(Tuple(b UInt8))') AS t, t[1].a. It is very unlikely that you used such queries, but we still have to mark this change as potentially backward incompatible. [#68435](https://github.com/ClickHouse/ClickHouse/pull/68435) (Alexey Milovidov).
- When the setting print_pretty_type_names is enabled, it will print Tuple data type in a pretty form in SHOW CREATE TABLE statements, formatQuery function, and in the interactive mode in clickhouse-client and clickhouse-local. In previous versions, this setting was only applied to DESCRIBE queries and toTypeName. This closes [#65753](https://github.com/ClickHouse/ClickHouse/issues/65753). [#68492](https://github.com/ClickHouse/ClickHouse/pull/68492) (Alexey Milovidov).
- Do not allow explicitly specifying UUID when creating a table in Replicated databases. Also, do not allow explicitly specifying Keeper path and replica name for *MergeTree tables in Replicated databases. It introduces a new setting database_replicated_allow_explicit_uuid and changes the type of database_replicated_allow_replicated_engine_arguments from Bool to UInt64 [#66104](https://github.com/ClickHouse/ClickHouse/pull/66104) (Alexander Tokmakov).

### 24.8 LTS, 2024-08-20

- clickhouse-client and clickhouse-local now default to multi-query mode (instead single-query mode). As an example, clickhouse-client -q "SELECT 1; SELECT 2" now works, whereas users previously had to add --multiquery (or -n). The --multiquery/-n switch became obsolete. INSERT queries in multi-query statements are treated specially based on their FORMAT clause: If the FORMAT is VALUES (the most common case), the end of the INSERT statement is represented by a trailing semicolon ; at the end of the query. For all other FORMATs (e.g. CSV or JSONEachRow), the end of the INSERT statement is represented by two newlines \n\n at the end of the query. [#63898](https://github.com/ClickHouse/ClickHouse/pull/63898) (FFish).
- In previous versions, it was possible to use an alternative syntax for LowCardinality data types by appending WithDictionary to the name of the data type. It was an initial working implementation, and it was never documented or exposed to the public. Now, it is deprecated. If you have used this syntax, you have to ALTER your tables and rename the data types to LowCardinality. [#66842](https://github.com/ClickHouse/ClickHouse/pull/66842) (Alexey Milovidov).
- Fix logical errors with storage Buffer used with distributed destination table. It's a backward incompatible change: queries using Buffer with a distributed destination table may stop working if the table appears more than once in the query (e.g., in a self-join). [#67015](https://github.com/ClickHouse/ClickHouse/pull/67015) (vdimir).
- In previous versions, calling functions for random distributions based on the Gamma function (such as Chi-Squared, Student, Fisher) with negative arguments close to zero led to a long computation or an infinite loop. In the new version, calling these functions with zero or negative arguments will produce an exception. This closes [#67297](https://github.com/ClickHouse/ClickHouse/issues/67297). [#67326](https://github.com/ClickHouse/ClickHouse/pull/67326) (Alexey Milovidov).
- The system table text_log is enabled by default. This is fully compatible with previous versions, but you may notice subtly increased disk usage on the local disk (this system table takes a tiny amount of disk space). [#67428](https://github.com/ClickHouse/ClickHouse/pull/67428) (Alexey Milovidov).
- In previous versions, arrayWithConstant can be slow if asked to generate very large arrays. In the new version, it is limited to 1 GB per array. This closes [#32754](https://github.com/ClickHouse/ClickHouse/issues/32754). [#67741](https://github.com/ClickHouse/ClickHouse/pull/67741) (Alexey Milovidov).
- Fix REPLACE modifier formatting (forbid omitting brackets). [#67774](https://github.com/ClickHouse/ClickHouse/pull/67774) (Azat Khuzhin).
- Backported in [#68349](https://github.com/ClickHouse/ClickHouse/pull/68349): Reimplement Dynamic type. Now when the limit of dynamic data types is reached new types are not casted to String but stored in a special data structure in binary format with binary encoded data type. Now any type ever inserted into Dynamic column can be read from it as subcolumn. [#68132](https://github.com/ClickHouse/ClickHouse/pull/68132) (Kruglov Pavel).

### 24.7, 2024-07-30

- Forbid CRATE MATERIALIZED VIEW ... ENGINE Replicated*MergeTree POPULATE AS SELECT ... with Replicated databases. [#63963](https://github.com/ClickHouse/ClickHouse/pull/63963) (vdimir).
- clickhouse-keeper-client will only accept paths in string literals, such as ls '/hello/world', not bare strings such as ls /hello/world. [#65494](https://github.com/ClickHouse/ClickHouse/pull/65494) (Alexey Milovidov).
- Metric KeeperOutstandingRequets was renamed to KeeperOutstandingRequests. [#66206](https://github.com/ClickHouse/ClickHouse/pull/66206) (Robert Schulze).
- Remove is_deterministic field from the system.functions table. [#66630](https://github.com/ClickHouse/ClickHouse/pull/66630) (Alexey Milovidov).
- Function tuple will now try to construct named tuples in query (controlled by enable_named_columns_in_function_tuple). Introduce function tupleNames to extract names from tuples. [#54881](https://github.com/ClickHouse/ClickHouse/pull/54881) (Amos Bird).
- Change how deduplication for Materialized Views works. Fixed a lot of cases like: - on destination table: data is split for 2 or more blocks and that blocks is considered as duplicate when that block is inserted in parallel. - on MV destination table: the equal blocks are deduplicated, that happens when MV often produces equal data as a result for different input data due to performing aggregation. - on MV destination table: the equal blocks which comes from different MV are deduplicated. [#61601](https://github.com/ClickHouse/ClickHouse/pull/61601) (Sema Checherinda).

### 24.6, 2024-07-01

- Enable asynchronous load of databases and tables by default. See the async_load_databases in config.xml. While this change is fully compatible, it can introduce a difference in behavior. When async_load_databases is false, as in the previous versions, the server will not accept connections until all tables are loaded. When async_load_databases is true, as in the new version, the server can accept connections before all the tables are loaded. If a query is made to a table that is not yet loaded, it will wait for the table's loading, which can take considerable time. It can change the behavior of the server if it is part of a large distributed system under a load balancer. In the first case, the load balancer can get a connection refusal and quickly failover to another server. In the second case, the load balancer can connect to a server that is still loading the tables, and the query will have a higher latency. Moreover, if many queries accumulate in the waiting state, it can lead to a "thundering herd" problem when they start processing simultaneously. This can make a difference only for highly loaded distributed backends. You can set the value of async_load_databases to false to avoid this problem. [#57695](https://github.com/ClickHouse/ClickHouse/pull/57695) (Alexey Milovidov).
- Setting replace_long_file_name_to_hash is enabled by default for MergeTree tables. [#64457](https://github.com/ClickHouse/ClickHouse/pull/64457) (Anton Popov). This setting is fully compatible, and no actions needed during upgrade. The new data format is supported from all versions starting from 23.9. After enabling this setting, you can no longer downgrade to a version 23.8 or older.
- Some invalid queries will fail earlier during parsing. Note: disabled the support for inline KQL expressions (the experimental Kusto language) when they are put into a kql table function without a string literal, e.g. kql(garbage | trash) instead of kql('garbage | trash') or kql($$garbage | trash$$). This feature was introduced unintentionally and should not exist. [#61500](https://github.com/ClickHouse/ClickHouse/pull/61500) (Alexey Milovidov).
- Rework parallel processing in Ordered mode of storage S3Queue. This PR is backward incompatible for Ordered mode if you used settings s3queue_processing_threads_num or s3queue_total_shards_num. Setting s3queue_total_shards_num is deleted, previously it was allowed to use only under s3queue_allow_experimental_sharded_mode, which is now deprecated. A new setting is added - s3queue_buckets. [#64349](https://github.com/ClickHouse/ClickHouse/pull/64349) (Kseniia Sumarokova).
- New functions snowflakeIDToDateTime, snowflakeIDToDateTime64, dateTimeToSnowflakeID, and dateTime64ToSnowflakeID were added. Unlike the existing functions snowflakeToDateTime, snowflakeToDateTime64, dateTimeToSnowflake, and dateTime64ToSnowflake, the new functions are compatible with function generateSnowflakeID, i.e. they accept the snowflake IDs generated by generateSnowflakeID and produce snowflake IDs of the same type as generateSnowflakeID (i.e. UInt64). Furthermore, the new functions default to the UNIX epoch (aka. 1970-01-01), just like generateSnowflakeID. If necessary, a different epoch, e.g. Twitter's/X's epoch 2010-11-04 aka. 1288834974657 msec since UNIX epoch, can be passed. The old conversion functions are deprecated and will be removed after a transition period: to use them regardless, enable setting allow_deprecated_snowflake_conversion_functions. [#64948](https://github.com/ClickHouse/ClickHouse/pull/64948) (Robert Schulze).

### 24.5, 2024-05-30

- Renamed "inverted indexes" to "full-text indexes" which is a less technical / more user-friendly name. This also changes internal table metadata and breaks tables with existing (experimental) inverted indexes. Please make sure to drop such indexes before upgrade and re-create them after upgrade. [#62884](https://github.com/ClickHouse/ClickHouse/pull/62884) (Robert Schulze).
- Usage of functions neighbor, runningAccumulate, runningDifferenceStartingWithFirstValue, runningDifference deprecated (because it is error-prone). Proper window functions should be used instead. To enable them back, set allow_deprecated_error_prone_window_functions = 1 or set compatibility = '24.4' or lower. [#63132](https://github.com/ClickHouse/ClickHouse/pull/63132) (Nikita Taranov).
- Queries from system.columns will work faster if there is a large number of columns, but many databases or tables are not granted for SHOW TABLES. Note that in previous versions, if you grant SHOW COLUMNS to individual columns without granting SHOW TABLES to the corresponding tables, the system.columns table will show these columns, but in a new version, it will skip the table entirely. Remove trace log messages "Access granted" and "Access denied" that slowed down queries. [#63439](https://github.com/ClickHouse/ClickHouse/pull/63439) (Alexey Milovidov).

### 24.4, 2024-04-30

- clickhouse-odbc-bridge and clickhouse-library-bridge are now separate packages. This closes [#61677](https://github.com/ClickHouse/ClickHouse/issues/61677). [#62114](https://github.com/ClickHouse/ClickHouse/pull/62114) (Alexey Milovidov).
- Don't allow to set max_parallel_replicas (for the experimental parallel reading from replicas) to 0 as it doesn't make sense. Closes [#60140](https://github.com/ClickHouse/ClickHouse/issues/60140). [#61201](https://github.com/ClickHouse/ClickHouse/pull/61201) (Kruglov Pavel).
Remove support for INSERT WATCH query (part of the deprecated LIVE VIEW feature). [#62382](https://github.com/ClickHouse/ClickHouse/pull/62382) (Alexey Milovidov).
- Removed the optimize_monotonous_functions_in_order_by setting. [#63004](https://github.com/ClickHouse/ClickHouse/pull/63004) (Raúl Marín).
- Remove experimental tag from the Replicated database engine. Now it is in Beta stage. [#62937](https://github.com/ClickHouse/ClickHouse/pull/62937) (Justin de Guzman).

### 24.3 LTS, 2024-03-27

- The setting allow_experimental_analyzer is enabled by default and it switches the query analysis to a new implementation, which has better compatibility and feature completeness. The feature "analyzer" is considered beta instead of experimental. You can turn the old behavior by setting the compatibility to 24.2 or disabling the allow_experimental_analyzer setting. Watch [the video on YouTube](https://www.youtube.com/watch?v=zhrOYQpgvkk).
- ClickHouse allows arbitrary binary data in the String data type, which is typically UTF-8. Parquet/ORC/Arrow Strings only support UTF-8. That's why you can choose which Arrow's data type to use for the ClickHouse String data type - String or Binary. This is controlled by the settings, output_format_parquet_string_as_string, output_format_orc_string_as_string, output_format_arrow_string_as_string. While Binary would be more correct and compatible, using String by default will correspond to user expectations in most cases. Parquet/ORC/Arrow supports many compression methods, including lz4 and zstd. ClickHouse supports each and every compression method. Some inferior tools lack support for the faster lz4 compression method, that's why we set zstd by default. This is controlled by the settings output_format_parquet_compression_method, output_format_orc_compression_method, and output_format_arrow_compression_method. We changed the default to zstd for Parquet and ORC, but not Arrow (it is emphasized for low-level usages). [#61817](https://github.com/ClickHouse/ClickHouse/pull/61817) (Alexey Milovidov).
- In the new ClickHouse version, the functions geoDistance, greatCircleDistance, and greatCircleAngle will use 64-bit double precision floating point data type for internal calculations and return type if all the arguments are Float64. This closes [#58476](https://github.com/ClickHouse/ClickHouse/issues/58476). In previous versions, the function always used Float32. You can switch to the old behavior by setting geo_distance_returns_float64_on_float64_arguments to false or setting compatibility to 24.2 or earlier. [#61848](https://github.com/ClickHouse/ClickHouse/pull/61848) (Alexey Milovidov). Co-authored with Geet Patel.
- The obsolete in-memory data parts have been deprecated since version 23.5 and have not been supported since version 23.10. Now the remaining code is removed. Continuation of [#55186](https://github.com/ClickHouse/ClickHouse/pull/55186) and [#45409](https://github.com/ClickHouse/ClickHouse/issues/45409). It is unlikely that you have used in-memory data parts because they were available only before version 23.5 and only when you enabled them manually by specifying the corresponding SETTINGS for a MergeTree table. To check if you have in-memory data parts, run the following query: SELECT part_type, count() FROM system.parts GROUP BY part_type ORDER BY part_type. To disable the usage of in-memory data parts, do ALTER TABLE ... MODIFY SETTING min_bytes_for_compact_part = DEFAULT, min_rows_for_compact_part = DEFAULT. Before upgrading from old ClickHouse releases, first check that you don't have in-memory data parts. If there are in-memory data parts, disable them first, then wait while there are no in-memory data parts and continue the upgrade. [#61127](https://github.com/ClickHouse/ClickHouse/pull/61127) (Alexey Milovidov).
- Changed the column name from duration_ms to duration_microseconds in the system.zookeeper table to reflect the reality that the duration is in the microsecond resolution. [#60774](https://github.com/ClickHouse/ClickHouse/pull/60774) (Duc Canh Le).
- Reject incoming INSERT queries in case when query-level settings async_insert and deduplicate_blocks_in_dependent_materialized_views are enabled together. This behaviour is controlled by a setting throw_if_deduplication_in_dependent_materialized_views_enabled_with_async_insert and enabled by default. This is a continuation of [#59699](https://github.com/ClickHouse/ClickHouse/pull/59699) needed to unblock [#59915](https://github.com/ClickHouse/ClickHouse/pull/59915). [#60888](https://github.com/ClickHouse/ClickHouse/pull/60888) (Nikita Mikhaylov).
- Utility clickhouse-copier is moved to a separate repository on GitHub: https://github.com/ClickHouse/copier. It is no longer included in the bundle but is still available as a separate download. This closes: [#60734](https://github.com/ClickHouse/ClickHouse/issues/60734) This closes: [#60540](https://github.com/ClickHouse/ClickHouse/issues/60540) This closes: [#60250](https://github.com/ClickHouse/ClickHouse/issues/60250) This closes: [#52917](https://github.com/ClickHouse/ClickHouse/issues/52917) This closes: [#51140](https://github.com/ClickHouse/ClickHouse/issues/51140) This closes: [#47517](https://github.com/ClickHouse/ClickHouse/issues/47517) This closes: [#47189](https://github.com/ClickHouse/ClickHouse/issues/47189) This closes: [#46598](https://github.com/ClickHouse/ClickHouse/issues/46598) This closes: [#40257](https://github.com/ClickHouse/ClickHouse/issues/40257) This closes: [#36504](https://github.com/ClickHouse/ClickHouse/issues/36504) This closes: [#35485](https://github.com/ClickHouse/ClickHouse/issues/35485) This closes: [#33702](https://github.com/ClickHouse/ClickHouse/issues/33702) This closes: [#26702](https://github.com/ClickHouse/ClickHouse/issues/26702).
- To increase compatibility with MySQL, the compatibility alias locate now accepts arguments (needle, haystack[, start_pos]) by default. The previous behavior (haystack, needle, [, start_pos]) can be restored by setting function_locate_has_mysql_compatible_argument_order = 0. [#61092](https://github.com/ClickHouse/ClickHouse/pull/61092) (Robert Schulze).
- Forbid SimpleAggregateFunction in ORDER BY of MergeTree tables (like AggregateFunction is forbidden, but they are forbidden because they are not comparable) by default (use allow_suspicious_primary_key to allow them). [#61399](https://github.com/ClickHouse/ClickHouse/pull/61399) (Azat Khuzhin).
- The Ordinary database engine is deprecated. You will receive a warning in clickhouse-client if your server is using it. This closes [#52229](https://github.com/ClickHouse/ClickHouse/issues/52229). [#56942](https://github.com/ClickHouse/ClickHouse/pull/56942) (shabroo).

### 24.2, 2024-02-29

- Validate suspicious/experimental types in nested types. Previously we didn't validate such types (except JSON) in nested types like Array/Tuple/Map. [#59385](https://github.com/ClickHouse/ClickHouse/pull/59385) (Kruglov Pavel).
- Add sanity check for number of threads and block sizes. [#60138](https://github.com/ClickHouse/ClickHouse/pull/60138) (Raúl Marín).
- Don't infer floats in exponential notation by default. Add a setting input_format_try_infer_exponent_floats that will restore previous behaviour (disabled by default). Closes [#59476](https://github.com/ClickHouse/ClickHouse/issues/59476). [#59500](https://github.com/ClickHouse/ClickHouse/pull/59500) (Kruglov Pavel).
- Allow alter operations to be surrounded by parenthesis. The emission of parentheses can be controlled by the format_alter_operations_with_parentheses config. By default, in formatted queries the parentheses are emitted as we store the formatted alter operations in some places as metadata (e.g.: mutations). The new syntax clarifies some of the queries where alter operations end in a list. E.g.: ALTER TABLE x MODIFY TTL date GROUP BY a, b, DROP COLUMN c cannot be parsed properly with the old syntax. In the new syntax the query ALTER TABLE x (MODIFY TTL date GROUP BY a, b), (DROP COLUMN c) is obvious. Older versions are not able to read the new syntax, therefore using the new syntax might cause issues if newer and older version of ClickHouse are mixed in a single cluster. [#59532](https://github.com/ClickHouse/ClickHouse/pull/59532) (János Benjamin Antal).
- Fix for the materialized view security issue, which allowed a user to insert into a table without required grants for that. Fix validates that the user has permission to insert not only into a materialized view but also into all underlying tables. This means that some queries, which worked before, now can fail with Not enough privileges. To address this problem, the release introduces a new feature of SQL security for views https://clickhouse.com/docs/en/sql-reference/statements/create/view#sql_security. [#54901](https://github.com/ClickHouse/ClickHouse/pull/54901) [#60439](https://github.com/ClickHouse/ClickHouse/pull/60439) (pufit).

### 24.1, 2024-01-30

- The setting print_pretty_type_names is turned on by default. You can turn it off to keep the old behavior or SET compatibility = '23.12'. [#57726](https://github.com/ClickHouse/ClickHouse/pull/57726) (Alexey Milovidov).
- The MergeTree setting clean_deleted_rows is deprecated, it has no effect anymore. The CLEANUP keyword for OPTIMIZE is not allowed by default (unless allow_experimental_replacing_merge_with_cleanup is enabled). [#58316](https://github.com/ClickHouse/ClickHouse/pull/58316) (Alexander Tokmakov).
- The function reverseDNSQuery is no longer available. This closes [#58368](https://github.com/ClickHouse/ClickHouse/issues/58368). [#58369](https://github.com/ClickHouse/ClickHouse/pull/58369) (Alexey Milovidov).
- Enable various changes to improve the access control in the configuration file. These changes affect the behavior, and you check the config.xml in the access_control_improvements section. In case you are not confident, keep the values in the configuration file as they were in the previous version. [#58584](https://github.com/ClickHouse/ClickHouse/pull/58584) (Alexey Milovidov).
- Improve the operation of sumMapFiltered with NaN values. NaN values are now placed at the end (instead of randomly) and considered different from any values. -0 is now also treated as equal to 0; since 0 values are discarded, -0 values are discarded too. [#58959](https://github.com/ClickHouse/ClickHouse/pull/58959) (Raúl Marín).
- The function visibleWidth will behave according to the docs. In previous versions, it simply counted code points after string serialization, like the lengthUTF8 function, but didn't consider zero-width and combining characters, full-width characters, tabs, and deletes. Now the behavior is changed accordingly. If you want to keep the old behavior, set function_visible_width_behavior to 0, or set compatibility to 23.12 or lower. [#59022](https://github.com/ClickHouse/ClickHouse/pull/59022) (Alexey Milovidov).
- Kusto dialect is disabled until these two bugs will be fixed: [#59037](https://github.com/ClickHouse/ClickHouse/issues/59037) and [#59036](https://github.com/ClickHouse/ClickHouse/issues/59036). [#59305](https://github.com/ClickHouse/ClickHouse/pull/59305) (Alexey Milovidov). Any attempt to use Kusto will result in exception.
- More efficient implementation of the FINAL modifier no longer guarantees preserving the order even if max_threads = 1. If you counted on the previous behavior, set enable_vertical_final to 0 or compatibility to 23.12.

</div></details>
<details><summary>2023年</summary><div>

### v23.12, 2023-12-28

- Fix check for non-deterministic functions in TTL expressions. Previously, you could create a TTL expression with non-deterministic functions in some cases, which could lead to undefined behavior later. This fixes [#37250](https://github.com/ClickHouse/ClickHouse/issues/37250). Disallow TTL expressions that don't depend on any columns of a table by default. It can be allowed back by SET allow_suspicious_ttl_expressions = 1 or SET compatibility = '23.11'. Closes [#37286](https://github.com/ClickHouse/ClickHouse/pull/37286). [#51858](https://github.com/ClickHouse/ClickHouse/pull/51858) (Alexey Milovidov).
- The MergeTree setting clean_deleted_rows is deprecated, it has no effect anymore. The CLEANUP keyword for the OPTIMIZE is not allowed by default (it can be unlocked with the allow_experimental_replacing_merge_with_cleanup setting). [#58267](https://github.com/ClickHouse/ClickHouse/pull/58267) (Alexander Tokmakov). This fixes [#57930](https://github.com/ClickHouse/ClickHouse/issues/57930). This closes [#54988](https://github.com/ClickHouse/ClickHouse/issues/54988). This closes [#54570](https://github.com/ClickHouse/ClickHouse/issues/54570). This closes [#50346](https://github.com/ClickHouse/ClickHouse/issues/50346). This closes [#47579](https://github.com/ClickHouse/ClickHouse/issues/47579). The feature has to be removed because it is not good. We have to remove it as quickly as possible, because there is no other option. [#57932](https://github.com/ClickHouse/ClickHouse/pull/57932) (Alexey Milovidov).

### v23.11, 2023-12-06

- The default ClickHouse server configuration file has enabled access_management (user manipulation by SQL queries) and named_collection_control (manipulation of named collection by SQL queries) for the default user by default. This closes [#56482](https://github.com/ClickHouse/ClickHouse/issues/56482). [#56619](https://github.com/ClickHouse/ClickHouse/pull/56619) (Alexey Milovidov).
- Multiple improvements for RESPECT NULLS/IGNORE NULLS for window functions. If you use them as aggregate functions and store the states of aggregate functions with these modifiers, they might become incompatible. [#57189](https://github.com/ClickHouse/ClickHouse/pull/57189) (Raúl Marín).
- Remove optimization optimize_move_functions_out_of_any. [#57190](https://github.com/ClickHouse/ClickHouse/pull/57190) (Raúl Marín).
- Formatters %l/%k/%c in function parseDateTime are now able to parse hours/months without leading zeros, e.g. select parseDateTime('2023-11-26 8:14', '%F %k:%i') now works. Set parsedatetime_parse_without_leading_zeros = 0 to restore the previous behavior which required two digits. Function formatDateTime is now also able to print hours/months without leading zeros. This is controlled by setting formatdatetime_format_without_leading_zeros but off by default to not break existing use cases. [#55872](https://github.com/ClickHouse/ClickHouse/pull/55872) (Azat Khuzhin).
- You can no longer use the aggregate function avgWeighted with arguments of type Decimal. Workaround: convert arguments to Float64. This closes [#43928](https://github.com/ClickHouse/ClickHouse/issues/43928). This closes [#31768](https://github.com/ClickHouse/ClickHouse/issues/31768). This closes [#56435](https://github.com/ClickHouse/ClickHouse/issues/56435). If you have used this function inside materialized views or projections with Decimal arguments, contact support@clickhouse.com. Fixed error in aggregate function sumMap and made it slower around 1.5..2 times. It does not matter because the function is garbage anyway. This closes [#54955](https://github.com/ClickHouse/ClickHouse/issues/54955). This closes [#53134](https://github.com/ClickHouse/ClickHouse/issues/53134). This closes [#55148](https://github.com/ClickHouse/ClickHouse/issues/55148). Fix a bug in function groupArraySample - it used the same random seed in case more than one aggregate state is generated in a query. [#56350](https://github.com/ClickHouse/ClickHouse/pull/56350) (Alexey Milovidov).

### 23.10, 2023-11-02

- There is no longer an option to automatically remove broken data parts. This closes [#55174](https://github.com/ClickHouse/ClickHouse/issues/55174). [#55184](https://github.com/ClickHouse/ClickHouse/pull/55184) (Alexey Milovidov). [#55557](https://github.com/ClickHouse/ClickHouse/pull/55557) (Jihyuk Bok).
- The obsolete in-memory data parts can no longer be read from the write-ahead log. If you have configured in-memory parts before, they have to be removed before the upgrade. [#55186](https://github.com/ClickHouse/ClickHouse/pull/55186) (Alexey Milovidov).
- Remove the integration with Meilisearch. Reason: it was compatible only with the old version 0.18. The recent version of Meilisearch changed the protocol and does not work anymore. Note: we would appreciate it if you help to return it back. [#55189](https://github.com/ClickHouse/ClickHouse/pull/55189) (Alexey Milovidov).
- Rename directory monitor concept into background INSERT. All the settings *directory_monitor* had been renamed to distributed_background_insert*. Backward compatibility should be preserved (since old settings had been added as an alias). [#55978](https://github.com/ClickHouse/ClickHouse/pull/55978) (Azat Khuzhin).
- Do not interpret the send_timeout set on the client side as the receive_timeout on the server side and vise-versa. [#56035](https://github.com/ClickHouse/ClickHouse/pull/56035) (Azat Khuzhin).
- Comparison of time intervals with different units will throw an exception. This closes [#55942](https://github.com/ClickHouse/ClickHouse/issues/55942). You might have occasionally rely on the previous behavior when the underlying numeric values were compared regardless of the units. [#56090](https://github.com/ClickHouse/ClickHouse/pull/56090) (Alexey Milovidov).
- Rewrited the experimental S3Queue table engine completely: changed the way we keep information in zookeeper which allows to make less zookeeper requests, added caching of zookeeper state in cases when we know the state will not change, improved the polling from s3 process to make it less aggressive, changed the way ttl and max set for trached files is maintained, now it is a background process. Added system.s3queue and system.s3queue_log tables. Closes [#54998](https://github.com/ClickHouse/ClickHouse/issues/54998). [#54422](https://github.com/ClickHouse/ClickHouse/pull/54422) (Kseniia Sumarokova).

### 23.9, 2023-09-28

- Remove the status_info configuration option and dictionaries status from the default Prometheus handler. [#54090](https://github.com/ClickHouse/ClickHouse/pull/54090) (Alexey Milovidov).
- The experimental parts metadata cache is removed from the codebase. [#54215](https://github.com/ClickHouse/ClickHouse/pull/54215) (Alexey Milovidov).

### 23.8 LTS, 2023-08-31

- If a dynamic disk contains a name, it should be specified as disk = disk(name = 'disk_name', ...) in disk function arguments. In previous version it could be specified as disk = disk_<disk_name>(...), which is no longer supported. [#52820](https://github.com/ClickHouse/ClickHouse/pull/52820) (Kseniia Sumarokova).
- clickhouse-benchmark will establish connections in parallel when invoked with --concurrency more than one. Previously it was unusable if you ran it with 1000 concurrent connections from Europe to the US. Correct calculation of QPS for connections with high latency. Backward incompatible change: the option for JSON output of clickhouse-benchmark is removed. If you've used this option, you can also extract data from the system.query_log in JSON format as a workaround. [#53293](https://github.com/ClickHouse/ClickHouse/pull/53293) (Alexey Milovidov).
- The microseconds column is removed from the system.text_log, and the milliseconds column is removed from the system.metric_log, because they are redundant in the presence of the event_time_microseconds column. [#53601](https://github.com/ClickHouse/ClickHouse/pull/53601) (Alexey Milovidov).
- Deprecate the metadata cache feature. It is experimental and we have never used it. The feature is dangerous: [#51182](https://github.com/ClickHouse/ClickHouse/issues/51182). Remove the system.merge_tree_metadata_cache system table. The metadata cache is still available in this version but will be removed soon. This closes [#39197](https://github.com/ClickHouse/ClickHouse/issues/39197). [#51303](https://github.com/ClickHouse/ClickHouse/pull/51303) (Alexey Milovidov).
- Disable support for 3DES in TLS connections. [#52893](https://github.com/ClickHouse/ClickHouse/pull/52893) (Kenji Noguchi).

### 23.7, 2023-07-27

- Add NAMED COLLECTION access type (aliases USE NAMED COLLECTION, NAMED COLLECTION USAGE). This PR is backward incompatible because this access type is disabled by default (because a parent access type NAMED COLLECTION ADMIN is disabled by default as well). Proposed in [#50277](https://github.com/ClickHouse/ClickHouse/issues/50277). To grant use GRANT NAMED COLLECTION ON collection_name TO user or GRANT NAMED COLLECTION ON * TO user, to be able to give these grants named_collection_admin is required in config (previously it was named named_collection_control, so will remain as an alias). [#50625](https://github.com/ClickHouse/ClickHouse/pull/50625) (Kseniia Sumarokova).
- Fixing a typo in the system.parts column name last_removal_attemp_time. Now it is named last_removal_attempt_time. [#52104](https://github.com/ClickHouse/ClickHouse/pull/52104) (filimonov).
- Bump version of the distributed_ddl_entry_format_version to 5 by default (enables opentelemetry and initial_query_idd pass through). This will not allow to process existing entries for distributed DDL after downgrade (but note, that usually there should be no such unprocessed entries). [#52128](https://github.com/ClickHouse/ClickHouse/pull/52128) (Azat Khuzhin).
Check projection metadata the same way we check ordinary metadata. This change may prevent the server from starting in case there was a table with an invalid projection. An example is a projection that created positional columns in PK (e.g. projection p (select * order by 1, 4) which is not allowed in table PK and can cause a crash during insert/merge). Drop such projections before the update. Fixes [#52353](https://github.com/ClickHouse/ClickHouse/issues/52353). [#52361](https://github.com/ClickHouse/ClickHouse/pull/52361) (Nikolai Kochetov).
- The experimental feature hashid is removed due to a bug. The quality of implementation was questionable at the start, and it didn't get through the experimental status. This closes [#52406](https://github.com/ClickHouse/ClickHouse/issues/52406). [#52449](https://github.com/ClickHouse/ClickHouse/pull/52449) (Alexey Milovidov).

### 23.6, 2023-06-29

- Delete feature do_not_evict_index_and_mark_files in the fs cache. This feature was only making things worse. [#51253](https://github.com/ClickHouse/ClickHouse/pull/51253) (Kseniia Sumarokova).
Remove ALTER support for experimental LIVE VIEW. [#51287](https://github.com/ClickHouse/ClickHouse/pull/51287) (Alexey Milovidov).
- Decrease the default values for http_max_field_value_size and http_max_field_name_size to 128 KiB. [#51163](https://github.com/ClickHouse/ClickHouse/pull/51163) (Mikhail f. Shiryaev).
- CGroups metrics related to CPU are replaced with one metric, CGroupMaxCPU for better usability. The Normalized CPU usage metrics will be normalized to CGroups limits instead of the total number of CPUs when they are set. This closes [#50836](https://github.com/ClickHouse/ClickHouse/issues/50836). [#50835](https://github.com/ClickHouse/ClickHouse/pull/50835) (Alexey Milovidov).

### 23.5, 2023-06-08

- Compress marks and primary key by default. It significantly reduces the cold query time. Upgrade notes: the support for compressed marks and primary key has been added in version 22.9. If you turned on compressed marks or primary key or installed version 23.5 or newer, which has compressed marks or primary key on by default, you will not be able to downgrade to version 22.8 or earlier. You can also explicitly disable compressed marks or primary keys by specifying the compress_marks and compress_primary_key settings in the <merge_tree> section of the server configuration file. Upgrade notes: If you upgrade from versions prior to 22.9, you should either upgrade all replicas at once or disable the compression before upgrade, or upgrade through an intermediate version, where the compressed marks are supported but not enabled by default, such as 23.3. [#42587](https://github.com/ClickHouse/ClickHouse/pull/42587) (Alexey Milovidov).
- Make local object storage work consistently with s3 object storage, fix problem with append (closes [#48465](https://github.com/ClickHouse/ClickHouse/issues/48465)), make it configurable as independent storage. The change is backward incompatible because the cache on top of local object storage is not compatible to previous versions. [#48791](https://github.com/ClickHouse/ClickHouse/pull/48791) (Kseniia Sumarokova).
- The experimental feature "in-memory data parts" is removed. The data format is still supported, but the settings are no-op, and compact or wide parts will be used instead. This closes [#45409](https://github.com/ClickHouse/ClickHouse/issues/45409). [#49429](https://github.com/ClickHouse/ClickHouse/pull/49429) (Alexey Milovidov).
- Changed default values of settings parallelize_output_from_storages and input_format_parquet_preserve_order. This allows ClickHouse to reorder rows when reading from files (e.g. CSV or Parquet), greatly improving performance in many cases. To restore the old behavior of preserving order, use parallelize_output_from_storages = 0, input_format_parquet_preserve_order = 1. [#49479](https://github.com/ClickHouse/ClickHouse/pull/49479) (Michael Kolupaev).
- Make projections production-ready. Add the optimize_use_projections setting to control whether the projections will be selected for SELECT queries. The setting allow_experimental_projection_optimization is obsolete and does nothing. [#49719](https://github.com/ClickHouse/ClickHouse/pull/49719) (Alexey Milovidov).
- Mark joinGet as non-deterministic (so as dictGet). It allows using them in mutations without an extra setting. [#49843](https://github.com/ClickHouse/ClickHouse/pull/49843) (Azat Khuzhin).
- Revert the "groupArray returns cannot be nullable" change (due to binary compatibility breakage for groupArray/groupArrayLast/groupArraySample over Nullable types, which likely will lead to TOO_LARGE_ARRAY_SIZE or CANNOT_READ_ALL_DATA). [#49971](https://github.com/ClickHouse/ClickHouse/pull/49971) (Azat Khuzhin).
- Setting enable_memory_bound_merging_of_aggregation_results is enabled by default. If you update from version prior to 22.12, we recommend to set this flag to false until update is finished. [#50319](https://github.com/ClickHouse/ClickHouse/pull/50319) (Nikita Taranov).

### 23.4, 2023-04-26

- Formatter '%M' in function formatDateTime() now prints the month name instead of the minutes. This makes the behavior consistent with MySQL. The previous behavior can be restored using setting "formatdatetime_parsedatetime_m_is_month_name = 0". [#47246](https://github.com/ClickHouse/ClickHouse/pull/47246) (Robert Schulze).
- This change makes sense only if you are using the virtual filesystem cache. If path in the virtual filesystem cache configuration is not empty and is not an absolute path, then it will be put in <clickhouse server data directory>/caches/<path_from_cache_config>. [#48784](https://github.com/ClickHouse/ClickHouse/pull/48784) (Kseniia Sumarokova).
- Primary/secondary indices and sorting keys with identical expressions are now rejected. This behavior can be disabled using setting allow_suspicious_indices. [#48536](https://github.com/ClickHouse/ClickHouse/pull/48536) (凌涛).

### 23.3 LTS, 2023-03-30

- Lightweight DELETEs are production ready and enabled by default. The DELETE query for MergeTree tables is now available by default.
- The behavior of *domain*RFC and netloc functions is slightly changed: relaxed the set of symbols that are allowed in the URL authority for better conformance. [#46841](https://github.com/ClickHouse/ClickHouse/pull/46841) (Azat Khuzhin).
- Prohibited creating tables based on KafkaEngine with DEFAULT/EPHEMERAL/ALIAS/MATERIALIZED statements for columns. [#47138](https://github.com/ClickHouse/ClickHouse/pull/47138) (Aleksandr Musorin).
- An "asynchronous connection drain" feature is removed. Related settings and metrics are removed as well. It was an internal feature, so the removal should not affect users who had never heard about that feature. [#47486](https://github.com/ClickHouse/ClickHouse/pull/47486) (Alexander Tokmakov).
- Support 256-bit Decimal data type (more than 38 digits) in arraySum/Min/Max/Avg/Product, arrayCumSum/CumSumNonNegative, arrayDifference, array construction, IN operator, query parameters, groupArrayMovingSum, statistical functions, min/max/any/argMin/argMax, PostgreSQL wire protocol, MySQL table engine and function, sumMap, mapAdd, mapSubtract, arrayIntersect. Add support for big integers in arrayIntersect. Statistical aggregate functions involving moments (such as corr or various TTests) will use Float64 as their internal representation (they were using Decimal128 before this change, but it was pointless), and these functions can return nan instead of inf in case of infinite variance. Some functions were allowed on Decimal256 data types but returned Decimal128 in previous versions - now it is fixed. This closes [#47569](https://github.com/ClickHouse/ClickHouse/issues/47569). This closes [#44864](https://github.com/ClickHouse/ClickHouse/issues/44864). This closes [#28335](https://github.com/ClickHouse/ClickHouse/issues/28335). [#47594](https://github.com/ClickHouse/ClickHouse/pull/47594) (Alexey Milovidov).
- Make backup_threads/restore_threads server settings (instead of user settings). [#47881](https://github.com/ClickHouse/ClickHouse/pull/47881) (Azat Khuzhin).
- Do not allow const and non-deterministic secondary indices [#46839](https://github.com/ClickHouse/ClickHouse/pull/46839) (Anton Popov).

### 23.2, 2023-02-23

- Extend function "toDayOfWeek()" (alias: "DAYOFWEEK") with a mode argument that encodes whether the week starts on Monday or Sunday and whether counting starts at 0 or 1. For consistency with other date time functions, the mode argument was inserted between the time and the time zone arguments. This breaks existing usage of the (previously undocumented) 2-argument syntax "toDayOfWeek(time, time_zone)". A fix is to rewrite the function into "toDayOfWeek(time, 0, time_zone)". [#45233](https://github.com/ClickHouse/ClickHouse/pull/45233) (Robert Schulze).
- Rename setting max_query_cache_size to filesystem_cache_max_download_size. [#45614](https://github.com/ClickHouse/ClickHouse/pull/45614) (Kseniia Sumarokova).
- The default user will not have permissions for access type SHOW NAMED COLLECTION by default (e.g. default user will no longer be able to grant ALL to other users as it was before, therefore this PR is backward incompatible). [#46010](https://github.com/ClickHouse/ClickHouse/pull/46010) (Kseniia Sumarokova).
- If the SETTINGS clause is specified before the FORMAT clause, the settings will be applied to formatting as well. [#46003](https://github.com/ClickHouse/ClickHouse/pull/46003) (Azat Khuzhin).
- Remove support for setting materialized_postgresql_allow_automatic_update (which was by default turned off). [#46106](https://github.com/ClickHouse/ClickHouse/pull/46106) (Kseniia Sumarokova).
- Slightly improve performance of countDigits on realistic datasets. This closed [#44518](https://github.com/ClickHouse/ClickHouse/pull/44518). In previous versions, countDigits(0) returned 0; now it returns 1, which is more correct, and follows the existing documentation. [#46187](https://github.com/ClickHouse/ClickHouse/pull/46187) (Alexey Milovidov).
- Disallow creation of new columns compressed by a combination of codecs "Delta" or "DoubleDelta" followed by codecs "Gorilla" or "FPC". This can be bypassed using setting "allow_suspicious_codecs = true". [#45652](https://github.com/ClickHouse/ClickHouse/pull/45652) (Robert Schulze).

### 23.1, 2023-01-26

- The SYSTEM RESTART DISK query becomes a no-op. [#44647](https://github.com/ClickHouse/ClickHouse/pull/44647) (alesapin).
- The PREALLOCATE option for HASHED/SPARSE_HASHED dictionaries becomes a no-op. [#45388](https://github.com/ClickHouse/ClickHouse/pull/45388) (Azat Khuzhin). It does not give significant advantages anymore.
- Disallow Gorilla codec on columns of non-Float32 or non-Float64 type. [#45252](https://github.com/ClickHouse/ClickHouse/pull/45252) (Robert Schulze). It was pointless and led to inconsistencies.
- Parallel quorum inserts might work incorrectly with *MergeTree tables created with the deprecated syntax. Therefore, parallel quorum inserts support is completely disabled for such tables. It does not affect tables created with a new syntax. [#45430](https://github.com/ClickHouse/ClickHouse/pull/45430) (Alexander Tokmakov).
- Use the GetObjectAttributes request instead of the HeadObject request to get the size of an object in AWS S3. This change fixes handling endpoints without explicit regions after updating the AWS SDK, for example. [#45288](https://github.com/ClickHouse/ClickHouse/pull/45288) (Vitaly Baranov). AWS S3 and Minio are tested, but keep in mind that various S3-compatible services (GCS, R2, B2) may have subtle incompatibilities. This change also may require you to adjust the ACL to allow the GetObjectAttributes request.
- Forbid paths in timezone names. For example, a timezone name like /usr/share/zoneinfo/Asia/Aden is not allowed; the IANA timezone database name like Asia/Aden should be used. [#44225](https://github.com/ClickHouse/ClickHouse/pull/44225) (Kruglov Pavel).

</div></details>
<details><summary>2022年</summary><div>

### 22.12, 2022-12-15

- Fixed backward incompatibility in (de)serialization of states of min, max, any*, argMin, argMax aggregate functions with String argument. The incompatibility affects 22.9, 22.10 and 22.11 branches (fixed since 22.9.6, 22.10.4 and 22.11.2 correspondingly). Some minor releases of 22.3, 22.7 and 22.8 branches are also affected: 22.3.13...22.3.14 (fixed since 22.3.15), 22.8.6...22.8.9 (fixed since 22.8.10), 22.7.6 and newer (will not be fixed in 22.7, we recommend upgrading from 22.7.* to 22.8.10 or newer). This release note does not concern users that have never used affected versions. Incompatible versions append an extra '\0' to strings when reading states of the aggregate functions mentioned above. For example, if an older version saved state of anyState('foobar') to state_column then the incompatible version will print 'foobar\0' on anyMerge(state_column). Also incompatible versions write states of the aggregate functions without trailing '\0'. Newer versions (that have the fix) can correctly read data written by all versions including incompatible versions, except one corner case. If an incompatible version saved a state with a string that actually ends with null character, then newer version will trim trailing '\0' when reading state of affected aggregate function. For example, if an incompatible version saved state of anyState('abrac\0dabra\0') to state_column then newer versions will print 'abrac\0dabra' on anyMerge(state_column). The issue also affects distributed queries when an incompatible version works in a cluster together with older or newer versions. [#43038](https://github.com/ClickHouse/ClickHouse/pull/43038) (Alexander Tokmakov, Raúl Marín). Note: all the official ClickHouse builds already include the patches. This is not necessarily true for unofficial third-party builds that should be avoided.

### 22.11, 2022-11-17

- JSONExtract family of functions will now attempt to coerce to the requested type. [#41502](https://github.com/ClickHouse/ClickHouse/pull/41502) (Márcio Martins).

### 22.10, 2022-10-25

- Rename cache commands: show caches -> show filesystem caches, describe cache -> describe filesystem cache. [#41508](https://github.com/ClickHouse/ClickHouse/pull/41508) (Kseniia Sumarokova).
- Remove support for the WITH TIMEOUT section for LIVE VIEW. This closes [#40557](https://github.com/ClickHouse/ClickHouse/issues/40557). [#42173](https://github.com/ClickHouse/ClickHouse/pull/42173) (Alexey Milovidov).
- Remove support for the {database} macro from the client's prompt. It was displayed incorrectly if the database was unspecified and it was not updated on USE statements. This closes [#25891](https://github.com/ClickHouse/ClickHouse/issues/25891). [#42508](https://github.com/ClickHouse/ClickHouse/pull/42508) (Alexey Milovidov).

### 22.9, 2022-09-22

- Upgrade from 20.3 and older to 22.9 and newer should be done through an intermediate version if there are any ReplicatedMergeTree tables, otherwise server with the new version will not start. [#40641](https://github.com/ClickHouse/ClickHouse/pull/40641) (Alexander Tokmakov).
- Remove the functions accurate_Cast and accurate_CastOrNull (they are different to accurateCast and accurateCastOrNull by underscore in the name and they are not affected by the value of cast_keep_nullable setting). These functions were undocumented, untested, unused, and unneeded. They appeared to be alive due to code generalization. [#40682](https://github.com/ClickHouse/ClickHouse/pull/40682) (Alexey Milovidov).
- Add a test to ensure that every new table function will be documented. See [#40649](https://github.com/ClickHouse/ClickHouse/pull/40649). Rename table function MeiliSearch to meilisearch. [#40709](https://github.com/ClickHouse/ClickHouse/pull/40709) (Alexey Milovidov).
Add a test to ensure that every new function will be documented. See [#40649](https://github.com/ClickHouse/ClickHouse/pull/40649). The functions lemmatize, synonyms, stem were case-insensitive by mistake. Now they are case-sensitive. [#40711](https://github.com/ClickHouse/ClickHouse/pull/40711) (Alexey Milovidov).
- Make interpretation of YAML configs to be more conventional. [#41044](https://github.com/ClickHouse/ClickHouse/pull/41044) (Vitaly Baranov).

### 22.8, 2022-08-18

- Extended range of Date32 and DateTime64 to support dates from the year 1900 to 2299. In previous versions, the supported interval was only from the year 1925 to 2283. The implementation is using the proleptic Gregorian calendar (which is conformant with ISO 8601:2004 (clause 3.2.1 The Gregorian calendar)) instead of accounting for historical transitions from the Julian to the Gregorian calendar. This change affects implementation-specific behavior for out-of-range arguments. E.g. if in previous versions the value of 1899-01-01 was clamped to 1925-01-01, in the new version it will be clamped to 1900-01-01. It changes the behavior of rounding with toStartOfInterval if you pass INTERVAL 3 QUARTER up to one quarter because the intervals are counted from an implementation-specific point of time. Closes [#28216](https://github.com/ClickHouse/ClickHouse/issues/28216), improves [#38393](https://github.com/ClickHouse/ClickHouse/issues/38393). [#39425](https://github.com/ClickHouse/ClickHouse/pull/39425) (Roman Vasin).
- Now, all relevant dictionary sources respect remote_url_allow_hosts setting. It was already done for HTTP, Cassandra, Redis. Added ClickHouse, MongoDB, MySQL, PostgreSQL. Host is checked only for dictionaries created from DDL. [#39184](https://github.com/ClickHouse/ClickHouse/pull/39184) (Nikolai Kochetov).
- Prebuilt ClickHouse x86 binaries now require support for AVX instructions, i.e. a CPU not older than Intel Sandy Bridge / AMD Bulldozer, both released in 2011. [#39000](https://github.com/ClickHouse/ClickHouse/pull/39000) (Robert Schulze).
- Make the remote filesystem cache composable, allow not to evict certain files (regarding idx, mrk, ..), delete old cache version. Now it is possible to configure cache over Azure blob storage disk, over Local disk, over StaticWeb disk, etc. This PR is marked backward incompatible because cache configuration changes and in order for cache to work need to update the config file. Old cache will still be used with new configuration. The server will startup fine with the old cache configuration. Closes [https://github.com/ClickHouse/ClickHouse/issues/36140](https://github.com/ClickHouse/ClickHouse/issues/36140). Closes [https://github.com/ClickHouse/ClickHouse/issues/37889](https://github.com/ClickHouse/ClickHouse/issues/37889). (Kseniia Sumarokova). [#36171](https://github.com/ClickHouse/ClickHouse/pull/36171))

### 22.7, 2022-07-21

- Enable setting enable_positional_arguments by default. It allows queries like SELECT ... ORDER BY 1, 2 where 1, 2 are the references to the select clause. If you need to return the old behavior, disable this setting. [#38204](https://github.com/ClickHouse/ClickHouse/pull/38204) (Alexey Milovidov).
- Ordinary database engine and old storage definition syntax for *MergeTree tables are deprecated. By default it's not possible to create new databases with Ordinary engine. If system database has Ordinary engine it will be automatically converted to Atomic on server startup. There are settings to keep old behavior (allow_deprecated_database_ordinary and allow_deprecated_syntax_for_merge_tree), but these settings may be removed in future releases. [#38335](https://github.com/ClickHouse/ClickHouse/pull/38335) (Alexander Tokmakov).
- Force rewriting comma join to inner by default (set default value cross_to_inner_join_rewrite = 2). To have old behavior set cross_to_inner_join_rewrite = 1. [#39326](https://github.com/ClickHouse/ClickHouse/pull/39326) (Vladimir C). If you will face any incompatibilities, you can turn this setting back.

### 22.6, 2022-06-16

- Remove support for octal number literals in SQL. In previous versions they were parsed as Float64. [#37765](https://github.com/ClickHouse/ClickHouse/pull/37765) (Yakov Olkhovskiy).
- Changes how settings using seconds as type are parsed to support floating point values (for example: max_execution_time=0.5). Infinity or NaN values will throw an exception. [#37187](https://github.com/ClickHouse/ClickHouse/pull/37187) (Raúl Marín).
- Changed format of binary serialization of columns of experimental type Object. New format is more convenient to implement by third-party clients. [#37482](https://github.com/ClickHouse/ClickHouse/pull/37482) (Anton Popov).
- Turn on setting output_format_json_named_tuples_as_objects by default. It allows to serialize named tuples as JSON objects in JSON formats. [#37756](https://github.com/ClickHouse/ClickHouse/pull/37756) (Anton Popov).
- LIKE patterns with trailing escape symbol ('\') are now disallowed (as mandated by the SQL standard). [#37764](https://github.com/ClickHouse/ClickHouse/pull/37764) (Robert Schulze).
- If you run different ClickHouse versions on a cluster with AArch64 CPU or mix AArch64 and amd64 on a cluster, and use distributed queries with GROUP BY multiple keys of fixed-size type that fit in 256 bits but don't fit in 64 bits, and the size of the result is huge, the data will not be fully aggregated in the result of these queries during upgrade. Workaround: upgrade with downtime instead of a rolling upgrade.

### 22.5, 2022-05-19

- Now, background merges, mutations and OPTIMIZE will not increment SelectedRows and SelectedBytes metrics. They (still) will increment MergedRows and MergedUncompressedBytes as it was before. This only affects the metric values, and makes them better. This change does not introduce any incompatibility, but you may wonder about the changes of metrics, so we put in this category. [#37040](https://github.com/ClickHouse/ClickHouse/pull/37040) (Nikolai Kochetov).
- Updated the BoringSSL module to the official FIPS compliant version. This makes ClickHouse FIPS compliant. [#35914](https://github.com/ClickHouse/ClickHouse/pull/35914) (Meena-Renganathan). The ciphers aes-192-cfb128 and aes-256-cfb128 were removed, because they are not included in the FIPS certified version of BoringSSL.
- max_memory_usage setting is removed from the default user profile in users.xml. This enables flexible memory limits for queries instead of the old rigid limit of 10 GB.
- Disable log_query_threads setting by default. It controls the logging of statistics about every thread participating in query execution. After supporting asynchronous reads, the total number of distinct thread ids became too large, and logging into the query_thread_log has become too heavy. [#37077](https://github.com/ClickHouse/ClickHouse/pull/37077) (Alexey Milovidov).
- Remove function groupArraySorted which has a bug. [#36822](https://github.com/ClickHouse/ClickHouse/pull/36822) (Alexey Milovidov).

### 22.4, 2022-04-19

- Do not allow SETTINGS after FORMAT for INSERT queries (there is compatibility setting parser_settings_after_format_compact to accept such queries, but it is turned OFF by default). [#35883](https://github.com/ClickHouse/ClickHouse/pull/35883) (Azat Khuzhin).
- Function yandexConsistentHash (consistent hashing algorithm by Konstantin "kostik" Oblakov) is renamed to kostikConsistentHash. The old name is left as an alias for compatibility. Although this change is backward compatible, we may remove the alias in subsequent releases, that's why it's recommended to update the usages of this function in your apps. [#35553](https://github.com/ClickHouse/ClickHouse/pull/35553) (Alexey Milovidov).

### v22.3-lts, 2022-03-17

- Make arrayCompact function behave as other higher-order functions: perform compaction not of lambda function results but on the original array. If you're using nontrivial lambda functions in arrayCompact you may restore old behaviour by wrapping arrayCompact arguments into arrayMap. Closes [#34010](https://github.com/ClickHouse/ClickHouse/issues/34010) [#18535](https://github.com/ClickHouse/ClickHouse/issues/18535) [#14778](https://github.com/ClickHouse/ClickHouse/issues/14778). [#34795](https://github.com/ClickHouse/ClickHouse/pull/34795) (Alexandre Snarskii).

- Change implementation specific behavior on overflow of function toDatetime. It will be saturated to the nearest min/max supported instant of datetime instead of wraparound. This change is highlighted as "backward incompatible" because someone may unintentionally rely on the old behavior. [#32898](https://github.com/ClickHouse/ClickHouse/pull/32898) (HaiBo Li).

### v22.2, 2022-02-17

- Applying data skipping indexes for queries with FINAL may produce incorrect result. In this release we disabled data skipping indexes by default for queries with FINAL (a new setting use_skip_indexes_if_final is introduced and disabled by default). [#34243](https://github.com/ClickHouse/ClickHouse/pull/34243) (Azat Khuzhin).

### v22.1, 2022-01-18

- The functions left and right were previously implemented in parser and now full-featured. Distributed queries with left or right functions without aliases may throw exception if cluster contains different versions of clickhouse-server. If you are upgrading your cluster and encounter this error, you should finish upgrading your cluster to ensure all nodes have the same version. Also you can add aliases (AS something) to the columns in your queries to avoid this issue. [#33407](https://github.com/ClickHouse/ClickHouse/pull/33407) (alexey-milovidov).
- Resource usage by scalar subqueries is fully accounted since this version. With this change, rows read in scalar subqueries are now reported in the query_log. If the scalar subquery is cached (repeated or called for several rows) the rows read are only counted once. This change allows KILLing queries and reporting progress while they are executing scalar subqueries. [#32271](https://github.com/ClickHouse/ClickHouse/pull/32271) (Raúl Marín).

</div></details>
<details><summary>2021年</summary><div>

### v21.12, 2021-12-15

- A fix for a feature that previously had unwanted behaviour. Do not allow direct select for Kafka/RabbitMQ/FileLog. Can be enabled by setting stream_like_engine_allow_direct_select. Direct select will be not allowed even if enabled by setting, in case there is an attached materialized view. For Kafka and RabbitMQ direct selectm if allowed, will not commit massages by default. To enable commits with direct select, user must use storage level setting kafka{rabbitmq}_commit_on_select=1 (default 0). [#31053](https://github.com/ClickHouse/ClickHouse/pull/31053) (Kseniia Sumarokova).
- A slight change in behaviour of a new function. Return unquoted string in JSON_VALUE. Closes [#27965](https://github.com/ClickHouse/ClickHouse/issues/27965). [#31008](https://github.com/ClickHouse/ClickHouse/pull/31008) (Kseniia Sumarokova).
- Setting rename. Add custom null representation support for TSV/CSV input formats. Fix deserialing Nullable(String) in TSV/CSV/JSONCompactStringsEachRow/JSONStringsEachRow input formats. Rename output_format_csv_null_representation and output_format_tsv_null_representation to format_csv_null_representation and format_tsv_null_representation accordingly. [#30497](https://github.com/ClickHouse/ClickHouse/pull/30497) (Kruglov Pavel).
- Further deprecation of already unused code. This is relevant only for users of ClickHouse versions older than 20.6. A "leader election" mechanism is removed from ReplicatedMergeTree, because multiple leaders are supported since 20.6. If you are upgrading from an older version and some replica with an old version is a leader, then server will fail to start after upgrade. Stop replicas with old version to make new version start. After that it will not be possible to downgrade to version older than 20.6. [#32140](https://github.com/ClickHouse/ClickHouse/pull/32140) (tavplubix).

### v21.11, 2021-11-09

- Change order of json_path and json arguments in SQL/JSON functions (to be consistent with the standard). Closes [#30449](https://github.com/ClickHouse/ClickHouse/issues/30449). [#30474](https://github.com/ClickHouse/ClickHouse/pull/30474) (Kseniia Sumarokova).
- Remove MergeTree table setting write_final_mark. It will be always true. [#30455](https://github.com/ClickHouse/ClickHouse/pull/30455) (Kseniia Sumarokova). No actions required, all tables are compatible with the new version.
- Function bayesAB is removed. Please help to return this function back, refreshed. This closes [#26233](https://github.com/ClickHouse/ClickHouse/issues/26233). [#29934](https://github.com/ClickHouse/ClickHouse/pull/29934) (alexey-milovidov).
- This is relevant only if you already started using the experimental clickhouse-keeper support. Now ClickHouse Keeper snapshots compressed with ZSTD codec by default instead of custom ClickHouse LZ4 block compression. This behavior can be turned off with compress_snapshots_with_zstd_format coordination setting (must be equal on all quorum replicas). Backward incompatibility is quite rare and may happen only when new node will send snapshot (happens in case of recovery) to the old node which is unable to read snapshots in ZSTD format. [#29417](https://github.com/ClickHouse/ClickHouse/pull/29417) (alesapin).

### v21.10, 2021-10-16

- Now the following MergeTree table-level settings: replicated_max_parallel_sends, replicated_max_parallel_sends_for_table, replicated_max_parallel_fetches, replicated_max_parallel_fetches_for_table do nothing. They never worked well and were replaced with max_replicated_fetches_network_bandwidth, max_replicated_sends_network_bandwidth and background_fetches_pool_size. [#28404](https://github.com/ClickHouse/ClickHouse/pull/28404) (alesapin).

### v21.9, 2021-09-09

- Do not output trailing zeros in text representation of Decimal types. Example: 1.23 will be printed instead of 1.230000 for decimal with scale 6. This closes [#15794](https://github.com/ClickHouse/ClickHouse/issues/15794). It may introduce slight incompatibility if your applications somehow relied on the trailing zeros. Serialization in output formats can be controlled with the setting output_format_decimal_trailing_zeros. Implementation of toString and casting to String is changed unconditionally. [#27680](https://github.com/ClickHouse/ClickHouse/pull/27680) (alexey-milovidov).
- Do not allow to apply parametric aggregate function with -Merge combinator to aggregate function state if state was produced by aggregate function with different parameters. For example, state of fooState(42)(x) cannot be finalized with fooMerge(s) or fooMerge(123)(s), parameters must be specified explicitly like fooMerge(42)(s) and must be equal. It does not affect some special aggregate functions like quantile and sequence* that use parameters for finalization only. [#26847](https://github.com/ClickHouse/ClickHouse/pull/26847) (tavplubix).
- Under clickhouse-local, always treat local addresses with a port as remote. [#26736](https://github.com/ClickHouse/ClickHouse/pull/26736) (Raúl Marín).
- Fix the issue that in case of some sophisticated query with column aliases identical to the names of expressions, bad cast may happen. This fixes [#25447](https://github.com/ClickHouse/ClickHouse/issues/25447). This fixes [#26914](https://github.com/ClickHouse/ClickHouse/issues/26914). This fix may introduce backward incompatibility: if there are different expressions with identical names, exception will be thrown. It may break some rare cases when enable_optimize_predicate_expression is set. [#26639](https://github.com/ClickHouse/ClickHouse/pull/26639) (alexey-milovidov).
- Now, scalar subquery always returns Nullable result if it's type can be Nullable. It is needed because in case of empty subquery it's result should be Null. Previously, it was possible to get error about incompatible types (type deduction does not execute scalar subquery, and it could use not-nullable type). Scalar subquery with empty result which can't be converted to Nullable (like Array or Tuple) now throws error. Fixes [#25411](https://github.com/ClickHouse/ClickHouse/issues/25411). [#26423](https://github.com/ClickHouse/ClickHouse/pull/26423) (Nikolai Kochetov).
- Introduce syntax for here documents. Example SELECT $doc$ VALUE $doc$. [#26671](https://github.com/ClickHouse/ClickHouse/pull/26671) (Maksim Kita). This change is backward incompatible if in query there are identifiers that contain $ [#28768](https://github.com/ClickHouse/ClickHouse/issues/28768).
- Now indices can handle Nullable types, including isNull and isNotNull. [#12433](https://github.com/ClickHouse/ClickHouse/pull/12433) and [#12455](https://github.com/ClickHouse/ClickHouse/pull/12455) (Amos Bird) and [#27250](https://github.com/ClickHouse/ClickHouse/pull/27250) (Azat Khuzhin). But this was done with on-disk format changes, and even though new server can read old data, old server cannot. Also, in case you have MINMAX data skipping indices, you may get Data after mutation/merge is not byte-identical error, since new index will have .idx2 extension while before it was .idx. That said, that you should not delay updating all existing replicas, in this case, otherwise, if old replica (<21.9) will download data from new replica with 21.9+ it will not be able to apply index for downloaded part.

### v21.7, 2021-07-09

- Improved performance of queries with explicitly defined large sets. Added compatibility setting legacy_column_name_of_tuple_literal. It makes sense to set it to true, while doing rolling update of cluster from version lower than 21.7 to any higher version. Otherwise distributed queries with explicitly defined sets at IN clause may fail during update. [#25371](https://github.com/ClickHouse/ClickHouse/pull/25371) (Anton Popov).
- Forward/backward incompatible change of maximum buffer size in clickhouse-keeper (an experimental alternative to ZooKeeper). Better to do it now (before production), than later. [#25421](https://github.com/ClickHouse/ClickHouse/pull/25421) (alesapin).

### 21.5, 2021-05-20

- Change comparison of integers and floating point numbers when integer is not exactly representable in the floating point data type. In new version comparison will return false as the rounding error will occur. Example: 9223372036854775808.0 != 9223372036854775808, because the number 9223372036854775808 is not representable as floating point number exactly (and 9223372036854775808.0 is rounded to 9223372036854776000.0). But in previous version the comparison will return as the numbers are equal, because if the floating point number 9223372036854776000.0 get converted back to UInt64, it will yield 9223372036854775808. For the reference, the Python programming language also treats these numbers as equal. But this behaviour was dependend on CPU model (different results on AMD64 and AArch64 for some out-of-range numbers), so we make the comparison more precise. It will treat int and float numbers equal only if int is represented in floating point type exactly. [#22595](https://github.com/ClickHouse/ClickHouse/pull/22595) (alexey-milovidov).
- Remove support for argMin and argMax for single Tuple argument. The code was not memory-safe. The feature was added by mistake and it is confusing for people. These functions can be reintroduced under different names later. This fixes [#22384](https://github.com/ClickHouse/ClickHouse/issues/22384) and reverts [#17359](https://github.com/ClickHouse/ClickHouse/pull/17359). [#23393](https://github.com/ClickHouse/ClickHouse/pull/23393) (alexey-milovidov).

### 21.4.1 2021-04-12

- The toStartOfIntervalFunction will align hour intervals to the midnight (in previous versions they were aligned to the start of unix epoch). For example, toStartOfInterval(x, INTERVAL 11 HOUR) will split every day into three intervals: 00:00:00..10:59:59, 11:00:00..21:59:59 and 22:00:00..23:59:59. This behaviour is more suited for practical needs. This closes [#9510](https://github.com/ClickHouse/ClickHouse/issues/9510). [#22060](https://github.com/ClickHouse/ClickHouse/pull/22060) (alexey-milovidov).
- Age and Precision in graphite rollup configs should increase from retention to retention. Now it's checked and the wrong config raises an exception. [#21496](https://github.com/ClickHouse/ClickHouse/pull/21496) (Mikhail f. Shiryaev).
- Fix cutToFirstSignificantSubdomainCustom()/firstSignificantSubdomainCustom() returning wrong result for 3+ level domains present in custom top-level domain list. For input domains matching these custom top-level domains, the third-level domain was considered to be the first significant one. This is now fixed. This change may introduce incompatibility if the function is used in e.g. the sharding key. [#21946](https://github.com/ClickHouse/ClickHouse/pull/21946) (Azat Khuzhin).
- Column keys in table system.dictionaries was replaced to columns key.names and key.types. Columns key.names, key.types, attribute.names, attribute.types from system.dictionaries table does not require dictionary to be loaded. [#21884](https://github.com/ClickHouse/ClickHouse/pull/21884) (Maksim Kita).
- Now replicas that are processing the ALTER TABLE ATTACH PART[ITION] command search in their detached/ folders before fetching the data from other replicas. As an implementation detail, a new command ATTACH_PART is introduced in the replicated log. Parts are searched and compared by their checksums. [#18978](https://github.com/ClickHouse/ClickHouse/pull/18978) (Mike Kot). Note:
- ATTACH PART[ITION] queries may not work during cluster upgrade.
- It's not possible to rollback to older ClickHouse version after executing ALTER ... ATTACH query in new version as the old servers would fail to pass the ATTACH_PART entry in the replicated log.
- In this version, empty <remote_url_allow_hosts></remote_url_allow_hosts> will block all access to remote hosts while in previous versions it did nothing. If you want to keep old behaviour and you have empty remote_url_allow_hosts element in configuration file, remove it. [#20058](https://github.com/ClickHouse/ClickHouse/pull/20058) (Vladimir Chebotarev).

### v21.3, 2021-03-12

- Now it's not allowed to create MergeTree tables in old syntax with table TTL because it's just ignored. Attach of old tables is still possible. [#20282](https://github.com/ClickHouse/ClickHouse/pull/20282) (alesapin).
- Now all case-insensitive function names will be rewritten to their canonical representations. This is needed for projection query routing (the upcoming feature). [#20174](https://github.com/ClickHouse/ClickHouse/pull/20174) (Amos Bird).
- Fix creation of TTL in cases, when its expression is a function and it is the same as ORDER BY key. Now it's allowed to set custom aggregation to primary key columns in TTL with GROUP BY. Backward incompatible: For primary key columns, which are not in GROUP BY and aren't set explicitly now is applied function any instead of max, when TTL is expired. Also if you use TTL with WHERE or GROUP BY you can see exceptions at merges, while making rolling update. [#15450](https://github.com/ClickHouse/ClickHouse/pull/15450) (Anton Popov).

### v21.2.2.8-stable, 2021-02-07

- Bitwise functions (bitAnd, bitOr, etc) are forbidden for floating point arguments. Now you have to do explicit cast to integer. [#19853](https://github.com/ClickHouse/ClickHouse/pull/19853) (Azat Khuzhin).
- Forbid lcm/gcd for floats. [#19532](https://github.com/ClickHouse/ClickHouse/pull/19532) (Azat Khuzhin).
- Fix memory tracking for OPTIMIZE TABLE/merges; account query memory limits and sampling for OPTIMIZE TABLE/merges. [#18772](https://github.com/ClickHouse/ClickHouse/pull/18772) (Azat Khuzhin).
- Disallow floating point column as partition key, see [#18421](https://github.com/ClickHouse/ClickHouse/issues/18421). [#18464](https://github.com/ClickHouse/ClickHouse/pull/18464) (hexiaoting).
- Excessive parenthesis in type definitions no longer supported, example: Array((UInt8)).

### v21.1.2.15-stable 2021-01-18

- The setting input_format_null_as_default is enabled by default. [#17525](https://github.com/ClickHouse/ClickHouse/pull/17525) (alexey-milovidov).
- Check settings constraints for profile settings from config. Server will fail to start if users.xml contain settings that do not meet corresponding constraints. [#18486](https://github.com/ClickHouse/ClickHouse/pull/18486) (tavplubix).
- Restrict ALTER MODIFY SETTING from changing storage settings that affects data parts (write_final_mark and enable_mixed_granularity_parts). [#18306](https://github.com/ClickHouse/ClickHouse/pull/18306) (Amos Bird).
- Set insert_quorum_parallel to 1 by default. It is significantly more convenient to use than "sequential" quorum inserts. But if you rely to sequential consistency, you should set the setting back to zero. [#17567](https://github.com/ClickHouse/ClickHouse/pull/17567) (alexey-milovidov).
- Remove sumburConsistentHash function. This closes [#18120](https://github.com/ClickHouse/ClickHouse/issues/18120). [#18656](https://github.com/ClickHouse/ClickHouse/pull/18656) (alexey-milovidov).
- Removed aggregate functions timeSeriesGroupSum, timeSeriesGroupRateSum because a friend of mine said they never worked. This fixes [#16869](https://github.com/ClickHouse/ClickHouse/issues/16869). If you have luck using these functions, write a email to clickhouse-feedback@yandex-team.com. [#17423](https://github.com/ClickHouse/ClickHouse/pull/17423) (alexey-milovidov).
- Prohibit toUnixTimestamp(Date()) (before it just returns UInt16 representation of Date). [#17376](https://github.com/ClickHouse/ClickHouse/pull/17376) (Azat Khuzhin).
- Allow using extended integer types (Int128, Int256, UInt256) in avg and avgWeighted functions. Also allow using different types (integer, decimal, floating point) for value and for weight in avgWeighted function. This is a backward-incompatible change: now the avg and avgWeighted functions always return Float64 (as documented). Before this change the return type for Decimal arguments was also Decimal. [#15419](https://github.com/ClickHouse/ClickHouse/pull/15419) (Mike).
- Expression toUUID(N) no longer works. Replace with toUUID('00000000-0000-0000-0000-000000000000'). This change is motivated by non-obvious results of toUUID(N) where N is non zero.
- SSL Certificates with incorrect "key usage" are rejected. In previous versions they are used to work. See [#19262](https://github.com/ClickHouse/ClickHouse/issues/19262).
- incl references to substitutions file (/etc/metrika.xml) were removed from the default config (<remote_servers>, <zookeeper>, <macros>, <compression>, <networks>). If you were using substitutions file and were relying on those implicit references, you should put them back manually and explicitly by adding corresponding sections with incl="..." attributes before the update. See [#18740](https://github.com/ClickHouse/ClickHouse/pull/18740) (alexey-milovidov).

</div></details>
<details><summary>2020年</summary><div>

### v20.12.3.3-stable, 2020-12-13

- Enable use_compact_format_in_distributed_parts_names by default (see the documentation for the reference). [#16728](https://github.com/ClickHouse/ClickHouse/pull/16728) (Azat Khuzhin).
- Accept user settings related to file formats (e.g. format_csv_delimiter) in the SETTINGS clause when creating a table that uses File engine, and use these settings in all INSERTs and SELECTs. The file format settings changed in the current user session, or in the SETTINGS clause of a DML query itself, no longer affect the query. [#16591](https://github.com/ClickHouse/ClickHouse/pull/16591) (Alexander Kuzmenkov).

### v20.11.2.1, 2020-11-11

- If some profile was specified in distributed_ddl config section, then this profile could overwrite settings of default profile on server startup. It's fixed, now settings of distributed DDL queries should not affect global server settings. [#16635](https://github.com/ClickHouse/ClickHouse/pull/16635) (tavplubix).
- Restrict to use of non-comparable data types (like AggregateFunction) in keys (Sorting key, Primary key, Partition key, and so on). [#16601](https://github.com/ClickHouse/ClickHouse/pull/16601) (alesapin).
- Remove ANALYZE and AST queries, and make the setting enable_debug_queries obsolete since now it is the part of full featured EXPLAIN query. [#16536](https://github.com/ClickHouse/ClickHouse/pull/16536) (Ivan).
- Aggregate functions boundingRatio, rankCorr, retention, timeSeriesGroupSum, timeSeriesGroupRateSum, windowFunnel were erroneously made case-insensitive. Now their names are made case sensitive as designed. Only functions that are specified in SQL standard or made for compatibility with other DBMS or functions similar to those should be case-insensitive. [#16407](https://github.com/ClickHouse/ClickHouse/pull/16407) (alexey-milovidov).
- Make rankCorr function return nan on insufficient data [#16124](https://github.com/ClickHouse/ClickHouse/issues/16124). [#16135](https://github.com/ClickHouse/ClickHouse/pull/16135) (hexiaoting).
- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.10.3.30, 2020-10-28

- Make multiple_joins_rewriter_version obsolete. Remove first version of joins rewriter. [#15472](https://github.com/ClickHouse/ClickHouse/pull/15472) (Artem Zuikov).
- Change default value of format_regexp_escaping_rule setting (it's related to Regexp format) to Raw (it means - read whole subpattern as a value) to make the behaviour more like to what users expect. [#15426](https://github.com/ClickHouse/ClickHouse/pull/15426) (alexey-milovidov).
- Add support for nested multiline comments /* comment /* comment */ */ in SQL. This conforms to the SQL standard. [#14655](https://github.com/ClickHouse/ClickHouse/pull/14655) (alexey-milovidov).
- Added MergeTree settings (max_replicated_merges_with_ttl_in_queue and max_number_of_merges_with_ttl_in_pool) to control the number of merges with TTL in the background pool and replicated queue. This change breaks compatibility with older versions only if you use delete TTL. Otherwise, replication will stay compatible. You can avoid incompatibility issues if you update all shard replicas at once or execute SYSTEM STOP TTL MERGES until you finish the update of all replicas. If you'll get an incompatible entry in the replication queue, first of all, execute SYSTEM STOP TTL MERGES and after ALTER TABLE ... DETACH PARTITION ... the partition where incompatible TTL merge was assigned. Attach it back on a single replica. [#14490](https://github.com/ClickHouse/ClickHouse/pull/14490) (alesapin).
- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.9.2.20, 2020-09-22

- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.8.2.3-stable, 2020-09-08

- Now OPTIMIZE FINAL query does not recalculate TTL for parts that were added before TTL was created. Use ALTER TABLE ... MATERIALIZE TTL once to calculate them, after that OPTIMIZE FINAL will evaluate TTL's properly. This behavior never worked for replicated tables. [#14220](https://github.com/ClickHouse/ClickHouse/pull/14220) (alesapin).
- Extend parallel_distributed_insert_select setting, adding an option to run INSERT into local table. The setting changes type from Bool to UInt64, so the values false and true are no longer supported. If you have these values in server configuration, the server will not start. Please replace them with 0 and 1, respectively. [#14060](https://github.com/ClickHouse/ClickHouse/pull/14060) (Azat Khuzhin).
- Remove support for the ODBCDriver input/output format. This was a deprecated format once used for communication with the ClickHouse ODBC driver, now long superseded by the ODBCDriver2 format. Resolves [#13629](https://github.com/ClickHouse/ClickHouse/issues/13629). [#13847](https://github.com/ClickHouse/ClickHouse/pull/13847) (hexiaoting).
- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.7.2.30-stable, 2020-08-31

- Function modulo (operator %) with at least one floating point number as argument will calculate remainder of division directly on floating point numbers without converting both arguments to integers. It makes behaviour compatible with most of DBMS. This also applicable for Date and DateTime data types. Added alias mod. This closes [#7323](https://github.com/ClickHouse/ClickHouse/issues/7323). [#12585](https://github.com/ClickHouse/ClickHouse/pull/12585) (alexey-milovidov).
- Deprecate special printing of zero Date/DateTime values as 0000-00-00 and 0000-00-00 00:00:00. [#12442](https://github.com/ClickHouse/ClickHouse/pull/12442) (alexey-milovidov).
- The function groupArrayMoving* was not working for distributed queries. It's result was calculated within incorrect data type (without promotion to the largest type). The function groupArrayMovingAvg was returning integer number that was inconsistent with the avg function. This fixes [#12568](https://github.com/ClickHouse/ClickHouse/issues/12568). [#12622](https://github.com/ClickHouse/ClickHouse/pull/12622) (alexey-milovidov).
- Add sanity check for MergeTree settings. If the settings are incorrect, the server will refuse to start or to create a table, printing detailed explanation to the user. [#13153](https://github.com/ClickHouse/ClickHouse/pull/13153) (alexey-milovidov).
- Protect from the cases when user may set background_pool_size to value lower than number_of_free_entries_in_pool_to_execute_mutation or number_of_free_entries_in_pool_to_lower_max_size_of_merge. In these cases ALTERs won't work or the maximum size of merge will be too limited. It will throw exception explaining what to do. This closes [#10897](https://github.com/ClickHouse/ClickHouse/issues/10897). [#12728](https://github.com/ClickHouse/ClickHouse/pull/12728) (alexey-milovidov).
- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.6.3.28-stable

- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.5.2.7-stable 2020-07-02

- Return non-Nullable result from COUNT(DISTINCT), and uniq aggregate functions family. If all passed values are NULL, return zero instead. This improves SQL compatibility. [#11661](https://github.com/ClickHouse/ClickHouse/pull/11661) (alexey-milovidov).
- Added a check for the case when user-level setting is specified in a wrong place. User-level settings should be specified in users.xml inside <profile> section for specific user profile (or in <default> for default settings). The server won't start with exception message in log. This fixes [#9051](https://github.com/ClickHouse/ClickHouse/issues/9051). If you want to skip the check, you can either move settings to the appropriate place or add <skip_check_for_incorrect_settings>1</skip_check_for_incorrect_settings> to config.xml. [#11449](https://github.com/ClickHouse/ClickHouse/pull/11449) (alexey-milovidov).
- The setting input_format_with_names_use_header is enabled by default. It will affect parsing of input formats -WithNames and -WithNamesAndTypes. [#10937](https://github.com/ClickHouse/ClickHouse/pull/10937) (alexey-milovidov).
- Remove experimental_use_processors setting. It is enabled by default. [#10924](https://github.com/ClickHouse/ClickHouse/pull/10924) (Nikolai Kochetov).
- Update zstd to 1.4.4. It has some minor improvements in performance and compression ratio. If you run replicas with different versions of ClickHouse you may see reasonable error messages Data after merge is not byte-identical to data on another replicas. with explanation. These messages are Ok and you should not worry. This change is backward compatible but we list it here in changelog in case you will wonder about these messages. [#10663](https://github.com/ClickHouse/ClickHouse/pull/10663) (alexey-milovidov).
- Added a check for meaningless codecs and a setting allow_suspicious_codecs to control this check. This closes [#4966](https://github.com/ClickHouse/ClickHouse/issues/4966). [#10645](https://github.com/ClickHouse/ClickHouse/pull/10645) (alexey-milovidov).
- Several Kafka setting changes their defaults. See [#11388](https://github.com/ClickHouse/ClickHouse/pull/11388).
- When upgrading from versions older than 20.5, if rolling update is performed and cluster contains both versions 20.5 or greater and less than 20.5, if ClickHouse nodes with old versions are restarted and old version has been started up in presence of newer versions, it may lead to Part ... intersects previous part errors. To prevent this error, first install newer clickhouse-server packages on all cluster nodes and then do restarts (so, when clickhouse-server is restarted, it will start up with the new version).

### v20.4.2.9, 2020-05-12

- System tables (e.g. system.query_log, system.trace_log, system.metric_log) are using compact data part format for parts smaller than 10 MiB in size. Compact data part format is supported since version 20.3. If you are going to downgrade to version less than 20.3, you should manually delete table data for system logs in /var/lib/clickhouse/data/system/.
- When string comparison involves FixedString and compared arguments are of different sizes, do comparison as if smaller string is padded to the length of the larger. This is intented for SQL compatibility if we imagine that FixedString data type corresponds to SQL CHAR. This closes [#9272](https://github.com/ClickHouse/ClickHouse/issues/9272). [#10363](https://github.com/ClickHouse/ClickHouse/pull/10363) (alexey-milovidov)
- Make SHOW CREATE TABLE multiline. Now it is more readable and more like MySQL. [#10049](https://github.com/ClickHouse/ClickHouse/pull/10049) (Azat Khuzhin)
- Added a setting validate_polygons that is used in pointInPolygon function and enabled by default. [#9857](https://github.com/ClickHouse/ClickHouse/pull/9857) (alexey-milovidov)

### v20.3.2.1, 2020-03-12

- Fixed the issue file name too long when sending data for Distributed tables for a large number of replicas. Fixed the issue that replica credentials were exposed in the server log. The format of directory name on disk was changed to [shard{shard_index}[_replica{replica_index}]]. [#8911](https://github.com/ClickHouse/ClickHouse/pull/8911) (Mikhail Korotov) After you upgrade to the new version, you will not be able to downgrade without manual intervention, because old server version does not recognize the new directory format. If you want to downgrade, you have to manually rename the corresponding directories to the old format. This change is relevant only if you have used asynchronous INSERTs to Distributed tables. In the version 20.3.3 we will introduce a setting that will allow you to enable the new format gradually.
- Changed the format of replication log entries for mutation commands. You have to wait for old mutations to process before installing the new version.
- Implement simple memory profiler that dumps stacktraces to system.trace_log every N bytes over soft allocation limit [#8765](https://github.com/ClickHouse/ClickHouse/pull/8765) (Ivan) [#9472](https://github.com/ClickHouse/ClickHouse/pull/9472) (alexey-milovidov) The column of system.trace_log was renamed from timer_type to trace_type. This will require changes in third-party performance analysis and flamegraph processing tools.
- Use OS thread id everywhere instead of internal thread number. This fixes [#7477](https://github.com/ClickHouse/ClickHouse/issues/7477) Old clickhouse-client cannot receive logs that are send from the server when the setting send_logs_level is enabled, because the names and types of the structured log messages were changed. On the other hand, different server versions can send logs with different types to each other. When you don't use the send_logs_level setting, you should not care. [#8954](https://github.com/ClickHouse/ClickHouse/pull/8954) (alexey-milovidov)
- Remove indexHint function [#9542](https://github.com/ClickHouse/ClickHouse/pull/9542) (alexey-milovidov)
- Remove findClusterIndex, findClusterValue functions. This fixes [#8641](https://github.com/ClickHouse/ClickHouse/issues/8641). If you were using these functions, send an email to clickhouse-feedback@yandex-team.com [#9543](https://github.com/ClickHouse/ClickHouse/pull/9543) (alexey-milovidov)
- Now it's not allowed to create columns or add columns with SELECT subquery as default expression. [#9481](https://github.com/ClickHouse/ClickHouse/pull/9481) (alesapin)
- Require aliases for subqueries in JOIN. [#9274](https://github.com/ClickHouse/ClickHouse/pull/9274) (Artem Zuikov)
- Improved ALTER MODIFY/ADD queries logic. Now you cannot ADD column without type, MODIFY default expression does not change type of column and MODIFY type does not loose default expression value. Fixes [#8669](https://github.com/ClickHouse/ClickHouse/issues/8669). [#9227](https://github.com/ClickHouse/ClickHouse/pull/9227) (alesapin)
- Require server to be restarted to apply the changes in logging configuration. This is a temporary workaround to avoid the bug where the server logs to a deleted log file (see [#8696](https://github.com/ClickHouse/ClickHouse/issues/8696)). [#8707](https://github.com/ClickHouse/ClickHouse/pull/8707) (Alexander Kuzmenkov)
- The setting experimental_use_processors is enabled by default. This setting enables usage of the new query pipeline. This is internal refactoring and we expect no visible changes. If you will see any issues, set it to back zero. [#8768](https://github.com/ClickHouse/ClickHouse/pull/8768) (alexey-milovidov)

### v20.1.2.4, 2020-01-22

- Make the setting merge_tree_uniform_read_distribution obsolete. The server still recognizes this setting but it has no effect. [#8308](https://github.com/ClickHouse/ClickHouse/pull/8308) (alexey-milovidov)
- Changed return type of the function greatCircleDistance to Float32 because now the result of calculation is Float32. [#7993](https://github.com/ClickHouse/ClickHouse/pull/7993) (alexey-milovidov)
- Now it's expected that query parameters are represented in "escaped" format. For example, to pass string a<tab>b you have to write a\tb or a\<tab>b and respectively, a%5Ctb or a%5C%09b in URL. This is needed to add the possibility to pass NULL as \N. This fixes [#7488](https://github.com/ClickHouse/ClickHouse/issues/7488). [#8517](https://github.com/ClickHouse/ClickHouse/pull/8517) (alexey-milovidov)
- Enable use_minimalistic_part_header_in_zookeeper setting for ReplicatedMergeTree by default. This will significantly reduce amount of data stored in ZooKeeper. This setting is supported since version 19.1 and we already use it in production in multiple services without any issues for more than half a year. Disable this setting if you have a chance to downgrade to versions older than 19.1. [#6850](https://github.com/ClickHouse/ClickHouse/pull/6850) (alexey-milovidov)
- Data skipping indices are production ready and enabled by default. The settings allow_experimental_data_skipping_indices, allow_experimental_cross_to_join_conversion and allow_experimental_multiple_joins_emulation are now obsolete and do nothing. [#7974](https://github.com/ClickHouse/ClickHouse/pull/7974) (alexey-milovidov)
- Add new ANY JOIN logic for StorageJoin consistent with JOIN operation. To upgrade without changes in behaviour you need add SETTINGS any_join_distinct_right_table_keys = 1 to Engine Join tables metadata or recreate these tables after upgrade. [#8400](https://github.com/ClickHouse/ClickHouse/pull/8400) (Artem Zuikov)
- Require server to be restarted to apply the changes in logging configuration. This is a temporary workaround to avoid the bug where the server logs to a deleted log file (see [#8696](https://github.com/ClickHouse/ClickHouse/issues/8696)). [#8707](https://github.com/ClickHouse/ClickHouse/pull/8707) (Alexander Kuzmenkov)

</div></details>
<details><summary>それ以前</summary><div>

### 19.6.2.11, 2019-05-13

- HTTP header Query-Id was renamed to X-ClickHouse-Query-Id for consistency. [#4972](https://github.com/ClickHouse/ClickHouse/pull/4972) (Mikhail)

### 19.3.3, 2019-02-13

- Removed allow_experimental_low_cardinality_type setting. LowCardinality data types are production ready. [#4323](https://github.com/ClickHouse/ClickHouse/pull/4323) (alexey-milovidov)
- Reduce mark cache size and uncompressed cache size accordingly to available memory amount. [#4240](https://github.com/ClickHouse/ClickHouse/pull/4240) (Lopatin Konstantin
- Added keyword INDEX in CREATE TABLE query. A column with name index must be quoted with backticks or double quotes: `index`. [#4143](https://github.com/ClickHouse/ClickHouse/pull/4143) (Nikita Vasilev)
- sumMap now promote result type instead of overflow. The old sumMap behavior can be obtained by using sumMapWithOverflow function. [#4151](https://github.com/ClickHouse/ClickHouse/pull/4151) (Léo Ercolanelli)

### 19.1.6, 2019-01-24

- Removed undocumented feature ALTER MODIFY PRIMARY KEY because it was superseded by the ALTER MODIFY ORDER BY command. [#3887](https://github.com/ClickHouse/ClickHouse/pull/3887) (Alex Zatelepin)
- Removed function shardByHash. [#3833](https://github.com/ClickHouse/ClickHouse/pull/3833) (alexey-milovidov)
- Forbid using scalar subqueries with result of type AggregateFunction. [#3865](https://github.com/ClickHouse/ClickHouse/pull/3865) (Ivan)

### 18.16.0, 2018-12-14

- Removed the ability to compare the Date type with a number. Instead of toDate('2018-12-18') = 17883, you must use explicit type conversion = toDate(17883) [#3687](https://github.com/ClickHouse/ClickHouse/pull/3687)

### 18.14.9, 2018-10-16

- Removed the allow_experimental_decimal_type option. The Decimal data type is available for default use. [#3329](https://github.com/ClickHouse/ClickHouse/pull/3329)

### 18.12.17, 2018-09-16

- The enable_optimize_predicate_expression option is enabled by default (which is rather optimistic). If query analysis errors occur that are related to searching for the column names, set enable_optimize_predicate_expression to 0. [Winter Zhang](https://github.com/ClickHouse/ClickHouse/pull/3107)

### 18.12.13, 2018-09-10

- In queries with JOIN, the star character expands to a list of columns in all tables, in compliance with the SQL standard. You can restore the old behavior by setting asterisk_left_columns_only to 1 on the user configuration level.

### 18.10.3, 2018-08-13

- Removed support for CHECK TABLE queries for Distributed tables.

### 18.4.0, 2018-07-28

- Parameters for Kafka engine was changed from Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format[, kafka_schema, kafka_num_consumers]) to Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format[, kafka_row_delimiter, kafka_schema, kafka_num_consumers]). If your tables use kafka_schema or kafka_num_consumers parameters, you have to manually edit the metadata files path/metadata/database/table.sql and add kafka_row_delimiter parameter with '' value.

### 18.1.0, 2018-07-23

- Converting a string containing the number zero to DateTime does not work. Example: SELECT toDateTime('0'). This is also the reason that DateTime DEFAULT '0' does not work in tables, as well as <null_value>0</null_value> in dictionaries. Solution: replace 0 with 0000-00-00 00:00:00.

### 1.1.54388, 2018-06-28

- Removed escaping in Vertical and Pretty* formats and deleted the VerticalRaw format.
- If servers with version 1.1.54388 (or newer) and servers with an older version are used simultaneously in a distributed query and the query has the cast(x, 'Type') expression without the AS keyword and does not have the word cast in uppercase, an exception will be thrown with a message like Not found column cast(0, 'UInt8') in block. Solution: Update the server on the entire cluster.


### 1.1.54380, 2018-04-21

- Removed support for expressions like (a, b) IN (SELECT (a, b)) (you can use the equivalent expression (a, b) IN (SELECT a, b)). In previous releases, these expressions led to undetermined WHERE filtering or caused errors.

### 1.1.54378, 2018-04-16

- Removed the special interpretation of an IN expression if an array is specified on the left side. Previously, the expression arr IN (set) was interpreted as “at least one arr element belongs to the set”. To get the same behavior in the new version, write arrayExists(x -> x IN (set), arr).
- Disabled the incorrect use of the socket option SO_REUSEPORT, which was incorrectly enabled by default in the Poco library. Note that on Linux there is no longer any reason to simultaneously specify the addresses :: and 0.0.0.0 for listen – use just ::, which allows listening to the connection both over IPv4 and IPv6 (with the default kernel config settings). You can also revert to the behavior from previous versions by specifying <listen_reuse_port>1</listen_reuse_port> in the config.

### 1.1.54362, 2018-03-11

- Removed the distributed_ddl_allow_replicated_alter option. This behavior is enabled by default.
- Removed the strict_insert_defaults setting. If you were using this functionality, write to clickhouse-feedback@yandex-team.com.
- Removed the UnsortedMergeTree engine.

### 1.1.54337, 2018-01-18

- The format for marks in Log type tables that contain Nullable columns was changed in a backward incompatible way. If you have these tables, you should convert them to the TinyLog type before starting up the new server version. To do this, replace ENGINE = Log with ENGINE = TinyLog in the corresponding .sql file in the metadata directory. If your table does not have Nullable columns or if the type of your table is not Log, then you do not need to do anything.
- Removed the experimental_allow_extended_storage_definition_syntax setting. Now this feature is enabled by default.
- The runningIncome function was renamed to runningDifferenceStartingWithFirstvalue to avoid confusion.
- Removed the FROM ARRAY JOIN arr syntax when ARRAY JOIN is specified directly after FROM with no table (Amos Bird).
- Removed the BlockTabSeparated format that was used solely for demonstration purposes.
- Changed the state format for aggregate functions varSamp, varPop, stddevSamp, stddevPop, covarSamp, covarPop, corr. If you have stored states of these aggregate functions in tables (using the AggregateFunction data type or materialized views with corresponding states), please write to clickhouse-feedback@yandex-team.com.
- In previous server versions there was an undocumented feature: if an aggregate function depends on parameters, you can still specify it without parameters in the AggregateFunction data type. Example: AggregateFunction(quantiles, UInt64) instead of AggregateFunction(quantiles(0.5, 0.9), UInt64). This feature was lost. Although it was undocumented, we plan to support it again in future releases.
- Enum data types cannot be used in min/max aggregate functions. This ability will be returned in the next release.

### 1.1.54310, 2017-11-01

- Creation of temporary tables with an engine other than Memory is not allowed.
- Explicit creation of tables with the View or MaterializedView engine is not allowed.
- During table creation, a new check verifies that the sampling key expression is included in the primary key.

### 1.1.54276, 2017-08-16

- Changed the binary format of aggregate states of groupArray(array_column) functions for arrays.

### 1.1.54245, 2017-07-04

- Removed SET GLOBAL

</div></details>

## 最後に

ClickHouse, Inc. が設立されました。おめでとうございます。　　

https://clickhouse.com/blog/en/2021/clickhouse-inc/

資金調達も順調なようでますます成長が見込める製品になったように思います。

https://clickhouse.com/blog/en/2021/clickhouse-raises-250m-series-b/