We have stumbled across this on a regular basis. Strangely enough I haven't found a bug report on this here although this should be a rather common and quite critical problem:
SELECT -- aim is to get 13,3% of 113 = 15,02900
-- With CAST
CAST((113 * (13.3 / 100)) AS NUMERIC(15,5)) AS "Wrong_1",
CAST((113 * (13.3 / 100.0)) AS NUMERIC(15,5)) AS "Wrong_2",
CAST((113 * 13.3 / 100) AS NUMERIC(15,5)) AS "Wrong_3",
\-\- Without CAST
\(113 \* \(13\.3 / 100\)\) AS "Wrong\_4",
\(113 \* \(13\.3 / 100\.0\)\) AS "Wrong\_5",
\(113 \* 13\.3 / 100\) AS "Wrong\_6",
CAST\(\(113 \* \(13\.3 / \(100 \+ 0\.00000\)\)\) AS NUMERIC\(15,5\)\) AS "Ok\_1",
CAST\(\(113 \* \(13\.3 / 100\.00000\)\) AS NUMERIC\(15,5\)\) AS "Ok\_2",
CAST\(113 \* \(13\.3 / \(100 \+ 0\.00000\)\) AS NUMERIC\(15,5\)\) AS "Ok\_3",
CAST\(113 \* \(13\.3 \* 0\.01\) AS NUMERIC\(15,5\)\) AS "Ok\_4",
CAST\(\(113 \* 13\.3 \* 0\.01\) AS NUMERIC\(15,5\)\) AS "Ok\_5",
\(113 \* 13\.3 \* 0\.01\) AS "Ok\_6"
The problem basically results from FB not adding the necessary amount of decimal digits, thus continuing to calculate with the initial amount, although the division should have added further digits.
The text was updated successfully, but these errors were encountered:
The rules for numerics are that the result of NUMERIC(A, X) / NUMERIC(B, Y) has the scale of exactly X + Y.
So everything you need is: 113 * (13.3 / 100.00). Or cast the every argument to the necessary scale. Or use double precision instead.
The SQL engine delivers just what you ask for. This is a support question, not a bug report. Post your examples to the firebird-support list and ask why your expressions are not getting the results you expect.
Read up about integer division, scale, precision and numeric types in standard SQL and you will start to understand why SQL arithmetic doesn't work like your pocket calculator.
Hint: Supply enough places of decimal in your operands to provide the scale you want in your result and to avoid being caught by integer division. (In SQL, 10/3 returns 3, which is correct. 5/3 returns 1, which is also correct.)
CAST((113.00* (13.300 / 100)) AS NUMERIC(15,5))
Please don't post bug reports until it is confirmed, via firebird-support first, and then in firebird-devel, that a bug exists.
Doing a SELECT with absolute values and getting different results depending on the way the calculation is written does not sound right to me. Maybe I'm expecting too much, but why does FB convert 13.3 to a NUMERIC(x.1) and not a double precision?
As for the documentation:
Please point me to the right place. I'm unable to find anything(!) on this matter. The only thing coming close to this ist something from Helen regarding the behaviour of Numerics in UNION-Selects in the FB 2.0 release notes...