Chapter 3 – Numbers¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.
On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.
For more information specific to CrateDB, check out the CrateDB Reference documentation.
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 |
|
|
.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,REALorDOUBLE 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>) andCASTnumeric_source_is_a_null_value AS<data type>) both result inNULL.You can
CASTan exact numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string,CLOBandNCLOB. You can alsoCASTan exact numeric source to an interval target, provided the target contains only one datetime field. That is, you canCASTan integer toINTERVAL YEARor toINTERVAL MONTH, but you can’t CAST it toINTERVAL YEAR TO MONTH. You canCASTan 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 theEXECUTEPrivilege on that user-defined cast.You can
CASTan approximate numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string,CLOBandNCLOB. You can alsoCASTan 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 theEXECUTEPrivilege 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 theDECIMALprecision is already at the maximum? What happens if the operation adds 1 to aSMALLINT– does the <data type> upgrade toINTEGERso 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 |
|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:SQLSTATEand 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.)