Fixed-Decimal (Scaled) Types - Firebird

Fixed-decimal types allow the management of numbers that need to be expressed with a fractional portion that is exact to a specific number of decimal places, or scale. Typically, you need scaled types for monetary values and any other numbers that result from counting or performing arithmetic on whole units and parts of units.

The predictability of results from multiplying and dividing fixed-point numbers favors choosing them for storing money values. However, because fixed-point types have a finite “window” in which numbers may be accommodated, they become prone to overflow/underflow exceptions near their upper and lower limits. In countries where the unit of currency represents a small value, number limits should be considered carefully.

For example, the following statement applies a tax rate (DECIMAL(5,4)) to a net profit (NUMERIC(18,2):

UPDATE ATABLE SET INCOME_AFTER_TAX = NET_PROFIT - (NET_PROFIT * TAX_RATE); Let the tax rate be 0.3333 Let the net profit be 1234567890123456.78 Result: ISC ERROR CODE:335544779 Integer overflow.

The result of an integer operation caused the most significant bit of the result to carry.

Firebird provides two fixed-decimal or scaled data types: NUMERIC and DECIMAL. Broadly, either scaled type is declared as TYPE(P, S), with P indicating precision (number of significant digits) and S indicating scale (number of decimal places—that is, digits to the right of the decimal-point symbol).

According to the SQL-92 standard, both NUMERIC and DECIMAL constrain the stored number to be within the declared scale. The difference between the two types is in the way the precision is constrained. Precision is exactly “as declared” for a column of type NUMERIC, whereas a DECIMAL column can accept a number whose precision is at least equal to that which was declared, up to the implementation limit.

NUMERIC and DECIMAL types, as implemented in Firebird, are identical except when precision is less than 5. Both types effectively conform to the standard DECIMAL type. NUMERIC is thus not compliant with SQL-92.

Internally, Firebird stores the scaled number as a SMALLINT (16 bits), INTEGER (32 bits), or BIGINT (64 bits) type, according to the size of precision declared. Its declared precision is stored, along with the declared scale negated to a sub-zero scale- factor, representing an exponent of 10. When the number is referred to for output or a calculation, it is produced by multiplying the stored integer by 10scale_factor.

For example, for a column defined as NUMERIC(4,3), Firebird stores the number internally as a SMALLINT. If you insert the number 7.2345, Firebird silently rounds the rightmost digit (4) and stores a 16-bit integer 7235 and a scale_factor of –3. The number is retrieved as 7. 235 (7235 * 10-3).

NUMERIC Data Type

The format of the NUMERIC data type is

NUMERIC(p,s)

For example, NUMERIC(4,2) formally defines a number consisting of up to four digits, including two digits to the right of the decimal point. Thus, the numbers 89.12 and 4.321 will be stored in a NUMERIC(4,2) column as 89.12 and 4.32, respectively. In the second example, the final 1-3 is out of scale and is simply dropped.

However, it is possible to store in this column a number of greater precision than that declared. The maximum here would be 327.67—that is, a number with a precision of 5. Because the database stores the actual number as a SMALLINT, numbers will not begin to cause overflow errors until the internally stored number is more than 32,767 or less than –32,768.

This is stored in the system table RDB$FIELDS as RDB$FIELD_PRECISION. This is stored in the system table RDB$FIELDS as RDB$FIELD_SCALE.

DECIMAL Data Type

The format of the DECIMAL data type is

DECIMAL(p,s)

Similar to NUMERIC, DECIMAL(4,2) formally defines a number consisting of at least four digits, including two digits to the right of the decimal point. However, because Firebird stores a DECIMAL of precision 4 and below as INTEGER, this type could, in a DECIMAL(4,1) column, potentially store a number as high as 214,748,364.7 or as low as –214,748,364.8 without causing an overflow error.

Exact numerics can be confusing, not just because of the slightness of difference between the two types but also because the dialect of the database affects the range of precision available. Table can assist as a summary guide for the precision and scale you need to specify for your various exact numeric requirements.

Range and Storage Type of Firebird NUMERIC and DECIMAL Types

Range and Storage Type of Firebird NUMERIC and DECIMAL Types

Converted Databases

If a dialect 1 database is upgraded to dialect 3 using a gbak backup and then restored, numeric fields defined with precision higher than 9 will remain implemented as DOUBLE PRECISION. Although they will still appear as they were originally defined (e.g., NUMERIC(15,2)), they will continue to be stored and used in calculations as DOUBLE PRECISION.

For more information about converting dialect 1 databases to dialect 3, refer to the section titled “Special Migration Topic” at the end of the last chapter.

Special Restrictions in Static SQL

The host languages of embedded applications cannot use or recognize small precision NUMERIC or DECIMAL data types with fractional portions when they are stored internally as SMALLINT or INTEGER types. To avoid this problem, in any database that is going to be accessed via embedded applications (ESQL)

  • Do not define NUMERIC or DECIMAL columns or domains of small precision in a dialect 1 database. Either store an integer and have your application code deal with scale, or use DOUBLE PRECISION and apply a suitable rounding algorithm for calculations.
  • In a dialect 3 database, define NUMERIC and DECIMAL columns or domains of any size using a precision of at least 10, to force them to be stored internally as BIGINT. Specify a scale if you want to control the precision and scale. Apply CHECK constraints if you need to control the ranges.

Behavior of Fixed Types in Operations

Division

When performing division on fixed types, dialects 1 and 3 behave differently.

In dialect 3, where both operands are of a fixed numeric type, Firebird adds together the scales of both operands to determine the scale of the result (quotient). The quotient has a precision of 18. When designing queries with division expressions, be aware that quotients will always have more precision than either of the operands, and take precautions where precision could potentially overflow the maximum of 18.

In dialect 1, division always produces a quotient of DOUBLE PRECISION type.

Examples

In dialect 3, the quotient of dividing a DECIMAL(12,3) by a DECIMAL(9,2) is a DECIMAL(18,5). The scales are added together:

SELECT 11223344.556/1234567.89 FROM RDB$DATABASE

This yields 9.09090.

Compare the difference in the quotient when the same query is run in dialect 1. The first operand is treated as a DOUBLE PRECISION number because its precision (12) is higher than the maximum for a dialect 1 scaled type. The quotient is also a DOUBLE PRECISION number. The result is 9.09090917308727 because of the errors inherent in floating-point types.

From the following table defined in dialect 3, division operations produce a variety of results:

The following query returns the NUMERIC(18,2) value 0.33, since the sum of the scales 0 (operand 1) and 2 (operand 2) is 2:

SELECT i1/n2 from t1

The following query returns the NUMERIC(18,4) value 0.3333, since the sum of the two operand scales is 4:

SELECT n1/n2 FROM t1

Integer/Integer Division

Using the preceding example, the following query in dialect 3 returns the integer 0 because each operand has a scale of 0, so the sum of the scales is 0:

SELECT i1/i2 FROM t1

In dialect 1, in line with many other DBMS software, dividing one integer by another produces a floating-point result of DOUBLE PRECISION type:

SELECT 1/3 AS RESULT FROM RDB$DATABASE

This yields .333333333333333.

Although this dialect 1 rule is intuitive for language programmers, it does not conform to the SQL-92 standard. Integer types have a scale of 0, which, for consistency, requires that the result (quotient) of any integer/integer operation conform with the scaling rules for fixed numerics and produce an integer.

Dialect 3 conforms to the standard and truncates the quotient of integer/integer division operations to integer. Hence, it can trap the unwary:

SELECT 1/3 AS RESULT FROM RDB$DATABASE

This yields 0.

When you need to preserve sub-integer parts in the result (quotient) of integer/integer divisions in dialect 3, be sure that you either scale one of the operands or include a “factor” in the expression that will guarantee a scaled result.

Examples:

SELECT (1 + 0.00)/3 AS RESULT FROM RDB$DATABASE

This yields .33.

SELECT (5 * 1.00)/2 AS RESULT FROM RDB$DATABASE

This yields 2.50.

Dialect 1 Database with Dialect 3 Client

A dialect 1 database that is opened with a dialect 3 client may cause some surprises with respect to integer division. When an operation does something that causes a CHECK condition to be checked, or a stored procedure to be executed, or a trigger to fire, the processing that takes place is based on the dialect under which the CHECK, stored procedure, or trigger was defined, not the dialect in effect when the application causes the check, stored procedure, or trigger to be executed.

For example, suppose that a dialect 1 database has a table MYCOL1 INTEGER, and MYCOL2 INTEGER with a table definition includes the following CHECK condition that was defined when the database was dialect 1:

CHECK(MYCOL1 / MYCOL2 > 0.5)

Now suppose that a user starts isql, or an application, and sets the dialect to 3. It tries to insert a row into the converted database:

INSERT INTO MYTABLE (COL1, COL2)
VALUES (2,3);

Because the CHECK constraint was defined in dialect 1, it returns a quotient of 0.666666666666667, and the row passes the CHECK condition. The reverse is also true. If the same CHECK constraint were added to the dialect 1 database through a dialect 3 client, dialect 3 arithmetic is stored for the constraint. The preceding INSERT statement would fail because the check would return a quotient of 0, violating the constraint.

Multiplication and Division

If both operands are exact numeric, multiplying the operands produces an exact numeric with a scale equal to the sum of the scales of the operands, for example:

The following query returns the number 1492.25076 because n1 has a scale of 2 and n2 has a scale of 3. The sum of the scales is 5.

SELECT n1*n2 FROM t1

In dialect 3, the precision of the result of multiplying a fixed numeric by a fixed numeric is 18. Precautions must be taken to ensure that potential overflows will not result from the propagation of scale in multiplication.

In dialect 1, if the propagation of scale caused by the calculation would produce a result with precision higher than 9, the result will be DOUBLE PRECISION.

Addition and Subtraction

If all operands are exact numeric, adding or subtracting the operands produces an exact numeric with a scale equal to that of the largest operand, for example:

The query returns 135.243, taking the scale of the operand with the larger scale. Similarly, the following query returns the numeric –111.003:

SELECT n1 - n2 FROM t1;

In dialect 3, the result of any addition or subtraction is a NUMERIC(18,n). In dialect 1, it is a NUMERIC(9,n), where n is the scale of the larger operand.

Numeric Input and Exponents

Any numeric string in DSQL that can be stored as a DECIMAL(18, S) is evaluated exactly, without the loss of precision that might result from intermediate storage as a DOUBLE. The DSQL parser can be forced to recognize a numeric string as floating-point by the use of scientific notation—that is, appending the character “e” or “E” followed by an exponent, which can be zero.

For example, DSQL will recognize 16.92 as a scaled exact numeric and passes it to the engine in that form. On the other hand, it will treat 16.92E0 as a floating-point value.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics