You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2012/09/13 00:49:34 UTC
svn commit: r1384155 - in /db/derby/code/branches/10.8: ./
java/client/org/apache/derby/client/net/
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Wed Sep 12 22:49:34 2012
New Revision: 1384155
URL: http://svn.apache.org/viewvc?rev=1384155&view=rev
Log:
DERBY-5911 WHERE condition getting pushed into sub-query with FETCH
Patch "derby5911b", which changes logic in ProjectRestrictNode#pushExpressions to avoid pushing WHERE conditions into SELECTs that uses windowing and/or FETCH FIRST/OFFSET clauses. This fixes the bug in this issue.
Adds new test cases in OrderByAndOffsetFetchInSubqueries.
Backported cleanly from trunk.
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/client/org/apache/derby/client/net/NetCursor.java (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
Merged /db/derby/code/trunk:r1384035
Propchange: db/derby/code/branches/10.8/java/client/org/apache/derby/client/net/NetCursor.java
------------------------------------------------------------------------------
Merged /db/derby/code/trunk/java/client/org/apache/derby/client/net/NetCursor.java:r1384035
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=1384155&r1=1384154&r2=1384155&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Wed Sep 12 22:49:34 2012
@@ -1102,22 +1102,25 @@ public class ProjectRestrictNode extends
if (pushPList != null &&
(childResult instanceof SelectNode))
{
- SelectNode childSelect = (SelectNode)childResult;
+ SelectNode childSelect = (SelectNode)childResult;
- if ( (childSelect.hasWindows() &&
- childSelect.orderByList != null) ) {
- // We can't push down if there is an ORDER BY and a window
- // function because that would make ROW_NUMBER give wrong
- // result:
- // E.g.
- // SELECT * from (SELECT ROW_NUMBER() OVER (), j FROM T
- // ORDER BY j) WHERE j=5
- //
- } else {
- pushPList.pushExpressionsIntoSelect((SelectNode) childResult,
- false);
- }
- }
+ // We can't push down if there is a window
+ // function because that would make ROW_NUMBER give wrong
+ // result:
+ // E.g.
+ // SELECT * from (SELECT ROW_NUMBER() OVER (), j FROM T
+ // ORDER BY j) WHERE j=5
+ //
+ // Similarly, don't push if we have OFFSET and/or FETCH FROM.
+ //
+ if ((childSelect.hasWindows() ||
+ childSelect.fetchFirst != null ||
+ childSelect.offset != null)){
+ } else {
+ pushPList.pushExpressionsIntoSelect((SelectNode) childResult,
+ false);
+ }
+ }
/* DERBY-649: Push simple predicates into Unions. It would be up to UnionNode
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java?rev=1384155&r1=1384154&r2=1384155&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java Wed Sep 12 22:49:34 2012
@@ -1020,4 +1020,49 @@ public class OrderByAndOffsetFetchInSubq
rollback();
}
+ /**
+ * Prevent pushing of where predicates into selects with fetch
+ * and/or offset (DERBY-5911). Similarly, for windowed selects.
+ */
+ public void testPushAvoidance() throws SQLException {
+ setAutoCommit(false);
+ Statement s = createStatement();
+ s.executeUpdate
+ ("CREATE TABLE COFFEES (COF_NAME VARCHAR(254),PRICE INTEGER)");
+ s.executeUpdate
+ ("INSERT INTO COFFEES VALUES ('Colombian', 5)");
+ s.executeUpdate
+ ("INSERT INTO COFFEES VALUES ('French_Roast', 5)");
+ s.executeUpdate
+ ("INSERT INTO COFFEES VALUES ('Colombian_Decaf', 20)");
+
+ ResultSet rs = s.executeQuery
+ ("select * from " +
+ " (select COF_NAME, PRICE from COFFEES " +
+ " order by COF_NAME fetch next 2 rows only" +
+ " ) t " +
+ "where t.PRICE < 10");
+
+ JDBC.assertFullResultSet(rs, new String[][]{{"Colombian", "5"}});
+
+ rs = s.executeQuery
+ ("select * from " +
+ " (select COF_NAME, PRICE from COFFEES " +
+ " order by COF_NAME offset 2 row" +
+ " ) t " +
+ "where t.PRICE < 10");
+
+ JDBC.assertFullResultSet(rs, new String[][]{{"French_Roast", "5"}});
+
+ rs = s.executeQuery
+ ("select cof_name, price from " +
+ " (select row_number() over() as rownum, COF_NAME, PRICE from " +
+ " (select * from COFFEES order by COF_NAME) i" +
+ " ) t where rownum <= 2 and PRICE < 10");
+
+ JDBC.assertFullResultSet(rs, new String[][]{{"Colombian", "5"}});
+
+
+ rollback();
+ }
}