#1224 Server fails with ClassCastException

[asamal]

Create custom function with declared result NUMERIC, but actual result is TABLE. When function is called, server fails.
Example:
CREATE FUNCTION GET_RATE(RATE_DATE DATE, CURR_FROM BIGINT, CURR_TO BIGINT) RETURNS NUMERIC(19, 6)
READS SQL DATA
BEGIN ATOMIC
RETURN (SELECT TOP 1 RATE.RATE, RATE.RATE_DATE
FROM RATE
WHERE RATE.RATE_DATE <= RATE_DATE AND RATE.CURR_FROM = CURR_FROM AND RATE.CURR_TO = CURR_TO
ORDER BY RATE.RATE_DATE DESC);
END;

[Fred Toussi]

Thanks for reporting.

Your routine has some problems.

You need to use different names for the parameters, for example (P_RATE_DATE DATE, P_CURR_FROM BIGINT, P_CURR_TO BIGINT), otherwise the query will not return the intended result.

The SELECT must return a single column, not two columns.

You also need to assign the result of the SELECT to a local variable, then return this variable. For example:

BEGIN ATOMIC
DECLARE V_TEMP NUMERIC(19,6);
SET V_TEMP = (SELECT OP 1 RATE.RATE FROM RATE WHERE RATE.RATE_DATE <= P_RATE_DATE ... );
RETURN V_TEMP;
END;

[asamal]

Thank you for your answer. Your advice is very useful!