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

WHERE condition getting pushed into sub-query with FETCH

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.8.3.0, 10.9.2.2, 10.10.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Tested with Derby 10.9.1.0 on Windows 7 x64, Java 1.6.0_27-b07 server
    • Bug behavior facts:
      Wrong query result

      Description

      Derby pushes query conditions down into subqueries with FETCH limits, thus creating wrong results. Take the following snippet:

      CREATE TABLE COFFEES (COF_NAME VARCHAR(254),PRICE INTEGER);

      INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('Colombian', 5);
      INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('French_Roast', 5);
      INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('Colombian_Decaf', 20);

      select COF_NAME, PRICE from COFFEES order by COF_NAME fetch next 2 rows only;

      select * from (
      select COF_NAME, PRICE from COFFEES order by COF_NAME fetch next 2 rows only
      ) t where t.PRICE < 10;

      The first query correctly returns the rows (Colombian,5), (Colombian_Decaf,20).

      The second query (which filters the result of the first one) returns (Colombian,5), (French_Roast,5). The row (French_Roast,5) should not be there since it is not a result of the first query. It shows up because (supposedly) the filter condition has been evaluated before the fetch limit.

        Attachments

        1. derby5911b.stat
          0.2 kB
          Dag H. Wanvik
        2. derby5911b.diff
          4 kB
          Dag H. Wanvik
        3. derby5911a.stat
          0.3 kB
          Dag H. Wanvik
        4. derby5911a.diff
          5 kB
          Dag H. Wanvik

          Activity

            People

            • Assignee:
              dagw Dag H. Wanvik
              Reporter:
              szeiger Stefan Zeiger
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: