Home > Software engineering >  Scalar function in mysql returns inconsistent values. Depending on where is being called
Scalar function in mysql returns inconsistent values. Depending on where is being called

Time:03-14

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.

  • Related