acbadz acbadz - 1 month ago 11
MySQL Question

MySQL user-defined function returns incorrect value when used in a SELECT statement

I met a problem when calling a user-defined function in MySQL. The computation is very simple but can't grasp where it went wrong and why it went wrong. Here's the thing.

So I created this function:

DELIMITER //
CREATE FUNCTION fn_computeLoanAmortization (_empId INT, _typeId INT)
RETURNS DECIMAL(17, 2)

BEGIN
SET @loanDeduction = 0.00;

SELECT TotalAmount, PeriodicDeduction, TotalInstallments, DeductionFlag
INTO @totalAmount, @periodicDeduction, @totalInstallments, @deductionFlag
FROM loans_table
WHERE TypeId = _typeId AND EmpId = _empId;

IF (@deductionFlag = 1) THEN
SET @remaining = @totalAmount - @totalInstallments;

IF(@remaining < @periodicDeduction) THEN
SET @loanDeduction = @remaining;
ELSE
SET @loanDeduction = @periodicDeduction;
END IF;
END IF;

RETURN @loanDeduction;
END;//
DELIMITER ;


If I call it like this, it works fine:

SELECT fn_computeLoanAmortization(3, 4)


But if I call it inside a SELECT statement, the result becomes erroneous:

SELECT Id, fn_computeLoanAmortization(Id, 4) AS Amort FROM emp_table


There's only one entry in the loans_table and the above statement should only result with one row having value in the Amort column but there are lots of random rows with the same Amort value as the one with the matching entry, which should not be the case.

Have anyone met this kind of weird dilemma? Or I might have done something wrong from my end. Kindly enlighten me.

Thank you very much.

EDIT:
By erroneous, I meant it like this:

loans_table has one record
EmpId = 1
TypeId = 2
PeriodicDeduction = 100
TotalAmount = 1000
TotalInstallments = 200
DeductionFlag = 1

emp_table has several rows
EmpId = 1
Name = Paolo

EmpId = 2
Name = Nikko

...

EmpId = 5
Name = Ariel


when I query the following statements, I get the correct value:

SELECT fn_computeLoanAmortization(1, 2)

SELECT Id, fn_computeLoanAmortization(Id, 2) AS Amort FROM emp_table WHERE EmpId = 1


But when I query this statement, I get incorrect values:

SELECT Id, fn_computeLoanAmortization(Id, 2) AS Amort FROM emp_table


Resultset would be:

EmpId | Amort
--------------------
1 | 100
2 | 100 (this should be 0, but the query returns 100)
3 | 100 (same error here)
...
5 | 100 (same error here up to the last record)

Answer

Inside your function, the variables you use to retrieve the values from the loans_table table are not local variables local to the function but session variables. When the select inside the function does not find any row, those variables still have the same values as from the previous execution of the function.

Use real local variables instead. In order to do that, use the variables names without @ as a prefix and declare the variables at the beginning of the function. See this answer for more details.