Scalar function in mysql returns inconsistent values. Depending on where is being called-CodePudding

I create a scalar function that calculates that multiplies the value of two columns and store that the result in a variable. That variable is returned. My objective is to use my function inside an insert statement in order to pupulate a field with the return value. My problem is that when I call my function like so select calculate_price(4,5) I obtain the desired result. However, when I call the function inside the insert statement I get an unexpected result.

My code looks like so:

CREATE DEFINER=`root`@`localhost` FUNCTION `calculate_price`(idcart int, idProduct int) RETURNS int
    DETERMINISTIC
BEGIN
select  price*quantity into @price
 from product inner join test_product_quantity_cart 
 on product.id_product=test_product_quantity_cart.id_product
 where id_cart=idcart and test_product_quantity_cart.id_product=idProduct;
RETURN @price;
END

a row in my table test_product_quantity_cart looks like this:

id_cart=4
quantity=5
id_product=2
price_product= -- expected 20

the related row in my table product looks like so:

id_product=2
price=4

if I call my function this way select calculate_price(4,2), I get 20. Which makes sense because the price of product with id 2 is 4. The quantity is 5, so 4*5=20. However, when I use the function inside a insert statement in order to create a new row like so:

insert into test_product_quantity_cart(id_cart,quantity,id_product,price_product )
    values (4,5,2, calculate_price(4,2))

I get 45. I would like to know what I am doing wrong that causes this inconsistente behavor. Thank you for your help.

CodePudding user response：

When you are doing the insert the db has not yet been written to when the function is invoked AND the function may find a preexisting row in test_product_quantity_cart so using the fiddle provided by akina and changing the first insert to 4/10/2 the second insert calculates a price of 40. Also the first insert does not acquire a price.

In my view a trigger would be more appropriate anyway.