SIZES_OF_ARRAYS_DONT_MATCH ClickHouse error¶

Common cause: You're trying to perform operations on arrays that have different lengths or sizes.

The SIZES_OF_ARRAYS_DONT_MATCH error occurs when you try to perform operations on arrays that have different sizes or lengths. This commonly happens when using array functions that require arrays of the same size, such as element-wise operations, array comparisons, or array concatenation.

What causes this error¶

This error typically happens when:

Different array lengths: Arrays have different numbers of elements
Element-wise operations: Using functions that require same-sized arrays
Array comparisons: Comparing arrays of different sizes
Array concatenation: Issues with array joining operations
Nested array operations: Working with nested arrays of different sizes
Array function parameters: Passing arrays of different sizes to functions

Example errors¶

-- Error: Arrays of different sizes
SELECT [1, 2, 3] + [1, 2];

-- Error: Array comparison with different sizes
SELECT [1, 2, 3] = [1, 2];

-- Error: Array concatenation with size mismatch
SELECT arrayConcat([1, 2], [3, 4, 5]);

-- Error: Element-wise operation on different sized arrays
SELECT arrayZip([1, 2, 3], [1, 2]);

-- Error: Array function with mismatched sizes
SELECT arrayJoin([1, 2, 3], [1, 2]);

How to fix it¶

1. Ensure arrays have the same size¶

Make sure arrays have matching lengths before operations:

-- ❌ Wrong: Arrays of different sizes
SELECT arrayMap(x, y -> x + y, [1, 2, 3], [1, 2]);

-- ✅ Correct: Arrays of same size
SELECT arrayMap(x, y -> x + y, [1, 2, 3], [1, 2, 3]);

-- ❌ Wrong: Different sized arrays
SELECT [1, 2, 3] = [1, 2];

-- ✅ Correct: Same sized arrays
SELECT [1, 2, 3] = [1, 2, 3];

2. Use array functions that handle size differences¶

Use functions that can handle arrays of different sizes:

-- Use arrayZip to handle different sizes
SELECT arrayZip([1, 2, 3], [1, 2]) AS zipped_array;

-- Use arrayConcat for joining arrays
SELECT arrayConcat([1, 2], [3, 4, 5]) AS combined_array;

-- Use arrayJoin for flattening
SELECT arrayJoin([1, 2, 3]) AS flattened_array;

-- Use arrayMap for element-wise operations
SELECT arrayMap(x, y -> x + y, [1, 2, 3], [1, 2, 3]) AS mapped_array;

3. Pad arrays to match sizes¶

Extend shorter arrays to match longer ones:

-- Pad shorter array with default values
SELECT arrayMap(x, y -> x + y,
    [1, 2, 3],
    arrayResize([1, 2], 3, 0)
) AS padded_result;

-- Use array functions to handle size differences
SELECT arrayZip(
    [1, 2, 3],
    arrayResize([1, 2], 3, NULL)
) AS zipped_with_padding;

4. Truncate arrays to match sizes¶

Cut longer arrays to match shorter ones:

-- Use arrayResize to truncate longer arrays
SELECT arrayResize([1, 2, 3, 4], 2) AS truncated_array;

-- Use arrayPushBack to extend shorter arrays
SELECT arrayPushBack([1, 2], 0) AS extended_array;

-- Truncate longer array to match shorter one
SELECT arrayMap(x, y -> x + y,
    arraySlice([1, 2, 3], 1, 2),
    [1, 2]
) AS truncated_result;

-- Use arraySlice to get matching sizes
SELECT arrayZip(
    arraySlice([1, 2, 3, 4], 1, 2),
    [1, 2]
) AS zipped_truncated;

Common patterns and solutions¶

Array element-wise operations¶

-- ❌ Wrong: Different sized arrays
SELECT arrayMap(x, y -> x + y, [1, 2, 3], [1, 2]);

-- ✅ Correct: Same sized arrays
SELECT arrayMap(x, y -> x + y, [1, 2, 3], [1, 2, 3]);

-- ✅ Correct: Handle size differences
SELECT arrayMap(x, y -> x + y,
    [1, 2, 3],
    arrayResize([1, 2], 3, 0)
);

Array comparisons¶

-- ❌ Wrong: Different sized arrays
SELECT [1, 2, 3] = [1, 2];

-- ✅ Correct: Same sized arrays
SELECT [1, 2, 3] = [1, 2, 3];

-- ✅ Correct: Compare truncated arrays
SELECT arraySlice([1, 2, 3], 1, 2) = [1, 2];

Array concatenation¶

-- ❌ Wrong: Size mismatch in concatenation
SELECT arrayConcat([1, 2], [3, 4, 5]);

-- ✅ Correct: Valid concatenation
SELECT arrayConcat([1, 2], [3, 4]) AS combined;

-- ✅ Correct: Handle different sizes
SELECT arrayConcat(
    [1, 2],
    arraySlice([3, 4, 5], 1, 2)
) AS controlled_concat;

Advanced solutions¶

Dynamic array size handling¶

-- Handle arrays of different sizes dynamically
SELECT
    CASE
        WHEN length(array1) = length(array2) THEN arrayMap(x, y -> x + y, array1, array2)
        WHEN length(array1) > length(array2) THEN arrayMap(x, y -> x + y, array1, arrayResize(array2, length(array1), 0))
        ELSE arrayMap(x, y -> x + y, arrayResize(array1, length(array2), 0), array2)
    END AS result
FROM (
    SELECT [1, 2, 3] AS array1, [1, 2] AS array2
);

Safe array operations¶

-- Safe array operations with size validation
SELECT
    CASE
        WHEN length(array1) = length(array2) THEN arrayMap(x, y -> x + y, array1, array2)
        ELSE NULL
    END AS safe_result
FROM (
    SELECT [1, 2, 3] AS array1, [1, 2] AS array2
);

Array size normalization¶

-- Normalize array sizes for operations
SELECT
    arrayMap(x, y -> x + y,
        array1,
        arrayResize(array2, length(array1), 0)
    ) AS normalized_result
FROM (
    SELECT [1, 2, 3, 4] AS array1, [1, 2] AS array2
);

Tinybird-specific notes¶

In Tinybird, this error commonly occurs when:

Data source processing: Working with array columns of different sizes
Pipe transformations: Array operations in pipe SQL
JSON data processing: Extracting arrays from JSON with different sizes
String processing: String array operations with size mismatches

Debugging in Tinybird¶

Check array column sizes: Verify array lengths in data source queries
Review pipe SQL: Ensure array operations handle size differences
Test with tb sql: Use CLI to test array operations
Validate array processing: Check array size handling in transformations
Use Query Builder: Test array operations interactively

Common Tinybird scenarios¶

-- In data source definitions, handle array size differences
SCHEMA >
    `timestamp` DateTime,
    `user_id` UInt32,
    `tags` Array(String),
    `scores` Array(Float64)

-- ❌ Wrong: Arrays of different sizes in pipe
NODE processed_data
SQL >
    SELECT
        user_id,
        arrayMap(tag, score -> tag + toString(score), tags, scores)
    FROM your_data_source;

-- ✅ Correct: Handle size differences in pipe
NODE processed_data
SQL >
    SELECT
        user_id,
        CASE
            WHEN length(tags) = length(scores) THEN arrayMap(tag, score -> tag + toString(score), tags, scores)
            ELSE NULL
        END AS processed_data
    FROM your_data_source;

Array data processing¶

-- In pipe definitions, handle array size safely
NODE array_processing
SQL >
    SELECT
        user_id,
        arrayMap(tag, score -> tag + toString(score),
            tags,
            arrayResize(scores, length(tags), 0.0)
        ) AS processed_tags
    FROM your_data_source
    WHERE length(tags) > 0;

Best practices¶

Always check array sizes before performing operations
Use array functions that handle size differences gracefully
Implement size validation in your array operations
Use padding or truncation to handle size mismatches
Test array operations with different size combinations
Document array size requirements for your team
Use Query Builder to test array operations interactively
Handle edge cases like empty arrays or null values

Data types

Table engines

Functions

ClickHouse errors

Function-specific issues

Performance issues

Schema and typing

Data ingestion

SIZES_OF_ARRAYS_DONT_MATCH ClickHouse error¶

What causes this error¶

Example errors¶

How to fix it¶

1. Ensure arrays have the same size¶

2. Use array functions that handle size differences¶

3. Pad arrays to match sizes¶

4. Truncate arrays to match sizes¶

Common patterns and solutions¶

Array element-wise operations¶

Array comparisons¶

Array concatenation¶

Advanced solutions¶

Dynamic array size handling¶

Safe array operations¶

Array size normalization¶

Tinybird-specific notes¶

Debugging in Tinybird¶

Common Tinybird scenarios¶

Array data processing¶

Best practices¶

See also¶