0

I am new to MySQL and stored functions, so I apologize if this question seems elementary.

I am writing a stored function in which I need to truncate a float to a certain number of decimal places. As I understand it, the built in TRUNCATE function should do this for me; however, the result that I am getting isn't truncated at all. The only noticeable change is that some of the digits after the intended truncation point are different than the original number.

I've tried experimenting with the truncate function on its own with the same numbers (raw), ie. SELECT TRUNCATE(0.00123456, 5) AS 'Example', and that works just as expected. I have a feeling my problem in the stored function has to do with the float type being used as a parameter in the truncate function.

I should note that the reason why I am truncating this number in the first place is a result of the round function failing to cut off the number of decimals as expected. I explain this more (and with the actual function as an example) here: What is the proper if clause syntax for a MySQL stored function?, but I've included some pared down code below for clarity.

CREATE FUNCTION 'sfround'(number FLOAT, sigFigs INT) RETURNS FLOAT
DETERMINISTIC
BEGIN

DECLARE nonTruncated FLOAT;
DECLARE truncated FLOAT;
DECLARE decimalsKept INT;

#some not relevant code in the middle

SET nonTruncated = ROUND(number, sigFigs-1-FLOOR(LOG10(ABS(number))));
SET truncated = TRUNCATE(nonTruncated, decimalsKept);

RETURN truncated;

END$$

When my inputs are any number between 1 and -1 (excluding 0 because I included an edge case to deal with 0), this function yields a number which is correct up until the intended point of truncation, after which a seemingly unrelated list of numbers appears (same numbers with each call of the function).

Ex. sfround(0.00123456, 5) yields 0.0012344999704509974.

Is there some reason why truncate is incompatible with FLOAT types? Or is there something else that I am missing here?

oliviaratliff
  • 35
  • 5

1 Answers1

0

Yes they are incompatible.

FLOAT stores the data in a IEEE-754 binary format which is stored in base 2. A base 10 fraction does not fit exactly. Thus your function gets something near 0.0012345 for the truncation but a FLOAT cannot hold that exact so you get the number required. See the many answers here and elsewhere especially What Every Computer Scientist Should Know About Floating-Point Arithmetic for what a IEEE format floating point number is represented as.

If you want to have an exact number of decimal places you need to use the DECIMAL type

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
  • Ah, I see. Thank you for that answer, that makes a lot of sense! To clarify, you're suggesting that I use the decimal class instead in this case - how would that work in terms of declaring a decimal variable with an unknown number of total digits and unknown number of digits after the decimal? – oliviaratliff Jun 05 '19 at 20:16