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

[Marc Portier (JIRA)]

    [ http://issues.cocoondev.org//browse/DSY-373?page=comments#action_12949 ] 

Marc Portier commented on DSY-373:
----------------------------------

uh, forcing round-half-down doesn't get any prettier

amybe there is a nicer version, but this seems to do the trick:

SIGN(scale) * CEILING(SIGN(val)*val*POWER(10;scale) - 0,5;1)/POWER(10;scale)

> 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