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.