TYPE_MISMATCH ClickHouse error¶

The TYPE_MISMATCH error in ClickHouse (and Tinybird) happens when you try to perform operations between values of different types that ClickHouse can't automatically convert. This is different from NO_COMMON_TYPE in that the types could potentially be compatible, but the specific operation or context doesn't allow automatic conversion.

What causes this error¶

You'll typically see it when:

• Comparing values of different types in WHERE clauses
• Performing arithmetic operations on mixed types
• Using incompatible types in JOIN conditions
• Passing wrong types to function parameters
• Using different types in ORDER BY or GROUP BY clauses

Example errors¶

SELECT * FROM events WHERE user_id = 'abc'

SELECT user_id = 'abc' FROM events

SELECT * FROM users u JOIN events e ON u.id = e.user_id_string

SELECT user_id IN ('1', '2', 'abc') FROM events

How to fix it¶

Use explicit type casting¶

Convert values to compatible types using CAST or type conversion functions:

SELECT * FROM events WHERE user_id = toInt32('123')

SELECT value + toInt32('10') FROM events

Fix JOIN conditions¶

Ensure JOIN conditions use compatible types:

SELECT * FROM users u 
JOIN events e ON u.id = toInt32(e.user_id_string)

Use proper type conversion functions¶

ClickHouse provides specific conversion functions:

SELECT * FROM events WHERE user_id = toInt32(user_id_string)

SELECT toString(user_id) as user_id_str FROM events

Common patterns and solutions¶

String vs Numeric comparisons¶

When comparing strings with numbers:

WHERE user_id = '123'

WHERE user_id = toInt32('123')

WHERE toString(user_id) = '123'

Date and DateTime operations¶

When working with dates and timestamps:

WHERE date_column = now()

WHERE toDate(date_column) = toDate(now())

Array operations¶

When working with arrays of different types:

SELECT [1, 2, 3] + ['a', 'b', 'c']

SELECT [toString(1), toString(2), toString(3)] + ['a', 'b', 'c']

Advanced solutions¶

Using CASE statements with mixed types¶

Convert all branches to a common type:

SELECT CASE 
    WHEN user_id = 123 THEN 'user_123'
    WHEN user_id = 456 THEN 'user_456'
    ELSE toString(user_id)
END as user_label FROM events

Working with JSON data¶

When extracting values from JSON:

SELECT 
    toInt32(JSONExtractRaw(data, 'user_id')) as user_id,
    toString(JSONExtractRaw(data, 'event_type')) as event_type
FROM events

Handling NULL values¶

Be careful with NULL values in type conversions:

SELECT toInt32OrZero(user_id_string) as user_id FROM events

Type conversion functions¶

Common ClickHouse type conversion functions:

• toInt8(), toInt16(), toInt32(), toInt64() - Convert to integers
• toUInt8(), toUInt16(), toUInt32(), toUInt64() - Convert to unsigned integers
• toFloat32(), toFloat64() - Convert to floating point
• toString() - Convert to string
• toDate(), toDateTime() - Convert to date/time types
• toUUID() - Convert to UUID

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Data Sources have inconsistent data types in the same column
• Pipes try to combine data from different sources with incompatible schemas
• Schema inference assigns wrong types to columns
• JSON data contains mixed types

To debug in Tinybird:

1. Check your Data Source schema for type consistency
2. Use Schema Hints to enforce proper types
3. Add explicit CAST operations in your Pipes
4. Validate incoming data types in Data Sources

See also¶

• ClickHouse Data Types
• Type Conversion Functions
• Common function errors