mysql function variable set not working

Question

I am writing a function that should return a floating value.

BEGIN


DECLARE due_amount DECIMAL(9,2);
  SET due_amount = (SELECT due_amount FROM (
                SELECT id,  MAX(date), due_amount, user_id
                FROM lunch_transaction
                GROUP BY user_id
                HAVING user_id = user) l);
  IF due_amount IS NULL THEN
    SET due_amount = 0.00;
  END IF;
  RETURN due_amount;
END

The function only returns value 0.00 even though the value should be something else.

Running only this query :

(SELECT due_amount FROM (
            SELECT id,  MAX(date), due_amount, user_id
            FROM lunch_transaction
            GROUP BY user_id
            HAVING user_id = user) l);

is giving the correct output though.

How should I set the query's output to the variable?

Answer 1

It is really bad practice to use variable name that can conflict with column names. Also, the subquery seems very unnecessary. I would try something more like this:

BEGIN
    DECLARE v_due_amount DECIMAL(9,2);

    SELECT v_due_amount := l.due_amount
    FROM lunch_transaction l
    WHERE l.user_id = in_user;  -- I'm guessing `user` is also a parameter

    IF v_due_amount IS NULL THEN
        SET v_due_amount = 0.00;
    END IF;
    RETURN v_due_amount;
END;

Your version has an aggregation function in the subquery. This makes no sense, because due_amount is not the argument of an aggregation function. This logic should perhaps be:

    SELECT v_due_amount := SUM(l.due_amount)
    FROM lunch_transaction l
    WHERE l.user_id = in_user;  -- I'm guessing `user` is also a variable