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

Prepared statement with OFFSET/FETCH gives different results on subsequent execute

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: 10.5.2.0, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Wrong query result

      Description

      A prepared statement with OFFSET/FETCH does not use the OFFSET/FETCH values after the first EXECUTE (e.g. 2nd invocation of EXECUTE).

      NOTE: Only tested in ij with toursdb database

      ij> prepare foo as 'select * from cities where city_id <= 10 fetch first 5 rows only';
      ij> execute foo;
      CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU&
      ------------------------------------------------------------------------------------------
      1 |Amsterdam |Netherlands |AMS |Dutch |NL
      2 |Athens |Greece |ATH |Greek |GR
      3 |Auckland |New Zealand |AKL |English |NZ
      4 |Beirut |Lebanon |BEY |Arabic |LB
      5 |Bogota |Colombia |BOG |Spanish |CO

      5 rows selected

      ij> execute foo;
      CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU&
      ------------------------------------------------------------------------------------------

      0 rows selected

      ij> remove foo;
      ij> prepare foo as 'select * from cities where city_id <= 10 offset 5 rows';
      ij> execute foo;
      CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU&
      ------------------------------------------------------------------------------------------
      6 |Bombay |India |BOM |Hindi |IN
      7 |Budapest |Hungary |BUD |Hungarian |HU
      8 |Buenos Aires |Argentina |BUE |Spanish |AR
      9 |Cairo |Egypt |CAI |Arabic |EG
      10 |Calcutta |India |CCU |Bengali |IN

      5 rows selected
      ij> execute foo;
      CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU&
      ------------------------------------------------------------------------------------------
      1 |Amsterdam |Netherlands |AMS |Dutch |NL
      2 |Athens |Greece |ATH |Greek |GR
      3 |Auckland |New Zealand |AKL |English |NZ
      4 |Beirut |Lebanon |BEY |Arabic |LB
      5 |Bogota |Colombia |BOG |Spanish |CO
      6 |Bombay |India |BOM |Hindi |IN
      7 |Budapest |Hungary |BUD |Hungarian |HU
      8 |Buenos Aires |Argentina |BUE |Spanish |AR
      9 |Cairo |Egypt |CAI |Arabic |EG
      10 |Calcutta |India |CCU |Bengali |IN

      10 rows selected

        Attachments

        1. derby-4212-2.stat
          0.2 kB
          Dag H. Wanvik
        2. derby-4212-2.diff
          4 kB
          Dag H. Wanvik
        3. derby-4212.stat
          0.2 kB
          Dag H. Wanvik
        4. derby-4212.diff
          4 kB
          Dag H. Wanvik
        5. test.diff
          1 kB
          Knut Anders Hatlen

          Activity

            People

            • Assignee:
              dagw Dag H. Wanvik
              Reporter:
              sradman Steve Radman
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: