ClickHouse DB::Exception: No common supertype for types

The "DB::Exception: There is no supertype for types" error occurs when ClickHouse needs to unify two or more types into a single common type but cannot find one. This happens in UNION ALL (where corresponding columns across branches must share a type), in IF/multiIf/CASE expressions (where all result branches must have a common type), and in array literals with mixed-type elements.

Impact

The query fails because ClickHouse cannot determine what type to use for the combined result. This prevents UNION queries, conditional expressions, and array construction from executing until the types are explicitly aligned.

Common Causes

1. UNION ALL with incompatible column types — one branch returns String and another returns Date for the same position.
2. IF or CASE with mismatched result types — IF(cond, 'text', 42) tries to unify String and UInt8.
3. Array literal with mixed types — [1, 'two', 3.0] has no single type that fits all elements.
4. Coalesce or ifNull with incompatible arguments — coalesce(string_col, 0) mixes String and integer.
5. Type incompatibility between Enum and String in conditional logic — while Enum can often be promoted to String, some edge cases fail.
6. Date vs DateTime vs String confusion — these types don't always have a natural supertype.

Troubleshooting and Resolution Steps

1. Identify the conflicting types. The error message names the types that ClickHouse could not unify, e.g., "There is no supertype for types String, UInt64."

2. Cast all branches to a common type explicitly in UNION ALL:

   SELECT toString(id) AS col FROM table_a
   UNION ALL
   SELECT toString(name) AS col FROM table_b;
   

3. Align IF/CASE branch types:

   -- Wrong: String vs UInt8
   SELECT IF(cond, 'yes', 0) FROM t;
   
   -- Correct: both branches return String
   SELECT IF(cond, 'yes', 'no') FROM t;
   
   -- Or both return UInt8
   SELECT IF(cond, 1, 0) FROM t;
   

4. Fix array literals by casting elements:

   -- Wrong: mixed types
   SELECT [1, 'two', 3.0];
   
   -- Correct: all strings
   SELECT ['1', 'two', '3.0'];
   
   -- Or use explicit casting
   SELECT [toString(1), 'two', toString(3.0)];
   

5. Handle coalesce and ifNull with matching types:

   -- Wrong
   SELECT coalesce(nullable_string_col, 0) FROM t;
   
   -- Correct
   SELECT coalesce(nullable_string_col, '') FROM t;
   

6. Use toTypeName() to debug which types ClickHouse sees:

   SELECT toTypeName(branch1_expr), toTypeName(branch2_expr);
   

Best Practices

• Always ensure all branches of IF, CASE, and multiIf return the same type.
• In UNION ALL queries, explicitly cast columns to a common type rather than relying on implicit type resolution.
• When constructing arrays, ensure all elements share the same type or explicitly cast them.
• Use toTypeName() to verify types during development before building complex conditional or union expressions.

Frequently Asked Questions

Q: What is a "supertype" in ClickHouse?
A: A supertype is the smallest type that can represent values from all given types without loss. For example, the supertype of Int32 and Int64 is Int64. The supertype of UInt16 and Float32 is Float32. But String and Int64 have no supertype because neither can represent the other's values.

Q: Can ClickHouse promote integers to strings automatically?
A: No. Unlike some databases, ClickHouse does not implicitly convert numbers to strings. You must explicitly use toString() if you want to unify numeric and string types.

Q: How do I handle a CASE expression where branches return different numeric types?
A: ClickHouse can usually find a common numeric supertype (e.g., Int32 and Int64 unify to Int64). The error only occurs when the types are fundamentally incompatible, like mixing String with numeric types. If needed, cast all branches to the widest type explicitly.

Q: Does Nullable affect supertype resolution?
A: ClickHouse handles Nullable gracefully in supertype resolution — Nullable(Int32) and Int32 unify to Nullable(Int32). The NO_COMMON_TYPE error is about base type incompatibility, not Nullable wrapping.