Derby
  1. Derby
  2. DERBY-2602

TIMESTAMP value is truncated when return to client

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: 10.6.1.0
    • Component/s: Network Client
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference, Wrong query result

      Description

      In ParameterMappingTest I see the following differences between embedded and client. Client is truncating the TIMESTAMP value. Look for this bug number in the test for reproduction.

      case java.sql.Types.TIMESTAMP:
      if (param == 2)
      if (usingEmbedded())
      assertEquals("2004-03-12 21:14:24.938222433", val.toString());
      else
      assertEquals("2004-03-12 21:14:24.938222", val.toString());
      else if (param == 3)
      if (usingEmbedded())
      assertEquals("2004-04-12 04:25:26.462983731", val.toString());
      else
      assertEquals("2004-04-12 04:25:26.462983", val.toString());
      break;

        Issue Links

          Activity

          Hide
          Daniel John Debrunner added a comment -

          Is this just when it is converted to a string using getString() or is the value as a java.sql.Timestamp object also incorrect?

          Show
          Daniel John Debrunner added a comment - Is this just when it is converted to a string using getString() or is the value as a java.sql.Timestamp object also incorrect?
          Hide
          A B added a comment -

          I was looking at the "wrong results" queries linked to DERBY-2034 and noticed DERBY-1816. Is there any chance that this issue is related to that one? If not, should this one be linked to DERBY-2034, as well?

          Show
          A B added a comment - I was looking at the "wrong results" queries linked to DERBY-2034 and noticed DERBY-1816 . Is there any chance that this issue is related to that one? If not, should this one be linked to DERBY-2034 , as well?
          Hide
          Kathey Marsden added a comment -

          The issue is with java.sql.Timestamp object as well
          Attached is a reproduction which shows the following behavior.

          Embedded Timestamp
          getHours:17
          getMinutes:14
          getSeconds:24
          getNanos:97625551

          Network Timestamp
          getHours:17
          getMinutes:14
          getSeconds:24
          getNanos:97625000

          Show
          Kathey Marsden added a comment - The issue is with java.sql.Timestamp object as well Attached is a reproduction which shows the following behavior. Embedded Timestamp getHours:17 getMinutes:14 getSeconds:24 getNanos:97625551 Network Timestamp getHours:17 getMinutes:14 getSeconds:24 getNanos:97625000
          Hide
          Kathey Marsden added a comment -

          DERBY-2602 is likely the same issue as DERBY-1816, but I am not sure enough of that to mark it duplicate. Linking the two issues for future reference.

          Show
          Kathey Marsden added a comment - DERBY-2602 is likely the same issue as DERBY-1816 , but I am not sure enough of that to mark it duplicate. Linking the two issues for future reference.
          Hide
          Daniel John Debrunner added a comment -

          I don't think DERBY-2602 and DERBY-1816 are the same issue.

          DERBY-1816 - SQL TIMESTAMP column to java.sql.Time object via RS.getTime() has a zero milli-seconds value.

          DERBY-2602 - SQL TIMESTAMP column to java.sql.Timestamp object via RS.getTimestamp() has a truncated, but non-zero, nano-seconds value.

          If they were the same I would expect 2602 to show a zero value for the factional second part.

          Show
          Daniel John Debrunner added a comment - I don't think DERBY-2602 and DERBY-1816 are the same issue. DERBY-1816 - SQL TIMESTAMP column to java.sql.Time object via RS.getTime() has a zero milli-seconds value. DERBY-2602 - SQL TIMESTAMP column to java.sql.Timestamp object via RS.getTimestamp() has a truncated, but non-zero, nano-seconds value. If they were the same I would expect 2602 to show a zero value for the factional second part.
          Hide
          A B added a comment -

          Okay, thanks Dan. I couldn't tell from the summary if "getTimestamp()" or something else was being used. Sorry for the red herring.

          Show
          A B added a comment - Okay, thanks Dan. I couldn't tell from the summary if "getTimestamp()" or something else was being used. Sorry for the red herring.
          Hide
          A B added a comment -

          For what little it may be worth, code comments in client/am/PreparedStatement seem to suggest that this truncation was intentional (or at least, known) at "set" time:

          ~line 873:

          setInput(parameterIndex, x);
          // once the nanosecond field of timestamp is trim to microsecond for DERBY, should we throw a warning
          //if (getParameterType (parameterIndex) == java.sql.Types.TIMESTAMP && x.getNanos() % 1000 != 0)
          // accumulateWarning (new SqlWarning (agent_.logWriter_, "DERBY timestamp can only store up to microsecond, conversion from nanosecond to microsecond causes rounding."));

          Of course the warning that's commented out refers to "rounding", which isn't quite right--we actually truncate.

          May be relevant, maybe not.

          Show
          A B added a comment - For what little it may be worth, code comments in client/am/PreparedStatement seem to suggest that this truncation was intentional (or at least, known) at "set" time: ~line 873: setInput(parameterIndex, x); // once the nanosecond field of timestamp is trim to microsecond for DERBY, should we throw a warning //if (getParameterType (parameterIndex) == java.sql.Types.TIMESTAMP && x.getNanos() % 1000 != 0) // accumulateWarning (new SqlWarning (agent_.logWriter_, "DERBY timestamp can only store up to microsecond, conversion from nanosecond to microsecond causes rounding.")); Of course the warning that's commented out refers to "rounding", which isn't quite right--we actually truncate. May be relevant, maybe not.
          Hide
          Mayuresh Nirhali added a comment -

          A quick look at client/amt/PreparedStatement:setTimestamp(int parameterIndex, java.sql.Timestamp x) showed following comment,

          setInput(parameterIndex, x);
          // once the nanosecond field of timestamp is trim to microsecond for DERBY, should we throw a warning
          //if (getParameterType (parameterIndex) == java.sql.Types.TIMESTAMP && x.getNanos() % 1000 != 0)
          // accumulateWarning (new SqlWarning (agent_.logWriter_, "DERBY timestamp can only store up to microsecond, conversion from nanosecond to microsecond causes rounding."));

          There seems to be some reason for trimming here. Before going further, I would like to know if anyone knows why it was done this way ????

          Show
          Mayuresh Nirhali added a comment - A quick look at client/amt/PreparedStatement:setTimestamp(int parameterIndex, java.sql.Timestamp x) showed following comment, setInput(parameterIndex, x); // once the nanosecond field of timestamp is trim to microsecond for DERBY, should we throw a warning //if (getParameterType (parameterIndex) == java.sql.Types.TIMESTAMP && x.getNanos() % 1000 != 0) // accumulateWarning (new SqlWarning (agent_.logWriter_, "DERBY timestamp can only store up to microsecond, conversion from nanosecond to microsecond causes rounding.")); There seems to be some reason for trimming here. Before going further, I would like to know if anyone knows why it was done this way ????
          Hide
          Kathey Marsden added a comment -

          Mayuresh, I see that you left this issue with a question that was unanswered. Sorry about that. Are you still interested in working on this issue. If so, I will look at your question. Otherwise please unassign yourself.

          Show
          Kathey Marsden added a comment - Mayuresh, I see that you left this issue with a question that was unanswered. Sorry about that. Are you still interested in working on this issue. If so, I will look at your question. Otherwise please unassign yourself.
          Hide
          Kathey Marsden added a comment -

          Haven't heard from the issue owner in some time. Unassigning so maybe someone else can pick it up. Please reassign yourself if you are still interested in pursuing this issue.

          Show
          Kathey Marsden added a comment - Haven't heard from the issue owner in some time. Unassigning so maybe someone else can pick it up. Please reassign yourself if you are still interested in pursuing this issue.
          Hide
          Rick Hillegas added a comment -

          Does anyone know the answer to Mayuresh's question? It seems to be the crux of the problem.

          Show
          Rick Hillegas added a comment - Does anyone know the answer to Mayuresh's question? It seems to be the crux of the problem.
          Hide
          Rick Hillegas added a comment -

          The Derby timestamp data type is simply a JDBC timestamp, that is, a java.sql.Timestamp. The header comment on that class indicates that it was created for the express purpose of allowing timestamps to have nanosecond precision.

          The SQL Standard does not impose any limitation on the precision of timestamps, leaving the issue to the implementer. The relevant sections of the Standard are part 2, section 4.6 (Datetimes and intervals) and part 2, section 5.3 (<literal>).

          The embedded driver supports the full range of timestamp values allowed by JDBC. In contrast, the network driver limits timestamps to microsecond precision. This truncation occurs both when timestamps pass from the client to the server and also in the reverse direction. I believe that that is the limit imposed by DB2; see http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.intro/db2z_datetimetimestamp.htm The microsecond limit is also enforced by Derby's builtin TIMESTAMP() function; see the header comment for SQLTimestamp.parseDateOrTimestamp(), which notes that an exception is raised if the argument to TIMESTAMP() "is incorrect for an IBM standard timestamp".

          I believe that the network behavior should conform to the existing embedded behavior and that Derby's network layer should support the full range of timestamps allowed by JDBC. There are of course compatibility issues here. I propose the following solution:

          o If both the client and the server are Derby code at 10.6 or higher, then timestamps should not be truncated.

          o However, if either the client or the server is not Derby code at 10.6 or higher, then truncation should continue to occur.

          Implementation notes:

          The truncation client->server happens in DateTime.timestampToTimestampBytes().
          which is read by DRDAConnThread.readAndSetParams()

          The truncation server->client happens in DRDAConnThread.writeFdocaVal()
          which is read by DateTime.timestampBytesToTimestamp()

          Show
          Rick Hillegas added a comment - The Derby timestamp data type is simply a JDBC timestamp, that is, a java.sql.Timestamp. The header comment on that class indicates that it was created for the express purpose of allowing timestamps to have nanosecond precision. The SQL Standard does not impose any limitation on the precision of timestamps, leaving the issue to the implementer. The relevant sections of the Standard are part 2, section 4.6 (Datetimes and intervals) and part 2, section 5.3 (<literal>). The embedded driver supports the full range of timestamp values allowed by JDBC. In contrast, the network driver limits timestamps to microsecond precision. This truncation occurs both when timestamps pass from the client to the server and also in the reverse direction. I believe that that is the limit imposed by DB2; see http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.intro/db2z_datetimetimestamp.htm The microsecond limit is also enforced by Derby's builtin TIMESTAMP() function; see the header comment for SQLTimestamp.parseDateOrTimestamp(), which notes that an exception is raised if the argument to TIMESTAMP() "is incorrect for an IBM standard timestamp". I believe that the network behavior should conform to the existing embedded behavior and that Derby's network layer should support the full range of timestamps allowed by JDBC. There are of course compatibility issues here. I propose the following solution: o If both the client and the server are Derby code at 10.6 or higher, then timestamps should not be truncated. o However, if either the client or the server is not Derby code at 10.6 or higher, then truncation should continue to occur. Implementation notes: The truncation client->server happens in DateTime.timestampToTimestampBytes(). which is read by DRDAConnThread.readAndSetParams() The truncation server->client happens in DRDAConnThread.writeFdocaVal() which is read by DateTime.timestampBytesToTimestamp()
          Hide
          Rick Hillegas added a comment -

          Attaching derby-2602-01-ad-dontTruncate.diff. This patch makes the client and server exchange full nanosecond-precision timestamps as happens in the embedded situation.

          Although the regression tests pass, this patch is not ready for commit. I would like to verify the compatibility of this patch with older clients.

          Once this new behavior is committed, we may want to correct some metadata. For instance, the maximum column width for timestamps is 26 characters today. That is correct for timestamps which only have millisecond-precision, but for full nanosecond-precision, the column width should be 29 characters. However, I don't want to muddy this patch with that change because I suspect that it may affect a lot of test results.

          One of the tricky bits about coding this patch was tracking down the relevant places in the code where the magic number 26 was used. I have replaced those magic numbers with a constant. Hopefully that will help the next person who has to study this logic.

          Touches the following files:

          M java/engine/org/apache/derby/iapi/types/TypeId.java
          M java/engine/org/apache/derby/iapi/reference/DRDAConstants.java

          Introduce constants to replace magic numbers.

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

          M java/drda/org/apache/derby/impl/drda/AppRequester.java
          M java/client/org/apache/derby/client/net/NetAgent.java
          M java/client/org/apache/derby/client/net/NetDatabaseMetaData.java
          M java/client/org/apache/derby/client/net/NetConnection.java
          M java/client/org/apache/derby/client/am/Connection.java
          M java/client/org/apache/derby/client/am/Agent.java

          Create methods for determining whether the connection supports nanosecond-precision timestamps.

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

          M java/drda/org/apache/derby/impl/drda/DRDAConnThread.java
          M java/drda/org/apache/derby/impl/drda/FdocaConstants.java
          M java/client/org/apache/derby/client/net/Request.java
          M java/client/org/apache/derby/client/am/Cursor.java
          M java/client/org/apache/derby/client/am/DateTime.java
          M java/client/org/apache/derby/client/net/NetStatementRequest.java

          Logic to use full nanosecond-precision timestamps if the connection allows it.

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

          M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java

          Updated this test to account for the corrected behavior.

          Show
          Rick Hillegas added a comment - Attaching derby-2602-01-ad-dontTruncate.diff. This patch makes the client and server exchange full nanosecond-precision timestamps as happens in the embedded situation. Although the regression tests pass, this patch is not ready for commit. I would like to verify the compatibility of this patch with older clients. Once this new behavior is committed, we may want to correct some metadata. For instance, the maximum column width for timestamps is 26 characters today. That is correct for timestamps which only have millisecond-precision, but for full nanosecond-precision, the column width should be 29 characters. However, I don't want to muddy this patch with that change because I suspect that it may affect a lot of test results. One of the tricky bits about coding this patch was tracking down the relevant places in the code where the magic number 26 was used. I have replaced those magic numbers with a constant. Hopefully that will help the next person who has to study this logic. Touches the following files: M java/engine/org/apache/derby/iapi/types/TypeId.java M java/engine/org/apache/derby/iapi/reference/DRDAConstants.java Introduce constants to replace magic numbers. --------------- M java/drda/org/apache/derby/impl/drda/AppRequester.java M java/client/org/apache/derby/client/net/NetAgent.java M java/client/org/apache/derby/client/net/NetDatabaseMetaData.java M java/client/org/apache/derby/client/net/NetConnection.java M java/client/org/apache/derby/client/am/Connection.java M java/client/org/apache/derby/client/am/Agent.java Create methods for determining whether the connection supports nanosecond-precision timestamps. --------------- M java/drda/org/apache/derby/impl/drda/DRDAConnThread.java M java/drda/org/apache/derby/impl/drda/FdocaConstants.java M java/client/org/apache/derby/client/net/Request.java M java/client/org/apache/derby/client/am/Cursor.java M java/client/org/apache/derby/client/am/DateTime.java M java/client/org/apache/derby/client/net/NetStatementRequest.java Logic to use full nanosecond-precision timestamps if the connection allows it. --------------- M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java Updated this test to account for the corrected behavior.
          Hide
          Knut Anders Hatlen added a comment -

          The patch looks fine to me. I only have two minor comments:

          1) The other serverSupports* helper methods are located in NetConnection instead of NetAgent. I think moving serverSupportsTimestampNanoseconds() over there as well would give the following benefits:

          a) no need to allocate a new NetDatabaseMetaData object and cache it in NetAgent, since NetConnection already has a cached instance

          b) no need to expose NetConnection.newDatabaseMetaData_() as a public method

          2) Some of the lines exceed the 80 characters limit

          Show
          Knut Anders Hatlen added a comment - The patch looks fine to me. I only have two minor comments: 1) The other serverSupports* helper methods are located in NetConnection instead of NetAgent. I think moving serverSupportsTimestampNanoseconds() over there as well would give the following benefits: a) no need to allocate a new NetDatabaseMetaData object and cache it in NetAgent, since NetConnection already has a cached instance b) no need to expose NetConnection.newDatabaseMetaData_() as a public method 2) Some of the lines exceed the 80 characters limit
          Hide
          Rick Hillegas added a comment -

          Thanks for those suggestions, Knut. Attaching derby-2602-01-ae-dontTruncate.diff, which eliminates the extra cached version of NetDatabaseMetaData. However, things are still a little pear-shaped. I am not able to remove serverSupportsTimestampNanoseconds() from the Agent classes. This is because that method is implemented in the net package but is needed by Cursor in the am package. It seems wrong to me for an am class to directly reference net classes. Mind you, I'm not a big fan of the split between am and net, but there it is. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for those suggestions, Knut. Attaching derby-2602-01-ae-dontTruncate.diff, which eliminates the extra cached version of NetDatabaseMetaData. However, things are still a little pear-shaped. I am not able to remove serverSupportsTimestampNanoseconds() from the Agent classes. This is because that method is implemented in the net package but is needed by Cursor in the am package. It seems wrong to me for an am class to directly reference net classes. Mind you, I'm not a big fan of the split between am and net, but there it is. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for the updated patch, Rick. I think you can get rid of the changes in the Agent classes by declaring serverSupportsTimestampNanoseconds() as an abstract method in am.Connection. There's precedence for that, see supportsSessionDataCaching() and serverSupportsLocators(). Then you can access it from am.Cursor via agent_.connection_.

          Show
          Knut Anders Hatlen added a comment - Thanks for the updated patch, Rick. I think you can get rid of the changes in the Agent classes by declaring serverSupportsTimestampNanoseconds() as an abstract method in am.Connection. There's precedence for that, see supportsSessionDataCaching() and serverSupportsLocators(). Then you can access it from am.Cursor via agent_.connection_.
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. You are right. The abstract method in Connection makes this better. I have made that change and added a compatibility test to the next rev of the patch: derby-2602-01-af-dontTruncate.diff. I am re-running the regression tests now. Thanks.

          Show
          Rick Hillegas added a comment - Thanks, Knut. You are right. The abstract method in Connection makes this better. I have made that change and added a compatibility test to the next rev of the patch: derby-2602-01-af-dontTruncate.diff. I am re-running the regression tests now. Thanks.
          Hide
          Rick Hillegas added a comment -

          Tests ran cleanly. Committed derby-2602-01-af-dontTruncate.diff at subversion revision 933726.

          Show
          Rick Hillegas added a comment - Tests ran cleanly. Committed derby-2602-01-af-dontTruncate.diff at subversion revision 933726.
          Hide
          Rick Hillegas added a comment -

          I believe that this bug is fixed. I opened a related issue, DERBY-4614, to address bad JDBC metadata for timestamps, discovered while fixing this bug.

          Show
          Rick Hillegas added a comment - I believe that this bug is fixed. I opened a related issue, DERBY-4614 , to address bad JDBC metadata for timestamps, discovered while fixing this bug.
          Hide
          Mamta A. Satoor added a comment -

          I am looking at backporting this to 10.5 since DERBY-4615 seems to have dependency on this jira. Let me know if there is any objection to this backport. The changes that went in for this jira seem to have specific code changes for 10.6 and higher codeline. I will try to change it to have those changes work for 10.5 and higher and run the regression tests to see how they go.

          Show
          Mamta A. Satoor added a comment - I am looking at backporting this to 10.5 since DERBY-4615 seems to have dependency on this jira. Let me know if there is any objection to this backport. The changes that went in for this jira seem to have specific code changes for 10.6 and higher codeline. I will try to change it to have those changes work for 10.5 and higher and run the regression tests to see how they go.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Mamta,

          Before you backport the fix, please check that a client running head of 10.5 still can send and receive timestamps when talking to a 10.6.1.0 server, and vice versa. I suspect that this scenario will break if the fix is backported with no other changes.

          Show
          Knut Anders Hatlen added a comment - Hi Mamta, Before you backport the fix, please check that a client running head of 10.5 still can send and receive timestamps when talking to a 10.6.1.0 server, and vice versa. I suspect that this scenario will break if the fix is backported with no other changes.
          Hide
          Mamta A. Satoor added a comment -

          Knut, thanks for the heads up. You are right, can't change this in 10.5 at this point since we have had branch releases on 10.5. I will close this jira back again and will change the test in DERBY-4615 to handle the 10.5 behavior.

          Show
          Mamta A. Satoor added a comment - Knut, thanks for the heads up. You are right, can't change this in 10.5 at this point since we have had branch releases on 10.5. I will close this jira back again and will change the test in DERBY-4615 to handle the 10.5 behavior.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Kathey Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development