📋

Description

This error indicates that a user-defined stored function in MariaDB executed to its end without encountering a `RETURN` statement. Stored functions are required to return a value, and this error signifies that the function's logic failed to provide one, leading to an incomplete execution.

💬

Error Message

FUNCTION %s ended without RETURN

🔍

Known Causes

3 known causes

⚠️

Missing RETURN Statement

The stored function's code path does not include a `RETURN` statement before the `END` keyword, which is mandatory for functions.

⚠️

Conditional Return Not Met

The `RETURN` statement is placed within an `IF` or `CASE` block that, under certain conditions, is not executed, causing the function to complete without returning a value.

⚠️

Unreachable RETURN Logic

Although a `RETURN` statement might exist, the function's execution flow never reaches it due to prior `LEAVE` or `SIGNAL` statements, or faulty conditional logic.

🛠️

Solutions

3 solutions available

1. Add a RETURN Statement to the Function easy

Ensure every execution path within your stored function explicitly returns a value.

1

Identify the stored function causing the error. The error message `FUNCTION %s ended without RETURN` will typically include the function name in place of `%s`.

2

Examine the function's logic. Look for conditional blocks (IF, CASE) or loops (WHILE, REPEAT) where a RETURN statement might be missing or only present in certain branches.

3

Add a `RETURN` statement at the end of the function's body. This is crucial for paths that might not otherwise reach a return. If the function can legitimately have no return value in some scenarios, consider changing its return type to allow NULL or throwing an error.

ALTER FUNCTION your_function_name RETURNS INT DETERMINISTIC BEGIN
    -- Function logic...
    IF some_condition THEN
        RETURN some_value;
    ELSE
        -- Ensure a return for all paths
        RETURN 0; -- Or an appropriate default/error value
    END IF;
END;

4

Recompile or re-create the function with the added RETURN statement.

DELIMITER //
CREATE OR REPLACE FUNCTION your_function_name(...) RETURNS ... DETERMINISTIC ...
BEGIN
    -- Function logic with guaranteed RETURN
END //
DELIMITER ;

2. Handle All Conditional Paths medium

Review and explicitly manage return values for all possible execution branches within the function.

1

Locate the stored function that is throwing error 1321.

2

Thoroughly analyze all `IF`, `CASE`, `WHILE`, and `REPEAT` statements within the function.

3

For each conditional block, ensure that a `RETURN` statement is present in *every* possible outcome. This includes the `ELSE` part of `IF` statements and the default case of `CASE` statements.

DELIMITER //
CREATE OR REPLACE FUNCTION calculate_discount(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2);
    IF amount > 100 THEN
        SET discount = amount * 0.10;
    ELSEIF amount > 50 THEN
        SET discount = amount * 0.05;
    ELSE
        SET discount = 0.00; -- Explicit return for the ELSE path
    END IF;
    RETURN discount;
END //
DELIMITER ;

4

If a path logically should not return a value (which is rare for functions), consider throwing an error or returning a specific indicator value (like NULL if the return type allows).

DELIMITER //
CREATE OR REPLACE FUNCTION get_status(id INT) RETURNS VARCHAR(50) DETERMINISTIC
BEGIN
    DECLARE status VARCHAR(50);
    SELECT column_status INTO status FROM your_table WHERE column_id = id;
    IF status IS NULL THEN
        -- Handle cases where no status is found, perhaps by returning an error message or a default
        RETURN 'NOT_FOUND';
    END IF;
    RETURN status;
END //
DELIMITER ;

3. Review Function Definition and Return Type medium

Verify that the function's declared return type aligns with all possible return values and that the function definition is complete.

1

Fetch the `CREATE FUNCTION` statement for the problematic function using `SHOW CREATE FUNCTION your_function_name;`.

SHOW CREATE FUNCTION your_function_name;

2

Carefully examine the declared `RETURNS` type. Ensure it can accommodate all values you intend to return from the function.

3

Look for any `RETURN` statements that might be attempting to return a value of a type incompatible with the declared `RETURNS` type. MariaDB might not always flag this as a type mismatch error but could lead to the missing return error if the type conversion is problematic.

4

Ensure the function body is syntactically correct and that the `BEGIN...END` block encloses all logic. An incomplete function definition can sometimes manifest as this error.

DELIMITER //
CREATE OR REPLACE FUNCTION safe_division(numerator INT, denominator INT) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    IF denominator = 0 THEN
        -- Return NULL or a specific error indicator if division by zero is a possibility
        RETURN NULL;
    ELSE
        RETURN numerator / denominator;
    END IF;
END //
DELIMITER ;

🔗

Related Errors

5 related errors

MariaDB Error 1321: Function Missing Return