Disappearing Data

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:

If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

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;
RIGHT_NOW TRUNCATED ROUNDED
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:

  1. Create a new datatype (called DAY, perhaps) that would be a valid date without a time component
  2. 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?

Advertisements

3 thoughts on “Disappearing Data

  1. Stewart,

    as for example
    “NUMBER(2,-1): a number from -990 through 990, always rounded to tens.”

    looks like it must be
    “NUMBER(2,-1): a number from -994 through 994, always rounded to tens.”
    ?

    Alex

    • Hi Alex,

      Sorry, I don’t follow you. Where do you get these numbers? I used 99.4 as sample input, but the value once rounded will always be as I stated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s