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 ma...@apache.org on 2014/10/10 19:00:48 UTC
svn commit: r1630938 - in /db/derby/code/branches/10.10: ./
java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Author: mamta
Date: Fri Oct 10 17:00:47 2014
New Revision: 1630938
URL: http://svn.apache.org/r1630938
Log:
DERBY-6688(NPE (or sane: ASSERT failure) with ROW_NUMBER in some subqueries)
Backporting to 10.10
Modified:
db/derby/code/branches/10.10/ (props changed)
db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
Merged /db/derby/code/branches/10.11:r1616515
Merged /db/derby/code/trunk:r1616332
Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=1630938&r1=1630937&r2=1630938&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Fri Oct 10 17:00:47 2014
@@ -628,7 +628,25 @@ public class SubqueryNode extends ValueN
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)
{
@@ -690,7 +708,7 @@ public class SubqueryNode extends ValueN
*/
flattenable = (resultSet instanceof RowResultSetNode) &&
underTopAndNode && !havingSubquery &&
- orderByList == null &&
+ !haveOrderBy &&
offset == null &&
fetchFirst == null &&
!isWhereExistsAnyInWithWhereSubquery() &&
@@ -762,7 +780,7 @@ public class SubqueryNode extends ValueN
flattenable = (resultSet instanceof SelectNode) &&
!((SelectNode)resultSet).hasWindows() &&
- orderByList == null &&
+ !haveOrderBy &&
offset == null &&
fetchFirst == null &&
underTopAndNode && !havingSubquery &&
@@ -859,20 +877,6 @@ public class SubqueryNode extends ValueN
}
}
- // 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/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java?rev=1630938&r1=1630937&r2=1630938&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java Fri Oct 10 17:00:47 2014
@@ -597,6 +597,34 @@ public class OLAPTest extends BaseJDBCTe
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"}});
+
// DERBY-6691: NPE before
assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR,
s,