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. 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

        Stefan Zeiger created issue -
        Dag H. Wanvik made changes -
        Field Original Value New Value
        Assignee Dag H. Wanvik [ dagw ]
        Dag H. Wanvik made changes -
        Attachment derby5911a.diff [ 12543383 ]
        Attachment derby5911a.stat [ 12543384 ]
        Dag H. Wanvik made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Dag H. Wanvik made changes -
        Status In Progress [ 3 ] Open [ 1 ]
        Dag H. Wanvik made changes -
        Bug behavior facts Wrong query result [ 10366 ]
        Issue & fix info Patch Available [ 10102 ]
        Dag H. Wanvik made changes -
        Attachment derby5911b.diff [ 12544554 ]
        Attachment derby5911b.stat [ 12544555 ]
        Dag H. Wanvik made changes -
        Attachment derby5911b.diff [ 12544554 ]
        Dag H. Wanvik made changes -
        Attachment derby5911b.diff [ 12544556 ]
        Dag H. Wanvik made changes -
        Fix Version/s 10.10.0.0 [ 12321550 ]
        Issue & fix info Patch Available [ 10102 ]
        Dag H. Wanvik made changes -
        Fix Version/s 10.9.1.1 [ 12321551 ]
        Dag H. Wanvik made changes -
        Fix Version/s 10.8.2.3 [ 12318540 ]
        Dag H. Wanvik made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Stefan Zeiger made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Kathey Marsden made changes -
        Fix Version/s 10.8.3.0 [ 12323456 ]
        Fix Version/s 10.8.2.3 [ 12318540 ]
        Kathey Marsden made changes -
        Fix Version/s 10.9.2.0 [ 12323562 ]
        Fix Version/s 10.9.1.1 [ 12321551 ]
        Gavin made changes -
        Workflow jira [ 12723017 ] Default workflow, editable Closed status [ 12801919 ]

          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