Bug 105101 - Firebird: Division of Decimal and Integer gives wrong values
Summary: Firebird: Division of Decimal and Integer gives wrong values
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.0.alpha0+
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Tamas Bunth
QA Contact:
URL:
Whiteboard: target:5.4.0
Keywords:
Depends on:
Blocks: Database-Firebird
  Show dependency treegraph
 
Reported: 2017-01-04 15:37 UTC by robert
Modified: 2017-02-02 17:52 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the query and have a look. Says all. (3.21 KB, application/vnd.oasis.opendocument.database)
2017-01-04 15:37 UTC, robert
Details
script for isql-fb for comparison (1.13 KB, text/plain)
2017-01-09 16:53 UTC, Terrence Enger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description robert 2017-01-04 15:37:46 UTC
Created attachment 130151 [details]
Open the query and have a look. Says all.

Open the attaches Firebird 3 database.
Open the query.
I tried to divide an Integer with 2:
Integer/2
1/2 = 0
This shows, as expected, wrong values, because there isn't defined a decimal-point.
So I tried Integer/2.0 , which works in HSQLDB.
Firebird divides with 2 and multiplicates the result with 10.
1/2.0 = 5

So I thought: Might be Firebird doesn't know how to handle the decimal point. Will try it with Decimal and a value with 2 digits.
1.00/2 = 50

Seems Firebird ignores the digits of the Decimal and works with 100 instead of 1.00. So I tried again with Decimal 2 digits and divide through 2.0
1.00/2.0 = 500

Tried all this with
Version: 5.4.0.0.alpha0+
Build ID: 2a4cd80abcf9e515d1ce3b3a944b573bdc42bff2
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-22_00:18:04
Locale: de-DE (de_DE.UTF-8); Calc: group

Firebird in this state is very experimental.
Comment 1 Buovjaga 2017-01-05 10:45:11 UTC
Confirmed.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.4.0.0.alpha0+
Build ID: 1a58cdf8af1aba52ce0a376666dd7d742234d7cf
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on January 4th 2016
Comment 2 Terrence Enger 2017-01-09 16:53:30 UTC
Created attachment 130280 [details]
script for isql-fb for comparison

Just in case there was any doubt, the problem is LibreOffice.  isql-fb
returns the expected results (abbreviating long column names in the
obvious way) ...

    ID  Int  Int/2  Int/2.0   D2D  D2D/2  D2D/2.0
    ==  ===  =====  =======  ====  =====  =======
     1    1      0      0.5  1.00   0.50    0.500
     2    2      1      1.0  2.00   1.00    1.000
     3    3      1      1.5  3.00   1.50    1.500
     4    4      2      2.0  4.00   2.00    2.000
     5    5      2      2.5  5.00   2.50    2.500
Comment 3 Lionel Elie Mamane 2017-01-10 07:52:52 UTC
It feels like a confusion between "." as thousand separator and decimal point.

It also happens in Tools/SQL.
Comment 4 robert 2017-01-25 20:18:34 UTC
Have tested this a little bit more:

SELECT CAST("Integer" AS FLOAT)/ 2.0 "Integer/2.0_Float",  CAST ("Decimal_2_Digit" AS FLOAT)/2.0 "Decimal_2_Digit/2.0_Float" FROM "Table"

gives the right value for both. The decimal point works right with Float (or Double Precision), but doesn't work with Decimal (or Numeric). If I set 

CAST("Integer" AS NUMERIC)/ 2.0

the result is as wrong as described in bug-report.

I will set this bug to version 5.3.0.0alpha0+, because the buggy behavior is introduced with Firebird 3.0.
Comment 5 Tamas Bunth 2017-01-29 13:43:13 UTC
It turns out that for computed numeric/decimal values firebird does not set subtype to either numeric or decimal.[1]

In isql and jaybird (jdbc driver for firebird) the problem is solved by checking not only for subtype but scale of the column too. So I decided to do the same in this patch:

https://gerrit.libreoffice.org/33660


[1] http://firebird.1100200.n4.nabble.com/Wrong-rdb-field-sub-type-for-numeric-computed-column-td4305957.html
Comment 6 Commit Notification 2017-01-30 14:58:57 UTC
Tamás Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4a193d39fb785c75668c977cf6b40d11b0ef4afe

tdf#105101 check scale to determine subtype

It will be available in 5.4.0.

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 7 robert 2017-02-02 17:52:19 UTC
Works now, also for other numeric functions.

Thanks Tamas.

Could this be backported to LO 5.3.1?