Derby
  1. Derby
  2. DERBY-5911

WHERE condition getting pushed into sub-query with FETCH

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major 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.

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

        Activity

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development