prestodb/presto · error · SemanticException

TYPE_MISMATCH

TYPE_MISMATCH

Error message

Function implementation type '%s' does not match declared return type '%s'

What it means

For CREATE FUNCTION with a RETURN <expression> body, Presto analyzes the expression and checks it can be coerced to the declared return type. When the body's inferred type is not coercible to the declared RETURNS type (e.g., returning VARCHAR from a function declared RETURNS BIGINT), the analyzer raises TYPE_MISMATCH showing both types.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:1187

                    .map(Entry::getKey)
                    .collect(toImmutableList());
            if (!duplicateParameters.isEmpty()) {
                throw new SemanticException(DUPLICATE_PARAMETER_NAME, node, "Duplicate function parameter name: %s", Joiner.on(", ").join(duplicateParameters));
            }

            // Check return type
            Type returnType = functionAndTypeResolver.getType(parseTypeSignature(node.getReturnType()));
            List<Field> fields = node.getParameters().stream()
                    .map(parameter -> Field.newUnqualified(parameter.getName().getLocation(), parameter.getName().getValue(), functionAndTypeResolver.getType(parseTypeSignature(parameter.getType()))))
                    .collect(toImmutableList());
            Scope functionScope = Scope.builder()
                    .withRelationType(RelationId.anonymous(), new RelationType(fields))
                    .build();
            if (node.getBody() instanceof Return) {
                Expression returnExpression = ((Return) node.getBody()).getExpression();
                Type bodyType = analyzeExpression(returnExpression, functionScope).getExpressionTypes().get(NodeRef.of(returnExpression));
                if (!functionAndTypeResolver.canCoerce(bodyType, returnType)) {
                    throw new SemanticException(TYPE_MISMATCH, node, "Function implementation type '%s' does not match declared return type '%s'", bodyType, returnType);
                }

                verifyNoAggregateWindowOrGroupingFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, returnExpression, "CREATE FUNCTION body");
                verifyNoExternalFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, returnExpression, "CREATE FUNCTION body");

                // TODO: Check body contains no SQL invoked functions
            }

            return createAndAssignScope(node, scope);
        }

        @Override
        protected Scope visitAlterFunction(AlterFunction node, Optional<Scope> scope)
        {
            checkFunctionName(node, node.getFunctionName(), false);
            return createAndAssignScope(node, scope);
        }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Cast the return expression to the declared type: RETURN CAST(expr AS <returnType>).
2. Change the declared RETURNS type to match the body's actual type.
3. Rewrite the body so it produces the expected type directly (e.g., use integer arithmetic instead of string concatenation).

Example fix

// before
CREATE FUNCTION f(x VARCHAR) RETURNS BIGINT RETURN x;
// after
CREATE FUNCTION f(x VARCHAR) RETURNS BIGINT RETURN CAST(x AS BIGINT);

// Ensure the RETURN expression's type matches RETURNS before executing
Type bodyType = inferExpressionType(returnExpr);
Type declared = parseType(returnsClause);
if (!typeResolver.canCoerce(bodyType, declared)) {
    throw new IllegalArgumentException("Body type " + bodyType + " != declared " + declared);
}

try {
    execute(createFunctionSql);
} catch (SemanticException e) {
    if (e.getCode() == TYPE_MISMATCH) {
        throw new IllegalStateException("Fix RETURNS clause or add an explicit CAST in the function body", e);
    }
    throw e;
}

When it happens

Trigger: CREATE FUNCTION ... RETURNS <T> RETURN <expr> where analyzeExpression(...).getExpressionTypes() yields bodyType for which functionAndTypeResolver.canCoerce(bodyType, returnType) is false.

Common situations: Declaring RETURNS INTEGER but returning a string literal or JSON; returning DOUBLE where BIGINT was declared without a cast; comparing two similarly named types (INTEGER vs BIGINT) that Presto will not implicitly coerce.

Related errors

• DUPLICATE_PARAMETER_NAME
• Invalid array block:
• Invalid map block:
• Invalid row block:
• Expected AbstractSingleRowBlock

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