ClickHouse DB::Exception: Array or tuple index is zero

The "DB::Exception: Array or tuple index is zero" error in ClickHouse occurs when you try to access an array or tuple element using index 0. Unlike many programming languages that use 0-based indexing, ClickHouse arrays and tuples are 1-based. The first element is at index 1, and index 0 is invalid. The error code is ZERO_ARRAY_OR_TUPLE_INDEX.

Impact

The query fails at execution time when the zero index is encountered. If the zero index comes from a computed expression or column value, the error may only affect certain rows, but the entire query will still fail.

Common Causes

1. Assuming 0-based indexing -- developers coming from Python, JavaScript, Java, or C-family languages naturally write arr[0] for the first element.
2. Computed index evaluating to zero -- an expression like arr[position - 1] produces 0 when position is 1.
3. Using indexOf() result directly -- indexOf(arr, value) returns 0 when the value is not found, and using that result as an index triggers this error.
4. Off-by-one errors in loop-like logic -- generating indices with range() (which starts at 0) and using them directly as array indices.

Troubleshooting and Resolution Steps

1. Switch from 0-based to 1-based indexing. The first element is at index 1:

   -- Wrong: 0-based indexing
   SELECT arr[0] FROM my_table;
   
   -- Correct: 1-based indexing
   SELECT arr[1] FROM my_table;
   

2. Handle indexOf() returning 0. When indexOf() does not find the value, it returns 0. Guard against this:

   -- Unsafe: indexOf returns 0 if not found
   SELECT arr[indexOf(arr, 'target')] FROM my_table;
   
   -- Safe: check first
   SELECT if(indexOf(arr, 'target') > 0,
              arr[indexOf(arr, 'target')],
              NULL) AS result
   FROM my_table;
   

   Or more efficiently with arrayFirst:

   SELECT arrayFirst(x -> x = 'target', arr) FROM my_table;
   

3. Adjust range-based indices. If generating indices with range(), add 1:

   -- range(0, 3) produces [0, 1, 2]
   -- For array access, add 1
   SELECT arrayMap(i -> arr[i + 1], range(length(arr))) FROM my_table;
   

4. Use arrayElement with safe defaults. The arrayElement function behaves the same as bracket access, but you can wrap it:

   SELECT if(idx > 0 AND idx <= length(arr), arr[idx], NULL)
   FROM my_table;
   

5. Check computed indices before use. When the index comes from a calculation:

   SELECT if(computed_idx >= 1 AND computed_idx <= length(arr),
              arr[computed_idx],
              'default_value') AS safe_result
   FROM my_table;
   

Best Practices

• Always remember ClickHouse is 1-based for array and tuple access. This is different from most programming languages.
• When using indexOf(), always check if the result is greater than 0 before using it as an index.
• Prefer high-level array functions (arrayFirst, arrayFilter, arrayExists) over manual indexing when possible -- they are both safer and more readable.
• Add comments in your SQL when 1-based indexing might confuse other developers on the team.
• Use negative indices to access elements from the end: arr[-1] gives the last element, arr[-2] the second-to-last.

Frequently Asked Questions

Q: Does ClickHouse support negative array indices?
A: Yes. Negative indices count from the end of the array. arr[-1] is the last element, arr[-2] is the second to last, and so on. However, arr[0] is still invalid -- there is no element between the last negative and the first positive index.

Q: What happens if I access an index beyond the array length?
A: ClickHouse returns a default value (0 for numbers, empty string for strings, etc.) when accessing beyond the array's length. Only index 0 triggers an error.

Q: Does this apply to Map type access as well?
A: No. Map access uses keys, not numeric indices, so the zero-index restriction does not apply. map_column['key'] uses the key directly.

Q: How do tuples handle 1-based indexing?
A: Tuples follow the same 1-based convention. tuple.1 accesses the first element. Using tuple.0 or tupleElement(tuple, 0) triggers this error.