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

Question

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)

You don't show us the real query. In the question you write my_table as the table name. I suppose, your real query has a join or is executed on a different table than loans_ table — NineBerry, Oct 21 '16 at 01:33
How about running: SELECT Id, fn_computeLoanAmortization(Id, 4) AS Amort FROM my_table WHERE Id = 3; ? — arkki, Oct 21 '16 at 01:35
@NineBerry - it's an employee table.. it's just a basic table with no joins or whatnot. it's not edited. thanks. — zbads, Oct 21 '16 at 01:54
@arkki - I get the correct value when I single out the Id, but when I use it in groups (like using IN in the query), it displays incorrect data.. (e.g. value should be 0 because it doesn't have a match in loans_table but it has a value of 1000 which came out of nowhere) — zbads, Oct 21 '16 at 01:59
@GordonLinoff - I updated my post. I hope that'd make it clearer. Thanks. — zbads, Oct 21 '16 at 02:08

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

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.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 21 '16 at 02:10

NineBerry

20,173
3
50
78

Thank you. Will try this, will provide feedback after. – zbads Oct 21 '16 at 02:12
This works for me. It's a new learning for me too. Thank you very much. – zbads Oct 21 '16 at 02:36

Gordon Linoff · Answer 2 · 2016-10-21T02:25:36.160

1

I suspect the problem is that the variables in the INTO are not re-set when there is no matching row.

Just set them before the INTO:

BEGIN
    SET @loanDeduction = 0.00;
    SET @totalAmount = 0;
    SET @periodicDeduction = 0;
    SET @totalInstallments = 0;
    SET @deductionFlag = 0;

    SELECT TotalAmount, PeriodicDeduction, TotalInstallments, DeductionFlag
    . . .

You might just want to set them to NULL.

Or, switch your logic to use local variables:

    SET v_loanDeduction = 0.00;
    SET v_totalAmount = 0;
    SET v_periodicDeduction = 0;
    SET v_totalInstallments = 0;
    SET v_deductionFlag = 0;

And so on.

edited Oct 21 '16 at 02:25

answered Oct 21 '16 at 02:12

Gordon Linoff

1,122,135
50
484
624

This will also solve this specific problem, but using session variables instead of local variables can have other unexpected side effects when using them with the same name in multiple functions or when using parallel execution of statements. – NineBerry Oct 21 '16 at 02:23

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

2 Answers2