NUMBER data type…what harm can it do ?

There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk

Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a friend at work.

Let’s start with a simple comparison between two dates. In this case, I’ve just used times, because it still serves to demonstrate the anomaly.

SQL> SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60 delta
  2  FROM   dual;

     DELTA
----------
        50

Thankfully, the database has come back with the right answer of 50 seconds. Of course, we might want to remember that result – so lets store it in a variable:

SQL> variable x number
SQL> begin
  2  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  3  into   :x
  4  FROM   dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
        50

So far so good…let’s now make the tiniest of changes. We’ll use a local PL/SQL variable

SQL> set serverout on
SQL> declare
  2    x number;
  3  begin
  4  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  5  into x
  6  FROM   dual;
  7  dbms_output.put_line(x);
  8  end;
  9  /
50.00000000000000000000000000000000000004

PL/SQL procedure successfully completed.

The use of arbitrary precision for ‘x’ shows how things can go a little bit awry. Similarly, lets look at what happens if convert the dates to simple ‘seconds past midnight’.

SQL> SELECT to_char(TO_DATE('14:03:15','hh24:mi:ss'),'SSSSS') d1,
  2         to_char(TO_DATE('14:02:25','hh24:mi:ss'),'SSSSS') d2
  3  FROM   dual;

D1    D2
----- -----
50595 50545

You might think that the calculation would the same, but when we insert those numbers into the equivalent calculation, we get a slightly different answer

SQL> exec dbms_output.put_line((50595/86400 - 50545/86400)* 24 * 60 * 60 );
49.99999999999999999999999999999999999968

Ultimately, this probably boils down to the fact that the certain division operations can never give a prefect answer in floating point arithmetic. In the example above, 50595/86400 yields the never ending result 0.58559027777777777777777…

Putting PL/SQL aside, there might also be hidden costs if you start using unbounded precision in your database tables. I always remember this example from Steve Adams many years ago.

SQL> create table T ( x1 number, x2 number(6,3));

Table created.

SQL> insert into T values ( 3*(1/3), 3*(1/3) );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T;

        X1         X2
---------- ----------
         1          1

No strange decimals there…but then take look at how you stored that data

SQL> select vsize(x1), vsize(x2) from T;

 VSIZE(X1)  VSIZE(X2)
---------- ----------
        21          2

Bottom line – things can go astray when you dont keep a handle on the appropriate precision to use for your data types.