> Early today Igor Lobov posted a case to the Tracker, regarding the results he was
> seeing for the following math operation in a dialect 3 database:
>
> select 1.0 / 1440.0 * 1440.0 from rdb$database
>
> Firebird returns a result of 0.
>
> Vlad Khorsun posted that the result is correct, based on Firebird handling of math
> operations, he quoted the Language Reference:
>
> Multiplication
> If both operands are exact numeric, multiplying the operands produces an exact numeric
> with a precision of 18 and a scale equal to the sum of the scales of the operands
> Division
> If both operands are exact numeric, dividing the operands produces an exact numeric with
> a precision of 18 and a scale equal to the sum of the scales of the operands
>
> So, 1.0 / 1440.0 == 0.00, since numeric(1, 1) / numeric(4, 1) => numeric(18, 2)
Should be numeric(2, 1) / numeric(5, 1) => numeric(18, 2), my mistake. But it change
nothing ;)
> I would to suggest the current scale rules are not correct, because the current result is
> not logical.
I want to cite SQL standard, just to make things more clear :
6.26 <numeric value expression>
Syntax Rules
1) If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the
declared type of the result is an implementation-defined exact numeric type, with precision and
scale determined as follows:
a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is implementation-defined, and the
scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is implementation-defined, and the scale is S1+S2.
d) The precision and scale of the result of division are implementation-defined.
So, Firebird not violates standard and we have space for maneuver
> I think that the scale rule for Division (at the very least) should follow the rules used by
> MS SQL Server (these were the only ones I could find with 15 minutes of reviewing
> Bing results).
>
> <Paraphrasing>
> Precision is the number of digits in a number. Scale is the number of digits to the right of
> the decimal point in a number. So where Expression e1, with precision p1 and scale s1,
> and Expression e2, with precision p2 and scale s2. The scale of the result shall be as follows:
>
> Operation, Result scale
> Addition, max(s1, s2)
> Subtraction, max(s1, s2)
> Multiplication, s1 + s2
> Division, max(6, s1 + p2 + 1)
> </Paraphrasing>
>
> (the original link is http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx)
MSSQL supports precision up to 38, IIRC. Using its rule is problematical for our maximum
precision of 18. I think we could use it when longer exact numerics will be implemented.
Regards,
Vlad
|