UNION_ALL_RESULT_STRUCTURES_MISMATCH ClickHouse error¶

The UNION_ALL_RESULT_STRUCTURES_MISMATCH error in ClickHouse (and Tinybird) happens when you try to combine multiple queries with UNION ALL, but the result structures don't match. This includes having different numbers of columns, different column orders, or incompatible data types.

What causes this error¶

You'll typically see it when:

• Using UNION ALL with different numbers of columns
• Having columns in different orders across queries
• Using incompatible data types in corresponding columns
• Missing columns in one part of the UNION
• Using different column names across UNION parts

Example errors¶

SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id FROM events

SELECT user_id, event_type FROM users
UNION ALL
SELECT event_type, user_id FROM events

SELECT user_id, event_type FROM users
UNION ALL
SELECT event_type, user_id FROM events

SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id, event_type FROM events

How to fix it¶

Match column count¶

Ensure all queries have the same number of columns:

SELECT user_id, event_type FROM users
UNION ALL
SELECT user_id, event_type FROM events

Match column order¶

Ensure columns are in the same order across all queries:

SELECT user_id, event_type FROM users
UNION ALL
SELECT user_id, event_type FROM events

Use compatible data types¶

Ensure corresponding columns have compatible types:

SELECT toString(user_id) as user_id, event_type FROM users
UNION ALL
SELECT toString(user_id) as user_id, event_type FROM events

Use column aliases¶

Use aliases to ensure consistent column names:

SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id as user_id, event_type FROM events

Common patterns and solutions¶

Simple UNION ALL¶

When combining similar tables:

SELECT user_id, event_type, timestamp FROM users
UNION ALL
SELECT user_id, event_type, timestamp FROM events

UNION ALL with different sources¶

When combining different data sources:

SELECT user_id, 'user' as source, name as description FROM users
UNION ALL
SELECT user_id, 'event' as source, event_type as description FROM events

UNION ALL with aggregations¶

When combining aggregated results:

SELECT user_id, COUNT(*) as count FROM users GROUP BY user_id
UNION ALL
SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id

UNION ALL with constants¶

When adding constant values:

SELECT user_id, event_type, 'user' as source FROM users
UNION ALL
SELECT user_id, event_type, 'event' as source FROM events

Advanced solutions¶

Using CTEs for complex unions¶

Break down complex unions using CTEs:

WITH user_data AS (
    SELECT user_id, name as description, 'user' as source
    FROM users
),
event_data AS (
    SELECT user_id, event_type as description, 'event' as source
    FROM events
)
SELECT * FROM user_data
UNION ALL
SELECT * FROM event_data

Handling different schemas¶

When tables have different schemas:

SELECT 
    user_id,
    name as description,
    'user' as source,
    created_at as timestamp
FROM users
UNION ALL
SELECT 
    user_id,
    event_type as description,
    'event' as source,
    timestamp
FROM events

Using type conversion¶

Convert types to make them compatible:

SELECT 
    toString(user_id) as user_id,
    name as description
FROM users
UNION ALL
SELECT 
    toString(user_id) as user_id,
    event_type as description
FROM events

Handling nullable types¶

When dealing with nullable types, ensure compatibility:

SELECT id, count(*) as count FROM users GROUP BY id
UNION ALL
SELECT id, toInt64(NULL) as count FROM events GROUP BY id

Best practices¶

Column alignment¶

• Always check column count and order
• Use consistent column names across queries
• Ensure compatible data types
• Use aliases for clarity

Query structure¶

• Test each part of the UNION separately
• Use CTEs for complex unions
• Document the expected structure
• Validate results after combining

Performance considerations¶

• Use UNION ALL instead of UNION when duplicates are acceptable
• Consider using Materialized Views for complex unions
• Monitor query performance on large datasets
• Use appropriate indexes for UNION queries

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Building Pipes that combine multiple Data Sources
• Using Materialized Views with UNION operations
• Working with nested Pipes that have different schemas
• Creating API endpoints that combine multiple queries

To debug in Tinybird:

1. Check your Data Source schemas for compatibility
2. Verify Pipe node outputs have consistent structures
3. Test UNION operations with simple queries first
4. Use the Query Builder to validate UNION structures

See also¶

• ClickHouse UNION Operations
• Working with Data Sources
• Common function errors