CODEWITHSUNDEEP
phpmysqlsqldatabase
user972946
user972946

Reputation:

My MySQL function totally loses math precision when multiplying decimals. Why is that?

I made this simple function, and the result returns 1 rather than 0.5

What did I do wrong?

DELIMITER //

DROP FUNCTION IF EXISTS test_decimal //

CREATE FUNCTION test_decimal(input DECIMAL)
  RETURNS DECIMAL
BEGIN
  SET @_credit = 0.5;
  RETURN input * @_credit;
END //

DELIMITER ;

SELECT test_decimal(1);

Upvotes: 7

Views: 650

Answers (1)

John Woo
John Woo

Reputation: 263703

Because you didn't specify the precision and scale that's why it's rounding the value. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. By default, the value of the precision is 10 and the value of scale is 0. So, RETURNS DECIMAL is the same as RETURNS DECIMAL(10,0). If the scale is 0, DECIMAL values contain no decimal point or fractional part. Try specifying in to your function. 

RETURNS DECIMAL(5,2) -- 999.99

DECIMAL, NUMERIC

SQLFiddle Sample

Upvotes: 9

Related Questions