ClickHouse DB::Exception: Data type cannot be used in sorting key

The "DB::Exception: Data type cannot be used in sorting key" error is raised when you try to include a column in a MergeTree table's ORDER BY or PRIMARY KEY whose data type does not support comparison or sorting. ClickHouse needs to be able to compare key values to maintain sorted order within parts and to perform merges, so types without a defined ordering — like Nullable, Array, Map, or Tuple with unsortable elements — are rejected.

Impact

The CREATE TABLE or ALTER TABLE statement fails, preventing you from defining the table's sorting key with the unsupported column type. Since the sorting key is fundamental to MergeTree table performance and functionality, this must be resolved before the table can be created.

Common Causes

1. Nullable columns in ORDER BY — Nullable(T) types cannot be used directly in sorting keys because NULL values have ambiguous sort order.
2. Array or Map columns in ORDER BY — these complex types do not have a well-defined sort order for MergeTree operations.
3. Tuple columns with non-comparable elements — a Tuple containing an Array or Map element.
4. Float types in PRIMARY KEY — while Float32/Float64 can technically be used in ORDER BY, they are discouraged because NaN values break ordering guarantees.
5. AggregateFunction types in the key — aggregate state columns cannot be compared.

Troubleshooting and Resolution Steps

1. Remove Nullable from key columns. If the column must be in the sorting key, make it non-Nullable:

   -- Wrong
   CREATE TABLE t (
       id Nullable(UInt64),
       value String
   ) ENGINE = MergeTree() ORDER BY id;
   
   -- Correct
   CREATE TABLE t (
       id UInt64,
       value String
   ) ENGINE = MergeTree() ORDER BY id;
   

2. Use a non-Nullable expression in the key. If the source data can contain NULLs, use a default value:

   CREATE TABLE t (
       id Nullable(UInt64),
       value String
   ) ENGINE = MergeTree()
   ORDER BY (ifNull(id, 0));
   

   Note that with this approach, the ORDER BY expression differs from the stored column.

3. Extract a sortable component from complex types:

   -- Instead of ordering by an Array column, use its length or first element
   CREATE TABLE t (
       tags Array(String),
       tag_count UInt32 MATERIALIZED length(tags)
   ) ENGINE = MergeTree()
   ORDER BY tag_count;
   

4. Avoid Float types in keys if possible. Use Decimal or integer types instead to get reliable sort ordering:

   -- Better for key usage
   CREATE TABLE t (
       price Decimal(18, 4),
       id UInt64
   ) ENGINE = MergeTree()
   ORDER BY (price, id);
   

5. For Tuple keys, ensure all elements are sortable:

   -- Wrong: Tuple contains Array
   ORDER BY (tuple_col)  -- where tuple_col is Tuple(String, Array(Int32))
   
   -- Correct: use individual sortable elements
   ORDER BY (tuple_col.1)
   

Best Practices

• Design your table schema so that key columns use simple, non-Nullable, comparable types (integers, dates, strings, fixed-width types).
• Keep Nullable columns outside the sorting key. Use DEFAULT values for key columns that need to handle missing data.
• Prefer integer and date types for keys — they sort efficiently and unambiguously.
• If you need to filter by an Array or Map column, consider using a secondary index (e.g., bloom_filter) instead of putting it in the sorting key.

Frequently Asked Questions

Q: Why can't Nullable types be used in sorting keys?
A: ClickHouse's MergeTree engine needs a total ordering for merge operations. NULL values create ambiguity in comparisons (NULL is neither less than, equal to, nor greater than any value), which would break merge correctness. Removing Nullable from key columns ensures a well-defined sort order.

Q: Can I use LowCardinality(String) in a sorting key?
A: Yes. LowCardinality(String) is fully sortable and works well in sorting keys. The dictionary encoding doesn't affect sort order.

Q: What about Enum types in the key?
A: Enum8 and Enum16 are valid for sorting keys. They sort by their underlying integer values.

Q: Can I use a Tuple in ORDER BY?
A: Yes, as long as all elements of the Tuple are individually sortable types. A Tuple(UInt32, String) is fine; a Tuple(UInt32, Array(String)) is not.