Created attachment 53333 [details] test case of a database containing currency fields in forms which are wrongly interpretated When you create a database form, which contains a table control in which a column is of type "currency field", the "value max." of the column is effectively divided by 10^n where n is the number corresponding to its "decimal accuracy" property. As an example : "Value max."|"Decimal accuracy"|Actual max value|Expected max value 1000000.00 2 10000.00 10000000.00 1000000.000 3 1000.000 10000000.000 The same happens to minimum values correspondingly. This does not happen to normal "numeric" fields inside the table control. This does not happen for "currency fields" outside of a table control. Tested under Linux/Mac OS 10.6 with postgresql as backend (via jdbc) and also using the HSQL backend (see below for test database). OpenOffice suffers from the same bug und the following additional OSs Windows XP/Vista (32bit) Attached is a test database containing one table and 3 forms (using the HSQL backend): The database table contains 6 columns of type double. The forms: Form "test_simple_fields": contains "currency fields" for the corresponding db-table columns with the following max./min values: real1 : 1000000.00/-1000000.00 real2 : 12345,67/-12345,67 real3 : 1000000.00/-1000000.00 real4 : 12345,67/-12345,67 real5 : 1000000.000/-1000000.000 real6 : 1000000.000/-1000000.000 Form "test_table_control": contains "currency fields" for the corresponding db-table columns with the following max./min values: real1 : 1000000.00/-1000000.00 real2 : 12345,67/-12345,67 real3 : 100000000.00/-100000000.00 real4 : 1234567,00/-1234567,00 real5 : 1000000.000/-1000000.000 real6 : 1000000000.000/-1000000000.000 Form "test_combined" contains both controls in one form.... In the table control multiplying the min/max values by 10^n (where n is the number of its decimal accuracy property) provides the "correct" behaviour but does _not_ constitute a valid workaround in my eyes. This was done in the columns real3 real4 and real6 of the table controls in the test database to check whether the decimal accuracy actually works (like for 12345.67 etc). Depending on via which control you use to change/enter the data the values in the database table may or may not be correct. If the table control only displays values exceeding its limits, the database entries are still correct, but incorrectly displayed. Only after changing the values via the table control the lower limits are enforced. Please play around with the forms and data. NOTE: Exceeding the max/min value leads to a rather silent change of the value in the database entries (no warning no flashing, I admit the change is visible in the control) I am not certain, that this is "wise"....
Hi, sorry to bump this, but since my initial bug report more than half a year ago nobody has actually reacted. I just checked that the bug is still there even in LibreOffice 3.5.1. I still think this one should be fixed in the long run in case LibreOffice Base is intended to be taken seriously. Especially when it comes to money it _is_ a bad behaviour to simply change the values by two orders of magnitude. So I am changing the Importance to: high and critical. Stefan
What's the difference between "test_simple_fields" and "test_table_control" on your description ? Could you please simplify your test example ? For example : reduce the number of fields, reduce the number of forms. Just put a field ok and a field ko for the comparison. Could you try with a brand new document ?
Before I do that, did you try the example? I think I stated quite clearly, why the simple fields are there. The simple fields are currency fields /outside/ of a table control. There, the min/max values work as defined. While for currency fileds /inside/ a table control they do not. There the currency fields' min/max values are actually divided by 10^n where n is number of decimal accuracy. In the combined form you can even see the effect on the data directly since the fields in the table control and simple fields display the very same data (something that wouldn't be wise in a real application, I know!) and you can see the effect of having the correct data, incorrectly displayed, and once changed in the table control, the data in the database table gets incorrect (as can be seen in the corresponding simple field). If you wish I can of course reduce the number of fields, but it will miss out some aspects of the problem.
Created attachment 59077 [details] test cxase for wrongly interpretated currency fields in tablControls, created with LibreOffice 3.5 The test case contains a simple table with two colmunns (real1, real2) and the corresponding form. The form contains two simple currency fields at the top (outside of a table control). min/max values are set to +/-1000000.00 for real1 (default) +/-12345.67 for real2 Both controls work as expected The table control below contains two columns displaying real1 and real2 as well (for demonstration purposes, nothing you would do in a real world application) min/max values are set as above to: +/-1000000.00 for real1 (default) +/-12345.67 for real2 For row 1 of the table I set the values of real1 and real2 to their maximum values. As can be seen upon opening the form, the values displayed in the table control are divided by 10^2 (2 being the decimal accuracy here). As long as the values in the table control are not changed - only displayed, the values in the table are still correct. Once you delete e.g. one digit in the table control column and re-enter that very same digit (in effect not change the value), the data actually gets changed in the table and the corresponding simple currency control field at the top of the form.
The more elaborate test case I uploaded last year did contain examples for decimal accuracies of 2 and 3 demonstrating that it is exactly number of accurate decimal digits that gets cut off, and a workaround to demonstrate how this could be fixed for the time being.
Why status "NEEDINFO"?
What status would you like? I now set it to "UNCONFIRMED"since nobody seem to care to try the test case....
I can confirm the bug under LO 3.3.4. It's only in the currency-field in tablecontrols. But I dont use this fields, because they could not display negativ values in a red color - so I haven't recognised this behaviour before. I only use formatted fields for this function. This are the fields, which are also created by the wizard for forms. The currency-field seem to work without looking, that there are decimal-places. Currently you have to multiplicate Minimum and Maximum with 100 (currencies have 2 decimal-places). I ste the Status to "New". Also I set the Importance to "Medium" and "Normal". There mus be a difference between those Problems, which could make a using of databases impossible and a Min-Max-problem in a field of the table-control. One hint for fields, which should be for currencies in a HSQLDB-table: use Decimal for the filed, not Double. Double is a floating-point number. With Decimal you could define the Decimal-Places correctly.
I greatly appreciate that someone took the time to confirm the problem. I agree, that the problem is not a classical show stopper, but it really can _silently_ alter your data almost without you noticing, which for currency fields dealing with money really _is_ a major problem. I still feel uneasy that even if you enter some value above/below the max/min values and everything works correctly, the values get simply corrected upon field update and the user is _not_ notified. This is not strictly part of this problem but it lead to the "almost silent" data alteration. BTW: I actually use DECIMAL for money values, I was just to lazy setting up the test case with it and for the point in question it does not matter
And again a bump, still present in the current release.
Version field must contain the oldest LO version which contains the bug. Taking a look at the history of the bugtracker, it seems it's 3.4.4
Ssorry, I didn't know that the version should be kept where it was... Changed it back. But it still applies to all versions.
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this. If I add a numeric field in subform, it works.
Here's a code pointer: http://opengrok.libreoffice.org/xref/core/svx/source/fmcomp/gridcell.cxx#2074 but quite difficult to understand the whole mechanism. Also, I noticed the class BigInt, do we still need this kind of class in addition with sal_(u)Int64, sal_(u)Int32, etc.?
Caolan/Lionel: Database tracker but it seems with svx (and vcl when try gdb) underlying cause.
Caolan McNamara committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=63885c86f866411f01f1b6fceaa07f054a5a3550 Resolves: fdo#42747 limits need to multiplied by num of decimal places The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The arg to the numeric formatter is a long, so to get it to retain decimal places one has to multiply the true limits by 10^no_decimal_places
Caolan McNamara committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4b19f9ced958fc732ad591768d7625ea69996dfd&h=libreoffice-4-3 Resolves: fdo#42747 limits need to multiplied by num of decimal places It will be available in LibreOffice 4.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Caolan McNamara committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=faa00db418db8a2885973306e5ad10f6b5307ca0&h=libreoffice-4-2 Resolves: fdo#42747 limits need to multiplied by num of decimal places It will be available in LibreOffice 4.2.6. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.