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
– NineBerry, Commented Oct 21, 2016 at 1:33
How about running: SELECT Id, fn_computeLoanAmortization(Id, 4) AS Amort FROM my_table WHERE Id = 3; ? — arkki
– arkki, Commented Oct 21, 2016 at 1:35
@NineBerry - it's an employee table.. it's just a basic table with no joins or whatnot. it's not edited. thanks. — zbads
– zbads, Commented Oct 21, 2016 at 1: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
– zbads, Commented Oct 21, 2016 at 1:59

Community · Accepted Answer · 2017-05-23 11:48:32Z

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, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 21, 2016 at 2:10

NineBerry

28.8k4 gold badges68 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zbads Over a year ago

Thank you. Will try this, will provide feedback after.

zbads Over a year ago

This works for me. It's a new learning for me too. Thank you very much.

Gordon Linoff · Accepted Answer · 2016-10-21 02:25:36Z

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, 2016 at 2:25

answered Oct 21, 2016 at 2:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

NineBerry Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related