Apache OpenOffice (AOO) Bugzilla – Issue 13607
Round off error in currency field
Last modified: 2013-08-07 15:45:41 UTC
I discovered a very repeatable problem that appears to be a round-off algorithm issue when entering certain values in a currency field (4 decimal places). The following is a list of the values entered and their committed results (verified with the DSB and CuteSQL): Typed, Stored 0, 0.0000 0.3, 0.3000 0.03, 0.0300 0.003, 0.0030 0.0003, 0.0003 3, 3.0000 3.3, 3.3000 3.03, 3.0299 3.003, 3.0030 3.0003, 3.0003 6, 6.0000 6.6, 6.6000 6.06, 6.0599 6.006, 6.0060 6.0006, 6.0006 9, 9.0000 9.9, 9.9000 9.09, 9.0900 9.009, 9.0090 9.0009, 9.0009 6.3, 6.3000 6.03, 6.0300 6.003, 6.0030 6.0003, 6.0003 1, 1.0000 1.1, 1.100 1.01, 1.0100 1.001, 1.0009 1.0001, 1.0001 2, 2.0000 2.1, 2.1000 2.01, 2.0099 2.001, 2.0010 2.0001, 2.0001 2.2, 2.2000 2.02, 2.0200 2.002, 2.0019 2.0002, 2.0002 If I perform an SQL INSERT from within the DSB of the failing values, they are stored correctly (3.03 = 3.0300 etc.) Windows XP SP1 MDAC 2.7 SP1 XP Regional and Language options - English (United States) Number: 123,456,789.00 Currency: $123,456,789.00 Time: 18:57:26 Short Date: 2003-04-18 Long Date: Friday, April 18, 2003 OOO 1.0.3 Language Settings/Language Locale setting: Default Default currency: Default Default languages for documents/Western: English Asian languages support: (not Enabled) Spreadsheet/Calculate Precision as shown (unchecked)
I just tried to reproduce the problem with a dBase 4 database but I couldn't. The dBase driver acts differently in that it truncates trailing zeros to the right of the decimal point in numeric or floating fields.
Jeff, which database are you connecting to? You only mention MDAC (which allows you to connect to a variety of databases) and CuteSQL (which, AFAIK, is a data query tool). I could not reproduce this problem with MySQL at least, but did not try any other database types, yet ....
It's a MS Access database. I will attach a ComCheck dump. I use CuteSQL to test SQL queries and confirm database contents. It's free and so is within my budget.
Created attachment 5980 [details] MDAC ComCheck dump
Created attachment 5981 [details] test database showing symptoms (money table)
I cannot reproduce this - I tried to insert 3.03 into this the "money" table of the database you attached, and this arrived fine in the database ... Hmmm ....
I just performed a fresh OOO 1.0.3 install on a different PC running Windows 98 SE, MDAC 2.1.3711.11(GA) (according to ComCheck), MS Access Driver 4.00.3711.08, Sun JRE 1.4.1_02 (latest). Reran the test on the money table and got a 3.0299 when I entered 3.03 into the DSB from Calc. Calc itself doesn't have this problem even with the cell format set to 4 decimal places. I performed another ComCheck dump and a SQL log. I will post them.
Created attachment 5991 [details] Data from Win98 PC
Just for laughs I uninstalled 000 1.0.3 and installed 1.1 beta on my Win98 PC. I then repeated the test. I entered 3.03 and got 3.0299 and 6.06 got a 6.0599 just like OOO 1.0.3 did.
Did another test with OOO 1.0.3 on Win XP Pro. The original Access database I used was created with an old freeware app named Munin. It uses Jet to create and modify Access databases. Just to make sure the mdb it created wasn't violating any format rules I tried another test database created with Access 2002. Got the same result. My boss said it sounded like a floating point error of some sort.
37.66 = 37.6599 UPDATE ..... 37.66 = 37.6600
9.54 = 9.5399 UPDATE ..... 9.54 = 9.5400
Problem still exists in 1.1 RC
I tried again with RC1, and I still fail to reproduce this. The only difference I noticed is my Jet-Engine version: My file version for msjetoledb40.dll is 4.00.4331.4, while yours is 4.00.3711.08 (but I can't really believe that this is the reason ...) Except this, I think I have the same situation as you have ... :(
I recently rebuilt my PC. My current verson of msjetoledb40.dll is 4.0.6807.0 (Jet 4 SP7). I will submit another MDAC Component Checker dump. It does flag one file, msjtes40.dll, as a version mismatch but I think that is because I am using MDAC 2.71 SP1 Refresh and the most recent version of CC is slightly out of date. I will try this some more on other versions of Windows and other hardware platforms. What Windows version and CPU are you using?
Created attachment 7849 [details] MDAC Component Checker 2.7 report (zip)
> What Windows version and CPU are you using? WinXP, Pentium 1.8GHz
There seems to be a noticable amount of oooqa here. If any of you here are OOo developers or QA members, would you be willing to add the oooqa keyword? If so, that would be great.
> There seems to be a noticable amount of oooqa here. Ehm, well, the only one I see - except the submitter - is me, and I am not member of OOo-QA, but a developer :) > If any of you here are OOo developers or QA members, would you be > willing to add the oooqa keyword? If so, that would be great. Interesting, because we just yesterday had a discussion about this. According to http://www.openoffice.org/issues/describekeywords.cgi, oooqa is not to be set by developers, but by "OOo QA volunteers". I'd appreciate anything which helps us to distinguish "bugs which nobody ever looked at" from "bugs where actions have been taken", but from my understanding so far, oooqa isn't the right thing. If we say it _is_ (and this would perfectly okay to me!), we should a) announce this to get a common understanding and b) change the keyword description page to properly reflect this.
>> There seems to be a noticable amount of oooqa here. >Ehm, well, the only one I see - except the submitter - is me, >and I am not member of OOo-QA, but a developer :) :^) Yes, I realize that are the only 1 here other than the submitter. Oddly enough, I have been told that whether the person is a developer or a QA member, the keyword should be there. >...we just yesterday had a discussion about this. >According to http://www.openoffice.org/issues/describekeywords.cgi, >oooqa is not to be set by developers, but by "OOo QA volunteers". Interesting. I never heard of such a discussion. That's probably because I'm not a developer, & also because I skim through much of my email. >I'd >appreciate anything which helps us to distinguish "bugs which nobody >ever looked at" from "bugs where actions have been taken", but from >my understanding so far, oooqa isn't the right thing. >If we say it _is_ (and this would perfectly okay to me!), we should >a) announce this to get a common understanding and b) change the >keyword description page to properly reflect this. I'll mention what I've heard, just for the sake of clarification. However, it's becoming more & more obvious that we'll need to collaborate to save time. I've heard that issues marked as Unconfirmed & don't have the keyword are fresh & nobody has looked @, in the sense of they haven't begun trying anything with that person. Perhaps someone read it but didn't know how to deal with the issue. That doesn't count, because the person didn't do anything according to the issue activity list. I've heard that anything has been done, even if it is just a simple comment, then the keyword is added, & once the issue is reproducable, then the status is changed to new. From there it is out of QA hands, & in the developer hands. Another thing that I've heard is that if a QA member tries to reproduce, but can't, then he can resolve the issue, changing the resolution to WorksForMe. However, I'll cut & paste exactly what you said to me above, & send it to the QA mailing list. I think that the thing that surprises me is that some of the people who told me this were probably developers.
Oops. Sorry for the repeat. It was unintentional, & caught me off-guard.
> Interesting. I never heard of such a discussion. This is because "we" meant "me and and Hamburg-located QA-colleague from the office next door" :). Sorry for being too inaccurate with my wording :). I will step in in the QA list, to keep this discussion away from the issue here. Thanks for your engagement!
utomo > FS: it look like no QA comment on this issue.
? Not sure what you want to say, sorry
Created attachment 11241 [details] Yet another test DB
I attached another test DB. It's an Access DB with 3 tables, Access 2003 Beta, Munin, and OOO 1.1. They are as close to identical as I can get. Just trying to figure out the equivalent field type names is a pain. I repeated my tests on two different XP Pro systems with OOO 1.1. The rounding problem shows up in records added OOO in OOO and Munin tables but not in the Access one. Only the currency field is affected. If I edit any table with Access the problem doesn't seem to show up in OOO. But Access shows the currency and floating fields with 2 decimal places unless I add them with Access with greater decimal places. Also, floating values in Single and Double precision fields in records added by Access are rounded to integers. Yet it shows the values added by OOO with the correct number of places. Munin shows the raw values and they are really bizarre. It looks like the Currency field is a truncated version of Single/Real type field when the error occurs. Maybe it's an ODBC Access driver rounding problem. You can get Munin from Simtel: http://www.simtel.net/pub/pd/69746.html
Hi, I can reproduce this. It only happend when you use ODBC when you use ADO it doesn't happend. - create a new odbc datasource with the attached DB 13607_test - open the table OOO - insert 3.03 in the currency field ==>> 3.0299 is displayed It doesn't happend with ADo and it also doesn't happend when you use 4.04 as value, it only happend with ODBC and 3.03 Bye Marc
I am confused. jhansonxi, do you encounter the bug with ADO or ODBC (or even ODBC via ADO)?
It's finally reproduced! I had almost given up hope! FS - I only use ODBC.
Ah! I never tried ODBC, since you only mentioned it in your very last comment (Thu Nov 13 09:35:39), but before, always talked about MDAC, which is the package with the ADO drivers. Well, *this* explains why I never could reproduce it :(
change subcomponent to 'none'