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

  1. Check array column sizes: Verify array lengths in data source queries
  2. Review pipe SQL: Ensure array operations handle size differences
  3. Test with tb sql: Use CLI to test array operations
  4. Validate array processing: Check array size handling in transformations
  5. 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

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

See also