Oracle and fractions of seconds...



  • Okay...  So I've been away from these beautiful forums for quite a while (weeks? months? time flies...) as I've been busy working on a truly interesting project.

    Today, while one of my colleagues was looking over the results of a report from this project, he showed me a bug that reported that the processing overhead time was greater than the total elapsed time.

    After a bit of research, I came to the shocking conclusion that something in or around the database cannot do math.  It simply fails at working the decimals like an English major back in elementary school.  I'm not posting this in Oracle Haters Club because I'm not convinced it's an Oracle bug.  I mean, it's so terrible, it couldn't possibly be, could it?  Maybe it's a problem with formatting, or maybe something in the Java JCDBCBDBDCCCDBJDC layer?  I don't know.  It's rather mind boggling, and I haven't gotten over the shock of it yet.

    Ok, here is the setup: Get an Oracle database and execute this simple SQL:

    select
        NUMTODSINTERVAL(.100, 'SECOND') tenth,
        NUMTODSINTERVAL(.010, 'SECOND') hundredth,
        NUMTODSINTERVAL(.001, 'SECOND') thousandth,
        NUMTODSINTERVAL(.002, 'SECOND') - NUMTODSINTERVAL(.001, 'SECOND') "2 milli minus 1 milli"
    from dual

    Here we have a tenth of a second, a hundredth of a second, and a thousandth of a second, as well as 2 milliseconds minus 1 millisecond.

    The NUMTODSINTERVAL function is a built-in that takes a number and a unit and turns it into an DSINTERVAL type, which represesnts a time interval in the range of days to fractions of seconds.

    The way I have both Oracle SQL Developer and my project formatting the DSINTERVAL type is [day] [hour]:[minute]:[second].[fractions of second].  I believe this is the default format.  Both SQL Developer and my project are using the same underlying libraries.

     

    Ok, so here is the shocking part:  When I run that query, this is what I get:


    tenth             hundredth        thousandth      2 milli minus 1 milli
    -----------       -----------      -----------     ---------------------
    0 0:0:0.100000000 0 0:0:0.10000000 0 0:0:0.1000000 0 0:0:0.1000000       



    That's right, they all return a tenth of a second.






    Please help me cope.





  • Works fine for me. 



  • @HighlyPaidContractor said:

    Works fine for me.
    I KNEW IT WAS A CONSPIRACY AGAINST ME!

    Fwiw, here are my banners:

    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    "CORE    10.2.0.3.0    Production"
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production

    Perhaps you have a newer version which can calculate decimals.  Or maybe I just need to [code]SET COMPUTE_DECIMALS_CORECTLY ON[/code]?



  • I'm running on Oracle 10.2.0.4.0

    Works in SQL Nav 5.5 and querying from .NET

    If you want more detail, you're SOL.  My employers barely give me access to run queries in DEV.



  • Maybe you forgot to unset the ROUND_UP_TO_NEXT_TENTH_OF_SECOND flag?



  • I think the problem is that you didn't specify which [fractions of second] you were interested in.  It's correctly returning 1 fraction for each result.



  • @HighlyPaidContractor said:

    I think the problem is that you didn't specify which [fractions of second] you were interested in.  It's correctly returning 1 fraction for each result.
    ... I think you're right.

    [code]to_char(NUMTODSINTERVAL(.001, 'SECOND'),'DD HH24:MI:SS.FF')[/code] correctly returns "+000000000 00:00:00.001000000"

    Uuugh...  so wtf is the default formatting lying to me?  I guess I have to go insert explicit formatting all over the place now... 

     

    Edit: actually, that format string is ignored for some reason.  Just stringifying it on the backend side is enough to fix it.  [code]to_char(NUMTODSINTERVAL(.001, 'SECOND'))[/code]

    LAME.

    PS, thanks for the insight.



  • @Xyro said:

    Uuugh...  so wtf is the default formatting lying to me? 

    Because it's Oracle.



  • It looks like a JDBC problem to me. SQLPLUS gives me the correct results, while using JDBC gives me the same results you received. It should be reported as a bug.



  • @Rick said:

    It looks like a JDBC problem to me. SQLPLUS gives me the correct results, while using JDBC gives me the same results you received. It should be reported as a bug.
     

    Nah, it's working totally like it's supposed to:  [url]http://forums.oracle.com/forums/thread.jspa?threadID=1127269&tstart=60[/url]

    What, are you some kind of moron thinking that "0.100" means 0.100?   Duuuuuuh.  It means zero seconds and 100 nanoseconds.  Geez you're so stupid.



  • Each one of those has a different number of significant figures depending on the magnitude of the result (9, 8, 7, 7). How very odd.



  • @Xyro said:

    What, are you some kind of moron thinking that "0.100" means 0.100?   Duuuuuuh.  It means zero seconds and 100 nanoseconds.  Geez you're so stupid.

    Read it again.  It clearly says "nunoseconds."



  • @Xyro said:

    @Rick said:

    It looks like a JDBC problem to me. SQLPLUS gives me the correct results, while using JDBC gives me the same results you received. It should be reported as a bug.
     

    Nah, it's working totally like it's supposed to:  http://forums.oracle.com/forums/thread.jspa?threadID=1127269&tstart=60

    What, are you some kind of moron thinking that "0.100" means 0.100?   Duuuuuuh.  It means zero seconds and 100 nanoseconds.  Geez you're so stupid.

    I stand corrected. I didn't count the number of zeros. 

     HUNDREDTH         TO_CHAR_HUNDREDTH            
     ----------------  -----------------------------
     0 0:0:0.10000000  +000000000 00:00:00.010000000 

     However, I would still like to see the documentation, (official Oracle documentation), for this behavior.

    Does anyone remember the old DOS Recover command? The documentation on one page told you how to recover a single file whose length was not recorded correctly in the FAT filesystem. The second page, which happened to be on the back, told you that if you executed the command without a filename it would rename every single file in all the directories on your C: drive to C:\RECOVER.001, C:\RECOVER.002, etc. That wasn't a bug either, but they changed the command anyway. :-)

     



  • @Rick said:

      However, I would still like to see the documentation, (official Oracle documentation), for this behavior.

    Being uncertain how to handle decimal time, (should everything be base 60?  Base 24??), we're just going to return crap.  I mean who really needs to know things that happen in milliseconds, thats like really lame.  For further information see documentation on boolean fields and/or empty string vs. null string operations, and WHERE EXISTS.



  • @Medezark said:

    @Rick said:

      However, I would still like to see the documentation, (official Oracle documentation), for this behavior.

    Being uncertain how to handle decimal time, (should everything be base 60?  Base 24??), we're just going to return crap.  I mean who really needs to know things that happen in milliseconds, thats like really lame.  For further information see documentation on boolean fields and/or empty string vs. null string operations, and WHERE EXISTS.

    Remember, that was appended in the latest patch notes:

    Decimal Time: I dunno, does this look right? I think it might be... oh fuck it, ship the damned thing.



  • @Rick said:

    Does anyone remember the old DOS Recover command? The documentation on one page told you how to recover a single file whose length was not recorded correctly in the FAT filesystem. The second page, which happened to be on the back, told you that if you executed the command without a filename it would rename every single file in all the directories on your C: drive to C:\RECOVER.001, C:\RECOVER.002, etc. That wasn't a bug either, but they changed the command anyway. :-)


    I don't see how it relates, but yeah I remember that horrible horrible command.

    I don't think I ever trashed anything with it, but I learned that lesson well enough that I still instinctively recoil from any command with "recover" in the name.



  • @aihtdikh said:

    @Rick said:

    Does anyone remember the old DOS Recover command? The documentation on one page told you how to recover a single file whose length was not recorded correctly in the FAT filesystem. The second page, which happened to be on the back, told you that if you executed the command without a filename it would rename every single file in all the directories on your C: drive to C:\RECOVER.001, C:\RECOVER.002, etc. That wasn't a bug either, but they changed the command anyway. :-)

    I don't see how it relates...
    You really didn't see how Oracle documenting a poor implementation relates to Microsoft documenting a poor implentation? Documenting the insane format of the Interval doesn't fix the problem.It didn't fix Recover and it wouldn't fix Intervals.

     



  • Intentional semi-necro for an update...

    FYI, in case anyone stumbles upon this thread, the 11.2.0.1.0 version of the Oracle driver does not contain this bug.

    (It also fixes a few other bugs I found, such as one of their ResultsSet implementations not overriding an abstract isClosed() method.  Did they even test the version 10 driver?  How did they even get that to compile??)

    Ok, that is all.

    But I still hate Oracle.



  • I was afraid to scroll down when I saw the date of the original post. Lately some spammer has been reviving old threads by posting porn images and I mostly read this site from work. My PC is in a pretty visible cubicle and I'm just waiting for one of my bosses to walk by right as I scroll into some unexpected content.

    "Honestly, I was only reading about bugs in Oracle!"


Log in to reply