Modeling SQL Decimals in Ion Schema
(Applies to Ion Schema 1.0 and Ion Schema 2.0.
Example code uses Ion Schema 2.0; for Ion Schema 1.0, replace exponent
with scale
and negate the argument.)
Ion decimal
and SQL DECIMAL
have some fundamental differences that make it complex to compare values between the two types.
A SQL DECIMAL
is an exact numeric type, having a precision P
and a scale S
. P
is a positive integer that
determines the number of significant digits in a particular radix (base-10 for DECIMAL
), and S
is a non-negative
integer. Every value of a DECIMAL
type of scale S
is of the form n × 10–S, where n is an integer such
that –10P < n < 10P. (For more details about SQL numeric types, including DECIMAL
, see SQL-92 §4.4.)
Therefore, the precision of the number 12.34
is not determined by the actual number of digits, but (speaking informally)
by the data type it is assigned. 12.34
can be assigned to DECIMAL(38,2)
just as easily as DECIMAL(4,2)
. That is to
say, precision and scale are a property of the DECIMAL
data type rather than being inherent in the value.
On the other hand, the Ion decimal data type has an exponent property instead of a scale property. Because an Ion decimal can have more than one encoding, the value of the exponent property is actually the adjusted exponent as defined in the IBM Hursley Lab General Decimal Arithmetic Specification:
The adjusted exponent is the value of the exponent of a number when that number is expressed as though in scientific notation with one digit (non-zero unless the coefficient is 0) before any decimal point. This is given by the value of the
exponent+(clength–1)
, whereclength
is the length of the coefficient in decimal digits.
For example, the adjusted exponent of the values 0.00123d2
and 0.123d0
is the exponent of the adjusted form, 1.23d-1
.
Informally, the exponent property is like the negation of scale, except that positive and negative numbers are valid exponents, but scale may only be non-negative.
Finally, the Ion decimal data type has no particular precision or exponent as it is an arbitrary precision data type.
Instead, the precision and exponent of an Ion decimal value are inherent in the value itself.
As a result of these differences, when converting a value from a SQL DECIMAL
to an Ion decimal
, it is possible to
preserve the scale of the DECIMAL
(as it becomes the exponent of the Ion decimal, multiplied by -1), but the precision
is lost. When converting from an Ion decimal
to a DECIMAL(p,s)
, the precision and exponent of the Ion value are always
lost because the value is converted into having the precision and scale of DECIMAL(p,s)
.
That being said, here are some questions that one might be trying to answer by modeling SQL DECIMAL
and how to approach
modeling them in Ion Schema.
Does x
have the exact precision and the exact scale of DECIMAL(p,s)
?
This is probably the least useful way to model a SQL DECIMAL
, and is included here only to help explain why exact
precision is not useful when modeling DECIMAL
in Ion Schema.
Using DECIMAL(5,2)
as our example, we can model an exact precision and scale like this:
type::{
precision: 5,
exponent: -2,
}
This will only allow decimal
s with 5 digits, and two after the decimal point. However, this will not accept valid
DECIMAL(5,2)
values such as 1.00
.
Does x
have a compatible precision and the exact scale of DECIMAL(p,s)
?
This way of modeling a SQL DECIMAL
is useful for scenarios where the value was a SQL DECIMAL(p,s)
before being
converted to Ion.
This can be separated into two parts:
- Does
x
have exactlys
digits after the decimal point? - Does
x
have less than or equal top
digits? (Or is-10^(p-s) < x < 10^(p-s)
?)
Using DECIMAL(5,2)
as our example, we can model a compatible precision and exact scale like this:
type::{
precision: range::[min, 5],
exponent: -2,
}
This will accept values such as 100.00
, 1.50
, and 0.01
. It will reject values such as 100
, 1.5
, 0.010
, and 1000.0
.
Instead of using the precision
constraint, we could also use valid_values: range::[-999.99, 999.99]
to achieve the same result.
Does x
fit in a DECIMAL(p,s)
without having to round or truncate any digits after the decimal point?
This is probably the most useful way to model a SQL DECIMAL
in Ion Schema. It can be used for validating that data
could have been a DECIMAL(p,s)
before it was converted to Ion, and that the value could be converted to a
DECIMAL(p,s)
without any rounding or truncating.
This can be separated into two parts:
- Does
x
have less than or equal tos
digits after the decimal point? - Is
-10^(p-s) < x < 10^(p-s)
?
Using DECIMAL(5,2)
as our example again, we can model a compatible precision and scale like this:
type::{
exponent: [-2, max],
// Exponent is not fixed, so we cannot use the `precision` constraint. Must use `valid_values` instead.
valid_values: range::[ -999.99, 999.99 ],
}
This will accept values such as 1.5
, 1.50
, 5d2
, and 500
. It will reject values such as 1200
, 1.2d5
, 1.501
,
and 1.500
.