prestodb/presto · error · PrestoException

INVALID_FUNCTION_ARGUMENT

INVALID_FUNCTION_ARGUMENT

Error message

SQL array indices start at 1

What it means

ArrayFindFirstIndexWithOffsetFunction shares the same helper: checkedIndexToBlockPosition converts a user-supplied start position (1-based, negatives count from the end) to a 0-based block offset. Index 0 is invalid under SQL semantics and throws INVALID_FUNCTION_ARGUMENT 'SQL array indices start at 1'; out-of-range magnitudes return -1 so startPosition yields no match.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/ArrayFindFirstIndexWithOffsetFunction.java:230

            if (!arrayBlock.isNull(i)) {
                element = elementType.getBoolean(arrayBlock, i);
            }
            Boolean match = function.apply(element);
            if (TRUE.equals(match)) {
                return Long.valueOf(i + 1);
            }
        }
        return null;
    }

    /**
     * @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
        }
        index = index > 0 ? index - 1 : arrayLength + index;
        return toIntExact(index);
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

1. Pass a start position >= 1 (or negative to search from the end).
2. Clamp: greatest(start_offset, 1) before calling.
3. Handle the zero case explicitly with CASE to return NULL/no-match.
4. Audit upstream expressions producing the offset for 0-based origins.

Example fix

// before
find_first_with_offset(a, pred, off) -- off may be 0
// after
find_first_with_offset(a, pred, CASE WHEN off = 0 THEN 1 ELSE off END)

SELECT CASE WHEN off = 0 THEN NULL ELSE find_first_index_with_offset(arr, pred, off) END

When it happens

Trigger: Invoking the offset variant of array find-first (e.g. array_find_first_index_with_offset style function) with start position 0.

Common situations: Computing a start offset from another query result that is 0-based or NULL-coalesced to 0; loops that decrement the offset to 0; mixing element_at (1-based) conventions across functions.

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/f106bf40825a95eb. Report an issue: GitHub.