ClickHouse DB::Exception: Illegal type of argument

The "DB::Exception: Illegal type of argument" error is one of the most common ClickHouse errors. It occurs when you pass a column or literal of the wrong data type to a function. ClickHouse is strongly typed and does not perform implicit conversions in many situations, so a function expecting a numeric argument will reject a String, and vice versa.

Impact

The query fails immediately. This error is widespread in ad-hoc analytics, dashboards, and application code because it often stems from simple type mismatches that go unnoticed until execution. In automated pipelines the failed query can block downstream processing.

Common Causes

1. Passing a String to a numeric function — for example, calling toDate('not-a-date') or sum(string_column).
2. Using the wrong date/time function variant — toDate vs toDateTime, or passing a DateTime64 where DateTime is expected.
3. Aggregate functions on non-numeric types — avg(), sum(), and similar aggregates require numeric input.
4. Array functions receiving non-array arguments — calling arrayJoin(), arrayMap(), or length() on a scalar when an Array is expected.
5. Nullable vs non-Nullable mismatch — some functions do not accept Nullable types directly.
6. Type confusion after JSON extraction — values extracted with JSONExtractRaw or similar functions return String, which then needs explicit casting.

Troubleshooting and Resolution Steps

1. Read the full error message. It specifies which function rejected which type. For example: Illegal type String of argument of function toInt32.

2. Check the column's actual type:

   SELECT name, type FROM system.columns
   WHERE database = 'your_db' AND table = 'your_table' AND name = 'suspect_column';
   

3. Cast the argument to the expected type. ClickHouse provides a rich set of conversion functions:

   -- Convert String to Int
   SELECT toInt32(string_column) FROM your_table;
   
   -- Convert String to Date
   SELECT toDate(date_string_column) FROM your_table;
   

4. Use toTypeName() to debug types in complex expressions:

   SELECT toTypeName(some_expression) FROM your_table LIMIT 1;
   

5. Handle Nullable types. If a function does not accept Nullable, unwrap it:

   SELECT yourFunction(assumeNotNull(nullable_column)) FROM your_table;
   -- Or provide a default
   SELECT yourFunction(ifNull(nullable_column, 0)) FROM your_table;
   

6. Use the correct function variant. Some functions have specialized versions for different types:

   • toDate vs toDateTime vs toDateTime64
   • reinterpretAsString vs toString
   • toInt32 vs toInt32OrNull vs toInt32OrZero

7. Check for LowCardinality wrappers. Most functions handle LowCardinality transparently, but in edge cases you may need to unwrap with toLowCardinality() or cast explicitly.

Best Practices

• Use toTypeName() liberally when debugging type errors — it reveals the exact type ClickHouse sees, including Nullable and LowCardinality wrappers.
• Prefer explicit casts over relying on implicit conversions. This makes queries self-documenting and avoids surprises.
• When extracting values from JSON, always cast the result to the appropriate type immediately.
• Use the OrNull and OrZero variants of conversion functions (e.g., toInt32OrNull) to handle invalid data gracefully instead of throwing errors.

Frequently Asked Questions

Q: Why does ClickHouse not just convert types automatically like MySQL does?
A: ClickHouse favors explicitness and performance. Implicit conversions can hide bugs and reduce query performance by adding unexpected casts. By requiring explicit types, ClickHouse ensures you get exactly the behavior you intend.

Q: How do I convert a String column that contains numbers to an integer?
A: Use toInt32(column) for strict conversion, or toInt32OrZero(column) / toInt32OrNull(column) if the column may contain non-numeric values.

Q: I get this error with arrayJoin. What's going on?
A: arrayJoin requires an Array argument. If your column is a String that looks like an array (e.g., '[1,2,3]'), you need to parse it first with JSONExtract or splitByChar before passing it to arrayJoin.

Q: Can Nullable cause this error even when the base type is correct?
A: Yes. Some functions explicitly reject Nullable arguments. Wrap the value with assumeNotNull() or ifNull() to provide a non-null value. The error message will mention Nullable(X) when this is the cause.