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:
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
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.
Arch Linux 64-bit, KDE Plasma 5
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
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
It feels like a confusion between "." as thousand separator and decimal point.
It also happens in Tools/SQL.
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 22.214.171.124alpha0+, because the buggy behavior is introduced with Firebird 3.0.
It turns out that for computed numeric/decimal values firebird does not set subtype to either numeric or decimal.
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:
Tamás Bunth committed a patch related to this issue.
It has been pushed to "master":
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:
Affected users are encouraged to test the fix and report feedback.
Works now, also for other numeric functions.
Could this be backported to LO 5.3.1?