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.
