prestodb/presto · error · PrestoException

INVALID_FUNCTION_ARGUMENT

INVALID_FUNCTION_ARGUMENT

Error message

SQL array indices start at 1

What it means

ArrayFindFirstWithOffsetFunction uses an identical checkedIndexToBlockPosition helper for its start-position argument. A start index of 0 violates 1-based SQL array indexing and throws INVALID_FUNCTION_ARGUMENT 'SQL array indices start at 1'; absolute values exceeding the array length return -1 (no match found) instead of erroring.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/ArrayFindFirstWithOffsetFunction.java:240

            Boolean match = function.apply(element);
            if (TRUE.equals(match)) {
                if (element == null) {
                    checkCondition(false, INVALID_FUNCTION_ARGUMENT, "FIND_FIRST finds NULL as match, which is not supported.");
                }
                return element;
            }
        }
        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. Convert to 1-based before calling: passed_offset + 1 for 0-based inputs.
2. Clamp with greatest(offset, 1).
3. Short-circuit in application code when offset is 0 and treat it as 'start at beginning'.
4. Validate bound parameters in the driver/app layer before issuing the query.

Example fix

// before
find_first(a, pred, resume_offset) -- resume_offset is 0-based
// after
find_first(a, pred, resume_offset + 1)

SELECT CASE WHEN start_pos = 0 THEN NULL ELSE find_first(arr, pred, start_pos) END

When it happens

Trigger: Calling the array find-first-with-offset function with startPosition = 0 — often from a computed or bound parameter that evaluates to 0.

Common situations: Applications keeping 0-based offsets from client code (JS/Java arrays) and passing them unconverted; streaming/resume logic that starts at position 0 of a page of array data.

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