Derby
  1. Derby
  2. DERBY-3856

difference between Embedded vs DerbyNetClient in format of return from timestamp(cast(? as varchar(32)))

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.3.1, 10.4.2.0, 10.5.1.1
    • Fix Version/s: 10.5.3.1, 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference

      Description

      There is a slight difference in how Embedded vs. DerbyNetClient return a specific cast.
      This showed up during conversion of the test lang/datetime.sql which before was only run with Embedded...

      The following sql:
      prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as varchar(32))))';
      execute dateTimePS using 'values(cast(1 as integer), ''2003-03-05-17.05.43.111111'')';
      gives:
      1 |2
      -------------------------------------
      Embedded: 1970-01-01|2003-03-05-17.05.43.111111
      DerbyNetClient: 1970-01-01|2003-03-05 17:05:43.111111
      (in Embedded there's a '-' between date and time part, with DerbyNetClient a space; with Embedded the separator between time elements is ., with DerbyNetClient :. Embedded reflects the data as passed in, with DerbyNetClient it seems to be the default timestamp format).

      I am not sure which is correct at this point, but I confirmed the behavior is like this in latest builds of trunk and 10.3 and 10.4 branches.

      1. fix.diff
        5 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          The SQL standard has these productions:

          <timestamp literal> ::= TIMESTAMP <timestamp string>
          <timestamp string> ::= <quote> <unquoted timestamp string> <quote>
          <unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>

          so DerbyNetClient seems the closest to the standard. Derby doesn't
          accept the TIMESTAMP literal either, of course,
          cf. http://wiki.apache.org/db-derby/SQLvsDerbyFeatures, but uses a
          built-in function. Still, it would be nice if the syntax for the
          <timestamp string> was compliant, or at least lenient to accept the
          standard form:

          ij> values timestamp('2003-03-05 17.05.43.111111');
          ERROR 22008: '2003-03-05 17.05.43.111111' is an invalid argument to the timestamp function.
          java.sql.SQLDataException: '2003-03-05 17.05.43.111111' is an invalid argument to the timestamp function.

          ij> values timestamp('2003-03-05-17.05.43.111111');
          1
          --------------------------
          2003-03-05 17:05:43.111111

          1 row selected

          Show
          Dag H. Wanvik added a comment - The SQL standard has these productions: <timestamp literal> ::= TIMESTAMP <timestamp string> <timestamp string> ::= <quote> <unquoted timestamp string> <quote> <unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string> so DerbyNetClient seems the closest to the standard. Derby doesn't accept the TIMESTAMP literal either, of course, cf. http://wiki.apache.org/db-derby/SQLvsDerbyFeatures , but uses a built-in function. Still, it would be nice if the syntax for the <timestamp string> was compliant, or at least lenient to accept the standard form: ij> values timestamp('2003-03-05 17.05.43.111111'); ERROR 22008: '2003-03-05 17.05.43.111111' is an invalid argument to the timestamp function. java.sql.SQLDataException: '2003-03-05 17.05.43.111111' is an invalid argument to the timestamp function. ij> values timestamp('2003-03-05-17.05.43.111111'); 1 -------------------------- 2003-03-05 17:05:43.111111 1 row selected
          Hide
          Kristian Waagan added a comment -

          Triaged July 3, 2009: Assigned normal urgency, marked as Deviation from standard.

          Show
          Kristian Waagan added a comment - Triaged July 3, 2009: Assigned normal urgency, marked as Deviation from standard.
          Hide
          Knut Anders Hatlen added a comment -

          When not using a cast, embedded returns the same value as the client driver, as can be seen from these two statements executed on the embedded driver:

          ij> values timestamp('2003-03-05-17.05.43.111111');
          1
          --------------------------
          2003-03-05 17:05:43.111111

          1 row selected
          ij> values timestamp(cast('2003-03-05-17.05.43.111111' as varchar(32)));
          1
          --------------------------
          2003-03-05-17.05.43.111111

          1 row selected

          I think it would be reasonable to expect those two statements to return the same value.

          Show
          Knut Anders Hatlen added a comment - When not using a cast, embedded returns the same value as the client driver, as can be seen from these two statements executed on the embedded driver: ij> values timestamp('2003-03-05-17.05.43.111111'); 1 -------------------------- 2003-03-05 17:05:43.111111 1 row selected ij> values timestamp(cast('2003-03-05-17.05.43.111111' as varchar(32))); 1 -------------------------- 2003-03-05-17.05.43.111111 1 row selected I think it would be reasonable to expect those two statements to return the same value.
          Hide
          Knut Anders Hatlen added a comment -

          IJ uses getString() and not getTimestamp() to fetch the timestamps. SQLTimestamp.getString() uses a cached string value, or generates a new string representing the timestamp if a cached string value is not available. The method that parses the timestamp for the TIMESTAMP function (SQLTimestamp.parseTimestamp()) initializes the cached string value to the input string.

          The reason why the result is different on embedded and client, is that the network server uses getTimestamp() instead of getString() to access the value that it sends to the client, so a normalized timestamp string is seen on the client.

          The two statements that give different results on the embedded driver, differ because the statement without a cast parses the timestamp at compile time and the one with a cast parses the timestamp at execution time. The timestamp in the first statement is represented by a java.sql.Timestamp in the execution plan, so that the input string is not preserved and a normalized timestamp string will be returned instead. The statement with the cast will do the parsing at execution time and return the SQLTimestamp returned from the parse method, which has cached the original input string.

          Show
          Knut Anders Hatlen added a comment - IJ uses getString() and not getTimestamp() to fetch the timestamps. SQLTimestamp.getString() uses a cached string value, or generates a new string representing the timestamp if a cached string value is not available. The method that parses the timestamp for the TIMESTAMP function (SQLTimestamp.parseTimestamp()) initializes the cached string value to the input string. The reason why the result is different on embedded and client, is that the network server uses getTimestamp() instead of getString() to access the value that it sends to the client, so a normalized timestamp string is seen on the client. The two statements that give different results on the embedded driver, differ because the statement without a cast parses the timestamp at compile time and the one with a cast parses the timestamp at execution time. The timestamp in the first statement is represented by a java.sql.Timestamp in the execution plan, so that the input string is not preserved and a normalized timestamp string will be returned instead. The statement with the cast will do the parsing at execution time and return the SQLTimestamp returned from the parse method, which has cached the original input string.
          Hide
          Knut Anders Hatlen added a comment -

          Same problem can be seen with the DATE function. Example output from the embedded driver:

          ij> values date('10/07/2008');
          1
          ----------
          2008-10-07

          1 row selected
          ij> values date(cast('10/07/2008' as varchar(32)));
          1
          ----------
          10/07/2008

          1 row selected

          Client driver returns 2008-10-07 for both.

          The TIME function does not appear to have this problem. With the embedded driver:

          ij> values time(cast('10.00.00' as varchar(32)));
          1
          --------
          10:00:00

          1 row selected

          Notice that the time string is normalized (dots turned into colons).

          Show
          Knut Anders Hatlen added a comment - Same problem can be seen with the DATE function. Example output from the embedded driver: ij> values date('10/07/2008'); 1 ---------- 2008-10-07 1 row selected ij> values date(cast('10/07/2008' as varchar(32))); 1 ---------- 10/07/2008 1 row selected Client driver returns 2008-10-07 for both. The TIME function does not appear to have this problem. With the embedded driver: ij> values time(cast('10.00.00' as varchar(32))); 1 -------- 10:00:00 1 row selected Notice that the time string is normalized (dots turned into colons).
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a suggested fix for the issue. I have only run DateTimeTest on it yet.

          Changes made by the patch:

          java/engine/org/apache/derby/iapi/types/SQLTimestamp.java:
          java/engine/org/apache/derby/iapi/types/SQLDate.java:

          • Don't set valueString in parseTimestamp()/parseDate(). Since it's left as null, it will be generated (and cached) on the first invocation of getString(), and it will be on a normalized form.

          java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java:

          • Removed code to work around this issue.
          • Added regression test cases for TIMESTAMP, DATE and TIME.
          Show
          Knut Anders Hatlen added a comment - Attaching a suggested fix for the issue. I have only run DateTimeTest on it yet. Changes made by the patch: java/engine/org/apache/derby/iapi/types/SQLTimestamp.java: java/engine/org/apache/derby/iapi/types/SQLDate.java: Don't set valueString in parseTimestamp()/parseDate(). Since it's left as null, it will be generated (and cached) on the first invocation of getString(), and it will be on a normalized form. java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java: Removed code to work around this issue. Added regression test cases for TIMESTAMP, DATE and TIME.
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests ran cleanly with the patch.

          Show
          Knut Anders Hatlen added a comment - All the regression tests ran cleanly with the patch.
          Hide
          Dag H. Wanvik added a comment - - edited

          Seems like the correct solution to treat all these cases uniformly so as to always get the normalized version. Test appears
          to cover the cases. Verified that two fixtures throw errors without the patch: test_DateAndDatetimeFunctionsMore and testDerby3856,
          but work when the rest of the patch is applied.

          +1 from me.

          Show
          Dag H. Wanvik added a comment - - edited Seems like the correct solution to treat all these cases uniformly so as to always get the normalized version. Test appears to cover the cases. Verified that two fixtures throw errors without the patch: test_DateAndDatetimeFunctionsMore and testDerby3856, but work when the rest of the patch is applied. +1 from me.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for testing out the patch, Dag!
          Committed revision 952581.

          Show
          Knut Anders Hatlen added a comment - Thanks for testing out the patch, Dag! Committed revision 952581.
          Hide
          Lily Wei added a comment -

          Reopen to 10.5 back port

          Show
          Lily Wei added a comment - Reopen to 10.5 back port
          Hide
          Mike Matrigali added a comment -

          i am working on backporting this issue.

          Show
          Mike Matrigali added a comment - i am working on backporting this issue.
          Hide
          Mike Matrigali added a comment -

          backporting change #952581 from trunk to 10.5 branch.

          m105_jdk16:37>svn commit
          Sending .
          Sending java/engine/org/apache/derby/iapi/types/SQLDate.java
          Sending java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
          Sending java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java
          Transmitting file data ...
          Committed revision 962640.

          Show
          Mike Matrigali added a comment - backporting change #952581 from trunk to 10.5 branch. m105_jdk16:37>svn commit Sending . Sending java/engine/org/apache/derby/iapi/types/SQLDate.java Sending java/engine/org/apache/derby/iapi/types/SQLTimestamp.java Sending java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java Transmitting file data ... Committed revision 962640.
          Hide
          Mike Matrigali added a comment -

          backporting change #952581 from trunk to 10.6 branch.

          m106_jdk16:14>svn commit
          Sending .
          Sending java/engine/org/apache/derby/iapi/types/SQLDate.java
          Sending java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
          Sending java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java
          Transmitting file data ...
          Committed revision 963150.

          Show
          Mike Matrigali added a comment - backporting change #952581 from trunk to 10.6 branch. m106_jdk16:14>svn commit Sending . Sending java/engine/org/apache/derby/iapi/types/SQLDate.java Sending java/engine/org/apache/derby/iapi/types/SQLTimestamp.java Sending java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java Transmitting file data ... Committed revision 963150.
          Hide
          Mike Matrigali added a comment -

          backported to 10.5 and 10.6, resolving as fixed, and reassigning orginal fixer.

          Show
          Mike Matrigali added a comment - backported to 10.5 and 10.6, resolving as fixed, and reassigning orginal fixer.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Myrna van Lunteren
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development