Issue 58101 - Arithmetics in SQL statement gets truncated in queries (MySQL/ODBC)
Summary: Arithmetics in SQL statement gets truncated in queries (MySQL/ODBC)
Status: CLOSED NOT_AN_OOO_ISSUE
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0 Beta
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: ocke.janssen
QA Contact: issues@dba
URL:
Keywords: needhelp, needmoreinfo
Depends on: 59676
Blocks:
  Show dependency tree
 
Reported: 2005-11-18 11:49 UTC by kaimartin
Modified: 2013-08-07 15:45 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description kaimartin 2005-11-18 11:49:36 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)>---
Comment 1 christoph.lukasiak 2006-02-14 13:38:19 UTC
what kind of driver do you use?
Comment 2 Mechtilde 2006-02-14 15:17:02 UTC
this seems to be similar to that what I describe in 
Issue 59676
Comment 3 christoph.lukasiak 2006-03-08 15:01:59 UTC
added keyword
Comment 4 Mechtilde 2006-03-08 17:28:34 UTC
I can confirm this.

At this time I use m158.

I have a similar Problem with the sum funktion.

see Issue 59676
Comment 5 christoph.lukasiak 2006-04-10 14:36:51 UTC
can anybody confirm this in a m162? (mechtilde has written that her similar
foult works in this version)

thx
Comment 6 drewjensen.inbox 2006-04-11 18:17:09 UTC
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.

Comment 7 christoph.lukasiak 2006-05-08 14:24:16 UTC
clu->kaimartin: does the last hint from atjensen (changing the format) help you
further / solve your problem?
Comment 8 kaimartin 2006-05-08 15:50:35 UTC
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)>---
Comment 9 drewjensen.inbox 2006-05-08 16:13:29 UTC
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.
Comment 10 christoph.lukasiak 2006-05-09 10:26:19 UTC
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?



Comment 11 Mechtilde 2006-05-09 11:21:41 UTC
@ 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.
Comment 12 christoph.lukasiak 2006-06-13 13:34:20 UTC
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')?
Comment 13 christoph.lukasiak 2006-06-13 13:40:34 UTC
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
Comment 14 Mechtilde 2006-06-13 16:11:44 UTC
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
Comment 15 Mechtilde 2006-06-13 19:14:42 UTC
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
Comment 16 christoph.lukasiak 2006-06-19 15:21:19 UTC
reopen issue
Comment 17 christoph.lukasiak 2006-06-19 15:23:18 UTC
clu->msc: may you have a look at that

thx
Comment 18 marc.neumann 2008-02-15 13:34:40 UTC
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.
Comment 19 ocke.janssen 2008-02-18 07:27:46 UTC
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?
Comment 20 Mechtilde 2008-03-25 12:09:49 UTC
-> closed
Comment 21 Mechtilde 2008-03-25 12:10:21 UTC
.