Derby
  1. Derby
  2. DERBY-4621

Invalid conversion from Timestamp to String when calling setTimestamp() with Calendar

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Wrong query result

      Description

      If you set a VARCHAR parameter with setTimestamp(), the string will be formatted differently depending on whether a Calendar is specified or not, even if the default calendar is used.

      Take for example this statement:

      VALUES CAST(? AS VARCHAR(30))

      I executed this statement twice with the same Timestamp instance. First like this:

      ps.setTimestamp(1, ts);

      and then like this

      ps.setTimestamp(1, ts, Calendar.getInstance());

      In this example, both of the methods should use the default Calendar to convert the timestamp to a string. However, I see that they generate different strings:

      2010-04-20 15:17:36.0 vs 2010-04-20 03:17:36

      Note there are two differences:

      1) The method that takes a Calendar object does not show the fraction part (.0)

      2) The method that takes a Calendar object is 12 hours off (03 instead of 15)

      1. TimestampToVarchar.java
        0.8 kB
        Knut Anders Hatlen
      2. derby-4621.diff
        4 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Attaching the file TimestampToVarchar.java which shows the problem with the two methods giving different results.

          Show
          Knut Anders Hatlen added a comment - Attaching the file TimestampToVarchar.java which shows the problem with the two methods giving different results.
          Hide
          Knut Anders Hatlen added a comment -

          Time has the same problem with the 12 hours off.

          Show
          Knut Anders Hatlen added a comment - Time has the same problem with the 12 hours off.
          Hide
          Knut Anders Hatlen added a comment -

          Changing component from JDBC to SQL since the incorrect conversion appears to happen in the SQLChar class.

          Show
          Knut Anders Hatlen added a comment - Changing component from JDBC to SQL since the incorrect conversion appears to happen in the SQLChar class.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch adds a regression test case to DateTimeTest and fixes the bug(s) in SQLChar.

          The problems were

          1) SQLChar.setValue(Timestamp,Calendar) used Timestamp.toString() if the calendar was null, but had its homegrown code to convert to a string if a calendar was specified. This code did not add the fraction part if it was zero. I added a special case for that.

          2) SQLChar.formatJDBCTime(Calendar,StringBuffer) used Calendar.HOUR (which uses 12-hour clock) instead of Calendar.HOUR_OF_DAY (which uses 24-hour clock) to get the hour component of Time and Timestamp values. I corrected that.

          Running the regression tests suites now.

          Show
          Knut Anders Hatlen added a comment - The attached patch adds a regression test case to DateTimeTest and fixes the bug(s) in SQLChar. The problems were 1) SQLChar.setValue(Timestamp,Calendar) used Timestamp.toString() if the calendar was null, but had its homegrown code to convert to a string if a calendar was specified. This code did not add the fraction part if it was zero. I added a special case for that. 2) SQLChar.formatJDBCTime(Calendar,StringBuffer) used Calendar.HOUR (which uses 12-hour clock) instead of Calendar.HOUR_OF_DAY (which uses 24-hour clock) to get the hour component of Time and Timestamp values. I corrected that. Running the regression tests suites now.
          Hide
          Knut Anders Hatlen added a comment -

          All tests ran cleanly.
          Committed revision 936215.

          Show
          Knut Anders Hatlen added a comment - All tests ran cleanly. Committed revision 936215.
          Hide
          Kathey Marsden added a comment -

          reopen for 10.5 back port

          Show
          Kathey Marsden added a comment - reopen for 10.5 back port
          Hide
          Mamta A. Satoor added a comment -

          Will work on backporting this to 10.5

          Show
          Mamta A. Satoor added a comment - Will work on backporting this to 10.5
          Hide
          Mamta A. Satoor added a comment -

          Finished the merge into 10.5 codeline

          Show
          Mamta A. Satoor added a comment - Finished the merge into 10.5 codeline
          Hide
          Mamta A. Satoor added a comment -

          Backport to 10.5 done

          Show
          Mamta A. Satoor added a comment - Backport to 10.5 done

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development