Chapter 3 – Numbers¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

For more information specific to CrateDB, check out the CrateDB Reference.

In SQL, a number – i.e.: any signed, or unsigned, combination of the digits 0 to 9 – is either an exact numeric value or an approximate numeric value. A numeric value may be a <literal>, the value of a parameter or a host language variable or the result of any expression or argument (including a possibly qualified <Column name>) that evaluates to a number.

Exact numeric values have a precision and a scale. The precision is a positive integer that determines the number of significant digits in the radix. The scale is a non-negative integer that specifies the number of digits to the right of the value’s decimal point. Exact numeric values are stored in one of the four exact numeric <data type>s: INTEGER, SMALLINT, NUMERIC or DECIMAL.

Approximate numeric values, or floating point numbers, have two parts: a signed decimal number (the mantissa) and a signed integer (the exponent). The exponent specifies the magnitude of the mantissa, so the value of such a number is the mantissa raised to the power of the exponent. Approximate numeric values also have a precision: a positive integer that specifies the number of significant bits in the mantissa. Approximate numeric values are stored in one of the three approximate numeric <data type>s: FLOAT, REAL or DOUBLE PRECISION.

Table of Contents

Numeric <literal>s ¶

A <numeric literal> is any number in one of two categories: the exact numeric integers and decimal numbers, and the approximate numeric floating point numbers.

<exact numeric literal>¶

An <exact numeric literal> is either an integer or a decimal number and its <data type> is exact numeric DECIMAL, though it is compatible with the INTEGER, SMALLINT, DECIMAL, NUMERIC, REAL, FLOAT and DOUBLE PRECISION <data type>s. The <literal>’s precision is the number of digits it contains and its scale is the number of digits to the right of its decimal point. A valid integer <literal> is a signed or unsigned decimal integer with an implicit scale of zero, e.g.: -65 or 476.

A valid decimal <literal> is a signed or unsigned decimal integer with an explicit scale (that can nevertheless default to zero), e.g.: 65 or -819.3 or .67 or -.02.

<approximate numeric literal>¶

An <approximate numeric literal> is a floating point number and its <data type> is approximate numeric FLOAT, though it is compatible with the INTEGER, SMALLINT, DECIMAL, NUMERIC, REAL, FLOAT and DOUBLE PRECISION <data type>s. The <literal>’s precision is the precision of its mantissa and its numeric value is the product of its mantissa raised to the power of its exponent. A valid <approximate numeric literal> is a floating point number consisting of a possibly signed decimal number (the mantissa) and a signed integer (the exponent), separated by the upper case letter “E”, e.g., -1.27982E+5 or .465E-7

Here are some equivalent <literal>s in “exact” and in “exponential” notation:

Exact	`REAL`	`DOUBLE PRECISION`
.0000000000000001	1.0000000E-15	1.00000000000000E-015
-0.1	-1.0000000E-01	-1.00000000000000E-001
1	1.0000000E+00	1.00000000000000E+000
+10	1.0000000E+01	1.00000000000000E+001
1000000000000000	1.0000000E+15	1.00000000000000E+015

In this example, we’ve shown the real and double precision numbers in a normalized form – with one digit before the decimal point. This is not mandatory, but strongly recommended. We also show a fixed number of digits after the decimal point so that maximum sizes will be apparent; in fact leading zeroes and signs, as well as post-decimal zeros are all optional. The one thing that is not optional is the letter “E” – always upper-case.

Numeric <data type>s ¶

A numeric <data type> is defined by a descriptor that contains four pieces of information:

The <data type>’s name: either INTEGER, SMALLINT, NUMERIC, DECIMAL, FLOAT, REAL or DOUBLE PRECISION.
The <data type>’s precision.
The <data type>’s scale (for exact numeric types).
Whether the <data type>’s precision and scale are expressed in decimal or binary terms.

INTEGER¶

The required syntax for an INTEGER <data type> specification is as follows.

INTEGER <data type> ::=
INTEGER

INTEGER may be abbreviated as INT; it defines a set of possibly signed whole numbers that have a scale of zero.

[NON-PORTABLE] INT’s precision must be greater than or equal to the precision of SMALLINT but is non-standard because the SQL Standard requires implementors to define INT’s precision. FIPS says that INT should have a precision of at least 9 digits. OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT as a 32-bit, signed binary numeric, i.e.: INT corresponds to the C long int data type. Thus, INT defines a set of values that are possibly signed whole numbers with a precision of 31 bits and a scale of zero, e.g., -6500 or 476673.

[NON-PORTABLE] INT’s radix must be the same as the radix chosen for SMALLINT but is non-standard because the SQL Standard requires implementors to define whether INT and SMALLINT have a binary radix or a decimal radix. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT and SMALLINT with a binary radix, i.e.: 2. This gives INT a valid range of -2,147,483,647 to +2,147,483,647.

SMALLINT¶

The required syntax for a SMALLINT <data type> specification is as follows.

SMALLINT <data type> ::=
SMALLINT

SMALLINT defines a set of possibly signed whole numbers that have a scale of zero.

[NON-PORTABLE] SMALLINT’s precision must be less than or equal to the precision of INT but is non-standard because the SQL Standard requires implementors to define SMALLINT’s precision. FIPS says that SMALLINT should have a precision of at least 4 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT as a 16-bit signed binary numeric, i.e.: SMALLINT corresponds to the C int data type. Thus, SMALLINT defines a set of values that are possibly signed whole numbers with a precision of 15 bits and a scale of zero, e.g., -65 or 476.

[NON-PORTABLE] SMALLINT’s radix must be the same as the radix chosen for INT but is non-standard because the SQL Standard requires implementors to define whether SMALLINT and INT have a binary radix or a decimal radix, i.e., 2. THis gives SMALLINT a range of -32,767 to +32,767. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT and INT with a binary radix, i.e.: 2. This gives SMALLINT a range of -32,767 to +32,767.

NUMERIC¶

The required syntax for a NUMERIC <data type> specification is as follows.

NUMERIC <data type> ::=
NUMERIC [ (precision[,scale]) ]

NUMERIC is a fixed-point numeric with a decimal precision and decimal scale that are equal to the explicit precision and the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally defined precision and optionally defined scale, e.g., or 65.73 or .6 or -819.3 or -.25.

The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. The minimum precision is 1.

[NON-PORTABLE] The default precision and the maximum precision for NUMERIC are non-standard because the SQL Standard requires implementors to define NUMERIC’s default and maximum precisions. Typically, the maximum precision is 15 (the FIPS requirement); it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of NUMERIC to range from 1 to 38, with a default precision of 1. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):

NUMERIC(4)

and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):

NUMERIC(1)
NUMERIC

The optional scale, if specified, is an unsigned integer, greater than zero, that defines the maximum number of digits in the scale of acceptable values. It must be less than the precision and defaults to zero if omitted. You may define a scale for NUMERIC only if you also define a precision: if no precision is defined, the scale must default to zero.

[NON-PORTABLE] The maximum scale for NUMERIC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define NUMERIC’s maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for NUMERIC. For example, this <data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):

NUMERIC(4,1)

DECIMAL¶

The required syntax for a DECIMAL <data type> specification is as follows.

DECIMAL <data type> ::=
DECIMAL [ (precision[,scale]) ]

DECIMAL may be abbreviated as DEC and is a fixed-point numeric with a decimal scale that is equal to the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally defined precision and optionally defined scale, e.g., 65.73 or .6 or -819.3 or -.25.

The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. DEC’s decimal precision must be at least equal to the precision you define – compare COBOL, which allows “PIC S9(3) COMP-1” but might allot a full-word “PIC S9(5)” for internal storage. The minimum precision is 1.

[NON-PORTABLE] The default precision, maximum precision and exact precision for DEC are non-standard because the SQL Standard requires implementors to define DEC’s default, maximum and exact precisions. Typically, the maximum precision is 15 (the FIPS requirement); it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of DEC to range from 1 to 38, with a default precision of 1. DEC’s decimal precision is equal to the precision you define, i.e.: OCELOT treats DEC and NUMERIC as synonyms. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):

DEC(4)

and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):

DEC(1)
DECIMAL

The optional scale, if specified, is an unsigned integer, greater than zero, that defines the maximum number of digits in the scale of acceptable values. It must be less than the precision and defaults to zero if omitted. You may define a scale for DEC only if you also define a precision: if no precision is defined, the scale must default to zero.

[NON-PORTABLE] The maximum scale for DEC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define DEC’s maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for DEC. For example, this <data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):

DEC(4,1)

FLOAT:¶

The required syntax for a FLOAT <data type> specification is as follows.

FLOAT <data type> ::=
FLOAT [ (precision) ]

FLOAT is a floating-point numeric with a binary precision; it defines a set of values that are possibly signed floating point numbers.

The optional precision, if specified, is an unsigned integer that defines the maximum number of bits (including the hidden bit) in the mantissa of acceptable values. FLOAT’s binary precision must be at least equal to the precision you define. The minimum precision is 1.

[NON-PORTABLE] The default precision, maximum precision and binary precision for FLOAT are non-standard because the SQL Standard requires implementors to define FLOAT’s default, maximum and exact precisions. FIPS says that FLOAT should have a binary precision of at least 20 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of FLOAT to range from 1 to 53, with a default precision of 53. Thus, FLOAT defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+ up to 14 digits+E+[sign]+ up to 3 digits

For example, -1.27982E+015 .465E-007. The IEEE Standard for Binary Floating-Point Arithmetic (IEEE Standard 754-1985) specifies two usual mantissa sizes: 24 and 53. OCELOT supports both: regardless of the actual precision specified for FLOAT, there are really only two possible results. If you define FLOAT with a precision that is less than or equal to 24, the actual binary precision will equal 24 bits in the mantissa. For example, these two <data type> specifications are equivalent: they both define a set of floating point values whose mantissa may range up to a precision of 24 bits:

FLOAT(12)
FLOAT(24)

If you define FLOAT with a precision between 25 and 53, the actual binary precision will equal 53 bits in the mantissa. For example, these three <data type> specifications are equivalent: they all define a set of floating point values whose mantissa may range up to a precision of 53 bits:

FLOAT
FLOAT(27)
FLOAT(53)

[NON-PORTABLE] The minimum exponent and the maximum exponent for FLOAT are non-standard because the SQL Standard requires implementors to define FLOAT’s minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -038 to +038 for FLOAT.

REAL¶

The required syntax for a REAL <data type> specification is as follows.

REAL <data type> ::=
REAL

REAL is a floating-point numeric with a binary precision, i.e.: REAL defines a set of values that are possibly signed floating point numbers.

[NON-PORTABLE] The binary precision of REAL must be less than the precision defined for DOUBLE PRECISION but is non-standard because the SQL Standard requires implementors to define REAL’s exact precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats REAL as a synonym for FLOAT(24). Thus, REAL defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+up to 6 digits+E+[sign]+ up to 2 digits

For example, -1.27982E+15 or .465E-07.

[NON-PORTABLE] The minimum exponent and the maximum exponent for REAL are non-standard because the SQL Standard requires implementors to define REAL’s minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -38 to +38 for REAL.

DOUBLE PRECISION¶

The required syntax for a DOUBLE PRECISION <data type> specification is as follows.

DOUBLE PRECISION <data type> ::=
DOUBLE PRECISION

DOUBLE PRECISION is a floating-point numeric with a binary precision, i.e.: DOUBLE PRECISION defines a set of values that are possibly signed floating point numbers.

[NON-PORTABLE] The binary precision of DOUBLE PRECISION must be greater than the precision defined for REAL but is non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION’s exact precision. FIPS says that DOUBLE PRECISION should have a binary precision of at least 30 digits. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats DOUBLE PRECISION as a synonym for FLOAT(53). Thus, DOUBLE PRECISION defines a set of values that are possibly signed floating point numbers with this format:

[sign]+digit+period+up to 14 digits+E+[sign]+up to 3 digits

For example, -1.27982E+015 or .465E-007

[NON-PORTABLE] The minimum exponent and the maximum exponent for DOUBLE PRECISION are non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION’s minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define an exponent ranging from -038 to +038 for DOUBLE PRECISION.

Warning

Throughout this section, we´ve shown examples of <numeric literal>s that conform to the various SQL <data type>s we´re describing. It is important to remember that your DBMS doesn´t see them that way. To an SQL DBMS, <exact numeric literal>s have a DECIMAL <data type> and <approximate numeric literal>s have a FLOAT <data type>.

Now that we’ve described SQL’s numeric <data type>s, let’s look at some example SQL statements that put them to use.

These SQL statements make a Table with four exact numeric Columns, insert a row, then search for any number less than -1.

CREATE TABLE Exact_Examples (
     occurrence_decimal DECIMAL(5),
     occurrence_numeric NUMERIC(7,2),
     occurrence_integer INTEGER,
     occurrence_smallint SMALLINT);
INSERT INTO Exact_Examples (
     occurrence_decimal,
     occurrence_numeric,
     occurrence_integer,
     occurrence_smallint)
     VALUES (12345, 12345, 12345, 12345);
SELECT occurrence_decimal,
       occurrence_numeric,
       occurrence_integer,
       occurrence_smallint
FROM   Exact_Examples
WHERE  occurrence_decimal < -1;

These SQL statements make a Table with three approximate numeric Columns, insert a row, then search for any number less than 50000.

CREATE TABLE Approximate_Examples (
     occurrence_float FLOAT(53),
     occurrence_real REAL,
     occurrence_double DOUBLE PRECISION);
INSERT INTO Approximate_Examples (
     occurrence_float,
     occurrence_real,
     occurrence_double)
     VALUES (5E+2, 5E+2, 5E+2);
SELECT occurrence_float,
       occurrence_real,
       occurrence_double
FROM   Approximate_Examples
WHERE  occurrence_float < 5E+4;

IEEE Binary Floats ¶

According to the IEEE Standard for Binary Floating-Point Arithmetic, “single-” and “double-precision” numbers are defined as follows.

`PRECISION`	`SIGN` `[BITS]`	`EXPONENT` `[BITS]*`	`MANTISSA` `[DECIMAL]`	`EXPONENT` `[DECIMAL]`	`RANGE` `[DECIMAL]`
Single	1	8	24	-38 to +35	7 digits
Double	1	11	53	-304 to +308	15 digits

The most significant mantissa bit is assumed to be 1. It is not stored.

You’d find the same specification in, say, an Intel FPU reference text or a C++ manual. But we found discrepancies when looking through documents for Java (where the exponent range is between -35 and +38), Delphi (where the exponent range is between -45 and +38 for single-precision and between -324 and -308 for double- precision), FIPS SQL (where the FLOAT exponent+size are 9+47 and the REAL exponent+size are 7+23). So, for portability reasons, it would be a good idea to avoid the extremes of the IEEE range.

Most DBMSs support IEEE float formats because FIPS requires that the decimal ranges be supported and because the DBMS code itself is written in a language that supports IEEE floats. But never does an official SQL standard tell vendors “how to store the data”. So it might be that your DBMS actually uses the IEEE sizes or it might be that your DBMS actually stores float decimal literals (as xBase does) and processes with base-10 arithmetic. If so, the following information doesn’t apply to you.

[Obscure Information] applies for the rest of this section.

Binary Floats are not exact. The danger with these numbers is easy to observe in a simple arithmetic exercise:

Represent the number one-third (1/3) in decimal. The maximum number of post-decimal digits (the scale) is large but not infinite. Result: 0.333333
Take the sum of three occurrences of this number. Result: 0.333333 + 0.333333 + 0.333333 = 0.999999
Note that the number is wrong (three thirds should equal 1). Increase the scale. Try again. You’ll never get the correct result because you can’t accurately represent 1/3 as a decimal fraction.

Now consider what would happen if your number was decimal, e.g.: one-hundredth (1/100). Try to represent that number as a binary fraction. If you have 16 binary digits (a 16-bit “word”), there are only 2^16 discrete values you can represent, so you are dealing in dividends which are sixty-five-thousand-five-hundred-and-thirty-sixths. The closest number to 1/100 is thus 655/65536 – i.e.: you have to store 655 in your word. This is a bit small. (Actually 655/65536 is closer to 0.09945, so our error is about one part in a thousand.) In other words: you cannot represent 1/100 as a binary fraction. Worse, if you now convert back to decimal, you will probably get 1/100 again (the smart computer rounds up) so you won’t see the inaccuracy. Now consider the result of this SQL code:

SUM(column_containing_the_fractional_value_one_hundredth)

If your Table has 1000 rows, then the conversion to binary happens 1000 times – cumulating the inaccuracy each time – and the conversion back to decimal happens only once, when the final SUM is returned. Rounding won’t save you, because the result – 99.45 – is good to the nearest hundredth. And you won’t check the result in your head. Yet the result is “wrong”.

In theory, this arithmetic exercise is not a “floating point” problem. We introduced the inaccuracy by converting a decimal fraction to binary. Both fixed-point and floating-point binary fractions have the same danger of inaccuracy, because the danger lies in the fact that we’re dealing with binary numbers – not in the fact that we’re dealing with floating-point numbers. So, in theory, the same “wrong” result could be returned for a DECIMAL Column or a NUMERIC Column. In practice, though, the better SQL DBMSs won’t use binary fractions for DECIMAL or NUMERIC values. Instead, like COBOL with “PIC 9V99”, they actually store an integer with an implied decimal point – so the number 1/100 is, internally, 1. No conversion occurs because an integral number of hundredths are being stored, rather than a fraction.

Tip

Because of this, for all financial transactions, both money and interest ought to be DECIMAL or NUMERIC. The frequency of definitions like: CREATE TABLE Table_1 (salary FLOAT); is a mistake, justified only by the fact that, in C or Pascal, it’s normal to define big or non-integer variables as floating-point.

Numeric Operations ¶

A number is compatible with, and comparable to, all other numbers – that is, all numbers are mutually comparable and mutually assignable. Numbers may not be directly compared with, or directly assigned to, any other <data type> class, though implicit type conversions can occur in expressions, SELECTs, INSERTs, DELETEs and UPDATEs. Explicit numeric type conversions can be forced with the CAST operator.

CAST¶

In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. The required syntax for the CAST operator is as follows.

CAST (<cast operand> AS <cast target>)
   <cast operand> ::= scalar_expression
   <cast target> ::= <Domain name> | <data type>

The CAST operator converts values of a source <data type> into values of a target <data type>, where each <data type> is an SQL pre-defined <data type> (data conversions between UDTs are done with a user-defined cast). The source <data type>, or <cast operand>, can be any expression that evaluates to a single value. The target <data type>, or <cast target>, is either an SQL predefined <data type> specification or the name of a Domain whose defined <data type> is the SQL predefined <data type> that you want to convert the value of “scalar_expression” into. If you use CAST (… AS <Domain name>), your current <AuthorizationID> must have the USAGE Privilege on that Domain.

It isn’t, of course, possible to convert the values of every <data type> into the values of every other <data type>. For numbers, the rules are:

CAST (NULL AS <data type>) and CAST numeric_source_is_a_null_value AS <data type>) both result in NULL.
You can CAST an exact numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB and NCLOB. You can also CAST an exact numeric source to an interval target, provided the target contains only one datetime field. That is, you can CAST an integer to INTERVAL YEAR or to INTERVAL MONTH, but you can’t CAST it to INTERVAL YEAR TO MONTH. You can CAST an exact numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.
You can CAST an approximate numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB and NCLOB. You can also CAST an approximate numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

When you CAST, an exact numeric value or an approximate numeric value to an exact numeric target, e.g., CAST (25 AS INTEGER), CAST (1.47E-5 AS DECIMAL(9,7)), or when you CAST an exact numeric value or an approximate numeric value to an approximate numeric target, e.g., CAST (25 AS FLOAT), CAST (1.47E-5 AS DOUBLE PRECISION) – your DBMS checks whether the source is a valid value for the target’s <data type> (or if a valid value (one that doesn’t lose any leading significant digits) can be obtained from the source by rounding or truncation. If so, then the source is converted to that target value. If neither of these are true, the CAST will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range".

[NON-PORTABLE] If your source value is not a valid value for your target <data type>, then the value CAST is non-standard because the SQL Standard requires implementors to define whether the DBMS will round or will truncate the source to obtain a valid target value. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the source to obtain a valid target value.

When you CAST an exact numeric value or an approximate numeric value to a fixed length character string target, your DBMS converts the number to the shortest string that represents that number,i.e.,

CAST (25 AS CHAR(2) results in the character string '25'
CAST (1.47E-5 AS CHAR(8)) results in the character string '.0000147'
CAST (-25 AS CHAR(3)) results in the character string '-25'
CAST (+25 AS CHAR(3)) results in the character string '25'
CAST (025 AS CHAR(3)) results in the character string '25'
CAST (25. AS CHAR(3)) results in the character string '25'
CAST (25.0 AS CHAR(4)) results in the character string '25'
...

If the length of the result equals the fixed length of the target, then the source is CAST to that result. If the length of the result is shorter than the fixed length of the target, then the source is CAST to that result, padded on the right with however many spaces is required to make the lengths the same. If the length of the result is longer than the fixed length of the target, the CAST will fail: your DBMS will return the SQLSTATE error 22001 data exception-string data, right truncation. And if the result contains any characters that don’t belong to the target’s Character set, the CAST will also fail: your DBMS will return the SQLSTATE error 22018 data exception-invalid character value for cast.

Note

If your approximate numeric source value is zero, the CAST result is this character string: ‘0E0’.

When you CAST an exact numeric value or an approximate numeric value to a variable length character string target or a CLOB or NCLOB target, your DBMS converts the number to the shortest string that represents that number. As with fixed length target, it strips off leading plus signs, leading zeros, and any insignificant decimal signs and trailing zeros. If the length of the result is less than or equals the maximum length of the target, then the source is CAST to that result. If the length of the result is longer than the maximum length of the target, the CAST will fail: your DBMS will return the SQLSTATE error 22001 “data exception-string data, right truncation”. And if the result contains any characters that don’t belong to the target’s Character set, the CAST will also fail: your DBMS will return the SQLSTATE error 22018 data exception-invalid character value for cast.

[Obscure Rule] The result of a CAST to a character string target has the COERCIBLE coercibility attribute; its Collation is the default Collation for the target’s Character set.

When you CAST an exact numeric value to an interval target, your DBMS converts it to the value of the interval’s single datetime field represented by that number – for example, CAST (25 AS INTERVAL YEAR) results in an interval of 25 years. If the number you’re casting is too large for the precision of the target – as in CAST (500 AS INTERVAL HOUR(2)` – the CAST will fail: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow.

When you CAST an exact numeric value or an approximate numeric value to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine’s argument. The CAST result is the value returned by the user defined cast.

If you want to restrict your code to Core SQL, don’t use <Domain name> as a CAST target: CAST only to a <data type>.

Assignment¶

In SQL, when an exact numeric or an approximate numeric value is assigned to an exact numeric target, the source is first converted to an exact numeric value with the precision and scale of the target. When an exact numeric or an approximate numeric value is assigned to an approximate numeric target, the source is first converted to an approximate numeric value with the precision of the target. In either case, if the assignment would result in the loss of any of the source value’s most significant digits, the assignment will fail: your DBMS will return the SQLSTATE error 22003 data exception-numeric value out of range.

[NON-PORTABLE] If the assignment of a numeric value would result in the loss of any of the source value’s least significant digits, the result is non-standard because the SQL Standard requires implementors to define the result using either of two options: (a) your DBMS may truncate the source to fit the target and then make the assignment or (b) your DBMS may round the source to fit the target and then make the assignment. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the source value to fit the target.

[Obscure Rule] Since only SQL accepts null values, when a null value is taken from SQL-data to be assigned to a numeric target, your target’s value is not changed. Instead, your DBMS will set the target’s indicator parameter to -1, to indicate that an assignment of the null value was attempted. If your target doesn’t have an indicator parameter, the assignment will fail: your DBMS will return the SQLSTATE error 22002 data exception-null value, no indicator parameter. Going the other way, there are two ways to assign a null value to an SQL-data target. Within SQL, you can use the <keyword> NULL in an INSERT or an UPDATE statement to indicate that the target should be set to NULL; that is, if your source is NULL, your DBMS will set your target to NULL. Outside of SQL, if your source has an indicator parameter that is set to -1, your DBMS will set your target to NULL (regardless of the value of the source). (An indicator parameter with a value less than -1 will cause an error: your DBMS will return the SQLSTATE error 22010 data exception-invalid indicator parameter value. We’ll talk more about indicator parameters in our chapters on SQL binding styles.

As an example, assume that you have an INTEGER‚ Column and need to assign a non-integer value to it. The result will depend not only on what the source value is, but also on whether your DBMS uses rounding or truncation to turn it into an integer. Here are the choices (note that “rounding toward zero” is really truncating):

Source value	Rounding toward +infinity	Rounding toward -infinity	Rounding toward zero	Rounding toward nearest
1.5	2	1	1	2
-1.5	1	-2	-1	-2

etc.

Most DBMSs use truncation, but these SQL statements show how to force the rounding method you prefer:

-- rounding toward positive infinity
CASE numeric_expression - CAST (numeric_expression AS INTEGER)
    WHEN > 0 numeric_expression+1
    WHEN < 0 numeric_expression-1
    ELSE numeric_expression
END

-- rounding toward negative infinity
CASE numeric_expression
     WHEN > 0 CAST (numeric_expression AS INTEGER)
     WHEN < 0 CAST (0 - (ABS(numeric_expression) + 0.5) AS INTEGER))
     ELSE numeric_expression
END

-- rounding toward zero
CAST (numeric_expression AS INTEGER)

-- rounding toward nearest
CAST (numeric_expression + 0.5 AS INTEGER)

Comparison¶

SQL provides the usual scalar comparison operators – = and <> and < and <= and > and >= – to perform operations on numbers. All of them will be familiar; there are equivalent operators in other computer languages. Numbers are compared in the usual manner. If any of the comparands are NULL, the result of the operation is UNKNOWN. For example:

97 = 105.2

returns FALSE.

97 <> {result is NULL}

returns UNKNOWN.

SQL also provides three quantifiers – ALL, SOME, ANY – which you can use along with a comparison operator to compare a value with the collection of values returned by a <table subquery>. Place the quantifier after the comparison operator, immediately before the <table subquery>. For example:

SELECT decimal_column
FROM   Table_1
WHERE  decimal_column < ALL (
   SELECT integer_column
   FROM   Table_2);

ALL returns TRUE either (a) if the collection is an empty set (i.e.: if it contains zero rows) or (b) if the comparison operator returns TRUE for every value in the collection. ALL returns FALSE if the comparison operator returns FALSE for at least one value in the collection.

SOME and ANY are synonyms. They return TRUE if the comparison operator returns TRUE for at least one value in the collection. They return FALSE either (a) if the collection is an empty set or (b) if the comparison operator returns FALSE for every value in the collection. The search condition = ANY (collection) is equivalent to IN (collection).

Other Operations¶

With SQL, you have several other operations that you can perform on numbers, or on other values to get a numeric result.

Arithmetic ¶

SQL provides the usual scalar arithmetic operators, + and - and * and /, to perform operations on numbers. All of them will be familiar; there are equivalent operators in other computer languages. If any of the operands are NULL, the result of the operation is also NULL.

Monadic + and Monadic -¶

When used alone, + and - change the sign of an operand (e.g.: a <literal> or a Column instance or a host variable). For example:

SELECT -5, -(-occurrence_decimal)
FROM   Exact_Examples
WHERE  occurrence_integer = +5;

Note

Because two dashes i.e.: -- means “comment start” in SQL, our example of a double negative has to be -(-occurrence_decimal) rather than --occurrence_decimal.

Dyadic + and Dyadic - and Dyadic and Dyadic /*¶

When used between two operands, + and - and * and / stand for add and subtract and multiply and divide, respectively, and return results according to the usual rules. For example:

SELECT occurrence_integer + 5, (occurrence_integer * 7) / 2
FROM   Exact_Examples
WHERE  occurrence_integer < (:host_variable - 7);

Precedence¶

Dyadic * and / have priority over dyadic + and -, but monadic + and - have top priority. It’s good style to use parentheses for any expressions with different operators.

Errors¶

The two common arithmetic exception conditions are:

SQLSTATE 22003 -- data exception - numeric value out of range
SQLSTATE 22012 -- data exception - division by zero

Here is a snippet of an embedded SQL program that checks for overflow after executing a statement that contains addition:

EXEC SQL UPDATE Exact_Examples
         SET    occurrence_smallint = occurrence_decimal + 1;
if (strcmp(sqlstate,"22003") printf("Overflow! Operation cancelled ...\n");

Error checks should follow every execution of an SQL statement, but imagine that the EXACT_EXAMPLES Table has a million rows. To avoid the situation where, after chugging through 999,999 rows, your application collapses on the last one with Overflow! Operation cancelled ..., try this code:

EXEC SQL UPDATE Exact_Examples
         SET    occurrence_smallint =
         CASE
           WHEN occurrence_smallint = 32767 THEN 0
           ELSE occurrence_smallint = occurrence_smallint + 1
         END;

Tip

CASE expressions are good for taking error-abating actions in advance.

Tip

SQL has no low-level debugging features, so sometimes you will need to force an error somewhere in a complex expression, to be sure it is actually being executed. For this purpose, insert code that would cause a numeric overflow.

Mixing numeric <data type>s¶

As we said earlier, all numbers – any <data type>, exact or approximate – are compatible. That means that you can mix them together in any numeric expression – which leads to the question: what comes out when you do mix them, i.e.: what is the <data type>, precision and scale of the result? The SQL Standard says these are the results you will get:

[NON-PORTABLE] An exact numeric value added to, subtracted from, multiplied by or divided by an exact numeric value yields an exact numeric value with a precision that is non-standard because the SQL Standard requires implementors to define the precision of the result. For all these operations, if the result of the operation can’t be exactly represented with the correct precision and scale, the operation will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range". [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between exact numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with SMALLINT and INT operands, the result is an INT.

An exact numeric value added to or subtracted from an exact numeric value yields a result with a scale size that matches the size of scale of the operand with the largest scale, e.g.: for an operation with DECIMAL(6,2) and INT operands, the result has a scale of 2.

An exact numeric value multiplied by an exact numeric value yields a result with a scale size that is the sum of the scale sizes of the operands, e.g.: for an operation with DECIMAL(6,2) and NUMERIC(10,4) operands, the result has a scale of 6.

[NON-PORTABLE] An exact numeric value divided by an exact numeric value yields a result with a scale size that is non-standard because the SQL Standard requires implementors to define the scale size of the result. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of a division operation between exact numeric operands a scale size that matches the size of scale of the operand with the largest scale, e.g.: for an operation with DECIMAL(6,2) and NUMERIC(10,4) operands, the result has a scale of 4.

[NON-PORTABLE] An approximate numeric value added to, subtracted from, multiplied by or divided by an approximate numeric value yields an approximate numeric value with a precision and scale that are non-standard because the SQL Standard requires implementors to define the precision and scale of the result. If the exponent of the result doesn’t fall within the DBMS’s supported exponent range, the operation will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range". [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between approximate numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with REAL and DOUBLE PRECISION operands, the result is a DOUBLE PRECISION type.

[NON-PORTABLE] An approximate numeric value added to, subtracted from, multiplied by or divided by an exact numeric value (or vice versa) yields an approximate numeric value with a precision and scale that are non-standard because the SQL Standard requires implementors to define the precision and scale of the result. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between approximate numeric and exact numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with REAL and DOUBLE PRECISION operands, the result is a DOUBLE PRECISION.

In other words, the Standard always evades the big question: what’s the result precision? To put this into perspective, consider a DBMS faced with a tough operation: add 1 to a Column which is defined as DECIMAL(5). Since the Column might already contain the value 99999, adding 1 might yield 100000 – a DECIMAL(6) value. For such cases, the DBMS must decide what to do before executing, because the application program, which will receive the result, must know the size in advance. The DBMS has two choices:

Let it grow. The result is DECIMAL(6) if the operation is addition and slightly more if the operation is multiplication. This choice has the advantage that it eliminates “overflow” errors. But there are still undefined areas: What happens if the DECIMAL precision is already at the maximum? What happens if the operation adds 1 to a SMALLINT – does the <data type> upgrade to INTEGER so that the increased precision is valid?
Chop it. The result is DECIMAL(5), regardless. This risks failure on even the most innocuous operations, but it’s a simple rule to follow: output precision = input precision. Programmers can understand it.

These choices are not mutually exclusive and your DBMS might make different decisions for different operations.

Tip

Before you divide, decide how many digits should follow the decimal point in the result. The number will almost certainly be greater than the number you start with; for instance, “12/5” (dividing scale-0 integers) yields “2.4” (a scale-1 decimal number) – you hope. Your DBMS may increase the scale automatically, but the Standard doesn’t say it must. To force the result, use this SQL code:

CAST (12 AS DECIMAL(3,1))/5    -- yields 2.4

Incidentally, there are several bad ways to cast. This SQL code:

CAST ((12/5) AS DECIMAL(3,1))

will yield 2.0 if your DBMS doesn’t increase the scale automatically – be sure to CAST the source, not the result. This SQL code:

CAST (12 AS DECIMAL(2,1))/5

will cause an error – be sure your source value fits in the CAST target.

Floating-Point Arithmetic¶

If you want fast and complex floating-point arithmetic, buy a good Fortran compiler: SQL can’t handle the fancy stuff. In particular:

SQL lacks useful functions which in other languages are built-in, e.g.: the ability to detect NaN (Not a Number).
SQL vendors are only obliged to define and to accept IEEE numbers. They can do arithmetic without paying any attention to the IEEE standard at all. In particular, some vendors may use the same routines for approximate numerics as they use for exact numerics, and exact is slower.

Still, you can do the basic arithmetic functions – add, subtract, divide, multiply, compare – provided you take sensible precautions.

Comparing Two Floating-Point Numbers for Equality¶

Think of the inexact result produced when 1/100 was converted to a binary fraction. Because of this, the following SQL code:

... WHERE float_column = 1.0E+1

will fail if, e.g.: the value of float_cloumn was originally produced by summing 1/100 one hundred times. To get the “approximately right” answer, compare the absolute difference between the two numbers against a constant, e.g.: with this SQL code:

... WHERE ABS(float_column - 1.0E+1) < :epsilon

To choose a value for epsilon, remember that the accuracy of floating point numbers varies – by definition – according to magnitude. For example, between 1.0 and 2.0 there are about 8 million numbers, but between 1023.0 and 1024.0 there are only about 8 thousand numbers (assuming IEEE single-precision standards). In this example, since the comparison is for equality, we know that float_column must be about the same magnitude as the <literal> 1.0E+1, therefore a reasonable value for epsilon is 1/8000000 or 1.25E-7. When you don’t know one of the comparands in advance, start with a value for epsilon that’s half as large, multiply it by the sum of the comparands (thus changing its magnitude to the comparands’ magnitude) and then compare with this SQL code:

... WHERE ABS(float_column_1 - float_column_2) <
          (ABS(float_column_1 + float_column_2) * :epsilon/2)

Subtraction¶

We did this operation with an IEEE-compatible compiler: 1234.567 - 1234.000 The result was 0.5670166.

Both inputs are single-precision floating point numbers (7 digits precision), accurate to the third decimal place. Unfortunately, so is the output. Although the subtraction decreased the magnitude, causing the decimal place to shift right, the accuracy was unaffected: the extra digits after 0.567 are spurious precision. If a subtraction causes a drop in magnitude, spurious precision is likely. (This is often called the “insignificant digits” problem and applies to addition too, if operands can have negative signs.)

Tip

Eliminate insignificant digits using two CASTs. In this example, we know what the input is, so we could clear everything after the result’s third decimal place with this SQL code:

CAST (CAST ((1.234567E+04 - 1.234000E+04) AS DEC(8,3)) AS REAL)

Here, by casting to DEC(8,3) we first change the result 0.5670166 to 0.567. The second CAST casts this back to REAL, with a subsequent result of 0.5670000. Casting is a straightforward way to strip – unfortunately, it’s only useful if you know a lot about the data.

Tip

If an SQL statement does both addition and subtraction, parenthesize so that the addition happens first – this makes a drop in magnitude less likely to occur. For example, change this SQL statement:

UPDATE Approximate_Examples
SET    occurrence_real = occurrence_real - :host_variable + 1.1E+01;

to this SQL statement:

UPDATE Approximate_Examples
SET    occurrence_real = occurrence_real - (:host_variable + 1.1E+01);

By the way, don’t just transpose the operands. Order of expression evaluation varies.

Division¶

When doing floating-point division, keep in mind that there is such a thing as “negative zero” and there are floating-point numbers which are so small that you’ll get an exception when you divide by them, even though they don’t exactly equal zero. This makes it a little harder to test for “division by zero” errors in advance.

Scalar Operations ¶

SQL provides ten scalar functions that return a number: the <case expression>, the <cast specification>, the <position expression>, the three <length expression>s, the <extract expression>, the <cardinality expression>, the <absolute value expression> and the <modulus expression>. Only the last two also operate exclusively on numbers; these are described below. We’ll discuss the rest in other chapters; for now, just remember that they evaluate to a number and can therefore be used anywhere in an SQL statement that a number could be used.

<absolute value expression>¶

The required syntax for an <absolute value expression> is:

<absolute value expression> ::=
ABS(numeric_argument)

ABS operates on an argument that evaluates to a number. It strips a negative sign (if it’s present) from the argument and returns a non-negative number whose <data type> is the same as the argument’s <data type>, e.g.: ABS(-17) returns 17, ABS(17) returns 17 and ABS(0) returns 0. If the argument is NULL, ABS returns NULL.

If the result of ABS is a number that doesn’t fit into the argument’s <data type> range, the function will fail: your DBMS will return the SQLSTATE error 22003 data exception-numeric value out of range. ABS is new to SQL with SQL3 and is also supported by ODBC. If your DBMS doesn’t support ABS, you can simulate it with this SQL statement:

CASE
   WHEN ...<0 THEN ...*-1
   ELSE ...
END

If your DBMS doesn’t support CASE, you can still get an absolute value of a number with this arithmetic expression:

(number * number) / number

[Obscure Rule] ABS can also operate on an interval. We’ve ignored this option for now – look for it in our chapter on temporal values.

<modulus expression>¶

The required syntax for a <modulus expression> is as follows.

<modulus expression> ::=
MOD(dividend_argument,divisor_argument)

MOD operates on two arguments, both of which must evaluate to an exact numeric integer. It divides the first number by the second number and returns the operation’s remainder as a non-negative exact numeric integer whose <data type> is the same as the divisor_argument’s <data type>, e.g.: MOD(35,4) returns 3 and MOD(32,4) returns 0. If either argument is NULL, MOD returns NULL. If the divisor_argument is zero, the function will fail: your DBMS will return the SQLSTATE error 22012 "data exception-division by zero".

MOD is new to SQL with SQL3. In the Standard, MOD stands for “modulus” but the result of this function is not actually a modulus – it is a remainder achieved “by means of a modulus”.

Set Functions ¶

SQL provides five set functions that return a number: COUNT(*), COUNT, AVG, SUM and GROUPING. All but COUNT(*) also operate on numbers (COUNT(*) operates on rows). AS do the set functions MAX and MIN also operate on numbers. Since none of these operate exclusively with numeric arguments, we won’t discuss them here; look for them in our chapter on set functions.

Predicates ¶

In addition to the comparison operators, SQL provides eight other predicates that operate on numbers: the <between predicate>, the <in predicate>, the <null predicate>, the <exists predicate>, the <unique predicate>, the <match predicate>, the <quantified predicate> and the <distinct predicate>. Each will return a boolean value: either TRUE, FALSE or UNKNOWN. None of these operate strictly on numbers, so we won’t discuss them here. Look for them in our chapter on search conditions.

Choosing the Right <data type>¶

When you’re defining a <data type> specification, think about whether you really need a numeric <data type> for the expected data. Don’t just ask: are the values always bunches of digits? For example, phone numbers are digits but if you define a DECIMAL <data type> for them you might lose a valuable piece of information – whether a leading zero is significant. Identification numbers are digits but if you define a DECIMAL <data type> for them you might have trouble calculating the check digit, which is usually based on a substring extraction. Instead, consider the question: will I ever need to do standard arithmetic operations on the data? If the answer is “no”, use a string <data type> rather than a numeric type.

If the answer is “yes”, then consider which numeric type to choose by answering the question: are the values going to be seen by users or by programs written in other computer languages? If the former: it’s a lot easier to explain to a user looking at a blank six-position field on a screen: “you can type in a number between -99999 and +9999” instead of “you can type in a number between -32767 and +32767”. If the latter: pick the numeric type that’s closest to the variable type that the other computer language will use. You can also follow this short decision tree:

IF (numeric values might be huge (> 1 quadrillion) or tiny (< 1 quadrillionth)
  /* you need an approximate numeric <data type> */
  IF (your host program uses C "float" or Delphi "Single")
  AND(7 digit precision is satisfactory)
    /* you need a REAL <data type> */
  IF (your host program uses C or Delphi "double")
  AND(15 digit precision is satisfactory)
    /* you need a DOUBLE PRECISION <data type> */
ELSE (if values are not huge or tiny)
  /* you need an exact numeric <data type> -- the usual case */
  IF (your host program uses C "short int" or Delphi "SmallInt" */
    /* you need a SMALLINT <data type> */
  IF (your host program uses C "int" or Delphi "Longint" */
    /* you need an INTEGER <data type> */
  ELSE
    /* you don't need an exact match with host-language variables */
    IF (you are accustomed to the word NUMERIC because Oracle uses it)
      /* you need a NUMERIC <data type> */
    ELSE
      /* you need a DECIMAL <data type> */

Once you’ve gone through the decision tree, calculate the required precision and scale by looking at all expected values.

Dialects ¶

The “typical” SQL DBMS supports most of the standard numeric data types, but often uses preferred local names. Here are some lists of local types derived from vendor manuals. The correlation with the leftmost (“Standard”) column is sometimes imprecise. “ODBC” is not a DBMS but a spec.

Standard	Oracle	DB2	Sybase	ODBC
`SMALLINT`	`NUMBER`	`SMALLINT`	`SMALLINT`	`SMALLINT`
`INTEGER`	`NUMBER`	`INTEGER`	`INT`	`INTEGER`
`DECIMAL`	`NUMBER`	`DECIMAL`	`MONEY`	`DECIMAL`
`NUMERIC`	`NUMBER`	`NUMERIC`	`MONEY`	`NUMERIC`
`REAL`	`NUMBER`	`REAL`	`FLOAT`	`REAL`
`FLOAT`	`NUMBER`	`FLOAT`	`FLOAT`	`FLOAT`
`DOUBLE PRECISION`	`NUMBER`	`DOUBLE PRECISION`	`FLOAT`	`DOUBLE PRECISION`

Other commonly-seen numeric data types include TINYINT (8-bit signed integer), BIGINT (64-bit signed integer) and SERIAL (integer that goes up by 1 for each new inserted row).

The SQL Library ¶

Before we finish discussing numbers, it’s time to add something to our “SQL library”. To be worthy of addition to the SQL library, a routine must (a) be good clean SQL, (b) be callable from C and Delphi, (c) be actually useful in C and Delphi because it does something that those languages can’t and (d) have nothing at all do with “databases” – it should be available for use just like any general function library.

Our addition to the SQL library for this chapter will be a calculator. It won’t match C and Delphi for floating-point arithmetic, but it will give more exact answers. Here it is.

Function: SQL_calculator (lp_calculation, lp_result, lp_error)

Pass: An arithmetic expression in the string lp_calculation. The string may contain any combination of numeric <literal>s (in valid SQL form), the operators * + * / MOD ABS and parentheses.

Return:: lp_result: Result of expression (a string containing a number).

lp_error: SQLSTATE and error message, if expression was invalid.

Example: Try passing the expression: 1.000001 + 1.999990) * 11000 to our calculator. Our proc gives the correct result: “33000.0000000”. The compilers we tested gave the wrong result: “32999.9901000”. (Remember that in SQL all the <literal>s in this expression are DECIMAL, not floating-point, <literal>s.)

Chapter 3 – Numbers¶

Subscribe to the CrateDB Newsletter now