db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1384035 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
Date Wed, 12 Sep 2012 16:53:37 GMT
Author: dag
Date: Wed Sep 12 16:53:37 2012
New Revision: 1384035

URL: http://svn.apache.org/viewvc?rev=1384035&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.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=1384035&r1=1384034&r2=1384035&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
Wed Sep 12 16:53:37 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java?rev=1384035&r1=1384034&r2=1384035&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
Wed Sep 12 16:53:37 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();
+    }
 }



Mime
View raw message