Download it now!
Bug 42747 - EDITING VIEWING max/min values in "currency field" columns of table controls in db forms are wrongly interpretated
Summary: EDITING VIEWING max/min values in "currency field" columns of table controls ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: Other All
: medium major
Assignee: Caolán McNamara
URL:
Whiteboard: target:4.4.0 target:4.3.0.2 target:4.2.6
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-09 08:40 UTC by Stefan M
Modified: 2014-07-02 21:48 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
test case of a database containing currency fields in forms which are wrongly interpretated (28.14 KB, application/vnd.oasis.opendocument.database)
2011-11-09 08:40 UTC, Stefan M
Details
test cxase for wrongly interpretated currency fields in tablControls, created with LibreOffice 3.5 (11.75 KB, application/vnd.oasis.opendocument.database)
2012-03-26 08:50 UTC, Stefan M
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan M 2011-11-09 08:40:16 UTC
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"....
Comment 1 Stefan M 2012-03-21 06:35:56 UTC
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
Comment 2 Julien Nabet 2012-03-24 00:32:16 UTC
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 ?
Comment 3 Stefan M 2012-03-26 02:27:03 UTC
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.
Comment 4 Stefan M 2012-03-26 08:50:39 UTC
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.
Comment 5 Stefan M 2012-03-26 08:54:31 UTC
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.
Comment 6 Jochen 2012-07-24 19:48:21 UTC
Why status "NEEDINFO"?
Comment 7 Stefan M 2012-07-30 13:00:31 UTC
What status would you like? I now set it to "UNCONFIRMED"since nobody seem to care to try the test case....
Comment 8 Robert Großkopf 2012-08-03 17:43:29 UTC
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.
Comment 9 Stefan M 2012-08-09 08:31:51 UTC
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
Comment 10 Stefan M 2014-02-17 15:44:40 UTC
And again a bump, still present in the current release.
Comment 11 Julien Nabet 2014-02-17 16:46:34 UTC
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
Comment 12 Stefan M 2014-02-20 16:08:54 UTC
Ssorry, I didn't know that the version should be kept where it was... 

Changed it back. 

But it still applies to all versions.
Comment 13 Julien Nabet 2014-06-29 16:54:28 UTC
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this.
If I add a numeric field in subform, it works.
Comment 14 Julien Nabet 2014-07-01 05:43:10 UTC
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.?
Comment 15 Julien Nabet 2014-07-01 05:45:18 UTC
Caolan/Lionel: Database tracker but it seems with svx (and vcl when try gdb) underlying cause.
Comment 16 Commit Notification 2014-07-01 12:32:06 UTC
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.
Comment 17 Caolán McNamara 2014-07-01 12:35:39 UTC
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
Comment 18 Commit Notification 2014-07-01 19:49:07 UTC
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.
Comment 19 Commit Notification 2014-07-02 21:48:41 UTC
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.