ClickHouse DB::Exception: Illegal syntax for data type

The "DB::Exception: Illegal syntax for data type" error occurs when a type expression is syntactically valid but violates ClickHouse's type system rules. The most common case is nesting Nullable inside Nullable — Nullable(Nullable(Int32)) — but it also covers other invalid combinations like LowCardinality(Array(...)) or malformed parametric type arguments.

Impact

The DDL or query that contains the invalid type definition fails immediately. This blocks table creation, ALTER statements, and CAST operations that use the malformed type. Fixing it is usually just a matter of correcting the type expression.

Common Causes

1. Double Nullable wrapping — Nullable(Nullable(T)) is not allowed. Nullable can only wrap a non-Nullable base type.
2. LowCardinality of an unsupported inner type — LowCardinality(Array(String)) is invalid because LowCardinality only supports certain base types.
3. Nested Array of Nullable at the wrong level — while Array(Nullable(Int32)) is valid, some nested combinations are not.
4. Invalid Enum definitions — Enum values that don't follow the required Enum8('val1' = 1, 'val2' = 2) syntax.
5. Malformed Decimal parameters — incorrect precision/scale values or missing parentheses.
6. Generated DDL from ORMs or migration tools — automated tools may produce type expressions that don't conform to ClickHouse's rules.

Troubleshooting and Resolution Steps

1. Read the error message carefully. It usually quotes the exact type expression that failed, making it easy to spot the problem.

2. Remove nested Nullable:

   -- Wrong
   CREATE TABLE t (col Nullable(Nullable(Int32))) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Correct
   CREATE TABLE t (col Nullable(Int32)) ENGINE = MergeTree() ORDER BY tuple();
   

3. Fix LowCardinality usage. LowCardinality supports String, FixedString, Date, DateTime, and numeric types:

   -- Wrong
   CREATE TABLE t (col LowCardinality(Array(String))) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Correct
   CREATE TABLE t (col Array(LowCardinality(String))) ENGINE = MergeTree() ORDER BY tuple();
   

4. Correct Enum syntax:

   -- Wrong
   CREATE TABLE t (status Enum('active', 'inactive')) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Correct
   CREATE TABLE t (status Enum8('active' = 1, 'inactive' = 2)) ENGINE = MergeTree() ORDER BY tuple();
   

5. Validate Decimal parameters. Precision must be between 1 and 76, and scale must not exceed precision:

   -- Wrong
   CREATE TABLE t (val Decimal(0, 5)) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Correct
   CREATE TABLE t (val Decimal(18, 5)) ENGINE = MergeTree() ORDER BY tuple();
   

6. Test type expressions in SELECT before using in DDL:

   SELECT CAST(1 AS Nullable(Int32));  -- verify the type is accepted
   

Best Practices

• Never double-wrap with Nullable — use Nullable(T) only on non-Nullable base types.
• Place LowCardinality on the innermost compatible type, not on container types like Array or Map.
• When using ORMs or migration tools, review the generated DDL before executing it on ClickHouse.
• Consult the ClickHouse documentation for the valid combinations of type modifiers (Nullable, LowCardinality, Array, etc.).

Frequently Asked Questions

Q: Can I have Nullable inside an Array?
A: Yes. Array(Nullable(Int32)) is valid and commonly used. What you cannot do is Nullable(Array(Int32)) — an Array itself cannot be Nullable in ClickHouse.

Q: Why can't I use LowCardinality(Array(String))?
A: LowCardinality works by dictionary-encoding individual values. Arrays are complex structures that don't fit this model. Instead, use Array(LowCardinality(String)) to dictionary-encode the array elements.

Q: Is Nullable(LowCardinality(String)) valid?
A: No, but LowCardinality(Nullable(String)) is valid. The LowCardinality wrapper must be on the outside, with Nullable inside.

Q: My ORM generates Nullable(Nullable(...)). How do I fix this?
A: This is a bug in the ORM's ClickHouse dialect. Check for an updated version of the driver/ORM, or add a post-processing step to your migration scripts that removes nested Nullable wrappers.