prestodb/presto · error · PrestoException

INVALID_FUNCTION_ARGUMENT

INVALID_FUNCTION_ARGUMENT

Error message

SQL array indices start at 1

What it means

element_at / array indexing is 1-based per the SQL standard. checkedIndexToBlockPosition validates the user index before mapping it to a 0-based block position and rejects index 0 outright with INVALID_FUNCTION_ARGUMENT 'SQL array indices start at 1'. Indices beyond the array length return -1 (caller yields NULL) rather than throwing.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/ArrayElementAtFunction.java:122

        int position = checkedIndexToBlockPosition(array, index);
        if (position == -1) {
            return null;
        }
        if (array.isNull(position)) {
            return null;
        }

        return (Block) elementType.getObject(array, position);
    }

    /**
     * @return PrestoException if the index is 0, -1 if the index is out of range (to tell the calling function to return null), and the element position otherwise.
     */
    private static int checkedIndexToBlockPosition(Block block, long index)
    {
        int arrayLength = block.getPositionCount();
        if (index == 0) {
            throw new PrestoException(INVALID_FUNCTION_ARGUMENT, "SQL array indices start at 1");
        }
        if (Math.abs(index) > arrayLength) {
            return -1; // -1 indicates that the element is out of range and "ELEMENT_AT" should return null
        }
        if (index > 0) {
            return toIntExact(index - 1);
        }
        else {
            return toIntExact(arrayLength + index);
        }
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

1. Use 1-based indices: element_at(arr, 1) for the first element.
2. Clamp or shift computed indices: greatest(index, 1).
3. If 0-based semantics are needed, add 1 before calling: element_at(arr, zero_based + 1).
4. Guard NULL/zero inputs with COALESCE or CASE before indexing.

Example fix

// before
element_at(a, idx) -- idx can be 0
// after
element_at(a, greatest(idx, 1)) -- or CASE WHEN idx = 0 THEN NULL ...

-- reject zero before indexing
SELECT CASE WHEN idx = 0 THEN NULL ELSE element_at(arr, idx) END

try { element_at(arr, idx); } catch (PrestoException e) { /* INVALID_FUNCTION_ARGUMENT: idx was 0 */ }

When it happens

Trigger: element_at(arr, 0) — passing a literal or computed index of exactly 0.

Common situations: Porting 0-based indexing habits from Python/JS/Java; computing an index arithmetically (e.g. i-1 in a loop) that can hit 0; off-by-one in generated SQL.

Related errors

• INVALID_FUNCTION_ARGUMENT
• INVALID_FUNCTION_ARGUMENT
• INVALID_FUNCTION_ARGUMENT
• NOT_SUPPORTED
• INVALID_FUNCTION_ARGUMENT

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/4c848dae7c8acacd. Report an issue: GitHub.