6 Replies Latest reply on Jan 6, 2015 7:36 PM by Adric

    Timestamp or TO_CHAR bug still not fixed in 4.0.3.16

    jgeidel

      I already posted this bug for SQL Developer versions 4.0.0.13 and 4.01.14, but it is still present in version 4.0.3.16.

       

      Here ist the test scenario:

       

      CREATE TABLE demo (a TIMESTAMP(6) );

      INSERT INTO demo a VALUES( TO_TIMESTAMP( '0187-01-31-15.03.16,309113', 'yyyy-mm-dd-hh24.mi.ss,ff6' ) );

      INSERT INTO demo a VALUES( TO_TIMESTAMP( '0187-01-31-15.03.17,309113', 'yyyy-mm-dd-hh24.mi.ss,ff6' ) );


      SELECT a, TO_CHAR( a, 'yyyy-mm-dd-hh24.mi.ss,ff6' ) FROM demo;

       

      My result is (of course depending on the NLS preferences:

       

      0187-01-31-15.03.17,3091130000187-01-31-15.03.18,309113
      0187-01-31-15.03.16,3091130000187-01-31-15.03.17,309113

       

      As you can see the part containing the "seconds" is not ok. Using the TO_CHAR built-in the result differs from not using the built-in. Looks like not using TO_CHAR brings us correct results.


      Using SQL*Plus works fine: the two resulting columns (with and without TO_CHAR) show the same values (the values used in the insert statement)!


      This bug does not occur in version 3 of SQL Developer!

       

      Nobody out there who's able to reproduce this problem? (except my co-workers)

        • 1. Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16
          thatJeffSmith-Oracle

          Bugs should be reported to My Oracle Support. The forums are not the proper channel for handling bugs. The main exception being the Early Adopter (Betas).

           

          That being said, where are your former posts for these issues? It's possible someone internal saw them and logged a bug, although that's not an expected or official policy.

          • 2. Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16

            That being said, where are your former posts for these issues? It's possible someone internal saw them and logged a bug, although that's not an expected or official policy.

            SQL Developer 4 bug when displaying timestamp values?

            Timestamp bug still not fixed in 4.0.1.14

            • 3. Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16
              jgeidel

              @rp0428: Thanks for posting the links!

               

              @Jeff Smith: I'm not sure if "SandraZhou-Oracle" is internal or not. At least her nickname states so. But she didn't manage to reproduce the problem.

              I will report it to My Oracle.

              • 4. Re: Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16
                Adric

                I can reproduce this via the following. It doesn't appear to matter if it's run as a script or standalone.

                 

                alter session set nls_date_format='yyyy-mm-dd-hh24.mi.ss,ff6';

                with data as (

                   select to_timestamp( '0187-01-31-15.03.16,309113', 'yyyy-mm-dd-hh24.mi.ss,ff6' ) ts from dual

                   union all

                   select to_timestamp( '0187-01-31-15.03.17,309113', 'yyyy-mm-dd-hh24.mi.ss,ff6' ) from dual

                )

                select ts, to_char( ts, 'yyyy-mm-dd-hh24.mi.ss,ff6' ) tc

                   from data;

                 

                Here are the results from SQL Developer 4.0.3.16. It's configured to use the 12.1.0.2.0 64-bit full client on Windows 7 SP1, with "Use OCI/Thick driver" enabled under Tools / Preferences / Database / Advanced.

                 

                TS                         TC

                -------------------------- --------------------------

                0187-01-31-15.03.15,309113 0187-01-31-15.03.16,309113

                0187-01-31-15.03.16,309113 0187-01-31-15.03.17,309113

                 

                SQL*Plus yields the following, properly matching output. It runs from from the same ORACLE_HOME which SQL Developer is using.

                 

                TS                         TC

                -------------------------- --------------------------

                0187-01-31 15:03:16.309113 0187-01-31-15.03.16,309113

                0187-01-31 15:03:17.309113 0187-01-31-15.03.17,309113

                • 5. Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16
                  Gary Graham-Oracle

                  Hi Jan,

                   

                  Not sure if you actually reported this issue via My Oracle Support.  If you did, as far as I can tell, there has yet to be a bug logged against SQL Developer for it.  Anyway, the bug is easily reproducible for any TIMESTAMP value prior to Jan 1, 1970.

                   

                  I ran some tests...

                  • Using SQL Developer 3.2.2 with either jdk1.6.0_45 or jdk1.7.0_67, the bug does not reproduce on an XE 11.2.0.2 DB.
                  • Using SQL Developer 4.0.3 with either jdk1.6.0_45 or jdk1.7.0_67, the bug does reproduce on an XE 11.2.0.2 DB.
                  • Running select dump(a) from demo, all data displays correctly in the expected internal format, matching SQL*Plus.

                  So the problem does not seem related to Java per se, but rather is some new issue in 4.0.x.  Perhaps this has not gained much traction since the displayed value is only off by 1 second for times over 35 years in the past, but still it is a bug.

                  • 6. Re: Timestamp or TO_CHAR bug still not fixed in 4.0.3.16
                    Adric

                    For what it's worth, this appears to be resolved in the 4.1 EA1 release.