rounding error with UPDATE and sql-calculation on FIXED

[Former Member]

Hello List -

don't have found relevant posts with keyword "FIXED" so I post one -

caused by some troubles of one our application developers (who has found a workaround for his problem - means: this is no emergency)

I am experiencing a quite impressing effect on my MaxDB installations v7.6.05.09 AND v7.6.06.03 - found on Linux/Windows 32/64 bit

(DBs are checked, recovered or simple fresh - does not matter)

We are Using UPDATE-statements containing calculation to update a FIXED column which produces rounding errors.

The appended SQL-script produced "stable-errors" on every tested MaxDB installation v7.6.05.09/v7.6.06.03.

On a MaxDB v7.7.06.09 (Windows 32/64 bit) this effect is not reproducable

Maybe anyone can give me a hint what is going wrong with this kind of FIXED-column calculations/rounding error in case of updating the FIXED.

If noone else can reproduce this effect (with above versions), it would be interesting too.

Best regards

Harald Flaucher

// Create a temporary table for demonstration
// (using Database Studio v7.7.06.09)
//
create table "ADMIN"."BAUTEST"(
      "BAUTEST_NR" INTEGER not null,
      "BAUTEST_FIXED" FIXED (15,2),
      "BAUTEST_FLOAT" FLOAT (17),
primary key ("BAUTEST_NR"))
//
// Fill test data into table
//
insert into "ADMIN"."BAUTEST" values(1,31.29,31.29)
//
insert into "ADMIN"."BAUTEST" values(2,32.29,32.29)
//
insert into "ADMIN"."BAUTEST" values(3,33.29,33.29)
//
// this "insert-calculation" works perfectly!!
insert into "ADMIN"."BAUTEST" values(4, 200.00 - 200.00 + 34.29, 200.00 - 200.00 + 34.29)
//
// Now lets update a row with sql-calculation
//   - the FIXED-column
UPDATE BAUTEST SET BAUTEST_FIXED = 200.00 - 200.00 + 38.29 WHERE BAUTEST_NR = 1
//
//   - the FLOAT-column
UPDATE BAUTEST SET BAUTEST_FLOAT = 200.00 - 200.00 + 38.29 WHERE BAUTEST_NR = 1
//
// lets take a look at the result:
SELECT * FROM BAUTEST
//
// DROP the temporary table...
DROP TABLE BAUTEST
//
//
// Result:
//
// BAUTEST_NR;BAUTEST_FIXED;BAUTEST_FLOAT;
// 1;         38.30;        38.29  <-- DEVINITV WRONG
// 2;         32.29;        32.29
// 3;         33.29;        33.29
// 4;         34.29;        34.29  <-- really funny - the insert-calculation works 

[Former Member · Accepted solution]

Hallo,

you can change this parameter to YES.

In your example it may help.

But it canNOT be assured that it will help in all cases as one byte is checked, which should not be checked.

And depending on its value (NOT really used, therefore not initialized) the problem may occur again or not.

The workaround to calculate on client side seems to be much better.

Elke

[Former Member · Accepted solution]

Ok - thank you,

with this info's I expect lesser surprises.

Thank's for the spend time - best regards

Harald

[Former Member]

Hi,

I am not astonished, that putting FIXED around the substraction does not help.

As the result of the substraction, the 0 looks quite nice internally, there is no need for, and no help from putting FIXED around.

I do not catch why it helps when Lars tried this.

On the other hand:

Leaving this calculation to the kernel, when only literals are included (and one could do it in mind) is not the most clever method.

I hope that this is only a shortened example.

And using parameter should do better, even with the full calculation done in the kernel.

How does you workaround look like?

Elke

[Former Member]

Hi,

my post with the

UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00 + 38.29,15,2) WHERE BAUTEST_NR = 1

maybe was missleading.

I've tried the original variant of Lars with no success.

Then I've tried some other variants (ok - all I can think of)

All with no success. The rounding error is resistant.

The SQL-script helped me to stable reproduce the error and quick-test on several systems.

I can confirm "UPDATE BAUTEST SET BAUTEST_FIXED = 200.00 - 200.00 WHERE BAUTEST_NR = 1" delivers a perfect "0.00" to the DB.

In our case the workaround was simple to realize - the calculation will be done by the application

(was not too much code in this case)

I think proggies could use that column as a FLOAT too - but this conclusion came up later

The reason to try to clarify this effect is the imagination, someone is calculating in this way without knowledge of this and loose (or earn) 1Cent per update

In case of a bug (not simple restricted to our systems because of missconfiguration) this should be communicated or be fixed. ( MaxDB v7.7 seemed to me already be fixed )

Best regards,

Harald Flaucher

[Former Member]

Hallo,

ooops, you've found a bug.

With update (different code-part than insert --> insert works correctly, but update not) there the final check/rounding into a fixed column missed a -1. Therefore it 'saw' the byte behind the real value causing the reproduceable trouble in your case, but will not cause trouble in every case.

The difference between 7.6 and 7.7 is: You are using installation parameter EnableVariableInput with YES (you should do so). With this setting, the value-handling is done differently, using a slightly (but in this context) important difference in code-path and therefore you do not see this problem.

Thank you for finding the bug and sorry for any inconveniences

Elke

[Former Member]

Hello again,

meanwhile I've tried that test with setting the support-parameter

USEVARIABLEINPUT=YES

on a testsystem.

Now the calculation is processed correctly and would solve the problem.

Application seems to work without probs.

The parameter ist described as "new" in v7.6.x but set to YES by default on a MaxDB v7.7

Do I have to fear any side-effects if I enable this - and if any - which kind would/could this effects be

Kind regards

Harald

[Former Member]

Thanks for the quick reply -

on first sight it sounds conclusive.

I've tried the statement but the rounding error is still there.

Even a

UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00,15,2) + 38.29 WHERE BAUTEST_NR = 1

or

UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00 + 38.29,15,2) WHERE BAUTEST_NR = 1

delivers the rounding error to me.

On the other hand - why does the origin "instert-calculation" the job?

Kind regards

Harald

[lbreddemann]

>

> Thanks for the quick reply -

> on first sight it sounds conclusive.

> I've tried the statement but the rounding error is still there.

>

> Even a

>

> UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00,15,2) + 38.29 WHERE BAUTEST_NR = 1
> 

>

> or

>

>

> UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00 + 38.29,15,2) WHERE BAUTEST_NR = 1
> 

>

> delivers the rounding error to me.

You have to place the FIXED() around the substraction only!

It's all about the internal representation of the intermediate result here and the data type that is concluded from it.

> UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00) + 38.29 WHERE BAUTEST_NR = 1

> On the other hand - why does the origin "instert-calculation" the job?

Sorry, I cannot (and will not) comment on that.

regards,

Lars

[lbreddemann]

> Maybe anyone can give me a hint what is going wrong with this kind of FIXED-column calculations/rounding error in case of updating the FIXED.

> If noone else can reproduce this effect (with above versions), it would be interesting too.

It's reproducible but not necessarily a bug.

Let's call it an ugliness, ok?

If you explicitly convert the result of your subtraction to a fixed data type you don't get the error:

UPDATE BAUTEST SET BAUTEST_FIXED = FIXED(200.00 - 200.00) + 38.29 WHERE BAUTEST_NR = 1
                                   ^^^^^

As a rule of thumb for MaxDB: whenever you want to perform something with the intermediate results from calculations or any other kind of manipulation - cast your data type manually!

regards,

Lars