Note that our handling of "approximate numeric" types is also incorrect according to the SQL standard. This specification does not address that problem, which should be dealt with elsewhere. The error is that the type which we call FLOAT, a 32-bit single-precision floating point datum, is supposed to be called REAL: according to the standard, a FLOAT declaration is followed by a precision declaration; a FLOAT may wind up being implemented the same as either REAL or DOUBLE, depending on the declared precision.

Up to release V5.0, a declaration such as NUMERIC(25,2) was accepted, and was treated the same is NUMERIC(15,2). In 6.0, a NUMERIC or DECIMAL declaration requesting precision greater than 18 digits is rejected with error isc_precision_err, which formerly read "precision should be greater than 0", but is now altered to "precision must be from 1 to 18". Declarations requesting 1 to 9 digits of precision have the same effect as in prior versions.

The 64-bit-integer types described above are available in all contexts where datatypes are defined or used, including the following:

- CREATE TABLE
- ALTER TABLE ADD
- CREATE DOMAIN
- Use a domain so defined to define a column with CREATE TABLE or ALTER TABLE ADD
- Arguments to, and returned values from, stored procedures
- Variables in stored procedures and triggers
- Arguments to, and returned values from, UDFs
- Arrays
- Unique or non-unique indices, primary keys, and foreign keys

MIN and MAX on an exact numeric column return an exact numeric result having the same precision and scale as the column. SUM and AVG on an exact numeric column return type NUMERIC(18,S) or DECIMAL(18,S), where S is the scale of the column. (The SQL standard specifies the scale of the result in such cases, while the precision of SUM or AVG on an exact-numeric column is implementation-defined: we define it as 18.)

If two operands OP1 and OP2 are exact numeric with scale S1 and S2 respectively, then OP1+OP2 and OP1-OP2 are exact numeric with precision 18 and scale the larger of S1 and S2, while OP1*OP2 and OP1/OP2 are exact numeric with precision 18 and scale S1+S2. (The scales of these operation except division are specified by the SQL standard. The standard makes the precision of all these operations, and the scale of divison, implementation-defined: we define the precision as 18, and the scale of division as S1+S2, the same as is required by the standard in the case of multiplication.) This means that many multiplication operations involving [NUMERIC|DECIMAL](9,m) datatypes, which caused arithmetic overflow error messages in V5.x, will return correct and meaningful 64-bit results in V6.0. This should be tested.

Whenever an arithmetic operation on exact numeric types overflows, V6 reports an overflow error, rather than returning an incorrect value. As a example which may not be obvious to the reader, if a DECIMAL(18,4) column contains the most-negative value of that type, -922337203685477.5808, an attempt to divide that column by -1 will report an overflow error, since the true result exceeds the largest positive value which can be represented in the type, namely 922337203685477.5807.

If one operand is exact numeric and the other is approximate numeric, then the result of any of the 4 dyadic operators is DOUBLE PRECISION. (The standard says that the result is approximate numeric with precision at least as great as the approximate numeric operand: we satisfy that requirement by always using DOUBLE, which is the largest-precision approximate numeric type we provide.)

Any value which can be stored in a DECIMAL(18,n) can also be specified as the default value for a column or domain.

Any value which can be stored in a DECIMAL(18,0) column can be supplied as the value on a SET GENERATOR statement.

Note that some but not all 19-digit numbers can be stored and retrieved in INT64s: we should test the most positive and most negative values, +9223372036854775807 and -9223372036854775808, respectively, as values inserted into a database, as the default value for a domain or a column, as a compared value in a WHERE clause, and as the value in a SET GENERATOR statement, and similar numbers with internal decimal points all the above mentioned contexts

As with numeric input, we should test for correct printing of the largest possible positive and negative values of exact types with various scales specified

Floating-point values are printed in ISQL and converted to text types in the engine in a consistent way. If the input is a DOUBLE PRECISION value with negative scale, that is, a InterBase Version 5.x NUMERIC(15,n) for some value of n, the column is printed or converted using the %f format specification of the C language's printf() family of subroutines, with the number of digits to the right of the decimal point equal to n. (In V5, ISQL printed DOUBLE PRECISION values with scale in this way, but the engine did not pay attention to the scale when carrying out a "CAST(value AS CHAR(n))" operation.) In all other cases, both ISQL and the engine use a %g format: they never emit more than 8 significant digits when formatting a FLOAT value, or 16 when formatting a DOUBLE PRECISION value. When the engine converts an approximate numeric value to a CHAR type which is longer than needed to store the maximum number of digits, the output is padded on the right with blanks. (This differs from V5.x, which would cast a DOUBLE PRECISION value to a long text type by printing dozens or hundreds of digits, even though a DOUBLE PRECISION column holds between 15 and 16 decimal digits of precision.) The printing or conversion of approximate types does not suppress trailing zeroes, nor does it suppress a decimal point which is followed only by zeroes: the reason is that a floating-point (and hence necessarily approximate) value should never look like an integer.

In isql's SHOW commands, such as SHOW DOMAINS and SHOW TABLE

INT64 values can be inserted, deleted, updated, selected, and used in "where" criteria via ISQL, DSQL, ESQL, and the API.

In tables RDB$FIELDS and RDB$FUNCTION_ARGUMENTS, if RDB$FIELD_TYPE is 7, 8, or 16 (SMALLINT, INTEGER, or INT64), then RDB$FIELD_SUB_TYPE has the following possible values:

0 or NULL: RDB$FIELD_TYPE is the defined type

1: The field is defined as NUMERIC

2: The field is defined as DECIMAL

This is in addition to the previously-documented uses of RDB$FIELD_SUB_TYPE with Blob and CHAR columns.

RDB$FIELD_PRECISION, a SMALLINT, is a new column in RDB$FIELDS and RDB$FUNCTION_ARGUMENTS. If the column was defined as NUMERIC or DECIMAL, the RDB$FIELD_PRECISION stores the defined precision. For example, if a column is defined as DECIMAL(13,4), the row in RDB$FIELDS describing that column has the following values: RDB$FIELD_TYPE is 16 (int64), RDB$FIELD_SUB_TYPE is 2 (decimal), RDB$FIELD_PRECISION is 13, and RDB$FIELD_SCALE is -4.

If there is a requirement to port V6 to a platform which lacks C compiler support of 64-bit integers, the porting effort will be major, involving implementation of a library of 64-bit integer arithmetic functions and/or macros, and recoding much arithmetic inside the engine in terms of calls to those library. Note that it would be quite as painful for the customers to write ESQL programs on such a platform as it would be for us to port the product to it: however, a 64-bit-integer-capable platform such as Win32 could connect to a server on such a platform and take advantage of the correct implementation of exact numeric types, so such a requirement, although unlikely, is conceivable.

To be more precise, defining columns and domains of the int64 types can be made to work easily, but getting the engine to translate a string of more than 9 digits into an int64, or implement infix arithmetic and the numeric built-in functions correctly as specified herein would be a major undertaking.

Client version | Dialect | Server version | ODS | Functionality |
---|---|---|---|---|

5.0 | 1 | 5.0 | 8,9 | V5 functionality |

5.0 | 1 | 5.0 | 10 | Server cannot open the database. |

5.0 | 1 | 6.0 | 8,9 | Server cannot open the database. |

5.0 | 1 | 6.0 | 10 | Fields which were defined using dialect 1 or a previous release behave in the old way. Definition of new fields also behaves the old way. Columns defined using the new 64-bit data types cannot be accessed. The least significant 32 bits of a generator are returned as an integer. Computations yield the results and result-types of V5. |

6.0 | 1 | all | all | The same as a V5 client with the same server and ODS |

6.0 | 2 | 5.0 | 8,9 | Error, server does not understand the dialect. Client library must check for this. |

6.0 | 2 | 5.0 | 10 | Server cannot open the database. |

6.0 | 2 | 6.0 | 8,9 | Server cannot open the database. |

6.0 | 2 | 6.0 | 10 | Data access for columns created with V5 functionality is the same as in V5; for columns created with V6 it is the same as with V6. All defines involving numeric or decimal data types produce V6 functionality, but also generate a warning message, since behavior differs between V5 and V6. |

6.0 | 3 | 5.0 | 8,9 | Error, server cannot understand the dialect. Client library must check for this. |

6.0 | 3 | 5.0 | 10 | Server cannot open the database. |

6.0 | 3 | 6.0 | 8,9 | Server cannot open the database. |

6.0 | 3 | 6.0 | 10 | Data access for columns created with V5 functionality is the same as in V5; for columns created with V6 it is full new functionality. All defines involving numeric or decimal data use integral types. |

QLI, an unsupported old tool which is still shipped with the product on platforms which had InterBase V3, cannot handle an INT64 value.

If at least one operand of a division operator has an approximate numeric type (FLOAT, REAL, or DOUBLE PRECISION) then the result of the division will be DOUBLE PRECISION, and will be exactly the same as that produced in V5 by division on any operand types. However, if both operands of a '/' operator have any exact numeric types, the result will be exact numeric with scale equal to the sum of the scales of the operands.

Here is a brief example:

define table t1

(i1 integer, i2 integer, n1 numeric(16,2), n2 numeric (16,2));

insert into t1 values (1, 3, 1.00, 3.00);

commit;

(i1 integer, i2 integer, n1 numeric(16,2), n2 numeric (16,2));

insert into t1 values (1, 3, 1.00, 3.00);

commit;

"select i1/i2 from t1" returns the integer value 0, because each operand had a scale of 0, so the scale of the result is 0, meaning that the result must be an integer.

"select i1/n2 from t1" or "select n1/i2 from t1" returns the numeric(18,2) value 0.33. Since one operand has scale 0 and the other has scale 2, the result has scale 0 + 2=2.

"select n1/n2 from t1" returns the numeric(18,4) value 0.3333. Since each operand has scale 2, the result has scale 2 + 2=4.

In V5, any of the / operations above would have returned the double precision value 0.3333333333333333. If a user wishes to obtain in V6 dialect 3 the results which he would have obtained in V5, he will need to alter his query to cast at least one of the operands into an approximate type

For example:

select i1 / cast(i2 as double precision) from t1;

Similarly, the result of taking an AVG on an exact numeric column has the same scale as the column itself: for example, the average of an INTEGER column would have type NUMERIC(18,0). If the values stored the column in different rows are 1, 1, 3, -3, and 0, then SUM(column_name) will return the integer 2, and AVG(column_name) will return the integer 0 in dialect 3, while a V5 system would have returned the double precision floating point value 0.2000000000000. To obtain from a V6 dialect 3 database the same result which would have been returned by a V5 system, the customer might change his query from

SELECT AVG(column_name) FROM table_name;

to
SELECT AVG( CAST (column_name AS DOUBLE_PRECISION)) FROM table_name ;

The examples shipped with V6 on platforms supporting GPRE shall include a subroutine which will perform the necessary formatting in a manner which preserves all the information contained in the int64 data, as well as inserting the decimal point in the right place when the scale is not 0. The function print_item_numeric in isql/isql.e can provide a starting point for the required example code.

Условия конфиденциальности

Copyright iBase.ru © 2002-2022