If you’re getting an “Out of Range Error” when using the generate_subscripts() function in DuckDB, it could be that you’re specifying a non-existent dimension for the array.

DuckDB’s generate_subscripts() function accepts the array as the first argument, and the dimension as the second argument. The second argument must correspond to an actual dimension present in the array.

To fix this issue, be sure to specify a dimension that actually exists in the array.

Example of Error

Here’s an example of code that produces the error:

SELECT generate_subscripts(
    array_value('Sun', 'Sky', 'Sea'), 
    2
    );

Result:

Out of Range Error: array_length dimension '2' out of range (min: '1', max: '1')

In this case I passed a one-dimensional array, but I specified a dimension of 2, which resulted in the error.

I get the same error if I pass a dimension of zero:

SELECT generate_subscripts(
    array_value('Sun', 'Sky', 'Sea'), 
    0
    );

Result:

Out of Range Error: array_length dimension '0' out of range (min: '1', max: '1')

Here’s a two-dimensional array:

SELECT generate_subscripts(
    array_value(
        array_value('Sun', 'Sky', 'Sea'),
        array_value('Tag', 'Rub', 'Tub')
        ), 
    3
    ) AS result;

Result:

Out of Range Error: array_length dimension '3' out of range (min: '1', max: '2')

This time I specified the third dimension, but the array only has two-dimensions.

Solution

To fix this issue, we need to specify a dimension that actually exists:

SELECT generate_subscripts(
    array_value('Sun', 'Sky', 'Sea'), 
    1
    );

Result:

+----------------------------------------------------------+
| generate_subscripts(array_value('Sun', 'Sky', 'Sea'), 1) |
+----------------------------------------------------------+
| 1                                                        |
| 2                                                        |
| 3                                                        |
+----------------------------------------------------------+

The same goes for the two-dimensional array:

SELECT generate_subscripts(
    array_value(
        array_value('Sun', 'Sky', 'Sea'),
        array_value('Tag', 'Rub', 'Tub')
        ), 
    2
    ) AS second_dimension;

Result:

+------------------+
| second_dimension |
+------------------+
| 1                |
| 2                |
| 3                |
+------------------+