Did you know that Oracle DML can silently change data you want to put into the database? A recent twitter discussion started by Martin Widlake (@MDWidlake) prompted me to study more closely what happens when.
Number, precision and scale
When we define a number, we can specify its “precision” (the maximum number of significant decimal digits) and its “scale” (the number of digits from the decimal point to the least significant digit). Here are some examples:
- NUMBER(2,0): an integer in the range of -99 through 99.
- NUMBER(2,1): a number from -9.9 through 9.9, always rounded to tenths.
- NUMBER(2,-1): a number from -990 through 990, always rounded to tens.
DML complains about precision
Suppose I have a table column defined as NUMBER(2,0) and I try to put the value 100 in there. There is no room for that value, so Oracle will raise an exception:
ORA-01438: value larger than specified precision allowed for this column
DML complains about scale: NO, it rounds
Now suppose I try to put the value 99.4 into my NUMBER(2,0) column. Oracle will not raise an exception: it will silently round the value to 99 and insert that! The input data is changed with no warning.
The Oracle documentation is clear on the subject:
What about other data types?
There are two other data types that are subject to rounding: TIMESTAMP (with or without time zone) and INTERVAL DAY TO SECOND. We can specify the “precision” of the fractional seconds. For example:
- TIMESTAMP(1) only allows one digit after the decimal point for seconds
- INTERVAL DAY TO SECOND(3) allows up to 3 digits after the decimal point.
Again, if we try to put in a more precise value, Oracle will silently round to make the data fit.
An apparent exception: TIMESTAMP to DATE
Going from TIMESTAMP to DATE is not rounding, it is a datatype conversion. When converting to a DATE, any fractional seconds are discarded. The effect is truncation rather than rounding.
If you want rounding to occur, then CAST the timestamp to TIMESTAMP(0) first.
select systimestamp right_now, cast(systimestamp as date) truncated, cast(cast(systimestamp as timestamp(0)) as date) rounded from dual;
|2017-01-06 13:24:19.606274 +01:00||2017-01-06 13:24:19||2017-01-06 13:24:20|
What does this have to do with Twitter?
In the recent twitter conversation that Martin started, there were two suggestions:
- Create a new datatype (called DAY, perhaps) that would be a valid date without a time component
- Allow for “scale” in the DATE datatype: allow it to contain only the year, or only year+month, or only year+month+day, etc.
Supposing Oracle managed these data types as it does the current types, then in case 1. converting from DATE to DAY would cause truncation of the time component, whereas in case 2. silent rounding would occur.
I think “rounding” to the nearest day, month or year would be strange, don’t you?