MySQL Bugs: #34711: Result of Round() functions differ, if argument is a variable

Bug #34711	Result of Round() functions differ, if argument is a variable
Submitted:	20 Feb 2008 23:55	Modified:	27 Feb 2008 13:39
Reporter:	Aurel Pekarcik	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51a-community-nt, 5.0.54, 5.1.23	OS:	Windows (MS Windows XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	Mathematical function, round

Description:
Result of Round() functions is different, if argument is user variable.

How to repeat:
Try this :

select @X:=2.945,Round(@X,2),Round(2.945,2)

Result: 2.94, 2.95

Suggested fix:
Round must returns the same result, without regard on type of arguments ( constant vs user variable )

Thank you for a bug report. Verified just as described.

note that

mysql> select @X:=2.945;select Round(@X,2),Round(2.945,2);
+-----------+
| @X:=2.945 |
+-----------+
|     2.945 | 
+-----------+
1 row in set (0.00 sec)

+-------------+----------------+
| Round(@X,2) | Round(2.945,2) |
+-------------+----------------+
|        2.95 |           2.95 | 
+-------------+----------------+
1 row in set (0.00 sec)

The result is correct.

Check the manual
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
starting from the words
"
Another issue with setting a variable and using it in the same statement is that
"

OK, the following also works:

mysql> select @X:=0,@X:=2.945,Round(@X,2),Round(2.945,2)
    -> ;
+-------+-----------+-------------+----------------+
| @X:=0 | @X:=2.945 | Round(@X,2) | Round(2.945,2) |
+-------+-----------+-------------+----------------+
|     0 |     2.945 |        2.95 |           2.95 |
+-------+-----------+-------------+----------------+
1 row in set (0.00 sec)

But this does NOT look consistent to me. Why assignment @X:=0 making @X a number, but @X:=2.945 does NOT? I think this is still a bug, even if proper good workaround is dicumented.

mysql> select @X:=0,@X:=2.945,Round(@X,2),Round(2.945,2);
+-------+-----------+-------------+----------------+
| @X:=0 | @X:=2.945 | Round(@X,2) | Round(2.945,2) |
+-------+-----------+-------------+----------------+
|     0 |     2.945 |        2.94 |           2.95 | 
+-------+-----------+-------------+----------------+
1 row in set (0.00 sec)

it's consistent. You got 2.95 because @X existed before the query. Tri it in a new session.

I will try documentate this by another way.

use test;
drop function if Exists aFnc1;
drop function if Exists aFnc2;

delimiter ||

create function aFnc1() returns double
begin
  declare D double;
  set D=2.945;
  return Round(D,2);
end||

create function aFnc2() returns double
begin
  return Round(2.945,2);
end||

delimiter ;

mysql> select aFnc1(),aFnc2();
+---------+---------+
| aFnc1() | aFnc2() |
+---------+---------+
|    2.94 |    2.95 |
+---------+---------+
1 row in set (0.00 sec)

mysql>

What do you think about it ?

and next...

mysql> select 2.48-2.47,'2.48'-'2.47'
    -> ;
+-----------+--------------------+
| 2.48-2.47 | '2.48'-'2.47'      |
+-----------+--------------------+
|      0.01 | 0.0099999999999998 |
+-----------+--------------------+
1 row in set (0.00 sec)

I think, conversion fails. But another values are converted properly.

mysql> select 2.95-2.94, '2.95'-'2.94'
    -> ;
+-----------+---------------+
| 2.95-2.94 | '2.95'-'2.94' |
+-----------+---------------+
|      0.01 |          0.01 |
+-----------+---------------+
1 row in set (0.00 sec)

I have a feel, that this conversion problem maybe is related to problem with Round as descripted above.

What do you think, is it another error (if is) ?

I forget open this thread.

both your last effects demonstrate the difference between DECIMAL and DOUBLE. It's expected, these types are different. DECIMAL is precise, DOUBLE is approximate.

See also
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Ok.

Sorry, but I still think that it is very serious bug.

At first. Result must NOT depend on assigmnet value to variables.
Aj know, that double has floating point representation, which is a aproximate, but calculation with the same value must return the same result ! 

Aproximately dont means random, by my opinion.

And second. When "select 2.48-2.47" result to 0.1, which internal numeric representation is used before substract ? Why do not use the same internal numeric representation before  substract in the second - quoted - case ? 
And when is used the same internal numeric representation, how is possible, then the results are different ?

Please, have a discuss this problem with wide area. Do you now another language, where value assigmnent to variable has this effect ? Effect of change results ?

This is no difference between double and decimal. This is diffence between double and double!

calculation with the same value returns the same result, if it's done with the same data type. When you store "1.2" in an integer, string, or double column you'll get different results, right ? In your example a variable created in the query has incorrect data type - this behavior is documented, and the documented solution is to create variable in advance, so that MySQL would not have to guess.

For "select 2.48-2.47" MySQL uses DECIMAL data type, as documented. It is exact data type and the result is exact. In the quoted case MySQL uses DOUBLE, as - again - documented. MySQL cannot use DECIMAL because the number encoded in a string may not necessarily be within DECIMAL range (e.g. it could be "1e100").