[daisy] [JIRA] Created: (DSY-373) ROUND() function implementation not consistent with mysql 5.0.3+

[Marc Portier (JIRA)]

ROUND() function implementation not consistent with mysql 5.0.3+
----------------------------------------------------------------

         Key: DSY-373
         URL: http://issues.cocoondev.org//browse/DSY-373
     Project: Daisy
        Type: Bug
 Reporter: Marc Portier
    Priority: Minor


Found this while performing the tests on the 1.5.x branch on top of my mysql 5.0.4

I get a failure on this section of the LocalQuery2Test (see AbstractQuery2Test, line 470)

>  result = queryManager.performQuery(
>    "select Round(5.22, 1), Round(5.28, 1), Round(5.25, 1) where Round(5.22, 1) = 5.2 and Round(5.28, 1) = 5.3 and Round(5.25, 1) = 5.2 and id = " + document.getId(), Locale.US
>  );

in my case the query returns 0 rows (leading to an indexoutofbounds at the next line where the expted only row in the result is asserted)

The reason for this is that daisy's round-function is doing a round-half-up:
(see RoundFunction, line 36)

>  return value1.setScale(scale, BigDecimal.ROUND_HALF_DOWN);

while my version of mysql is doing a round-half-down.

>  $ mysql -uroot -p -qBe "select round(5.25,1);"
>  Enter password:
>  round(5.25,1)
>  5.3

reading up on mysql's behaviour I found this:
(see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html)

Before MySQL 5.0.3, the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR()  instead.


If I parse that correctly then this would mean that depending on your C-lib/mysql version combo the round function might or might not work as expected by the Daisy code.

Taking up the 'use truncate' advise I think the folowing general rewrite of 

ROUND( VAL, SCALE)

to

TRUNCATE((VAL+SIGN(VAL)*(POW(10,(0-SCALE))/2)),SCALE)

should guarantee a round-half-down behaviour


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.cocoondev.org//secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira