1 2 3 Previous Next 31 Replies Latest reply on Aug 1, 2013 12:55 PM by Etbin Go to original post
      • 15. Re: Bug in julian date
        Etbin

        Seems year 0 is a kind og ghost year since everything seems to work

         

        select date '0000-01-01' - date '-0001-01-01' "year -1 days",

               date '0001-01-01' - date '0000-01-01' "year 0 days",

               date '0002-01-01' - date '0001-01-01' "year 1 days",

               date '0001-01-01' - date '-0001-01-01' "expecting 365 days",

               date '0002-01-01' - date '-0001-01-01' "expecting 2 * 365 days",

               date '0000-03-01' - date '0000-02-01' "year 0 february days"

          from dual

        • 16. Re: Bug in julian date
          riedelme
          1. SQL> select * from v$version; 
          2. BANNER 
          3. -------------------------------------------------------------------------------- 
          4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
          5. PL/SQL Release 11.2.0.3.0 - Production 
          6. CORE    11.2.0.3.0      Production 
          7. TNS for Linux: Version 11.2.0.3.0 - Production 
          8. NLSRTL Version 11.2.0.3.0 - Production 
          9. 5 rows selected. 
          10. SQL> drop table t purge; 
          11. Table dropped. 
          12. SQL> create table t (dt date); 
          13. Table created. 
          14. SQL> insert into t (dt) values (trunc(to_date('0001','yyyy') - 365, 'yyyy')); 
          15. 1 row created. 
          16. SQL> select * from t; 
          17. DT 
          18. ------------------- 
          19. 01-01-0000 00:00:00 
          20. 1 row selected. 

           

           

          What am I not seeing?  You already established that the year 0 works (whether or it should is an ongoing argument).  You are subtracting 365 days from the year 1 and are getting the same date January 1 in the year 0.

           

          But I am getting unexpected results of my own.  I am getting July instead of January, different days depending on when I subtract the 365 days, and subtracting 365 days from the year 1 still gives me the year 1 (sometimes).

           

          "sometimes" looks like bugs, although I suspect user error is prowling around my method  somewhere

           

          >create table drop_me (d date);

          >insert into drop_me (d) values (to_date('0001','yyyy'));
          >insert into drop_me (d) values (trunc(to_date('0001','yyyy')-365));

          >alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

          >select d, d-365, trunc(d)-365 from drop_me

          >select * from v$version

           

          >1 rows inserted.
          >1 rows inserted.
          >D                    D-365                TRUNC(D)-365       
          >--------------------- --------------------- ---------------------
          >07/01/0001 00:00:00  07/01/0001 00:00:00  07/01/0001 00:00:00 
          >07/01/0001 00:00:00  07/02/0001 00:00:00  07/02/0001 00:00:00

          >BANNER                                                                       
          >--------------------------------------------------------------------------------
          >Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production   
          >PL/SQL Release 11.2.0.1.0 - Production                                         
          >CORE 11.2.0.1.0 Production                                                       
          >TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                         
          >NLSRTL Version 11.2.0.1.0 - Production                                    

           

          Message was edited by: riedelme

           

          IE would not let me cut Hoek's statement and I missed the 'YYYY' format for the truncate on the insert explaining the July dates.  the 2nd date magically went away after that had been corrected

          • 17. Re: Bug in julian date
            Hoek

            > What am I not seeing?  You already established that the year 0 works (whether or it should is an ongoing argument).

             

            Well, this time I even managed to store the year 0 in a DATE column, which violates the documentation

            • 18. Re: Bug in julian date
              Greg Spall

              Do these shed any light on the subject? Doesn't seem new ..

               

              About Oracle: Year zero

               

              Julian days and year 0 (bug)

              • 19. Re: Bug in julian date
                riedelme

                It seems that working with the year 0 is like traveling through another dimension -- a dimension not only of sight and sound but of mind. A journey into a wondrous land whose boundaries are that of imagination.

                • 20. Re: Bug in julian date
                  riedelme

                  Great links, Greg.  No not new but since I don't use that info day to day I had long since forgotten.  Thanks for posting.

                  • 21. Re: Bug in julian date
                    SomeoneElse

                    I guess Oracle dates are like a big ball of wibbly-wobbly, timey-wimey stuff.

                    • 22. Re: Bug in julian date
                      Hoek

                      Well, I went on a journey to Metalink/MyOracle/Support and raised a SR.

                      Got this reply within an hour:

                      "I totally agree with you this is a buggy behavior, I created Bug 17249144 so that it could be fixed by DEV [...]"

                       

                      So, they're on it 


                      • 23. Re: Bug in julian date
                        Greg Spall

                        How are they going to fix it? Change history?

                        (might be easier at this point)

                        • 24. Re: Bug in julian date
                          BluShadow

                          SomeoneElse wrote:

                           

                          I guess Oracle dates are like a big ball of wibbly-wobbly, timey-wimey stuff.

                           

                          I think the difficulty they have is all the places that dates can be calculated, as that is where the validation needs to be consistent.

                          Validation certainly doesn't exist against the datatype itself (or columns of that datatype) as it's perfectly possible to store invalid dates in them if we bypass the validation...

                           

                          SQL> ed
                          Wrote file afiedt.buf

                            1  create or replace function raw_date(p_in varchar2) return date is
                            2    v_date date;
                            3  begin
                            4    dbms_stats.convert_raw_value(utl_raw.cast_to_raw(p_in), v_date);
                            5    return v_date;
                            6* end;
                          SQL> /

                           

                          Function created.

                           

                          SQL> create table mydates (dt date);

                           

                          Table created.

                          So, first we insert a valid date... like today....


                          SQL> insert into mydates (dt) values (raw_date(chr(120)||chr(113)||chr(8)||chr(1)||chr(8)||chr(31)||chr(1)));

                           

                          1 row created.

                           

                          SQL> select * from mydates;

                           

                          DT
                          --------------------
                          01-AUG-2013 07:30:00

                          Now we insert a completely invalid date...

                           

                          SQL> insert into mydates (dt) values (raw_date(chr(0)||chr(0)||chr(8)||chr(1)||chr(8)||chr(31)||chr(1)));

                           

                          1 row created.

                           

                          SQL> select * from mydates;
                          ERROR:
                          ORA-01801: date format is too long for internal buffer

                           

                          no rows selected

                          Ooops, it didn't like that.  The date data is stored, but we cannot query it as it doesn't have a clue how to format it.  Fair enough, it's just not valid data... not even year 0.

                           

                          What if we insert a year 0 date...

                           

                          SQL> delete from mydates;

                           

                          2 rows deleted.

                           

                          SQL> insert into mydates (dt) values (raw_date(chr(100)||chr(100)||chr(8)||chr(1)||chr(8)||chr(31)||chr(1)));

                           

                          1 row created.

                           

                          SQL> select * from mydates;

                           

                          DT
                          --------------------
                          01-AUG-0000 07:30:00

                          Yep, it's happy with that.

                           

                          So the validation obviously only happens in certain places, like the TO_DATE function etc. but not necessarily when further date arithmetic is done on them.  The underlying datatype just stores whatever it's told to.

                           

                          Note: for those who aren't familiar with the above internal date format bytes, the first two bytes supplied are the century+100 and the year+100, so supplying 100,100 is year 0.  supplying 0,0 is ????.  (and for reference byte 3 is the month, byte 4 is the day, byte 5 is the hours+1, byte 6 is the minutes+1 and byte 7 is the seconds+1)

                          • 25. Re: Bug in julian date
                            bencol

                            So if I do*:

                            insert into mydates (dt) values (raw_date(chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0)));

                            Have I just created an empty, rather then null date? I can then use this as my marriage_date column to distinguish between people who are not married and those who have forgotten when they got married.

                             

                            * obviously I won't, ever.

                            • 26. Re: Bug in julian date
                              BluShadow

                              Yep, I guess you could treat an all 0 byte date as an 'empty date' if you want. 

                               

                              You have to be aware of trying to use it though as it will certainly cause problems for people wants to do simple queries from the table....

                               

                               

                              SQL> insert into mydates values (sysdate);

                               

                              1 row created.

                               

                              SQL> insert into mydates (dt) values (raw_date(chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0)));

                               

                              1 row created.

                               

                              SQL> select * from mydates;
                              ERROR:
                              ORA-01801: date format is too long for internal buffer

                               

                              no rows selected

                               

                              And internally the insertion to the table has done some wierd manipulation if we look...

                               

                              SQL> select dump(dt) from mydates;

                              DUMP(DT)
                              --------------------------------------------------------------------------------------------------------
                              Typ=12 Len=7: 120,113,8,1,10,31,16
                              Typ=12 Len=7: 255,100,0,0,0,0,0

                              ... converting the century/year to year effectively be 15500 (welcome to the future!)

                               

                              Of course you can use it in PL/SQL, just so long as you don't try and convert/format etc.

                              You could get away with comparing it to another raw created date of the same.

                               

                              SQL> insert into mydates values (null);

                               

                              1 row created.

                               

                              SQL> select dump(dt) from mydates;

                               

                              DUMP(DT)
                              --------------------------------------------------------------------------------------------------
                              Typ=12 Len=7: 120,113,8,1,10,31,16
                              Typ=12 Len=7: 255,100,0,0,0,0,0
                              NULL

                               

                              SQL> ed
                              Wrote file afiedt.buf

                                1  declare
                                2    type t_dts is table of date;
                                3    v_dts t_dts;
                                4    v_empty_date date := raw_date(chr(255)||chr(100)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0));
                                5  begin
                                6    select dt bulk collect into v_dts from mydates;
                                7    for i in 1 .. v_dts.count
                                8    loop
                                9      if v_dts(i) = v_empty_date then
                              10        dbms_output.put_line('Empty Date');
                              11      elsif v_dts(i) is null then
                              12        dbms_output.put_line('Null Date');
                              13      else
                              14        dbms_output.put_line(to_char(v_dts(i),'DD/MM/YYYY HH24:MI:SS'));
                              15      end if;
                              16    end loop;
                              17* end;
                              SQL> /
                              01/08/2013 09:30:15
                              Empty Date
                              Null Date

                               

                              PL/SQL procedure successfully completed.

                              • 27. Re: Bug in julian date
                                BluShadow

                                Interstingly that weird conversion of century/year from 0,0 to 255,100 seems to be something to do with SQL.

                                 

                                SQL> select dump(raw_date(chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0)||chr(0))) from dual;

                                DUMP(RAW_DATE(CHR(0)||CHR(0)||CHR(0)||CHR(0)||CHR(0)||CHR(0)||CHR(0)))
                                ---------------------------------------------------------------------------------------------
                                Typ=12 Len=7: 255,100,0,0,0,0,0

                                In PL/SQL if I create a raw date of all 0 bytes and try to compare it to the date from the table which has been weirdly converted, it doesn't match, so the conversion isn't happening in PL/SQL, just when it gets to SQL by the look so fit.

                                • 28. Re: Bug in julian date
                                  Hoek

                                  Keep on going, Blu, this thread is being followed by MOS/DEV, since we've all posted some interesting cases 

                                  Just received an update:

                                  "[snip]...Thanks a lot for sharing this test results with us. Now DEV are actively working on it (BUG 4402804 - WRONG HANDLING OF BC DATES )...[snip]"

                                  • 29. Re: Bug in julian date
                                    BluShadow

                                    To be honest, I wouldn't expect them to try and put validation against the actual datatype, thus preventing raw dates from being inserted.  I imagine such a validation would cause a serious overhead across the database and PL/SQL languages, hence why it's not done there.

                                    I first came across such corrupt dates when we had some corrupt date data and found it was being created by a 3rd party tool that was able to push dates into the tables that had invalid years.... and curious to know how such dates could get into the tables, further investigation showed we could create dates using the convert raw value function which would bypass the date validation (not that the 3rd party tool was doing that, it was just supplying dates that were not invalid via it's own criteria - or it's complete lack of validation), so we were able to replicate the date data the 3rd party tool was creating and get a better understanding of how validation is only within certain aspects of Oracle functionality.

                                     

                                    What is surprising though is what this thread has shown, that you can create a valid date and then 'shift' it using date arithmetic to create year 0 dates etc. especially as you cannot create year 0 dates directly.  That's certainly an inconsistency in the high level language where you're not consciously trying to bypass validation.