db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5911) WHERE condition getting pushed into sub-query with FETCH
Date Tue, 04 Sep 2012 18:57:07 GMT

    [ 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

Mime
View raw message