As outlined primarily on pages 22 and 23 of InterBase Language reference manual; mathmetical operations, primarily division, may provide different results in InterBase 6 dialect 3 versus InterBase 5 dialect 1. This is because InterBase 5 and Interbase 6 dialect 1 use double precision for mathmatical operations, whereas InterBase 6 dialect 3 uses the maximum precison used for the numbers in the calculation. This results in exact results for addition and subtraction (your pennies in accounting applications always come out right!) where they were not possible in InterBase 5, at the compromise of potentially having to cast some numeric results where you did not have to in the past.
Let's look at some examples. (Note that the rdb$database system table is used here because it is a table guaranteed to exist and to have 1 record.)
select 1/3 from rdb$database;
This resuls in 0 in InterBase 6 dialect 3 and 0.3333333333333333 in InterBase 5 and Interbase dialect 6 dialect 1.
InterBase 6 dialect 3 add the precisons of the numbers in involved. So,
select 1.0/3.0 from rdb$database;
results in 0.33 in Interbase 6 dialect 3. InterBase 5 and InterBase 6 dialect 1 still result in 0.3333333333333333. 0.33 is probably not what you are looking for, because:
select (1.0/3.0)*3 from rdb$database;
results in 0.99. To get what you want, cast one of the numbers as a double precision or float:
select 1/cast (3 as double precision) from rdb$database;
This concept applies to multiplication as well, but shouldn't be issue. Consider:
select 1.3*1.35*1.349 from rdb$database;
This returns 2.367495 in InterBase 6 dialect 3 and 2.367495000000000 in InterBase 6 dialect 1 and Interbase 5. Both numbers are numerically equal.
The upside to the way interBase 6 treats numbers is addition and subtraction. Consider:
select 1.300+1.35+1.349 from rdb$database;
This returns 3.999000000000001 in InterBase 5 and InterBase 6 dialect 1 and there is nothing you can really do about it. In InterBase 6 dialect 3 it returns what you expect, 3.999.
Note that numeric precision affects triggers and stored procedures. Consider the following stored procedure:
set term ^;
create procedure mytestprocedure
returns (x char(1))
as
declare variable counter decimal (10,0);
begin
counter = 0;
counter = counter + .05;
counter = counter + .05;
counter = counter + .05;
counter = counter - .05;
counter = counter - .05;
counter = counter - .05;
if (counter = 0) then
x='Y';
else
x='n';
end^
set term ;^
Now execute the procedure:
execedure mytestprocedure;
InterBase 5 and InterBase 6 dialect 1 this will return n! In Interbase 6 it will return Y as you would expect.
Last Modified: 12-SEP-02