Commented by: @dyemanov

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.

Commented by: @hvlad

Can't resist :

Many users never read documentation.
Strangely enough I haven't found a bug report on this here although this should be a rather common and quite critical problem (c)

Commented by: @helebor

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.)

Try
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.

Commented by: Hilmar Brodner (syn)

Sorry, but in my eyes it's still a bug:

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...

Commented by: @dyemanov

13.3 is a NUMERIC(x, 1) literal because the SQL specification declares it being so. 13.3e0 is a double precision literal though.

Commented by: @hvlad

Hilmar,

read IB6 beta docs, Language Reference (available at http://www.ibphoenix.com/downloads/60LangRef.zip)

page 22 - Multiplication and Division

we want to migrate Fb25 to Fb4 this code below bad calculation.
Fb25 calculation is good. it is very important to avoid calculation errors and deviations
migration is impossible.
I will note it 1/3 result is 0 or 5/3 result is 1 is not good result. compilers minimum sign warning error
if result var as integer then good result but this sample result type not integer!!
if i will store 1/3 numeric(15,x) result is 0 wrong wery wrong
anything wroted in 20 years ago in documentation. but is doc no firebird doc

execute block
as
declare variable grossAmount numeric(17,4);
declare variable taxPercent numeric(17,4);
declare variable calc_vat_amount_1 numeric(17,8);
declare variable calc_vat_amount_2 numeric(17,8);
declare variable calc_vat_amount_3 numeric(17,8);
declare variable calc_vat_amount_4 numeric(17,8);
declare variable calc_15_16_a numeric(15,6);
declare variable calc_15_16_b1 numeric(15,6);
declare variable calc_15_16_b2 numeric(15,6);
declare variable calc_15_16_c numeric(15,6);
declare variable calc_15_16_d numeric(15,6);
declare variable calc_17_4_a numeric(17,4);
declare variable calc_17_4_b1 numeric(17,4);
declare variable calc_17_4_b2 numeric(17,4);
declare variable calc_17_4_c numeric(17,4);
declare variable calc_17_4_d numeric(17,4);

begin
taxPercent = 5;
grossAmount = 169558;

-- calculate vat amount from gross amount and tax percent
-- grossAmount and taxPercent from invoice table fields type numeric(17,4)
-- firebird 2.5 good calculate result all expression

-- expected vat amount 8074.190476;

-- expected 8074.190476
calc_vat_amount_1 = grossAmount * (1 - 100 / (100 + :taxPercent)); -- Fb4 8087.9166
calc_vat_amount_2 = grossAmount * (1 - 100.000000 / (100 + :taxPercent)); -- Fb4 8074.19049
calc_vat_amount_3 = grossAmount * (1 - 100 / (100.000000 + :taxPercent)); -- Fb4 8074.35196
calc_vat_amount_4 = grossAmount * (1 - 100.000000 / (100.000000 + :taxPercent)); -- Fb4 8074.190476 good!! only this one

-- expected 0,047619
calc_15_16_a = (1 - 100 / (100 + 5)); -- Fb4 1 wow
calc_15_16_b1 = (1 - 100.0 / (100.0 + 5.0)); -- Fb4 0,05 ???
calc_15_16_b2 = (1 - 100.00000 / (100.00000 + 5.00000)); -- Fb4 0.047619 ohh good!!
calc_15_16_c = (1 - 100 / (100 + taxPercent)); -- Fb4 0.0477 round why, and bad round?!!
calc_15_16_d = (1 - 100.0 / (100.0 + taxPercent)); -- Fb4 0,04762 round to why?!!

-- expected 0,0476
calc_17_4_a = (1 - 100 / (100 + 5)); --Fb4 1 ohhh
calc_17_4_b1 = (1 - 100.0 / (100.0 + 5.0)); --fb4 0.05 auuuu
calc_17_4_b2 = (1 - 100.00000 / (100.00000 + 5.00000)); --Fb4 0,0476 this OK!!!
calc_17_4_c = (1 - 100 / (100 + taxPercent)); --Fb4 0,0477 bad round?!!
calc_17_4_d = (1 - 100.0 / (100.0 + taxPercent)); --Fb4 0,0476 this ok

end

@kollerpal ,use FB40 with dialect 1, not with dialect 3.

@kollerpal ,use FB40 with dialect 1, not with dialect 3.

Or use double precision literals (or casts to double precision), to force the calculation itself to happen in double precision.

Suggesting dialect 1 is not good as 1) it has been deprecated since 2000 (if you read the IB 6.0 docs, it was only intended to exist for one version), and 2) Firebird 4.0 has formally announced it deprecated in the release notes, announcing future removal.

Deprecated but used by.....

#6980

#6928

https://www.mail-archive.com/search?l=firebird-devel@lists.sourceforge.net&q=subject:%22%5C%5BFirebird%5C-devel%5C%5D+Dialect+3+inconsistent+round%5C%2Ftrunc+%5C-+configurable+calculation+method+needed%22&o=newest&f=1

I'm locking this conversation as it is becoming repetitive. For support questions, please go to https://groups.google.com/g/firebird-support.