You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2012/09/04 20:57:07 UTC
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
[ 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