{"record":{"id":"fb1d4ffa4ab3aad5","repo":"prestodb/presto","slug":"type-mismatch-fb1d4f","errorCode":"TYPE_MISMATCH","errorMessage":"Function implementation type '%s' does not match declared return type '%s'","messagePattern":"Function implementation type '(.+?)' does not match declared return type '(.+?)'","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java","lineNumber":1187,"sourceCode":"                    .map(Entry::getKey)\n                    .collect(toImmutableList());\n            if (!duplicateParameters.isEmpty()) {\n                throw new SemanticException(DUPLICATE_PARAMETER_NAME, node, \"Duplicate function parameter name: %s\", Joiner.on(\", \").join(duplicateParameters));\n            }\n\n            // Check return type\n            Type returnType = functionAndTypeResolver.getType(parseTypeSignature(node.getReturnType()));\n            List<Field> fields = node.getParameters().stream()\n                    .map(parameter -> Field.newUnqualified(parameter.getName().getLocation(), parameter.getName().getValue(), functionAndTypeResolver.getType(parseTypeSignature(parameter.getType()))))\n                    .collect(toImmutableList());\n            Scope functionScope = Scope.builder()\n                    .withRelationType(RelationId.anonymous(), new RelationType(fields))\n                    .build();\n            if (node.getBody() instanceof Return) {\n                Expression returnExpression = ((Return) node.getBody()).getExpression();\n                Type bodyType = analyzeExpression(returnExpression, functionScope).getExpressionTypes().get(NodeRef.of(returnExpression));\n                if (!functionAndTypeResolver.canCoerce(bodyType, returnType)) {\n                    throw new SemanticException(TYPE_MISMATCH, node, \"Function implementation type '%s' does not match declared return type '%s'\", bodyType, returnType);\n                }\n\n                verifyNoAggregateWindowOrGroupingFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, returnExpression, \"CREATE FUNCTION body\");\n                verifyNoExternalFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, returnExpression, \"CREATE FUNCTION body\");\n\n                // TODO: Check body contains no SQL invoked functions\n            }\n\n            return createAndAssignScope(node, scope);\n        }\n\n        @Override\n        protected Scope visitAlterFunction(AlterFunction node, Optional<Scope> scope)\n        {\n            checkFunctionName(node, node.getFunctionName(), false);\n            return createAndAssignScope(node, scope);\n        }\n","sourceCodeStart":1169,"sourceCodeEnd":1205,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L1169-L1205","documentation":"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.","triggerScenarios":"CREATE FUNCTION ... RETURNS <T> RETURN <expr> where analyzeExpression(...).getExpressionTypes() yields bodyType for which functionAndTypeResolver.canCoerce(bodyType, returnType) is false.","commonSituations":"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.","solutions":["Cast the return expression to the declared type: RETURN CAST(expr AS <returnType>).","Change the declared RETURNS type to match the body's actual type.","Rewrite the body so it produces the expected type directly (e.g., use integer arithmetic instead of string concatenation)."],"exampleFix":"// before\nCREATE FUNCTION f(x VARCHAR) RETURNS BIGINT RETURN x;\n// after\nCREATE FUNCTION f(x VARCHAR) RETURNS BIGINT RETURN CAST(x AS BIGINT);","handlingStrategy":"validation","validationCode":"// Ensure the RETURN expression's type matches RETURNS before executing\nType bodyType = inferExpressionType(returnExpr);\nType declared = parseType(returnsClause);\nif (!typeResolver.canCoerce(bodyType, declared)) {\n    throw new IllegalArgumentException(\"Body type \" + bodyType + \" != declared \" + declared);\n}","typeGuard":null,"tryCatchPattern":"try {\n    execute(createFunctionSql);\n} catch (SemanticException e) {\n    if (e.getCode() == TYPE_MISMATCH) {\n        throw new IllegalStateException(\"Fix RETURNS clause or add an explicit CAST in the function body\", e);\n    }\n    throw e;\n}","preventionTips":["Always CAST the return expression to the declared return type explicitly.","Keep RETURNS and body types aligned in the function definition source of truth.","Test CREATE FUNCTION statements in CI against the same Presto version as production."],"tags":["presto","sql-functions","type-mismatch","semantic-analysis"],"backgroundTag":"type-mismatch","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}