[
https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13447946#comment-13447946
]
Knut Anders Hatlen commented on DERBY-5911:
-------------------------------------------
The fix looks right to me. The patch seems to prevent the predicate from being
pushed if any of the sub-queries under the query with the fetch clause is
ordered. Is that broader than it needs to be? Could it be pushed further down
until it reaches the node on top of the ordered one?
> WHERE condition getting pushed into sub-query with FETCH
> --------------------------------------------------------
>
> Key: DERBY-5911
> URL: https://issues.apache.org/jira/browse/DERBY-5911
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.9.1.0
> Environment: Tested with Derby 10.9.1.0 on Windows 7 x64, Java
> 1.6.0_27-b07 server
> Reporter: Stefan Zeiger
> Assignee: Dag H. Wanvik
> Attachments: derby5911a.diff, derby5911a.stat
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
