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 2013/10/28 21:37:34 UTC
svn commit: r1536507 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/junit/
Author: dag
Date: Mon Oct 28 20:37:34 2013
New Revision: 1536507
URL: http://svn.apache.org/r1536507
Log:
DERBY-6378 OFFSET/FETCH NEXT ignored when query is enclosed in parentheses
Patch derby-6378-4.
To address the required generality of several layers of nested
multiple clauses allowed by SQL, this patch introduces a stack
mechanism for order by and offset/fetch clauses, factored out into
ResultSetNode#QueryExpressionClause (nested class). From the javadoc
for that class:
For ease of handling order by clause, offset clause and fetch
first/next clauses in subclasses. Clauses on the same nesting level
have the same index in the lists, so at any level, any of the lists'
elements may be empty. For example, (select * from t order by a fetch
next 5 rows only) order by b would have
obl[0] = "order by a",
offset[0] = null,
fetchFirst[0] = "next 5 rows"
and
obl[1] = "order by b",
offset[1] = null
fetchFirst[1] = null
When starting a new level, always remember to do a push() before
adding clauses via
setOffset(org.apache.derby.impl.sql.compile.ValueNode),
setFetchFirst(org.apache.derby.impl.sql.compile.ValueNode),
setOrderByList(org.apache.derby.impl.sql.compile.OrderByList) and
setHasJDBCLimitClause(java.lang.Boolean).
Added tests to OrderByAndOffsetFetchInSubqueries in #testDerby6378,
running regressions.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Mon Oct 28 20:37:34 2013
@@ -578,6 +578,7 @@ public class CursorNode extends DMLState
@Override
public void optimizeStatement() throws StandardException
{
+ resultSet.pushQueryExpressionSuffix();
// Push the order by list down to the ResultSet
if (orderByList != null)
{
@@ -592,7 +593,9 @@ public class CursorNode extends DMLState
orderByList = null;
}
- resultSet.pushOffsetFetchFirst( offset, fetchFirst, hasJDBClimitClause );
+ resultSet.pushOffsetFetchFirst(offset, fetchFirst, hasJDBClimitClause);
+ offset = null;
+ fetchFirst = null;
super.optimizeStatement();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Mon Oct 28 20:37:34 2013
@@ -390,6 +390,7 @@ class FromSubquery extends FromTable
FromList fromList)
throws StandardException
{
+ subquery.pushQueryExpressionSuffix();
// Push the order by list down to the ResultSet
if (orderByList != null)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Mon Oct 28 20:37:34 2013
@@ -840,6 +840,7 @@ public final class InsertNode extends DM
@Override
public void optimizeStatement() throws StandardException
{
+ resultSet.pushQueryExpressionSuffix();
// Push the order by list down to the ResultSet
if (orderByList != null)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java Mon Oct 28 20:37:34 2013
@@ -136,22 +136,27 @@ public class IntersectOrExceptNode exten
intermediateOrderByColumns = new int[ getResultColumns().size()];
intermediateOrderByDirection = new int[ intermediateOrderByColumns.length];
intermediateOrderByNullsLow = new boolean[ intermediateOrderByColumns.length];
- /* If there is an order by on the result of the intersect then use that because we know that doing so
- * will avoid a sort. If the output of the intersect/except is small relative to its inputs then in some
- * cases it would be better to sort the inputs on a different sequence of columns, but it is hard to analyze
- * the input query expressions to see if a sort can be avoided.
+
+ /* If there is an order by on the result of the intersect then use
+ * that because we know that doing so will avoid a sort. If the
+ * output of the intersect/except is small relative to its inputs then
+ * in some cases it would be better to sort the inputs on a different
+ * sequence of columns, but it is hard to analyze the input query
+ * expressions to see if a sort can be avoided.
*/
- if( orderByLists[0] != null)
+ final OrderByList obl = qec.getOrderByList(0);
+
+ if( obl != null)
{
BitSet colsOrdered = new BitSet( intermediateOrderByColumns.length);
- int orderByListSize = orderByLists[0].size();
+ int orderByListSize = obl.size();
int intermediateOrderByIdx = 0;
for( int i = 0; i < orderByListSize; i++)
{
if( colsOrdered.get(i))
continue;
OrderByColumn orderByColumn =
- orderByLists[0].getOrderByColumn(i);
+ obl.getOrderByColumn(i);
intermediateOrderByDirection[intermediateOrderByIdx] = orderByColumn.isAscending() ? 1 : -1;
intermediateOrderByNullsLow[intermediateOrderByIdx] = orderByColumn.isNullsOrderedLow();
int columnIdx = orderByColumn.getResultColumn().getColumnPosition() - 1;
@@ -169,7 +174,7 @@ public class IntersectOrExceptNode exten
intermediateOrderByIdx++;
}
}
- orderByLists[0] = null; // It will be pushed down.
+ qec.setOrderByList(0, null); // It will be pushed down.
}
else // The output of the intersect/except does not have to be ordered
{
@@ -216,6 +221,7 @@ public class IntersectOrExceptNode exten
orderByList.addOrderByColumn( orderByColumn);
}
orderByList.bindOrderByColumns( rsn);
+ rsn.pushQueryExpressionSuffix();
rsn.pushOrderByList( orderByList);
} // end of pushOrderingDown
@@ -321,28 +327,37 @@ public class IntersectOrExceptNode exten
ResultSetNode treeTop = this;
- if( orderByLists[0] != null) {
- // Generate an order by node on top of the intersect/except
- treeTop = new OrderByNode(
- treeTop,
- orderByLists[0],
- tableProperties,
- getContextManager());
- }
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if(obl != null) {
+ // Generate an order by node on top of the intersect/except
+ treeTop = new OrderByNode(
+ treeTop,
+ obl,
+ tableProperties,
+ getContextManager());
+ }
+
+ final ValueNode offset = qec.getOffset(i);
+ final ValueNode fetchFirst = qec.getFetchFirst(i);
- if (offset != null || fetchFirst != null) {
- ResultColumnList newRcl =
- treeTop.getResultColumns().copyListAndObjects();
- newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
-
- treeTop = new RowCountNode(
- treeTop,
- newRcl,
- offset,
- fetchFirst,
- hasJDBClimitClause,
- getContextManager());
+ if (offset != null || fetchFirst != null) {
+ ResultColumnList newRcl =
+ treeTop.getResultColumns().copyListAndObjects();
+ newRcl.genVirtualColumnNodes(
+ treeTop, treeTop.getResultColumns());
+
+ treeTop = new RowCountNode(
+ treeTop,
+ newRcl,
+ offset,
+ fetchFirst,
+ qec.getHasJDBCLimitClause()[i].booleanValue(),
+ getContextManager());
+ }
}
+
return treeTop;
} // end of addNewNodes
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java Mon Oct 28 20:37:34 2013
@@ -668,6 +668,12 @@ class NormalizeResultSetNode extends Sin
dependentScan);
}
+ @Override
+ public void pushQueryExpressionSuffix() {
+ childResult.pushQueryExpressionSuffix();
+ }
+
+
/**
* Push the order by list down from InsertNode into its child result set so
* that the optimizer has all of the information that it needs to consider
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=1536507&r1=1536506&r2=1536507&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 Mon Oct 28 20:37:34 2013
@@ -1085,9 +1085,8 @@ class ProjectRestrictNode extends Single
//
// Similarly, don't push if we have OFFSET and/or FETCH FROM.
//
- if ((childSelect.hasWindows() ||
- childSelect.fetchFirst != null ||
- childSelect.offset != null)){
+ if (childSelect.hasWindows() ||
+ childSelect.hasOffsetFetchFirst()) {
} else {
pushPList.pushExpressionsIntoSelect((SelectNode) childResult,
false);
@@ -1845,6 +1844,12 @@ class ProjectRestrictNode extends Single
this.restriction = restriction;
}
+ @Override
+ public void pushQueryExpressionSuffix() {
+ childResult.pushQueryExpressionSuffix();
+ }
+
+
/**
* Push the order by list down from InsertNode into its child result set so
* that the optimizer has all of the information that it needs to consider
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Mon Oct 28 20:37:34 2013
@@ -21,6 +21,7 @@
package org.apache.derby.impl.sql.compile;
+import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import org.apache.derby.catalog.types.DefaultInfoImpl;
@@ -29,16 +30,12 @@ import org.apache.derby.iapi.reference.C
import org.apache.derby.iapi.services.classfile.VMOpcode;
import org.apache.derby.iapi.services.compiler.MethodBuilder;
import org.apache.derby.iapi.services.context.ContextManager;
-import org.apache.derby.shared.common.sanity.SanityManager;
import org.apache.derby.iapi.sql.ResultColumnDescriptor;
import org.apache.derby.iapi.sql.ResultDescription;
import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.compile.CostEstimate;
-import org.apache.derby.iapi.sql.compile.OptimizableList;
-import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
import org.apache.derby.iapi.sql.compile.Optimizer;
import org.apache.derby.iapi.sql.compile.OptimizerFactory;
-import org.apache.derby.iapi.sql.compile.OptimizerPlan;
import org.apache.derby.iapi.sql.compile.Parser;
import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
import org.apache.derby.iapi.sql.compile.Visitable;
@@ -51,6 +48,7 @@ import org.apache.derby.iapi.sql.diction
import org.apache.derby.iapi.store.access.TransactionController;
import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.shared.common.sanity.SanityManager;
/**
* A ResultSetNode represents a result set, that is, a set of rows. It is
@@ -1588,7 +1586,8 @@ public abstract class ResultSetNode exte
* Push the order by list down from the cursor node
* into its child result set so that the optimizer
* has all of the information that it needs to
- * consider sort avoidance.
+ * consider sort avoidance. Presumes a new level
+ * has been initialized by {@link #pushQueryExpressionSuffix()}.
*
* @param orderByList The order by list
*/
@@ -1604,7 +1603,8 @@ public abstract class ResultSetNode exte
/**
* Push down the offset and fetch first parameters, if any. This method
- * should be overridden by the result sets that need this.
+ * should be overridden by the result sets that need this. Presumes a new
+ * level has been initialized by {@link #pushQueryExpressionSuffix()}.
*
* @param offset the OFFSET, if any
* @param fetchFirst the OFFSET FIRST, if any
@@ -1837,4 +1837,173 @@ public abstract class ResultSetNode exte
return null;
}
+
+ /**
+ * For ease of pushing order by, offset and fetch first/next clauses into
+ * nodes. Clauses on the same nesting level have the same index in the
+ * lists, so at any level, any of the lists' elements may be empty. For
+ * example,
+ *
+ * {@code (select * from t order by a fetch next 5 rows only) order by b}
+ *
+ * would have
+ * <pre>
+ * obl[0] = "order by a",
+ * offset[0] = null,
+ * fetchFirst[0] = "next 5 rows"
+ * </pre>
+ * and
+ * <pre>
+ * obl[1] = "order by b",
+ * offset[1] = null
+ * fetchFirst[1] = null
+ * </pre>
+ *
+ * When starting pushing clauses for a new level, always remember to do a
+ * {@link #push} before adding the clauses via {@link #setOffset}, {@link
+ * #setFetchFirst}, {@link #setOrderByList} and {@link
+ * #setHasJDBCLimitClause}.
+ */
+ static class QueryExpressionClauses {
+ private final List<OrderByList> obl = new ArrayList<OrderByList>();
+ private final List<ValueNode> offset = new ArrayList<ValueNode>();
+ private final List<ValueNode> fetchFirst = new ArrayList<ValueNode>();
+ private final List<Boolean> hasJDBCLimitClause = new ArrayList<Boolean>();
+
+ public QueryExpressionClauses() {
+ // Push one level initially; that way we won't get out-of-range
+ // errors when checking in cases where we have no clauses.
+ // When pushing, we reuse unused levels anyway.
+ push();
+ }
+
+ int size() {
+ return obl.size();
+ }
+
+ void push() {
+ int s = size();
+
+ if (s > 0 &&
+ obl.get(s-1) == null &&
+ offset.get(s-1) == null &&
+ fetchFirst.get(s-1) == null) {
+ // Reuse this level's holders since no clauses are present
+ // here "hasJDBCLimitClause" implies offset or fetchFirst are
+ // not null, so we don't need to check that.
+ if (SanityManager.DEBUG) {
+ SanityManager.ASSERT(hasJDBCLimitClause.get(s-1) == null);
+ }
+ } else {
+ // Push holders for a new level
+ obl.add(null);
+ offset.add(null);
+ fetchFirst.add(null);
+ hasJDBCLimitClause.add(null);
+ }
+ }
+
+ void setOrderByList(OrderByList obl) {
+ this.obl.set(size() - 1, obl);
+ }
+
+ void setOffset(ValueNode v) {
+ this.offset.set(size() - 1, v);
+ }
+
+ void setFetchFirst(ValueNode v) {
+ this.fetchFirst.set(size() - 1, v);
+ }
+
+ void setHasJDBCLimitClause(Boolean b) {
+ this.hasJDBCLimitClause.set(size() - 1, b);
+ }
+
+ OrderByList getOrderByList(int i) {
+ return obl.get(i);
+ }
+
+ void setOrderByList(int i, OrderByList obl) {
+ this.obl.set(i, obl);
+ }
+
+ ValueNode getOffset(int i) {
+ return offset.get(i);
+ }
+
+ void setOffset(int i, ValueNode v) {
+ this.offset.set(i, v);
+ }
+
+ ValueNode getFetchFirst(int i) {
+ return fetchFirst.get(i);
+ }
+
+ void setFetchFirst(int i, ValueNode v) {
+ this.fetchFirst.set(i, v);
+ }
+
+
+ Boolean[] getHasJDBCLimitClause() {
+ return hasJDBCLimitClause.toArray(new Boolean[1]);
+ }
+
+ boolean hasOffsetFetchFirst() {
+ for (ValueNode o : offset) {
+ if (o != null) {
+ return true;
+ }
+ }
+
+ for (ValueNode ff : fetchFirst) {
+ if (ff != null) {
+ return true;
+ }
+ }
+
+ return false;
+ }
+ }
+
+ /**
+ * Set up a new level for order by and fetch/offset clauses.
+ * See Javadoc for {@link #QueryExpressionClauses}.
+ * Overridden by implementors of pushOrderByNode, pushOffsetFetchFirst.
+ */
+ void pushQueryExpressionSuffix() {
+ if (SanityManager.DEBUG) {
+ SanityManager.NOTREACHED();
+ }
+ }
+
+ void printQueryExpressionSuffixClauses(
+ int depth,
+ QueryExpressionClauses qec) {
+
+ for (int i = 0; i < qec.size(); i++) {
+ OrderByList obl = qec.getOrderByList(i);
+ if ( obl != null ) {
+ printLabel(depth, "orderByLists[" + i + "]:");
+ obl.treePrint(depth + 1);
+ }
+
+ ValueNode offset = qec.getOffset(i);
+ if (offset != null) {
+ printLabel(depth, "offset:");
+ offset.treePrint(depth + 1);
+ }
+
+ ValueNode fetchFirst = qec.getFetchFirst(i);
+ if (fetchFirst != null) {
+ printLabel(depth, "fetch first/next:");
+ fetchFirst.treePrint(depth + 1);
+ }
+
+ Boolean hasJDBCLimitClause = qec.getHasJDBCLimitClause()[i];
+ if (hasJDBCLimitClause != null) {
+ printLabel(depth,
+ "hasJDBCLimitClause:" + hasJDBCLimitClause + "\n");
+ }
+ }
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java Mon Oct 28 20:37:34 2013
@@ -54,10 +54,8 @@ class RowResultSetNode extends FromTable
{
SubqueryList subquerys;
private List<AggregateNode> aggregates;
- OrderByList orderByList;
- ValueNode offset; // OFFSET n ROWS
- ValueNode fetchFirst; // FETCH FIRST n ROWS ONLY
- boolean hasJDBClimitClause; // were OFFSET/FETCH FIRST specified by a JDBC LIMIT clause?
+
+ QueryExpressionClauses qec = new QueryExpressionClauses();
/**
* Constructor for a RowResultSetNode.
@@ -77,27 +75,6 @@ class RowResultSetNode extends FromTable
}
}
- /**
- * Convert this object to a String. See comments in QueryTreeNode.java
- * for how this should be done for tree printing.
- *
- * @return This object as a String
- */
- @Override
- public String toString()
- {
- if (SanityManager.DEBUG)
- {
- return "orderByList: " +
- (orderByList != null ? orderByList.toString() : "null") + "\n" +
- super.toString();
- }
- else
- {
- return "";
- }
- }
-
String statementToString()
{
return "VALUES";
@@ -122,21 +99,7 @@ class RowResultSetNode extends FromTable
subquerys.treePrint(depth + 1);
}
- if (orderByList != null) {
- printLabel(depth, "orderByList:");
- orderByList.treePrint(depth + 1);
- }
-
- if (offset != null) {
- printLabel(depth, "offset:");
- offset.treePrint(depth + 1);
- }
-
- if (fetchFirst != null) {
- printLabel(depth, "fetch first/next:");
- fetchFirst.treePrint(depth + 1);
- }
-
+ printQueryExpressionSuffixClauses(depth, qec);
}
}
@@ -273,12 +236,16 @@ class RowResultSetNode extends FromTable
SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");
- if (orderByList != null) {
- orderByList.pullUpOrderByColumns(this);
- orderByList.bindOrderByColumns(this);
- }
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
- bindOffsetFetch(offset, fetchFirst);
+ if (obl != null) {
+ obl.pullUpOrderByColumns(this);
+ obl.bindOrderByColumns(this);
+ }
+
+ bindOffsetFetch(qec.getOffset(i), qec.getFetchFirst(i));
+ }
}
/**
@@ -388,6 +355,11 @@ class RowResultSetNode extends FromTable
{
}
+ @Override
+ public void pushQueryExpressionSuffix() {
+ qec.push();
+ }
+
/**
* Push the order by list down from the cursor node
* into its child result set so that the optimizer
@@ -399,7 +371,7 @@ class RowResultSetNode extends FromTable
@Override
void pushOrderByList(OrderByList orderByList)
{
- this.orderByList = orderByList;
+ qec.setOrderByList(orderByList);
}
/**
@@ -412,9 +384,9 @@ class RowResultSetNode extends FromTable
@Override
void pushOffsetFetchFirst( ValueNode offset, ValueNode fetchFirst, boolean hasJDBClimitClause )
{
- this.offset = offset;
- this.fetchFirst = fetchFirst;
- this.hasJDBClimitClause = hasJDBClimitClause;
+ qec.setOffset(offset);
+ qec.setFetchFirst(fetchFirst);
+ qec.setHasJDBCLimitClause(hasJDBClimitClause);
}
@@ -469,8 +441,13 @@ class RowResultSetNode extends FromTable
// Well, not very likely here, since we're here:
// VALUES x followed by ORDER BY 1,1,2
// but for completeness...
- if (orderByList != null && orderByList.size() > 1) {
- orderByList.removeDupColumns();
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+
+ if (obl != null && obl.size() > 1) {
+ obl.removeDupColumns();
+ }
}
return this;
@@ -664,26 +641,33 @@ class RowResultSetNode extends FromTable
/* Generate the OrderByNode if a sort is still required for
* the order by.
*/
- if (orderByList != null)
- {
- treeTop = new OrderByNode(treeTop,
- orderByList,
- tableProperties,
- getContextManager());
- }
-
- if (offset != null || fetchFirst != null) {
- ResultColumnList newRcl =
- treeTop.getResultColumns().copyListAndObjects();
- newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
-
- treeTop = new RowCountNode(
- treeTop,
- newRcl,
- offset,
- fetchFirst,
- hasJDBClimitClause,
- getContextManager());
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+ if (obl != null) {
+ treeTop = new OrderByNode(treeTop,
+ obl,
+ tableProperties,
+ getContextManager());
+ }
+
+ final ValueNode offset = qec.getOffset(i);
+ final ValueNode fetchFirst = qec.getFetchFirst(i);
+ Boolean hasJDBClimitClause = qec.getHasJDBCLimitClause()[i];
+
+ if (offset != null || fetchFirst != null) {
+ ResultColumnList newRcl =
+ treeTop.getResultColumns().copyListAndObjects();
+ newRcl.genVirtualColumnNodes(
+ treeTop, treeTop.getResultColumns());
+
+ treeTop = new RowCountNode(
+ treeTop,
+ newRcl,
+ offset,
+ fetchFirst,
+ hasJDBClimitClause.booleanValue(),
+ getContextManager());
+ }
}
return treeTop;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Mon Oct 28 20:37:34 2013
@@ -29,7 +29,6 @@ import org.apache.derby.iapi.error.Stand
import org.apache.derby.iapi.reference.Limits;
import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.services.context.ContextManager;
-import org.apache.derby.shared.common.sanity.SanityManager;
import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.compile.CostEstimate;
import org.apache.derby.iapi.sql.compile.OptimizableList;
@@ -43,6 +42,7 @@ import org.apache.derby.iapi.sql.conn.Au
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.shared.common.sanity.SanityManager;
/**
* A SelectNode represents the result set for any of the basic DML
@@ -104,20 +104,9 @@ class SelectNode extends ResultSetNode
*/
private boolean wasGroupBy;
- /**
- * List of columns in ORDER BY list. Usually size 1, if size 2, we
- * are a VALUES top node UNION node and element 2 has been passed
- * from InterceptOrExceptNode to prepare for merge implementation
- * of intersect or except.
- */
- OrderByList[] orderByLists = new OrderByList[1];
-
boolean orderByQuery ;
- ValueNode offset; // OFFSET n ROWS, if given
- ValueNode fetchFirst; // FETCH FIRST n ROWS ONLY, if given
- boolean hasJDBClimitClause; // were OFFSET/FETCH FIRST specified by a JDBC LIMIT clause?
-
+ QueryExpressionClauses qec = new QueryExpressionClauses();
/* PredicateLists for where clause */
PredicateList wherePredicates;
@@ -346,27 +335,7 @@ class SelectNode extends ResultSetNode
havingClause.treePrint(depth + 1);
}
- if (orderByLists != null)
- {
- for (int i = 0; i < orderByLists.length; i++)
- {
- if ( orderByLists[i] != null )
- {
- printLabel(depth, "orderByLists[" + i + "]:");
- orderByLists[i].treePrint(depth + 1);
- }
- }
- }
-
- if (offset != null) {
- printLabel(depth, "offset:");
- offset.treePrint(depth + 1);
- }
-
- if (fetchFirst != null) {
- printLabel(depth, "fetch first/next:");
- fetchFirst.treePrint(depth + 1);
- }
+ printQueryExpressionSuffixClauses(depth, qec);
if (preJoinFL != null)
{
@@ -552,12 +521,17 @@ class SelectNode extends ResultSetNode
int fromListSize = fromList.size();
int numDistinctAggs;
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(fromList != null && resultColumns != null,
- "Both fromList and resultColumns are expected to be non-null");
+ if (SanityManager.DEBUG) {
+ SanityManager.ASSERT(fromList != null && resultColumns != null,
+ "Both fromList and resultColumns are expected to be non-null");
+ }
- if (orderByLists[0] != null) {
- orderByLists[0].pullUpOrderByColumns(this);
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null) {
+ obl.pullUpOrderByColumns(this);
+ }
}
/* NOTE - a lot of this code would be common to bindTargetExpression(),
@@ -727,11 +701,15 @@ class SelectNode extends ResultSetNode
throw StandardException.newException(SQLState.LANG_USER_AGGREGATE_MULTIPLE_DISTINCTS);
}
- if (orderByLists[0] != null) {
- orderByLists[0].bindOrderByColumns(this);
- }
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null) {
+ obl.bindOrderByColumns(this);
+ }
- bindOffsetFetch(offset, fetchFirst);
+ bindOffsetFetch(qec.getOffset(i), qec.getFetchFirst(i));
+ }
}
/**
@@ -964,6 +942,11 @@ class SelectNode extends ResultSetNode
fromList.rejectParameters();
}
+ @Override
+ public void pushQueryExpressionSuffix() {
+ qec.push();
+ }
+
/**
* Push the order by list down from the cursor node
* into its child result set so that the optimizer
@@ -975,34 +958,7 @@ class SelectNode extends ResultSetNode
@Override
void pushOrderByList(OrderByList orderByList)
{
- if (orderByLists[0] != null) {
- // A push down of an internal ordering from IntersectOrExceptNode
- // on a SELECT that already has an ORDER BY. The following sanity
- // check helps verify that this is indeed the case.
- if (SanityManager.DEBUG) {
- SanityManager.ASSERT(
- orderByList.size() == resultColumns.visibleSize());
- OrderByColumn obc = orderByList.elementAt(0);
- SanityManager.ASSERT(
- obc.getExpression() instanceof NumericConstantNode);
- try {
- SanityManager.ASSERT(
- ((NumericConstantNode)obc.getExpression()).
- value.getInt() == 1);
- } catch (Exception e) {
- SanityManager.THROWASSERT(e);
- }
- }
-
- // Possible optimization: check to see if this extra ordering can
- // be eliminated, i.e. the two orderings are the same.
- OrderByList[] newOrderByLists = {
- orderByLists[0],
- orderByList};
- orderByLists = newOrderByLists;
- } else {
- orderByLists[0] = orderByList;
- }
+ qec.setOrderByList(orderByList);
// remember that there was an order by list
orderByQuery = true;
}
@@ -1017,9 +973,9 @@ class SelectNode extends ResultSetNode
@Override
void pushOffsetFetchFirst( ValueNode offset, ValueNode fetchFirst, boolean hasJDBClimitClause )
{
- this.offset = offset;
- this.fetchFirst = fetchFirst;
- this.hasJDBClimitClause = hasJDBClimitClause;
+ qec.setOffset(offset);
+ qec.setFetchFirst(fetchFirst);
+ qec.setHasJDBCLimitClause(Boolean.valueOf(hasJDBClimitClause));
}
@@ -1177,21 +1133,23 @@ class SelectNode extends ResultSetNode
performTransitiveClosure(numTables);
}
- for (int i = 0; i < orderByLists.length; i++) {
- if (orderByLists[i] != null)
+
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+ if (obl != null)
{
// Remove constant columns from order by list. Constant
// columns are ones that have equality comparisons with
// constant expressions (e.g. x = 3)
- orderByLists[i].removeConstantColumns(wherePredicates);
+ obl.removeConstantColumns(wherePredicates);
/*
** It's possible for the order by list to shrink to
** nothing as a result of removing constant columns. If
** this happens, get rid of the list entirely.
*/
- if (orderByLists[i].size() == 0)
+ if (obl.size() == 0)
{
- orderByLists[i] = null;
+ qec.setOrderByList(i, null);
resultColumns.removeOrderByColumns();
}
}
@@ -1247,7 +1205,7 @@ class SelectNode extends ResultSetNode
}
}
- for (int i = 0; i < orderByLists.length; i++) {
+ for (int i = 0; i < qec.size(); i++) {
/* If we were unable to eliminate the distinct and we have
* an order by then we can consider eliminating the sort for
* the order by. All of the columns in the order by list must
@@ -1263,15 +1221,16 @@ class SelectNode extends ResultSetNode
* above the select so that the shape of the result set
* is as expected.
*/
- if (isDistinct && orderByLists[i] != null &&
- orderByLists[i].allAscending())
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (isDistinct && obl != null && obl.allAscending())
{
/* Order by list currently restricted to columns in select
* list, so we will always eliminate the order by here.
*/
- if (orderByLists[i].isInOrderPrefix(resultColumns))
+ if (obl.isInOrderPrefix(resultColumns))
{
- orderByLists[i] = null;
+ qec.setOrderByList(i, null);
}
else
{
@@ -1282,11 +1241,10 @@ class SelectNode extends ResultSetNode
* order.
*/
newTop = genProjectRestrictForReordering();
- orderByLists[i].resetToSourceRCs();
- resultColumns =
- orderByLists[i].reorderRCL(resultColumns);
+ obl.resetToSourceRCs();
+ resultColumns = obl.reorderRCL(resultColumns);
newTop.getResultColumns().removeOrderByColumns();
- orderByLists[i] = null;
+ qec.setOrderByList(i, null);
}
orderByAndDistinctMerged = true;
}
@@ -1321,7 +1279,7 @@ class SelectNode extends ResultSetNode
}
- if (orderByLists[0] != null) { // only relevant for first one
+ if (qec.getOrderByList(0) != null) { // only relevant for first one
// Collect window function calls and in-lined window definitions
// contained in them from the orderByList.
@@ -1329,7 +1287,7 @@ class SelectNode extends ResultSetNode
CollectNodesVisitor<WindowFunctionNode> cnvw =
new CollectNodesVisitor<WindowFunctionNode>(
WindowFunctionNode.class);
- orderByLists[0].accept(cnvw);
+ qec.getOrderByList(0).accept(cnvw);
for (WindowFunctionNode wfn : cnvw.getList()) {
windowFuncCalls.add(wfn);
@@ -1502,18 +1460,18 @@ class SelectNode extends ResultSetNode
return false;
}
- /* Don't flatten if selectNode now has an order by */
- if ((orderByLists[0] != null) &&
- (orderByLists[0].size() > 0))
- {
- return false;
- }
+ for (int i = 0; i < qec.size(); i++) {
+ // Don't flatten if selectNode now has an order by or offset/fetch
+ // clause
+ if ((qec.getOrderByList(i) != null) &&
+ (qec.getOrderByList(i).size() > 0)) {
+ return false;
+ }
- /* Don't flatten if selectNode has OFFSET or FETCH */
- if ((offset != null) ||
- (fetchFirst != null))
- {
- return false;
+ if ((qec.getOffset(i) != null) ||
+ (qec.getFetchFirst(i) != null)) {
+ return false;
+ }
}
return true;
@@ -1533,7 +1491,7 @@ class SelectNode extends ResultSetNode
ResultSetNode genProjectRestrict(int origFromListSize)
throws StandardException
{
- boolean[] eliminateSort = new boolean[orderByLists.length];
+ boolean[] eliminateSort = new boolean[qec.size()];
ResultSetNode prnRSN;
@@ -1677,13 +1635,13 @@ class SelectNode extends ResultSetNode
* the order by.
*/
- for (int i=0; i < orderByLists.length; i++) {
- if (orderByLists[i] != null)
- {
- if (orderByLists[i].getSortNeeded())
+ for (int i=0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+ if (obl != null) {
+ if (obl.getSortNeeded())
{
prnRSN = new OrderByNode(prnRSN,
- orderByLists[i],
+ obl,
null,
getContextManager());
prnRSN.costEstimate = costEstimate.cloneMe();
@@ -1723,7 +1681,10 @@ class SelectNode extends ResultSetNode
// Do this only after the main ORDER BY; any extra added by
// IntersectOrExceptNode should sit on top of us.
- if (i == 0 && (offset != null || fetchFirst != null)) {
+ ValueNode offset = qec.getOffset(i);
+ ValueNode fetchFirst = qec.getFetchFirst(i);
+
+ if (offset != null || fetchFirst != null) {
// Keep the same RCL on top, since there may be references to
// its result columns above us.
ResultColumnList topList = prnRSN.getResultColumns();
@@ -1735,7 +1696,7 @@ class SelectNode extends ResultSetNode
topList,
offset,
fetchFirst,
- hasJDBClimitClause,
+ qec.getHasJDBCLimitClause()[i].booleanValue(),
getContextManager());
}
}
@@ -1779,9 +1740,10 @@ class SelectNode extends ResultSetNode
getContextManager());
}
- for (int i=0; i < orderByLists.length; i++) {
- if (!(orderByLists[i] != null && orderByLists[i].getSortNeeded()) &&
- orderByQuery)
+ for (int i=0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (!(obl != null && obl.getSortNeeded()) && orderByQuery)
{
// Remember whether or not we can eliminate the sort.
eliminateSort[i] = true;
@@ -1794,7 +1756,7 @@ class SelectNode extends ResultSetNode
*/
if (eliminateSort[i])
{
- prnRSN.adjustForSortElimination(orderByLists[i]);
+ prnRSN.adjustForSortElimination(obl);
}
/* Set the cost of this node in the generated node */
@@ -1900,15 +1862,18 @@ class SelectNode extends ResultSetNode
/* Optimize any subquerys before optimizing the underlying result set */
/* selectSubquerys is always allocated at bind() time */
- if (SanityManager.DEBUG)
- SanityManager.ASSERT(selectSubquerys != null,
- "selectSubquerys is expected to be non-null");
+ if (SanityManager.DEBUG) {
+ SanityManager.ASSERT(selectSubquerys != null,
+ "selectSubquerys is expected to be non-null");
+ }
// If we have more than 1 ORDERBY columns, we may be able to
// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
- for (int i=0; i < orderByLists.length; i++) {
- if (orderByLists[i] != null && orderByLists[i].size() > 1) {
- orderByLists[i].removeDupColumns();
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null && obl.size() > 1) {
+ obl.removeDupColumns();
}
}
@@ -1990,10 +1955,10 @@ class SelectNode extends ResultSetNode
}
opt = getOptimizer(fromList,
- wherePredicates,
- dataDictionary,
- orderByLists[0], // use first one
- overridingPlan);
+ wherePredicates,
+ dataDictionary,
+ qec.getOrderByList(0), // use first one
+ overridingPlan);
opt.setOuterRows(outerRows);
/* Optimize this SelectNode */
@@ -2595,25 +2560,26 @@ class SelectNode extends ResultSetNode
groupByList = (GroupByList) groupByList.accept( v );
}
- if (orderByLists != null)
- {
- for (int i = 0; i < orderByLists.length; i++)
- {
- if ( orderByLists[ i ] != null )
- {
- orderByLists[i] = (OrderByList) orderByLists[ i ].accept( v );
- }
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null) {
+ qec.setOrderByList(i, (OrderByList)obl.accept(v));
}
- }
- if (offset != null) {
- offset = (ValueNode) offset.accept( v );
- }
-
- if (fetchFirst != null) {
- fetchFirst = (ValueNode) fetchFirst.accept( v );
+ final ValueNode offset = qec.getOffset(i);
+
+ if (offset != null) {
+ qec.setOffset(i, (ValueNode)offset.accept(v));
+ }
+
+ final ValueNode fetchFirst = qec.getFetchFirst(i);
+
+ if (fetchFirst != null) {
+ qec.setFetchFirst(i, (ValueNode)fetchFirst.accept(v));
+ }
}
-
+
if (preJoinFL != null)
{
preJoinFL = (FromList) preJoinFL.accept( v );
@@ -2677,4 +2643,9 @@ class SelectNode extends ResultSetNode
throws StandardException
{
}
+
+ boolean hasOffsetFetchFirst() {
+ return qec.hasOffsetFetchFirst();
+ }
+
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java Mon Oct 28 20:37:34 2013
@@ -52,17 +52,8 @@ abstract class SetOperatorNode extends T
*/
boolean all;
- /**
- * List of columns in ORDER BY list. Usually size 1, if size 2, we
- * are a VALUES top node UNION node and element 2 has been passed
- * from InterceptOrExceptNode to prepare for merge implementation
- * of intersect or except.
- */
- OrderByList[] orderByLists = new OrderByList[1];
+ QueryExpressionClauses qec = new QueryExpressionClauses();
- ValueNode offset; // OFFSET n ROWS
- ValueNode fetchFirst; // FETCH FIRST n ROWS ONLY
- boolean hasJDBClimitClause; // were OFFSET/FETCH FIRST specified by a JDBC LIMIT clause?
// List of scoped predicates for pushing during optimization.
private PredicateList leftOptPredicates;
private PredicateList rightOptPredicates;
@@ -547,22 +538,7 @@ abstract class SetOperatorNode extends T
{
super.printSubNodes(depth);
- if (orderByLists[0] != null) {
- for (int i = 0; i < orderByLists.length; i++) {
- printLabel(depth, "orderByLists[" + i + "]:");
- orderByLists[i].treePrint(depth + 1);
- }
- }
-
- if (offset != null) {
- printLabel(depth, "offset:");
- offset.treePrint(depth + 1);
- }
-
- if (fetchFirst != null) {
- printLabel(depth, "fetch first/next:");
- fetchFirst.treePrint(depth + 1);
- }
+ printQueryExpressionSuffixClauses(depth, qec);
}
}
/**
@@ -782,12 +758,17 @@ abstract class SetOperatorNode extends T
public void bindExpressions(FromList fromList) throws StandardException {
// Actions for UnionNode qua top node of a multi-valued table value
// constructor
- if (orderByLists[0] != null) {
- orderByLists[0].bindOrderByColumns(this);
- orderByLists[0].pullUpOrderByColumns(this);
+ for (int i = 0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null) {
+ obl.bindOrderByColumns(this);
+ obl.pullUpOrderByColumns(this);
+ }
+
+ bindOffsetFetch(qec.getOffset(i), qec.getFetchFirst(i));
}
- bindOffsetFetch(offset, fetchFirst);
super.bindExpressions(fromList);
}
@@ -808,6 +789,11 @@ abstract class SetOperatorNode extends T
rightResultSet.bindTargetExpressions(fromListParam);
}
+ @Override
+ public void pushQueryExpressionSuffix() {
+ qec.push();
+ }
+
/**
* Push the order by list down from the cursor node
* into its child result set so that the optimizer
@@ -819,32 +805,7 @@ abstract class SetOperatorNode extends T
@Override
void pushOrderByList(OrderByList orderByList)
{
- if (this.orderByLists[0] != null) {
- // Presumably a push down order by from IntersectOrExceptNode
- // on a VALUES clause that already has an ORDER BY.
- if (SanityManager.DEBUG) {
- SanityManager.ASSERT(
- orderByList.size() == resultColumns.visibleSize());
- OrderByColumn obc = orderByList.elementAt(0);
- SanityManager.ASSERT(
- obc.getExpression() instanceof NumericConstantNode);
- try {
- SanityManager.ASSERT(
- ((NumericConstantNode)obc.getExpression())
- .value.getInt() == 1);
- } catch (Exception e) {
- SanityManager.THROWASSERT(e);
- }
- }
-
- // FIXME: Check to see if this extra ordering can be eliminated
- OrderByList[] newOrderByLists = new OrderByList[2];
- newOrderByLists[0] = orderByLists[0];
- newOrderByLists[1] = orderByList;
- this.orderByLists = newOrderByLists;
- } else {
- this.orderByLists[0] = orderByList;
- }
+ qec.setOrderByList(orderByList);
}
/**
@@ -857,9 +818,9 @@ abstract class SetOperatorNode extends T
@Override
void pushOffsetFetchFirst( ValueNode offset, ValueNode fetchFirst, boolean hasJDBClimitClause )
{
- this.offset = offset;
- this.fetchFirst = fetchFirst;
- this.hasJDBClimitClause = hasJDBClimitClause;
+ qec.setOffset(offset);
+ qec.setFetchFirst(fetchFirst);
+ qec.setHasJDBCLimitClause(hasJDBClimitClause);
}
@@ -918,16 +879,19 @@ abstract class SetOperatorNode extends T
* above the select so that the shape of the result set
* is as expected.
*/
- for (int i = 0; i < orderByLists.length; i++) {
- if ((! all) && orderByLists[i] != null &&
- orderByLists[i].allAscending())
+ for (int i = 0; i < qec.size(); i++) {
+ OrderByList obl = qec.getOrderByList(i);
+
+ if ((! all) && obl != null &&
+ obl.allAscending())
{
/* Order by list currently restricted to columns in select
* list, so we will always eliminate the order by here.
*/
- if (orderByLists[i].isInOrderPrefix(resultColumns))
+ if (obl.isInOrderPrefix(resultColumns))
{
- orderByLists[i] = null;
+ obl = null;
+ qec.setOrderByList(i, null);
}
/* RESOLVE - We currently only eliminate the order by if it is
* a prefix of the select list. We do not currently do the
@@ -946,8 +910,8 @@ abstract class SetOperatorNode extends T
// UnionNode qua top of table value constructor with ordering
// If we have more than 1 ORDERBY columns, we may be able to
// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
- if (orderByLists[i] != null && orderByLists[i].size() > 1) {
- orderByLists[i].removeDupColumns();
+ if (obl != null && obl.size() > 1) {
+ obl.removeDupColumns();
}
}
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=1536507&r1=1536506&r2=1536507&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 Mon Oct 28 20:37:34 2013
@@ -853,6 +853,8 @@ 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
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java Mon Oct 28 20:37:34 2013
@@ -463,18 +463,22 @@ class UnionNode extends SetOperatorNode
/* Generate the OrderByNode if a sort is still required for
* the order by.
*/
- for (int i=0; i < orderByLists.length; i++) {
- if (orderByLists[i] != null)
+ for (int i=0; i < qec.size(); i++) {
+ final OrderByList obl = qec.getOrderByList(i);
+
+ if (obl != null)
{
treeTop = new OrderByNode(treeTop,
- orderByLists[i],
+ obl,
tableProperties,
getContextManager());
}
// Do this only after the main ORDER BY; any extra added by
- // IntersectOrExceptNode should sit on top of us.
- if (i == 0 && (offset != null || fetchFirst != null)) {
+ final ValueNode offset = qec.getOffset(i);
+ final ValueNode fetchFirst = qec.getFetchFirst(i);
+
+ if (offset != null || fetchFirst != null) {
ResultColumnList newRcl =
treeTop.getResultColumns().copyListAndObjects();
newRcl.genVirtualColumnNodes(treeTop,
@@ -485,7 +489,7 @@ class UnionNode extends SetOperatorNode
newRcl,
offset,
fetchFirst,
- hasJDBClimitClause,
+ qec.getHasJDBCLimitClause()[i].booleanValue(),
getContextManager());
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon Oct 28 20:37:34 2013
@@ -1818,7 +1818,7 @@ public class SQLParser
}
}
- private static boolean hasSelectSuffixClause(
+ private static boolean hasQueryExpressionSuffix(
OrderByList orderBy, ValueNode[] offsetClauses)
{
return
@@ -5114,11 +5114,12 @@ nonJoinQueryPrimary() throws StandardExc
hasJDBClimitClause = offsetFetchFirstClause( offsetClauses )
<RIGHT_PAREN>
{
- if (hasSelectSuffixClause(orderCols, offsetClauses)) {
+ if (hasQueryExpressionSuffix(orderCols, offsetClauses)) {
if (primary instanceof SelectNode ||
primary instanceof UnionNode || // table value constructor
primary instanceof RowResultSetNode) {
+ primary.pushQueryExpressionSuffix();
primary.pushOrderByList(orderCols);
primary.pushOffsetFetchFirst(
offsetClauses[OFFSET_CLAUSE],
@@ -8217,7 +8218,9 @@ nullOrdering(OrderByColumn orderCol) :
*/
boolean
offsetFetchFirstClause( ValueNode[] clauses ) throws StandardException :
-{}
+{
+ boolean result = false;
+}
{
LOOKAHEAD ( { getToken(1).kind == OFFSET || getToken(1).kind == FETCH } )
sqlStandardOffsetFetchFirst( clauses )
@@ -8228,9 +8231,12 @@ offsetFetchFirstClause( ValueNode[] clau
[
LOOKAHEAD ( { getToken(1).kind == LEFT_BRACE } )
jdbcLimitOffset( clauses )
+ {
+ result = true;
+ }
]
{
- return true;
+ return result;
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Mon Oct 28 20:37:34 2013
@@ -21,24 +21,22 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.sql.ParameterMetaData;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
-import java.sql.PreparedStatement;
-import java.sql.ParameterMetaData ;
-
import junit.framework.Test;
import junit.framework.TestSuite;
-
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
/**
- * Test <result offset clause> and <fetch first clause>.
+ * Test {@code <result offset clause>} and {@code <fetch first clause>}.
*/
public class OffsetFetchNextTest extends BaseJDBCTestCase {
@@ -82,6 +80,7 @@ public class OffsetFetchNextTest extends
return new CleanDatabaseTestSetup(
new TestSuite(OffsetFetchNextTest.class,
suiteName)) {
+ @Override
protected void decorateSQL(Statement s)
throws SQLException {
createSchemaObjects(s);
@@ -94,8 +93,7 @@ public class OffsetFetchNextTest extends
* Creates tables used by the tests (never modified, we use rollback after
* changes).
*/
- private static void createSchemaObjects(Statement st)
- throws SQLException
+ private static void createSchemaObjects(Statement st) throws SQLException
{
// T1 (no indexes)
st.executeUpdate("create table t1 (a int, b bigint)");
@@ -116,9 +114,10 @@ public class OffsetFetchNextTest extends
/**
* Negative tests. Test various invalid OFFSET and FETCH NEXT clauses.
+ *
+ * @throws java.sql.SQLException
*/
- public void testErrors()
- throws Exception
+ public void testErrors() throws SQLException
{
Statement st = createStatement();
@@ -148,10 +147,11 @@ public class OffsetFetchNextTest extends
/**
* Positive tests. Check that the new keyword OFFSET introduced is not
- * reserved so we don't risk breaking existing apps.
+ * reserved so we don't risk breaking existing applications.
+ *
+ * @throws java.sql.SQLException
*/
- public void testNewKeywordNonReserved()
- throws Exception
+ public void testNewKeywordNonReserved() throws SQLException
{
setAutoCommit(false);
prepareStatement("select a,b as offset from t1 offset 0 rows");
@@ -194,9 +194,10 @@ public class OffsetFetchNextTest extends
/**
* Positive tests.
+ *
+ * @throws java.sql.SQLException
*/
- public void testOffsetFetchFirstReadOnlyForwardOnlyRS()
- throws Exception
+ public void testOffsetFetchFirstReadOnlyForwardOnlyRS() throws SQLException
{
Statement stm = createStatement();
@@ -412,9 +413,10 @@ public class OffsetFetchNextTest extends
/**
* Positive tests.
+ *
+ * @throws java.sql.SQLException
*/
- public void testOffsetFetchFirstUpdatableForwardOnlyRS()
- throws Exception
+ public void testOffsetFetchFirstUpdatableForwardOnlyRS() throws SQLException
{
Statement stm = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
@@ -427,9 +429,9 @@ public class OffsetFetchNextTest extends
* offset 0 rows (a no-op), update a row and verify result
*/
variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "0", null );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next();
rs.next(); // at row 2
rs.updateInt(1, -rs.getInt(1));
@@ -449,9 +451,9 @@ public class OffsetFetchNextTest extends
* offset 1 rows, update a row and verify result
*/
variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "1", null );
- for ( int i = 0; i < variants.length; i++ )
+ for ( String variant : variants )
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next(); // at row 1, but row 2 of underlying rs
rs.updateInt(1, -rs.getInt(1));
@@ -473,9 +475,10 @@ public class OffsetFetchNextTest extends
/**
* Positive tests with scrollable read-only.
+ *
+ * @throws java.sql.SQLException
*/
- public void testOffsetFetchFirstReadOnlyScrollableRS()
- throws Exception
+ public void testOffsetFetchFirstReadOnlyScrollableRS() throws SQLException
{
Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
@@ -486,9 +489,9 @@ public class OffsetFetchNextTest extends
* offset 0 rows (a no-op), update a row and verify result
*/
variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "0", null );
- for ( int i = 0; i < variants.length; i++ )
+ for ( String variant : variants )
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next();
rs.next(); // at row 2
assertTrue(rs.getInt(2) == 2);
@@ -499,9 +502,9 @@ public class OffsetFetchNextTest extends
* offset 1 rows, fetch 3 row, check that we have the right ones
*/
variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "1", "3" );
- for ( int i = 0; i < variants.length; i++ )
+ for ( String variant : variants )
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next();
rs.next(); // at row 2, but row 3 of underlying rs
@@ -527,9 +530,10 @@ public class OffsetFetchNextTest extends
/**
* Positive tests with SUR (Scrollable updatable result set).
+ *
+ * @throws java.sql.SQLException
*/
- public void testOffsetFetchFirstUpdatableScrollableRS()
- throws Exception
+ public void testOffsetFetchFirstUpdatableScrollableRS() throws SQLException
{
Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
@@ -543,9 +547,9 @@ public class OffsetFetchNextTest extends
* also try the "for update" syntax so we see that it still works
*/
variants = makeVariants( "select * from t1 % for update", FIRST_ROWS_ONLY, "0", null );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next();
rs.next(); // at row 2
rs.updateInt(1, -rs.getInt(1));
@@ -565,9 +569,9 @@ public class OffsetFetchNextTest extends
* offset 1 rows, fetch 3 row, update some rows and verify result
*/
variants = makeVariants( "select * from t1 %", NEXT_ROWS_ONLY, "1", "3" );
- for ( int i = 0; i < variants.length; i++ )
+ for ( String variant : variants )
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
rs.next();
rs.next(); // at row 2, but row 3 of underlying rs
@@ -615,9 +619,9 @@ public class OffsetFetchNextTest extends
// Test with projection
variants = makeVariants( "select * from t1 where a + 1 < b%", NEXT_ROWS_ONLY, "1", null );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- rs = stm.executeQuery( variants[ i ] );
+ rs = stm.executeQuery( variant );
// should yield 2 rows
rs.absolute(2);
assertTrue(rs.getInt(2) == 5);
@@ -637,7 +641,7 @@ public class OffsetFetchNextTest extends
}
- public void testValues() throws Exception
+ public void testValues() throws SQLException
{
Statement stm = createStatement();
@@ -658,17 +662,19 @@ public class OffsetFetchNextTest extends
/**
* Positive tests, result set metadata
+ *
+ * @throws java.sql.SQLException
*/
- public void testMetadata() throws Exception
+ public void testMetadata() throws SQLException
{
Statement stm = createStatement();
ResultSet rs;
String[] variants;
variants = makeVariants( "select * from t1%", NEXT_ROWS_ONLY, "1", null );
- for ( int j = 0; j < variants.length; j++ )
+ for (String variant : variants)
{
- rs = stm.executeQuery( variants[ j ] );
+ rs = stm.executeQuery( variant );
ResultSetMetaData rsmd= rs.getMetaData();
int cnt = rsmd.getColumnCount();
@@ -692,21 +698,23 @@ public class OffsetFetchNextTest extends
/**
* Test that we see correct traces of the filtering in the statistics
+ *
+ * @throws java.sql.SQLException
*/
- public void testRunTimeStatistics() throws Exception
+ public void testRunTimeStatistics() throws SQLException
{
Statement stm = createStatement();
ResultSet rs;
String[] variants;
variants = makeVariants( "select a,b from t1%", NEXT_ROWS_ONLY, "2", null );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
stm.executeUpdate( "call syscs_util.syscs_set_runtimestatistics(1)" );
queryAndCheck(
stm,
- variants[ i ],
+ variant,
new String [][] {
{"1","3"}, {"1","4"},{"1","5"}});
@@ -731,8 +739,10 @@ public class OffsetFetchNextTest extends
/**
* Test against a bigger table
+ *
+ * @throws java.sql.SQLException
*/
- public void testBigTable() throws Exception
+ public void testBigTable() throws SQLException
{
Statement stm = createStatement();
@@ -772,17 +782,18 @@ public class OffsetFetchNextTest extends
/**
* Test that the values of offset and fetch first are not forgotten if
* a {@code PreparedStatement} is executed multiple times (DERBY-4212).
+ *
+ * @throws java.sql.SQLException
*/
- public void testRepeatedExecution() throws Exception
+ public void testRepeatedExecution() throws SQLException
{
PreparedStatement ps;
- ResultSet rs;
String[] variants;
variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "2", "2" );
- for ( int j = 0; j < variants.length; j++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ j ] );
+ ps = prepareStatement( variant );
String[][] expected = {{"1", "3"}, {"1", "4"}};
for (int i = 0; i < 10; i++) {
JDBC.assertFullResultSet(ps.executeQuery(), expected);
@@ -792,8 +803,10 @@ public class OffsetFetchNextTest extends
/**
* Test dynamic arguments
+ *
+ * @throws java.sql.SQLException
*/
- public void testDynamicArgs() throws Exception
+ public void testDynamicArgs() throws SQLException
{
PreparedStatement ps;
String[] variants;
@@ -801,9 +814,9 @@ public class OffsetFetchNextTest extends
// Check look-ahead also for ? in grammar since offset is not reserved
variants = makeVariants( "select * from t1%", NEXT_ROWS_ONLY, "?", null );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ i ] );
+ ps = prepareStatement( variant );
}
@@ -869,35 +882,35 @@ public class OffsetFetchNextTest extends
// Mix of prepared and not
variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "?", "3" );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ i ] );
+ ps = prepareStatement( variant );
ps.setLong(1, 1L);
JDBC.assertFullResultSet(ps.executeQuery(), expected);
}
variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "4", "?" );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ i ] );
+ ps = prepareStatement( variant );
ps.setLong(1, 1L);
JDBC.assertFullResultSet(ps.executeQuery(), new String[][]{{"1", "5"}});
}
// Mix of other dyn args and ours:
variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "?", "3" );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ i ] );
+ ps = prepareStatement( variant );
ps.setInt(1, 1);
ps.setLong(2, 1L);
JDBC.assertFullResultSet(ps.executeQuery(), expected);
}
variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "1", "?" );
- for ( int i = 0; i < variants.length; i++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ i ] );
+ ps = prepareStatement( variant );
ps.setInt(1, 1);
ps.setLong(2, 2L);
expected = new String[][]{{"1", "2"}, {"1", "3"}};
@@ -927,8 +940,10 @@ public class OffsetFetchNextTest extends
/**
* Test dynamic arguments
+ *
+ * @throws java.sql.SQLException
*/
- public void testDynamicArgsMetaData() throws Exception
+ public void testDynamicArgsMetaData() throws SQLException
{
//since there is no getParameterMetaData() call available in JSR169
@@ -939,9 +954,9 @@ public class OffsetFetchNextTest extends
String[] variants;
variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "?", "?" );
- for ( int j = 0; j < variants.length; j++ )
+ for (String variant : variants)
{
- ps = prepareStatement( variants[ j ] );
+ ps = prepareStatement( variant );
ParameterMetaData pmd = ps.getParameterMetaData();
int[] expectedTypes = { Types.INTEGER, Types.BIGINT, Types.BIGINT };
@@ -960,8 +975,10 @@ public class OffsetFetchNextTest extends
/**
* Test some additional corner cases in JDBC limit/offset syntax.
+ *
+ * @throws java.sql.SQLException
*/
- public void testJDBCLimitOffset() throws Exception
+ public void testJDBCLimitOffset() throws SQLException
{
// LIMIT 0 is allowed. It means: everything from the OFFSET forward
PreparedStatement ps = prepareStatement( "select a from t2 order by a { limit ? }" );
@@ -1005,14 +1022,12 @@ public class OffsetFetchNextTest extends
*/
private void vetStatement
( Statement stmt, String sqlState, String stub, String fetchFormat, String offset, String fetchFirst, String[][] expectedResults )
- throws Exception
+ throws SQLException
{
String[] variants = makeVariants( stub, fetchFormat, offset, fetchFirst );
- for ( int i = 0; i < variants.length; i++ )
+ for (String text : variants)
{
- String text = variants[ i ];
-
if ( sqlState != null )
{
assertStatementError( sqlState, stmt, text );
@@ -1030,7 +1045,6 @@ public class OffsetFetchNextTest extends
*/
private String[] makeVariants
( String stub, String fetchFormat, String offset, String fetchFirst )
- throws Exception
{
String[] result = new String[ VARIANT_COUNT ];
@@ -1045,7 +1059,6 @@ public class OffsetFetchNextTest extends
*/
private String makeSQLStandardText
( String stub, String fetchFormat, String offset, String fetchFirst )
- throws Exception
{
String sqlStandardText = "";
@@ -1069,7 +1082,6 @@ public class OffsetFetchNextTest extends
*/
private String makeJDBCText
( String stub, String offset, String fetchFirst )
- throws Exception
{
String jdbcText = "";
@@ -1093,13 +1105,7 @@ public class OffsetFetchNextTest extends
return jdbcText;
}
- private String substitute( String stub, String token, int replacement )
- throws Exception
- {
- return substitute( stub, token, Integer.toString( replacement ) );
- }
private String substitute( String stub, String token, String replacement )
- throws Exception
{
int substitutionIndex = stub.indexOf( token );
if ( substitutionIndex < 0 ) { fail( "Bad stub: " + stub + ". Can't find token: " + token ); }
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=1536507&r1=1536506&r2=1536507&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 Mon Oct 28 20:37:34 2013
@@ -21,18 +21,18 @@
package org.apache.derbyTesting.functionTests.tests.lang;
-import java.sql.SQLException;
-import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
import junit.framework.Test;
import junit.framework.TestSuite;
-import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
-import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.TestConfiguration;
/**
* Tests for DERBY-4397 Allow {@code ORDER BY} in subqueries
@@ -74,6 +74,7 @@ public class OrderByAndOffsetFetchInSubq
{
return new CleanDatabaseTestSetup(
new TestSuite(OrderByAndOffsetFetchInSubqueries.class)) {
+ @Override
protected void decorateSQL(Statement s)
throws SQLException {
getConnection().setAutoCommit(false);
@@ -123,6 +124,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* Test {@code INSERT INTO t SELECT .. FROM .. ORDER BY}.
+ *
+ * @throws java.sql.SQLException
*/
public void testInsertSelectOrderBy() throws SQLException {
//
@@ -130,7 +133,7 @@ public class OrderByAndOffsetFetchInSubq
//
setAutoCommit(false);
Statement s = createStatement();
- ResultSet rs = null;
+ ResultSet rs;
s.execute("insert into temp1 values 'x','a','c','b','a'");
s.execute("insert into temp2(s) select s from temp1 order by s");
@@ -336,6 +339,8 @@ public class OrderByAndOffsetFetchInSubq
* <p/>
* This test is a variant made my modifying {@code testInsertSelectOrderBy}
* with suitable {@code OFFSET/FETCH FIRST} clauses.
+ *
+ * @throws java.sql.SQLException
*/
public void testInsertSelectOrderByOffsetFetch() throws SQLException {
//
@@ -343,7 +348,6 @@ public class OrderByAndOffsetFetchInSubq
//
setAutoCommit(false);
Statement s = createStatement();
- ResultSet rs = null;
s.execute("insert into temp1 values 'x','a','c','b','a'");
s.execute("insert into temp2b(s) select s from temp1 order by s " +
@@ -365,7 +369,7 @@ public class OrderByAndOffsetFetchInSubq
"insert into temp2b(s) select * from temp1 order by s " +
" offset 1 rows fetch next 4 rows only");
- rs = s.executeQuery("select * from temp2b");
+ ResultSet rs = s.executeQuery("select * from temp2b");
JDBC.assertFullResultSet(rs, new String[][]{
{"1", "a"},
{"2", "b"},
@@ -511,6 +515,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* {@code SELECT} subqueries with {@code ORDER BY}
+ *
+ * @throws java.sql.SQLException
*/
public void testSelectSubqueriesOrderBy() throws SQLException {
setAutoCommit(false);
@@ -652,6 +658,8 @@ public class OrderByAndOffsetFetchInSubq
* This test is a variant made my modifying {@code
* testSelectSubqueriesOrderBy} with suitable {@code OFFSET/FETCH FIRST}
* clauses.
+ *
+ * @throws java.sql.SQLException
*/
public void testSelectSubqueriesOrderByAndOffsetFetch()
throws SQLException {
@@ -858,6 +866,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* Test JOIN with delimited subqueries
+ *
+ * @throws java.sql.SQLException
*/
public void testJoinsWithOffsetFetch() throws SQLException {
@@ -897,6 +907,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* Test {@code ORDER BY} in a view definition
+ *
+ * @throws java.sql.SQLException
*/
public void testView() throws SQLException {
@@ -929,6 +941,8 @@ public class OrderByAndOffsetFetchInSubq
* <p/>
* This test is a variant made my modifying {@code testView} with suitable
* {@code OFFSET/FETCH FIRST} clauses.
+ *
+ * @throws java.sql.SQLException
*/
public void testViewFetchOffset() throws SQLException {
@@ -962,6 +976,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* {@code SELECT} subqueries with {@code ORDER BY} - negative tests
+ *
+ * @throws java.sql.SQLException
*/
public void testSelectSubqueriesOrderByNegative() throws SQLException {
setAutoCommit(false);
@@ -1005,6 +1021,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* {@code SELECT} subqueries with {@code ORDER BY} - check sort avoidance
+ *
+ * @throws java.sql.SQLException
*/
public void testSelectSubqueriesSortAvoidance() throws SQLException {
setAutoCommit(false);
@@ -1038,6 +1056,8 @@ public class OrderByAndOffsetFetchInSubq
/**
* Prevent pushing of where predicates into selects with fetch
* and/or offset (DERBY-5911). Similarly, for windowed selects.
+ *
+ * @throws java.sql.SQLException
*/
public void testPushAvoidance() throws SQLException {
setAutoCommit(false);
@@ -1094,7 +1114,10 @@ public class OrderByAndOffsetFetchInSubq
* </pre>
* The corresponding production in {@code sqlgrammar.jj} is
* {@code nonJoinQueryPrimary}.
+ *
* Cf. DERBY-6008.
+ *
+ * @throws java.sql.SQLException
*/
public void testNestingInsideSetOperation() throws SQLException {
setAutoCommit(false);
@@ -1230,4 +1253,88 @@ public class OrderByAndOffsetFetchInSubq
rollback();
}
+
+ /**
+ * Nested query expression body, with each level contributing to the set of
+ * ORDER BY and/or OFFSET/FETCH FIRST clauses.
+ *
+ * Cf. these productions in SQL 2011, section 7.11:
+ *
+ * <pre>
+ * <query expression> ::=
+ * [ <with clause> ] <query expression body>
+ * [ <order by clause> ] [ <result offset clause> ]
+ * [ <fetch first clause> ]
+ *
+ * <query expression body> ::=
+ * <query term> ...
+ * </pre>
+ *
+ * One of the productions of {@code <query expression body>}, is
+ *
+ * <pre>
+ * <left paren> <query expression body
+ * [ <order by clause> ] [ <result offset clause> ]
+ * [ <fetch first clause> ] <right paren>
+ * </pre>
+ * so our clauses nests to arbitrary depth given enough parentheses,
+ * including ORDER BY and OFFSET/FETCH FIRST clauses. This nesting
+ * did not work correctly, cf. DERBY-6378.
+ *
+ * The corresponding productions in {@code sqlgrammar.jj} is
+ * {@code queryExpression} and {@code nonJoinQueryPrimary}.
+ *
+ * @throws Exception
+ */
+ public void testDerby6378() throws Exception
+ {
+ setAutoCommit(false);
+ Statement stm = createStatement();
+ stm.executeUpdate("create table t1 (a int, b bigint)");
+ stm.executeUpdate("delete from t1");
+ stm.executeUpdate("insert into t1 values " +
+ "(1,-10), (2,-11), (3,-9), (4,-20), (5,-1)");
+
+ queryAndCheck(stm,
+ "(select * from t1 offset 1 row fetch first 1 row only)",
+ new String [][] {{"2","-11"}});
+
+ queryAndCheck(stm,
+ "(select * from t1 order by a desc fetch first 3 rows only) " +
+ " offset 1 row fetch first 1 row only",
+ new String [][] {{"4","-20"}});
+
+ queryAndCheck(stm,
+ "((select * from t1 order by a desc) " +
+ " fetch first 3 rows only)",
+ new String [][] {{"5","-1"}, {"4","-20"}, {"3","-9"}});
+
+ queryAndCheck(stm,
+ "((((select * from t1 order by a desc) " +
+ " fetch first 3 rows only)) " +
+ " order by b) " +
+ "fetch first 1 row only",
+ new String [][] {{"4","-20"},});
+
+ queryAndCheck(
+ stm,
+ "(((((values (1,-10), (2,-11), (3,-9), (4,-20), (5,-1))" +
+ " order by 1 desc) " +
+ " fetch first 3 rows only)) " +
+ " order by 2) " +
+ "fetch first 1 row only",
+ new String [][] {{"4","-20"},});
+ rollback();
+ stm.close();
+ }
+
+ private void queryAndCheck(
+ Statement stm,
+ String queryText,
+ String [][] expectedRows) throws SQLException {
+
+ ResultSet rs = stm.executeQuery(queryText);
+ JDBC.assertFullResultSet(rs, expectedRows);
+ }
+
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=1536507&r1=1536506&r2=1536507&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Mon Oct 28 20:37:34 2013
@@ -25,6 +25,7 @@ import java.io.IOException;
import java.io.OutputStream;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
+import java.io.PrintStream;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
@@ -1598,6 +1599,70 @@ public abstract class BaseJDBCTestCase
conn.commit();
}
+
+ protected static void dumpRs(ResultSet s, PrintStream out)
+ throws SQLException
+ {
+ if (s == null) {
+ out.println("<NULL>");
+ return;
+ }
+
+ ResultSetMetaData rsmd = s.getMetaData();
+
+ // Get the number of columns in the result set
+ int numCols = rsmd.getColumnCount();
+
+ if (numCols <= 0) {
+ out.println("(no columns!)");
+ return;
+ }
+
+ StringBuilder heading = new StringBuilder("\t ");
+ StringBuilder underline = new StringBuilder("\t ");
+
+ int len;
+ // Display column headings
+ for (int i=1; i<=numCols; i++) {
+ if (i > 1) {
+ heading.append(",");
+ underline.append(" ");
+ }
+
+ len = heading.length();
+ heading.append(rsmd.getColumnLabel(i));
+ len = heading.length() - len;
+
+ for (int j = len; j > 0; j--) {
+ underline.append("-");
+ }
+ }
+
+ out.println(heading.toString());
+ out.println(underline.toString());
+
+
+ StringBuilder row = new StringBuilder();
+ // Display data, fetching until end of the result set
+ while (s.next()) {
+ row.append("\t{");
+ // Loop through each column, getting the
+ // column data and displaying
+ for (int i=1; i<=numCols; i++) {
+ if (i > 1) row.append(",");
+ row.append(s.getString(i));
+ }
+
+ row.append("}\n");
+ }
+
+ out.println(row.toString());
+ s.close();
+ }
+
+ protected static void dumpRs(ResultSet s) throws SQLException {
+ dumpRs(s, System.out);
+ }
} // End class BaseJDBCTestCase