SQL Numeric Types

SQL numeric data types include integer, fixed point types, and floating point types. NUMBER is a NuoDB extension that differs from the SQL standard definition for this fixed point type.

The number of digits in a numeric type can be no more than 25000. This applies to all numbers: positive, negative, stored numbers and numbers that are part of calculations.

About Storage in SQL

Numeric data does not have a concept of storage length. Partly because of this, the data storage algorithm does not impose a penalty for specifying the size of a field as larger than actually required.

Integer Types: SMALLINT, INTEGER, BIGINT

Range of values for integer types are as follows:

Type Lowest Highest
SMALLINT -32768 32767
INTEGER -2147483648 2147483647
BIGINT -9223372036854775808 9223372036854775807

If you assign a DOUBLE to an integer type, or if you cast it to an integer type, it is rounded to an integer type. The rounding is to the nearest value, where .5 and higher are rounded up. For example, CAST(0.5 AS INTEGER) returns 1.

Fixed Point Types: NUMERIC, DECIMAL, NUMBER

The DECIMAL data type is an alias for the NUMERIC data type. For a NUMERIC fixed point type, the precision and scale are usually specified using the following syntax:

NUMERIC [ (precision [,scale]) ]

DECIMAL [ (precision [,scale]) ]

where

• precision is the number of digits in the number.
• scale is the number of digits to the right of the decimal point in the number.

If precision is not specified the default is 18. If scale is not specified the default is 0. If you want to specify scale then you must also specify precision. For example, a column declared:

DECIMAL(6,4)

has values with 6 digits, 4 of which are after the decimal point, such as 12.3456.

Limits for fixed point types are determined by the specified precision and scale. If a value has a scale that is greater than allowed, then it is rounded to fit. A value of 5 or more is rounded up. If a value has a greater precision than is allowed then it is rejected as an integrity violation. However, if rounding is needed then NuoDB SQL rounds the value before it evaluates the number of digits in the value. For example:

SQL> select cast(12.345 as numeric(4,2)) from dual;
12.35

In the previous example, although the original number has 5 digits, the rounding reduces the number to 4 digits and this puts the number within the (4,2) specification. Now consider this example:

SQL> select cast(12.345 as numeric(3,2)) from dual;
Numeric Overflow: 12.35 does not fit in precision scale (3,2)

NuoDB SQL rounds first, as you can see by the 12.35 in the error message. The rounded value has 4 digits but the precision specification allows only 3 digits so the value is rejected.

Values with fixed point type, and precision greater than 18, have larger ranges than INTEGER types. After 19 digits these values are stored using an internal data type designed for large integers, allowing larger ranges than INTEGER types, and not restricted to ranges specified for BIGINT.

You can think of the NuoDB NUMBER data type as a DECIMAL or NUMERIC type without a specified maximum precision and scale. A NUMBER type is limited to no more than 25000 digits just like the other NuoDB SQL numeric types.

Although NuoDB does support the NUMBER data type, it is recommended that NUMBER only be used when the precision and scale value of the data is not known. The reasons for this are:

1. When the NUMBER data type is used, and the scale of the data (the number of digits after the decimal point) is greater than 38, the results of sorting can be unpredictable, as NuoDB will only use the first 38 characters to determine ordering.
2. Storing a NUMBER data type, no matter how small and no matter the scale, will require extra storage space.

Example

Shows the NuoDB handling of NUMBER data type versus DECIMAL data type with precision and scale values provided.

USE test;
CREATE TABLE test_tab (column1 NUMBER);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678901);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678908);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678905);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678903);

/* sorting only uses the first 38 characters of the scale value */
SELECT * FROM test_tab ORDER BY column1;
COLUMN1
-------------------------------------------
1.12345678901234567890123456789012345678901
1.12345678901234567890123456789012345678908
1.12345678901234567890123456789012345678905
1.12345678901234567890123456789012345678903

DROP TABLE IF EXISTS test_tab;
CREATE TABLE test_tab (column1 DECIMAL(42,41));
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678901);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678908);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678905);
INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678903);

SELECT * FROM test_tab ORDER BY column1;
COLUMN1
-------------------------------------------
1.12345678901234567890123456789012345678901
1.12345678901234567890123456789012345678903
1.12345678901234567890123456789012345678905
1.12345678901234567890123456789012345678908

Floating Point Types: DOUBLE

NuoDB supports DOUBLE as a floating type.

The following table shows the range of values for DOUBLE:

Type Lowest Highest
DOUBLE -1.7976931348623157E+308 -2.2250738585072014E-308
0
2.2250738585072014E-308 1.7976931348623157E+308

About NaN, Inf, -Inf

NuoSQL supports NaN, Inf, -Inf, and Null as values for DOUBLE. There is no concept of -Nan. The order of values with regard to ORDER BY, from lowest to highest, is Null, Nan, -Inf, Inf. When comparing (that is, if Nan or Null is greater than or less than any other value) the comparison always returns false. For example:

SELECT * FROM t2;

S1
------
-inf
nan
20
0
999.99
inf
-1000

SELECT * FROM t2 WHERE s1>0;

S1
------
20
999.99
inf

NuoDB treats FLOAT values the same as DOUBLE. Specifying (float)Nan is the same as specifying (double)Nan.

Aggregate functions supported by NuoDB include COUNT, AVG and SUM. These functions work with NaN, Inf, and -Inf as shown in the following table. COUNT works as expected. AVG and SUM report values as described in the following table, based on the kind of values specified as column values.

Kind of Values in Column Kind of Value Reported
Nan NaN
Inf Inf
-Inf -Inf
Any combination of NaN, Inf, or -Inf NaN