Numeric type promotion in expressions
Derby Reference Manual
187
· Exact numeric
·
(storage based on precision)
·
)
Numeric type promotion in expressions
In expressions that use only integer types, Derby promotes the type of the result to at
least INTEGER. In expressions that mix integer with non-integer types, Derby promotes
the result of the expression to the highest type in the expression. The following table
shows the promotion of data types in expressions.
Table 11.
Type promotion in expressions
Largest Type That Appears in Expression
Resulting Type of Expression
DOUBLE PRECISION
DOUBLE PRECISION
REAL
DOUBLE PRECISION
DECIMAL
DECIMAL
BIGINT
BIGINT
INTEGER
INTEGER
SMALLINT
INTEGER
For example:
-- returns a double precision
VALUES 1 + 1.0e0
-- returns a decimal
VALUES 1 + 1.0
-- returns an integer
VALUES CAST (1 AS INT) + CAST (1 AS INT)
Storing values of one numeric data type in columns of another numeric data type
An attempt to put a floating-point type of a larger storage size into a location of a smaller
size fails only if the value cannot be stored in the smaller-size location. For example:
create table mytable (r REAL, d DOUBLE PRECISION);
0 rows inserted/updated/deleted
INSERT INTO mytable (r, d) values (3.4028236E38, 3.4028235E38);
ERROR X0X41: The number '3.4028236E38' is outside the range for
the data type REAL.
You can store a floating point type in an INTEGER column; the fractional part of the
number is truncated. For example:
INSERT INTO mytable(integer_column) values (1.09e0);
1 row inserted/updated/deleted
SELECT integer_column
FROM mytable;
I
---------------
1
Integer types can always be placed successfully in approximate numeric values, although
with the possible loss of some precision.
Integers can be stored in decimals if the DECIMAL precision is large enough for the
value. For example:
ij> insert into mytable (decimal_column)
VALUES (55555555556666666666);
ERROR X0Y21: The number '55555555556666666666' is outside the
