db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5911) WHERE condition getting pushed into sub-query with FETCH
Date Mon, 10 Sep 2012 16:54:08 GMT

    [ 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

Mime
View raw message