ClickHouse DB::Exception: Argument out of bound

The "DB::Exception: Argument out of bound" error in ClickHouse indicates that a value passed to a function falls outside its acceptable range. While the argument type may be correct, its value is too large, too small, negative when only positive is allowed, or otherwise invalid for that specific function. The error code is ARGUMENT_OUT_OF_BOUND.

Impact

The query fails at execution time (or sometimes at analysis time for constant expressions). This error prevents results from being returned. If it occurs inside a materialized view, data ingestion to the source table can stall.

Common Causes

1. Negative or zero values where positive is required -- functions like toFixedString(s, N) require N to be a positive integer.
2. Exceeding maximum allowed values -- for example, requesting more decimal places than ClickHouse supports in toDecimal128(value, scale).
3. Invalid date or time components -- passing month 13 or day 32 to date construction functions.
4. Array or tuple index beyond size -- accessing an element beyond the length of an array or tuple.
5. Overflow in type conversion -- converting a value that exceeds the target type's range, like casting 300 to UInt8 (max 255).
6. Invalid precision or scale parameters -- specifying a scale larger than precision in Decimal types.

Troubleshooting and Resolution Steps

1. Read the full error message. It typically includes the function name and the offending value:

   DB::Exception: Argument out of bound: scale must be between 0 and 38 for Decimal128
   

2. Check the allowed range for the function. Consult the ClickHouse documentation for the specific function to find min/max constraints on each argument.

3. Validate input data before passing it to functions. Use conditional logic to clamp or filter out-of-range values:

   SELECT toFixedString(name, least(length(name), 100))
   FROM users;
   

4. Handle date/time boundaries explicitly:

   -- Validate month is in range before constructing a date
   SELECT if(month BETWEEN 1 AND 12,
              makeDate(year, month, day),
              NULL) AS safe_date
   FROM raw_data;
   

5. Use appropriate data types. If values regularly exceed a type's range, use a wider type:

   -- Instead of UInt8 (0-255), use UInt16 or UInt32
   ALTER TABLE my_table MODIFY COLUMN value UInt16;
   

6. Check array access patterns. Ensure you are not accessing beyond array bounds:

   -- Safe array access
   SELECT if(length(arr) >= 3, arr[3], NULL) FROM my_table;
   

Best Practices

• Validate data at ingestion time to ensure values are within expected ranges before they reach ClickHouse.
• Use CAST with explicit type checking rather than implicit conversions that might overflow silently.
• When working with Decimal types, carefully plan precision and scale parameters at table creation time.
• Add range checks in application code before constructing queries with user-supplied numeric arguments.
• Prefer functions like toDateOrNull and toUInt32OrNull that return NULL instead of throwing errors on invalid input.

Frequently Asked Questions

Q: How can I make my query continue even if some rows have out-of-bound values?
A: Use the OrNull or OrZero variants of conversion functions (e.g., toUInt8OrNull, toDateOrZero). These return NULL or zero instead of throwing an error. You can also wrap expressions in if() to validate ranges beforehand.

Q: What is the maximum scale for Decimal types in ClickHouse?
A: Decimal32 supports scale 0-9, Decimal64 supports 0-18, and Decimal128 supports 0-38. Decimal256 supports 0-76. Specifying a scale outside these bounds triggers ARGUMENT_OUT_OF_BOUND.

Q: Does this error apply to the number argument in functions like toFixedString?
A: Yes. toFixedString(s, N) requires N to be a positive integer within reasonable limits. Passing 0 or a negative number triggers this error.

Q: Can I catch this error and provide a default value in SQL?
A: Not directly with try-catch syntax, but you can prevent it by validating inputs. Use if() or multiIf() to check values before passing them to functions that might reject them.