Clickhouse 主键或排序键不能为null的报错和解决方法Sorting key cannot contain nullable columns

默认Clickhouse的排序键和主键是不允许null的，否则会报Sorting key cannot contain nullable columns的错误信息。可以通过参数强行设置允许为null，但不建议使用。

建表语句和报错样例

localhost :) create table t2(id Nullable(int), name String)engine=MergeTree order by id;

CREATE TABLE t2
(
    `id` Nullable(int),
    `name` String
)
ENGINE = MergeTree
ORDER BY id

Query id: 26906160-ea00-4d9b-ae18-d7d7e1307a19


0 rows in set. Elapsed: 0.005 sec.

Received exception from server (version 21.4.5):
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Sorting key cannot contain nullable columns.

localhost :)

解决方案

localhost :) create table t2(id Nullable(int), name String)engine=MergeTree order by id settings allow_nullable_key=1;

CREATE TABLE t2
(
    `id` Nullable(int),
    `name` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS allow_nullable_key = 1

Query id: 5bbf4418-3bf9-44eb-86df-b931796b68b8

Ok.

0 rows in set. Elapsed: 0.018 sec.
localhost :) insert into t2 values(1,'111'),(null,'NNN'),(2,'2222');

INSERT INTO t2 VALUES

Query id: b5c5bb0d-3300-44bb-ac9b-06f3893177d9

Ok.

3 rows in set. Elapsed: 0.021 sec.

localhost :) select * from t2;

SELECT *
FROM t2

Query id: 40fd61ee-d00a-4793-b240-a5dde25e5de7

┌───id─┬─name─┐
│    1 │ 111  │
│    2 │ 2222 │
│ ᴺᵁᴸᴸ │ NNN  │
└──────┴──────┘

3 rows in set. Elapsed: 0.018 sec.