I execute a query to the MySQL database in order to get interest for payment:

select cp.id, cp.percent, round(cp.percent, 2) from payment cp where cp.id = 4118098 and month(cp.payment_date) = 9

Issues:

enter image description here

Question: Why does rounding to the hundredth round down, although at the end of the figure 5?

In this case, the select round(75.725, 2) query rounds everything up correctly - up to 75.73.
The type of the percent column in the table is double .

  • one
    For approximate-value numbers, it depends on the C library. If you use the “round to the nearest even integer.” Manual . - Akina
  • Try setting the 3rd parameter so that it goes like this: round(cp.percent, 2, 0) - Vladimir Afanasyev
  • one
    Try replacing ROUND(percent, 2) with CAST(percent, DECIMAL(5,2)) . - Akina
  • 2
    @Akina yes, the campaign, it is written in the database as 75.7249999999, which affects the round , cast helps to bring in a normal view. make it a reply for other participants? - Denis
  • one
    @Alex No question, done ... - Akina

1 answer 1

Why does rounding to hundredths round down, although at the end of the figure 5?

The type of the percent column in the table is double.

Type DOUBLE refers to the so-called. "inaccurate" data types - i.e. the binary representation used to store it is not exactly equal to the one introduced, but only as close as possible to it. As a result, the infamous 0.1000000001 or 0.0999999999 instead of the originally entered 0.1. And false when comparing such a value with a literal value of 0.1 that is directly specified as a literal.

According to the documentation for these types of data

For approximate-value numbers, it depends on the C library. If you use the “round to the nearest even integer.”

or, if in Russian, "the result of rounding depends on the library of functions used, in many cases the rounding to the nearest even one is performed" (the so-called "accounting" rounding).

In order to get the half-up rounding, it is necessary first to reduce the existing value to the exact numeric type, i.e. The desired rounding could be obtained as follows:

 SELECT ROUND(CAST(cp.percent AS DECIMAL(6,3)), 2)

However, the type conversion itself requires rounding (remember that the initial value is imprecise!), And therefore it makes sense to immediately cast the desired final type, i.e. with the right amount of decimal places. And the final version will look like

 SELECT CAST(cp.percent AS DECIMAL(5,2))

In this case, the select round (75.725, 2) query rounds everything up correctly - up to 75.73.

In this case, the constant literal is immediately created with the type DECIMAL. Therefore, rounding works as indicated in the same documentation:

For the exact-value numbers, ROUND () is the “round half away from zero” or “round the distance” rule: next integer if negative. (In other words, it is rounded away from zero.)

or, if in Russian, "rounding to the nearest integer from zero", i.e. positive - up, and negative - down.

Ps. Please note that the conversion functions of the CAST () and CONVERT () types do not at all provide for converting a number to an inaccurate numeric type.