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 "Stefan Zeiger (JIRA)" <ji...@apache.org> on 2012/08/28 13:19:07 UTC
[jira] [Created] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Stefan Zeiger created DERBY-5911:
------------------------------------
Summary: 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
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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Issue & fix info: (was: Patch Available)
Fix Version/s: 10.10.0.0
> 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
> Fix For: 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452521#comment-13452521 ]
Mamta A. Satoor commented on DERBY-5911:
----------------------------------------
Dag, I will try the patch for the OFFSET clause query and see how that goes. We probably should add a test case for OFFSET in the new test fixture. Also, I was wondering if the comments in ProjectRestrictNode.java should mention OFFSET and FETCH along with windows function for ORDER BY. Thanks
> 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452818#comment-13452818 ]
Knut Anders Hatlen commented on DERBY-5911:
-------------------------------------------
> Knut: I refrained from pushing inside, cf item #3 above.
+1. It makes sense to keep the fix simple for now. If someone comes up with a real-world example that's meaningful without ORDER BY, and whose performance would benefit from predicate push-down, we can always add more sophisticated logic later.
> 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, derby5911b.diff, derby5911b.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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Attachment: derby5911b.stat
derby5911b.diff
Thanks, Mamta. Uploaded version b of this patch:
- added comment as suggested in PRN also for FETCH/OFFSET
- added test case for OFFSET alone
- removed check for nested ORDER BY: there should always be one if windowing or FETCH/OFFSET is used, and if the user has omitted it, I think it's better to avoid pushing in any case (least surprise principle)
Running regressions.
> 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, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13455955#comment-13455955 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Thanks for confirming that, Mamta!
> 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
> Fix For: 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13454122#comment-13454122 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Regressions ran ok, committed as svn 1384035.
> 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
> Fix For: 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Closed] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Stefan Zeiger (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Stefan Zeiger closed DERBY-5911.
--------------------------------
The fix works for me (tested with current source from branches/10.9 vs 10.9.1.0 binaries).
> 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
> Fix For: 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Fix Version/s: 10.8.2.3
> 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
> Fix For: 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452134#comment-13452134 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Yes that is true, although we'd want to stop pushing one level above the FETCH/OFFSET or windowing, since the WHERE clause is evaluated before the ORDER BY.
> 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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Fix Version/s: 10.9.1.1
> 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
> Fix For: 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13455344#comment-13455344 ]
Mamta A. Satoor commented on DERBY-5911:
----------------------------------------
I forgot to add a comment that I ran ij script with OFFSET(from jira comment 10/Sep/12 21:14) after applying Dag's patch and it gave the correct results.
> 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
> Fix For: 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Stefan Zeiger (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13443082#comment-13443082 ]
Stefan Zeiger commented on DERBY-5911:
--------------------------------------
Note that this attempt at a work-around suffers from the same issue:
select cof_name, price from (
select row_number() over() as rownum, COF_NAME, PRICE from (select * from COFFEES order by COF_NAME) t
) as t where t.rownum <= 2 and t.PRICE < 10
> 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
>
> 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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Attachment: (was: derby5911b.diff)
> 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, derby5911b.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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Attachment: derby5911b.diff
> 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, derby5911b.diff, derby5911b.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
[jira] [Assigned] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik reassigned DERBY-5911:
------------------------------------
Assignee: Dag H. Wanvik
> 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
>
> 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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Attachment: derby5911a.stat
derby5911a.diff
Uploading a patch for this issue. The problem is, as suggested, that the predicate is pushed down during optimization. In presence of ROW_NUMBER and/or FETCH/OFFSET, this cannot be presumed not be correct.
The patch adds logic to check for this, and adds new test cases to OrderByAndOffsetFetchInSubqueries.
Running regressions.
> 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
[jira] [Resolved] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik resolved DERBY-5911.
----------------------------------
Resolution: Fixed
> 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
> Fix For: 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Updated] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5911:
---------------------------------
Issue & fix info: Patch Available
Bug behavior facts: Wrong query result
> 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452561#comment-13452561 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Knut: I refrained from pushing inside, cf item #3 above.
> 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, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452402#comment-13452402 ]
Mamta A. Satoor commented on DERBY-5911:
----------------------------------------
I tried following script in ij for OFFSET(notice the data here is different than the earlier script in this jira)
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', 15);
INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('Colombian_Decaf', 15);
ij> select COF_NAME, PRICE from COFFEES order by COF_NAME offset 1 rows;
COF_NAME |PRICE
--------------------------------------------------------------------------------------------------------------------------------------------
Colombian_Decaf |15
French_Roast |15
ij> select * from(
select COF_NAME, PRICE from COFFEES order by COF_NAME offset 1 rows
)t where t.price>10;
COF_NAME |PRICE
--------------------------------------------------------------------------------------------------------------------------------------------
French_Roast |15
If I understand this correctly, the last query should have returned two rows.
> 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452369#comment-13452369 ]
Mamta A. Satoor commented on DERBY-5911:
----------------------------------------
Can the OFFSET clause in combination with order by cause any problems?
> 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
[jira] [Comment Edited] (DERBY-5911) WHERE condition getting pushed
into sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452134#comment-13452134 ]
Dag H. Wanvik edited comment on DERBY-5911 at 9/11/12 3:49 AM:
---------------------------------------------------------------
Yes that is true, and we'd want to stop pushing one level above the FETCH/OFFSET or windowing, since the WHERE clause is evaluated before the ORDER BY. I'll see if I can make it better without complicating the code too much.
was (Author: dagw):
Yes that is true, although we'd want to stop pushing one level above the FETCH/OFFSET or windowing, since the WHERE clause is evaluated before the ORDER BY.
> 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
[jira] [Comment Edited] (DERBY-5911) WHERE condition getting pushed
into sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452515#comment-13452515 ]
Dag H. Wanvik edited comment on DERBY-5911 at 9/11/12 9:14 AM:
---------------------------------------------------------------
Yes, the OFFSET clause alone is also susceptible to the problem. I thought my patch addressed that also? Did you run this query against the patch, Mamta?
was (Author: dagw):
Yes, the OFFSET clause alone is also susceptible to problem. I thought my patch addressed that also? Did you run this query against the patch, Mamta?
> 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13454456#comment-13454456 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Backported to 10.8 as svn 1384155, resolving. Stefan, if you are OK with the fix, please close this issue.
> 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
> Fix For: 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13452515#comment-13452515 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Yes, the OFFSET clause alone is also susceptible to problem. I thought my patch addressed that also? Did you run this query against the patch, Mamta?
> 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
[jira] [Commented] (DERBY-5911) WHERE condition getting pushed into
sub-query with FETCH
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13454134#comment-13454134 ]
Dag H. Wanvik commented on DERBY-5911:
--------------------------------------
Backported to 10.9 as svn 1384041.
> 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
> Fix For: 10.9.1.1, 10.10.0.0
>
> Attachments: derby5911a.diff, derby5911a.stat, derby5911b.diff, derby5911b.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