oracle fails math 101

If Oracle's numeric calculations give you exciting results, don't assume you've discovered something new. Double check Oracle's arithmetic.

Since I didn't see a tech alert bulletin on this one, I thought I'd give folks a heads up before somebody else gets bit like we did.

One of my users noticed that a 6.0.30 database produced a couple anomalous averages when 'avg' and 'stddev' were applied to columns in the SELECT clause. When the order of the columns was changed, it produced believable averages, but changed the standard deviations. Loading the same dataset into a 6.0.33 database yielded consistent results regardless of column order. But we still aren't sure if these results are 'correct' because of a bug in 6.0.33 where it rounds down by a factor of 10 when it should round up. (OK, we received the SUN 4 patch for that one last week, but I just discovered I shouldn't install it because it causes core dump problems with oracle executables).

One can only wonder what Oracle's QA procedures must be like to miss on such basic math functions. In the meantime, my advice to my site license users is to not publish any results based on Oracle SQL calculations, but to double check them with a C or FORTRAN program.

• jean

+-----------------------------------------------------------------------+


| Jean Anderson, DBA                       email:  jean_at_seismo.css.gov  |

| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |

| 10210 Campus Point Drive                 phone:  (619)458-2727        |


| San Diego, CA  92121                       fax:  (619)458-4993        |


 +-----------------------------------------------------------------------+


|                execute sp_disclaimer _at_opinion="mine"                  |


 +-----------------------------------------------------------------------+