Apache OpenOffice (AOO) Bugzilla – Issue 58101
Arithmetics in SQL statement gets truncated in queries (MySQL/ODBC)
Last modified: 2013-08-07 15:45:09 UTC
Arithmetics in SQL statement gets truncated in ooffice queries. Example: A simple MySQL table that contains just one column `test` of type varchar(10). The first row of this colunm is filled with the value "1.23" This value can be retrieved by openoffice and is correctly displayed as a numeric value with two digits after the decimal point. Query as created by the query editor is in this case: SELECT `test` FROM `lila`.`Waren` `Waren` returns 1.23 If there is any arithmetics involved in the SQL statement the calculation the calculation is carried out correctly, but the result is truncated. Example: SELECT `test`+1 FROM `lila`.`Waren` `Waren` returns 2 In a form based on this query, this value shows up like "2.00" The very same SQL command works correct outside openoffice: mysql> SELECT `test`+1 FROM `lila`.`Waren` `Waren`; +----------+ | `test`+1 | +----------+ | 2.23 | +----------+ The option "execute SQL command directly" in the query editor still gives the same truncated result. ---<(kaimartin)>---
what kind of driver do you use?
this seems to be similar to that what I describe in Issue 59676
added keyword
I can confirm this. At this time I use m158. I have a similar Problem with the sum funktion. see Issue 59676
can anybody confirm this in a m162? (mechtilde has written that her similar foult works in this version) thx
I tried this with m162, Win XP Sp2, MySQL 5.0.18, JDBC 3.1.12 Initialy it failed, actually I could not get the query to run at all because it failed to recognize either a decimal or numeric field. I then switched JDBC drivers to the 5.0.0 beta (nightly build 20060214) release. The tables open fine, the query runs without a problem. The form is created and the value displayed reflects the decimal places properly. this was true for columns of type DECIMAL and NUMERIC Then I realized that is not what was needed to test...sorry. Tired a VARCHAR column, everything works as expected - the query query works, with the column in the result set formatted as General Number. The form wizard reads the column as such. This works for a value with up to 2 digits after the decimal place. I tested with a sring of 1.123 and the result displayed was 2.12, to fix this I simply needed to edit the form and change the format for the control to show three digits - the form then displayed the correct 2.123.
clu->kaimartin: does the last hint from atjensen (changing the format) help you further / solve your problem?
clu wrote: > kaimartin: does the last hint from atjensen (changing the format) help > you further / solve your problem? No. I allready stated in my initial report, that the format varchar() works fine if the SQL statement simply queries the database. The problem arises if there is any arithmetics involved. See the example given above. The format decimal() is a different issue. However, I now use a completely different application (sql-ledger) to do my business related forms. So I quit looking for solutions and workarounds for this specific problem. ---<(kaimartin)>---
I am glad you have a working solution for you business. Just to be clear however, in my post earlier, using the 5.0 JDBC driver I was performing arithmetic on a varchar column and was able to display the full results from that operation in the form. It wasn't a workaround at all, just a display setting at that point.
i cannot repro this behavior (i followed the first instruction) neither in a m124 nor in a newer version (m164) with a jdbc 3.1.12 driver and text field in the form - it works fine in the query and in bounded form (the problem with the 'setting loose' in newer versions atjensen described is an other topic and can be workarounded if you click into any other field after inserting your settings, then it is saved) has anybody a further hint for me to repro this issue?
@ clu it works if you use an English localisation it doesn't work if you use a German localisation I found this in a discussion with marc (msc). there must be something "wrong" with the comma.
clu->mechtilde: 1. is the 'localisation thing' a guess or have you seen that? 2. what localisation you mean: system localisation or oo localisation (tools/options/) 3. what means work ('it works like it should' or 'the bug works')?
i tried it like described above with an english and a german system language and engish/german oo language setting in a src680m171 and nothing gets truncated => everything work like it should - probably anything differs
the localisation thing I saw as discussed with msc in IRC on 26.04.06 I change to a x-term, change the localisation with "export Lang=en_US" then start soffice on console. OOo started in English. Then The Sum is right as msc said ODBC use the system locale. "Right" means, the the SUM has the right decimal and not "00" this for the first I will test it also with version >=m171 at home
Also in m172 I have the same situation. This and the depended issue 59676 doesn't show the right result when you use the German system localisation I use Debian Sarge In the English system localisation the result is right. Mechtilde
reopen issue
clu->msc: may you have a look at that thx
I can reproduce the following behaviour 1. create the following table CREATE TABLE `issue_58101` ( `id` decimal(10,2) NOT NULL default '0.00', `test` varchar(100) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2. insert the following data INSERT INTO `issue_58101` VALUES ('1.10','1.2'),('2.20','2,3'); 3. create the query and execute it SELECT `id` + 2, `test`+ 1 FROM `test`.`issue_58101` AS `issue_58101` ==>> as you can see. The decimal field is correct but the textfield is wrong if a , is used as decimal point. -->> msc -> oj: please have a look.
Text types can not be used for calculation. MySQL tries to convert into a double value but when an error occurs it seems to cancel the calculation. I don't know if this is a MySQL or do you have to use number types?
-> closed
.