ClickHouse DB::Exception: UNION ALL result structures mismatch

The "DB::Exception: UNION ALL result structures mismatch" error in ClickHouse occurs when the branches of a UNION ALL (or UNION DISTINCT) query produce different column structures. The UNION_ALL_RESULT_STRUCTURES_MISMATCH error code (258) is raised when the SELECT clauses differ in the number of columns or in the data types of columns at the same position.

Impact

The query is rejected during parsing or analysis and returns no results. No data is read or processed. This error must be fixed in the query itself before it can execute.

Common Causes

1. The SELECT clauses in the UNION branches return a different number of columns
2. Corresponding columns in different branches have incompatible data types (e.g., String in one branch and UInt64 in another)
3. Columns at the same position are in a different order across branches, so their types no longer line up
4. One branch includes extra columns from a SELECT * that another branch does not have
5. A schema change in an underlying table caused one branch to produce different columns than before
6. Using subqueries or CTEs where column definitions drift between branches

Troubleshooting and Resolution Steps

1. Identify the structural differences. Run each branch of the UNION separately and compare:

   -- Run first branch
   SELECT toTypeName(col1) AS t1, toTypeName(col2) AS t2
   FROM (SELECT col1, col2 FROM table_a LIMIT 0);
   
   -- Run second branch
   SELECT toTypeName(col1) AS t1, toTypeName(col2) AS t2
   FROM (SELECT col1, col2 FROM table_b LIMIT 0);
   

2. Ensure both branches return the same number of columns. Add placeholder columns if needed:

   SELECT id, name, value FROM table_a
   UNION ALL
   SELECT id, name, 0 AS value FROM table_b;  -- table_b has no value column
   

3. Cast columns to matching types explicitly:

   SELECT id, toString(status) AS status FROM table_a
   UNION ALL
   SELECT id, status FROM table_b;  -- status is already String in table_b
   

4. Avoid SELECT * in UNION queries. Always specify columns explicitly:

   -- Fragile (schema changes break it):
   SELECT * FROM table_a UNION ALL SELECT * FROM table_b;
   
   -- Robust:
   SELECT id, name, value FROM table_a
   UNION ALL
   SELECT id, name, value FROM table_b;
   

5. If columns are in different orders, reorder them to match:

   SELECT id, name FROM table_a
   UNION ALL
   SELECT id, name FROM table_b;  -- not: SELECT name, id FROM table_b
   

6. Use DESCRIBE to check column structures when debugging:

   DESCRIBE (SELECT id, name, value FROM table_a);
   DESCRIBE (SELECT id, name, value FROM table_b);
   

Best Practices

• Always list columns explicitly in UNION queries rather than using SELECT *.
• Use consistent column aliases and explicit type casts to make UNION branches compatible.
• When building UNION queries programmatically, validate that all branches produce the same column list before executing.
• Document the expected schema for each UNION branch, especially when the branches query different tables.
• Test UNION queries after schema changes to any of the underlying tables.

Frequently Asked Questions

Q: Does ClickHouse match UNION columns by name or by position?
A: ClickHouse matches UNION columns by position (ordinal), not by name. The column names of the result come from the first branch. Ensure the order and types match in every branch.

Q: Can ClickHouse automatically cast types in UNION branches?
A: ClickHouse can perform some implicit type widening (e.g., UInt32 to UInt64), but it will not convert between fundamentally different types (e.g., String to Int). Use explicit CAST for incompatible types.

Q: Is there a difference between UNION ALL and UNION DISTINCT for this error?
A: No. Both require the same column structure across all branches. The difference is only in deduplication behavior — UNION DISTINCT removes duplicate rows, while UNION ALL keeps all rows.