IBSurgeon Research Labs,

research@ib-aid.com

InterBase has an interesting undocumented feature. Usually when the COMPUTED BY field is declared, the following syntax is used:

<col_def> = col { datatype | COMPUTED [BY] (<expr>) |
domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ <col_constraint>]
[COLLATE collation]

As you see, syntax requires specifying either type of the column (datatype), or the calculated expression (computed by). The “either/or” directive is the | symbol. Usually, type of the COMPUTED BY field is similar to the source one (which it is based on). However, it is possible to specify column type, even if it would not coincide with the source.

The following experiment can be performed. Create a table with structure as shown below:

(the PRIMARY KEY definition may be omitted, since in this case it is used only for Database Explorer tables’ usability: BDE does not allow update tables, which do not have a primary key)

CREATE TABLE TESTCOMP(
t_data FLOAT NOT NULL PRIMARY KEY,
c_int INTEGER computed by (t_data),
c_num NUMERIC(15, 2) computed by (t_data),
c_char CHAR(20) computed by (t_data))

Now, in the table, try to enter a record with the following T_DATA value: 1.88, 3.2, 3.51 (this test was done in dialect 1). You would see that the values the FLOAT field stores on disk differ from what you have entered. The C_INT field contains the rounded value of T_DATA. The C_NUM field would contain either exact or rounded value of T_DATA. It depends on the parameter value of the BDE ENABLE BCD = TRUE/FALSE alias. At the same time, C_CHAR would contain more precise value of the C_DATA real number. When doing this trick, it would be helpful to view the NUMERIC(15, 2) values as strings. The thing is that real numbers’ accuracy cannot be stored as integers’ one, and therefore when one enters 1.88, in NUMERIC(15, 2) it would look as 1.88, though actually (as a string) will turn out to be 1.8799999952316.

Thus, we can make up several conclusions:

• real numbers’ accuracy is bounded, and therefore numbers stored as real, should never be used in equality (everyone knows that) precision of the FLOAT fields is quite short (similar to Delphi’s single). That is why it is better to use DOUBLE PRECISION instead.
• not all types are interconvertible: the NUMERIC(15, 2) field as INTEGER COMPUTED BY… will contain 0.
• inaccuracy should be taken into account when processing real numbers (rounding, aggregation, comparison, addition/subtraction and multiplication/division). Also, do not forget the bookkeeper’s rule: when multiplying and dividing, multiplication should be calculated in the first place.
• it is not recommended to use real types as table primary keys. Due to inaccuracy and/or peculiarities of how client’s and servers processors handle real numbers: seemingly one and the same number may lead to different results.