Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1877

Wrong results using the RTRIM function in Derby 10.0 and Derby 10.1 ...

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.3.1
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

      When RTRIM is added to the following query, it gives incorrect results for column XVAL on Derby 10.0 and Derby 10.1. The same RTRIM query gives correct results when run on Derby 10.2 (I will attach the reproducible script RTRIMreproducibleScript.txt to this Jira entry which can be used to demonstrate the problem on Derby 10.0 and Derby 10.1)

      Kathey Marsden had started a thread titled "Did DERBY-883 fix this wrong results bug?" at http://www.nabble.com/Did-DERBY-883-fix-this-wrong-results-bug--tf2255734.html#a6273521 to track what has changed between Derby 10.1 and Derby 10.2 which has fixed the problem. There were some responses to that thread but no resolution has been reached as to what is causing this query to work fine under Derby 10.2

      – Query w/o RTRIM gives correct results
      select '' HOUR_INTERVAL,
      sum(double(1) / case
      when SAMPLE_RATE = 0 then
      1
      else
      SAMPLE_RATE
      end) / 3600 THROUGHPUT_PER_SEC,
      ((char(r.y_t))) || '' || '' ||
      ((char(r.h_t))) XVAL,
      '' NA,
      '' SORTCOLUMN,
      count SAMPLE_COUNT
      from (select year(s.END_TIME) y_t,
      hour(s.END_TIME) h_t,
      day(s.END_TIME) d_t,
      month(s.END_TIME) mn_t,
      minute(s.END_TIME) mi_t,
      s.SAMPLE_RATE
      from SAMPLE s) r
      GROUP BY r.y_t, r.h_t
      ORDER BY r.y_t, r.h_t;

      – INCORRECT results with the following RTRIM query in Derby 10.1 and Derby 10.0 –
      select '' HOUR_INTERVAL,
      sum(double(1) / case
      when SAMPLE_RATE = 0 then
      1
      else
      SAMPLE_RATE
      end) / 3600 THROUGHPUT_PER_SEC,
      rtrim((char(r.y_t))) || '' || '' ||
      rtrim((char(r.h_t))) XVAL,
      '' NA,
      '' SORTCOLUMN,
      count SAMPLE_COUNT
      from (select year(s.END_TIME) y_t,
      hour(s.END_TIME) h_t,
      day(s.END_TIME) d_t,
      month(s.END_TIME) mn_t,
      minute(s.END_TIME) mi_t,
      s.SAMPLE_RATE
      from SAMPLE s) r
      GROUP BY r.y_t, r.h_t
      ORDER BY r.y_t, r.h_t;

        Attachments

        1. RTRIMreproducibleScript.txt
          2 kB
          Mamta A. Satoor

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              mamtas Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: