[
https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452143#comment-13452143
]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
If there is no ORDER BY inside the FETCH/OFFSET or windowing, one could push
the predicate all the way without breaking SQL semantics, but it might still
confuse users (give unexpected results), since the row order is usually
deterministic in Derby, so it might be a good idea to never push past
FETCH/OFFSET or windowing, what do you think?
> 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
