db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1616332 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/SubqueryNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Date Wed, 06 Aug 2014 20:01:27 GMT
Author: dag
Date: Wed Aug  6 20:01:26 2014
New Revision: 1616332

URL: http://svn.apache.org/r1616332
Log:
DERBY-6688 NPE (or sane: ASSERT failure) with ROW_NUMBER in some subqueries

In FromSubquery (used in the OK subquery) the phasing is different
than in SubqueryNode (used in the failing query): the order by list is
pushed down too late in the SubqueryNode (after the
SelectNode#preprocess), so that the fact that the order by carries an
implicit window definition isn't recorded, causing the "windows" field
to be null, leading to missing rewriting in the getProjectRestrict
phase, hence the error.

In FromSubquery, the order by list is pushed down into the child
select node just before calling its preprocess method, i.e. the order
by list is present what that happens.

The patch (derby-6688-b) moves the pushing down of the order by list
in SubqueryNode#preprocess to the resultSet (the SelectNode) to just
before the call to preprocess of the resultSet, so the rest of the
windows rewriting machinery kicks in.

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=1616332&r1=1616331&r2=1616332&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Wed
Aug  6 20:01:26 2014
@@ -622,7 +622,25 @@ class SubqueryNode extends ValueNode
 		boolean		flattenable;
 		ValueNode	topNode = this;
 
-		resultSet = resultSet.preprocess(numTables, null, (FromList) null);
+        final boolean haveOrderBy; // need to remember for flattening decision
+
+        // Push the order by list down to the ResultSet
+        if (orderByList != null) {
+            haveOrderBy = true;
+            // If we have more than 1 ORDERBY columns, we may be able to
+            // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+            if (orderByList.size() > 1)
+            {
+                orderByList.removeDupColumns();
+            }
+
+            resultSet.pushOrderByList(orderByList);
+            orderByList = null;
+        } else {
+            haveOrderBy = false;
+        }
+
+        resultSet = resultSet.preprocess(numTables, null, (FromList) null);
 
         if (leftOperand != null)
         {
@@ -684,7 +702,7 @@ class SubqueryNode extends ValueNode
 		 */
 		flattenable = (resultSet instanceof RowResultSetNode) &&
 					  underTopAndNode && !havingSubquery &&
-                      orderByList == null &&
+                      !haveOrderBy &&
                       offset == null &&
                       fetchFirst == null &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
@@ -756,7 +774,7 @@ class SubqueryNode extends ValueNode
 
 		flattenable = (resultSet instanceof SelectNode) &&
  			          !((SelectNode)resultSet).hasWindows() &&
-                      orderByList == null &&
+                      !haveOrderBy &&
                       offset == null &&
                       fetchFirst == null &&
 					  underTopAndNode && !havingSubquery &&
@@ -855,20 +873,6 @@ class SubqueryNode extends ValueNode
 
         resultSet.pushQueryExpressionSuffix();
 
-		// Push the order by list down to the ResultSet
-		if (orderByList != null) {
-			// If we have more than 1 ORDERBY columns, we may be able to
-			// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
-			if (orderByList.size() > 1)
-			{
-				orderByList.removeDupColumns();
-			}
-
-			resultSet.pushOrderByList(orderByList);
-			orderByList = null;
-		}
-
-
         resultSet.pushOffsetFetchFirst( offset, fetchFirst, hasJDBClimitClause );
 
 		/* We transform the leftOperand and the select list for quantified 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java?rev=1616332&r1=1616331&r2=1616332&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Wed Aug  6 20:01:26 2014
@@ -588,11 +588,37 @@ public class OLAPTest extends BaseJDBCTe
             "select * from t4 t_1 join t4 t_2 on " +
             "                     t_1.a = row_number() over () + t_2.a");
 
-        // DERBY-6565
+        // DERBY-6565: NPE before
         assertStatementError(
                 LANG_WINDOW_FUNCTION_CONTEXT_ERROR,
                 s,
                 "update t3 set y = y - row_number() over ()");
+
+        // DERBY-6688: subquery using SubqueryNode rather than FromSubquery
+        // had problems with presence of window function in order by.
+
+        JDBC.assertFullResultSet(s.executeQuery("select * from t3"),
+                new String[][]{{"4"},{"5"},{"6"},{"7"},{"8"}});
+
+        // failed prior to DERBY-6688
+        s.executeUpdate(
+            "update t3 set y = y - " +
+            "    (select y from t3 order by row_number() over () " +
+            "     fetch first 1 row only)");
+        JDBC.assertFullResultSet(s.executeQuery("select * from t3"),
+                new String[][]{{"0"},{"1"},{"2"},{"3"},{"4"}});
+
+        // Used to work before
+        JDBC.assertFullResultSet(s.executeQuery(
+            "select * from  " +
+            "    (select y from t3 order by row_number() over () fetch first 1 row only)
tt"),
+            new String[][]{{"0"}});
+
+        // failed prior to DERBY-6688
+        JDBC.assertFullResultSet(s.executeQuery(
+            "select * from t3 where y = " +
+                "    (select y from t3 order by row_number() over () fetch first row only)"),
+            new String[][]{{"0"}});
     }
 
 



Mime
View raw message