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/12/15 01:34:49 UTC
svn commit: r1422164 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Sat Dec 15 00:34:47 2012
New Revision: 1422164
URL: http://svn.apache.org/viewvc?rev=1422164&view=rev
Log:
DERBY-6008 Allow ORDER BY and FETCH/OFFSET in set operands
Patch derby-6008-e; adds this feature as well as accompanying new tests.
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/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/OrderByColumn.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.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/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/OrderByAndOffsetFetchInSubqueries.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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -204,10 +204,20 @@ public class CursorNode extends DMLState
{
super.printSubNodes(depth);
- if (orderByList != null) {
- printLabel(depth, "orderByList: " + depth);
- orderByList.treePrint(depth + 1);
- }
+ if (orderByList != null) {
+ printLabel(depth, "orderByList: " + depth);
+ 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);
+ }
}
}
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -215,6 +215,16 @@ public final class InsertNode extends DM
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);
+ }
+
/* RESOLVE - need to print out targetTableDescriptor */
}
}
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -151,16 +151,17 @@ public class IntersectOrExceptNode exten
* 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( orderByList != null)
+ if( orderByLists[0] != null)
{
BitSet colsOrdered = new BitSet( intermediateOrderByColumns.length);
- int orderByListSize = orderByList.size();
+ int orderByListSize = orderByLists[0].size();
int intermediateOrderByIdx = 0;
for( int i = 0; i < orderByListSize; i++)
{
if( colsOrdered.get(i))
continue;
- OrderByColumn orderByColumn = orderByList.getOrderByColumn(i);
+ OrderByColumn orderByColumn =
+ orderByLists[0].getOrderByColumn(i);
intermediateOrderByDirection[intermediateOrderByIdx] = orderByColumn.isAscending() ? 1 : -1;
intermediateOrderByNullsLow[intermediateOrderByIdx] = orderByColumn.isNullsOrderedLow();
int columnIdx = orderByColumn.getResultColumn().getColumnPosition() - 1;
@@ -178,7 +179,7 @@ public class IntersectOrExceptNode exten
intermediateOrderByIdx++;
}
}
- orderByList = null; // It will be pushed down.
+ orderByLists[0] = null; // It will be pushed down.
}
else // The output of the intersect/except does not have to be ordered
{
@@ -314,12 +315,12 @@ public class IntersectOrExceptNode exten
ResultSetNode treeTop = this;
- if( orderByList != null) {
+ if( orderByLists[0] != null) {
// Generate an order by node on top of the intersect/except
treeTop = (ResultSetNode) getNodeFactory().getNode(
C_NodeTypes.ORDER_BY_NODE,
treeTop,
- orderByList,
+ orderByLists[0],
tableProperties,
getContextManager());
}
@@ -338,7 +339,6 @@ public class IntersectOrExceptNode exten
Boolean.valueOf( hasJDBClimitClause ),
getContextManager());
}
-
return treeTop;
} // end of addNewNodes
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java Sat Dec 15 00:34:47 2012
@@ -586,4 +586,7 @@ public class OrderByColumn extends Order
}
}
+ ValueNode getExpression() {
+ return expression;
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Sat Dec 15 00:34:47 2012
@@ -2488,9 +2488,9 @@ public class ResultColumnList extends Qu
")");
}
- // Generated grouping columns should have been removed for the RCL
- // of a SetOperatorNode, so that size and visible size are equal
- // (DERBY-3764).
+ // Generated grouping columns and unselected ORDER BY columns
+ // should have been removed for the RCL of a SetOperatorNode, so
+ // that size and visible size are equal (DERBY-3764).
SanityManager.ASSERT(size() == visibleSize(),
"size() and visibleSize() should be equal");
}
@@ -2645,9 +2645,9 @@ public class ResultColumnList extends Qu
// The visible size of the two RCLs must be equal.
SanityManager.ASSERT(visibleSize() == otherRCL.visibleSize(),
"visibleSize() should match");
- // The generated grouping columns should have been removed from the
- // RCL of the SetOperatorNode, so size and visible size should be
- // equal (DERBY-3764).
+ // The generated grouping columns and unselected ORDER BY columns
+ // should have been removed from the RCL of the SetOperatorNode,
+ // so size and visible size should be equal (DERBY-3764).
SanityManager.ASSERT(size() == visibleSize(),
"size() and visibleSize() should match");
}
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -123,6 +123,22 @@ public class RowResultSetNode extends Fr
printLabel(depth, "subquerys: ");
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);
+ }
+
}
}
@@ -253,7 +269,14 @@ public class RowResultSetNode extends Fr
}
SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");
- }
+
+ if (orderByList != null) {
+ orderByList.pullUpOrderByColumns(this);
+ orderByList.bindOrderByColumns(this);
+ }
+
+ bindOffsetFetch(offset, fetchFirst);
+ }
/**
* Bind the expressions in this ResultSetNode if it has tables. This means binding the
@@ -438,7 +461,17 @@ public class RowResultSetNode extends Fr
/* Allocate a dummy referenced table map */
referencedTableMap = new JBitSet(numTables);
referencedTableMap.set(tableNumber);
- return this;
+
+ // If we have more than 1 ORDERBY columns, we may be able to
+ // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+ // 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();
+ }
+
+ return this;
}
/**
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -22,6 +22,7 @@
package org.apache.derby.impl.sql.compile;
+import java.util.Arrays;
import org.apache.derby.iapi.sql.compile.CostEstimate;
import org.apache.derby.iapi.sql.compile.Optimizer;
import org.apache.derby.iapi.sql.compile.Visitor;
@@ -101,8 +102,14 @@ public class SelectNode extends ResultSe
*/
private boolean wasGroupBy;
- /* List of columns in ORDER BY list */
- OrderByList orderByList;
+ /**
+ * 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
@@ -330,11 +337,23 @@ public class SelectNode extends ResultSe
havingClause.treePrint(depth + 1);
}
- if (orderByList != null) {
- printLabel(depth, "orderByList:");
- orderByList.treePrint(depth + 1);
+ 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);
+ }
+
if (preJoinFL != null)
{
printLabel(depth, "preJoinFL: ");
@@ -519,6 +538,10 @@ public class SelectNode extends ResultSe
SanityManager.ASSERT(fromList != null && resultColumns != null,
"Both fromList and resultColumns are expected to be non-null");
+ if (orderByLists[0] != null) {
+ orderByLists[0].pullUpOrderByColumns(this);
+ }
+
/* NOTE - a lot of this code would be common to bindTargetExpression(),
* so we use a private boolean to share the code instead of duplicating
* it. bindTargetExpression() is responsible for toggling the boolean.
@@ -690,7 +713,13 @@ public class SelectNode extends ResultSe
{
throw StandardException.newException(SQLState.LANG_USER_AGGREGATE_MULTIPLE_DISTINCTS);
}
- }
+
+ if (orderByLists[0] != null) {
+ orderByLists[0].bindOrderByColumns(this);
+ }
+
+ bindOffsetFetch(offset, fetchFirst);
+ }
/**
* Bind the expressions in this ResultSetNode if it has tables. This means binding the
@@ -933,7 +962,34 @@ public class SelectNode extends ResultSe
*/
void pushOrderByList(OrderByList orderByList)
{
- this.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 = (OrderByColumn)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;
+ }
// remember that there was an order by list
orderByQuery = true;
}
@@ -1107,23 +1163,25 @@ public class SelectNode extends ResultSe
performTransitiveClosure(numTables);
}
- if (orderByList != null)
- {
- // Remove constant columns from order by list. Constant
- // columns are ones that have equality comparisons with
- // constant expressions (e.g. x = 3)
- orderByList.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 (orderByList.size() == 0)
- {
- orderByList = null;
- resultColumns.removeOrderByColumns();
- }
- }
+ for (int i = 0; i < orderByLists.length; i++) {
+ if (orderByLists[i] != 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);
+ /*
+ ** 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)
+ {
+ orderByLists[i] = null;
+ resultColumns.removeOrderByColumns();
+ }
+ }
+ }
}
/* A valid group by without any aggregates or a having clause
@@ -1175,45 +1233,50 @@ public class SelectNode extends ResultSe
}
}
- /* 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
- * be ascending in order to do this. There are 2 cases:
- * o The order by list is an in order prefix of the columns
- * in the select list. In this case the output of the
- * sort from the distinct will be in the right order
- * so we simply eliminate the order by list.
- * o The order by list is a subset of the columns in the
- * the select list. In this case we need to reorder the
- * columns in the select list so that the ordering columns
- * are an in order prefix of the select list and put a PRN
- * above the select so that the shape of the result set
- * is as expected.
- */
- if (isDistinct && orderByList != null && orderByList.allAscending())
- {
- /* Order by list currently restricted to columns in select
- * list, so we will always eliminate the order by here.
- */
- if (orderByList.isInOrderPrefix(resultColumns))
- {
- orderByList = null;
- }
- else
- {
- /* Order by list is not an in order prefix of the select list
- * so we must reorder the columns in the the select list to
- * match the order by list and generate the PRN above us to
- * preserve the expected order.
- */
- newTop = genProjectRestrictForReordering();
- orderByList.resetToSourceRCs();
- resultColumns = orderByList.reorderRCL(resultColumns);
- newTop.getResultColumns().removeOrderByColumns();
- orderByList = null;
- }
- orderByAndDistinctMerged = true;
- }
+ for (int i = 0; i < orderByLists.length; 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
+ * be ascending in order to do this. There are 2 cases:
+ * o The order by list is an in order prefix of the columns
+ * in the select list. In this case the output of the
+ * sort from the distinct will be in the right order
+ * so we simply eliminate the order by list.
+ * o The order by list is a subset of the columns in the
+ * the select list. In this case we need to reorder the
+ * columns in the select list so that the ordering columns
+ * are an in order prefix of the select list and put a PRN
+ * above the select so that the shape of the result set
+ * is as expected.
+ */
+ if (isDistinct && orderByLists[i] != null &&
+ orderByLists[i].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))
+ {
+ orderByLists[i] = null;
+ }
+ else
+ {
+ /* Order by list is not an in order prefix of the
+ * select list so we must reorder the columns in the
+ * the select list to match the order by list and
+ * generate the PRN above us to preserve the expected
+ * order.
+ */
+ newTop = genProjectRestrictForReordering();
+ orderByLists[i].resetToSourceRCs();
+ resultColumns =
+ orderByLists[i].reorderRCL(resultColumns);
+ newTop.getResultColumns().removeOrderByColumns();
+ orderByLists[i] = null;
+ }
+ orderByAndDistinctMerged = true;
+ }
+ }
}
/*
@@ -1244,14 +1307,14 @@ public class SelectNode extends ResultSe
}
- if (orderByList != null) {
+ if (orderByLists[0] != null) { // only relevant for first one
// Collect window function calls and in-lined window definitions
// contained in them from the orderByList.
CollectNodesVisitor cnvw =
new CollectNodesVisitor(WindowFunctionNode.class);
- orderByList.accept(cnvw);
+ orderByLists[0].accept(cnvw);
Vector wfcInOrderBy = cnvw.getList();
for (int i=0; i < wfcInOrderBy.size(); i++) {
@@ -1426,8 +1489,8 @@ public class SelectNode extends ResultSe
}
/* Don't flatten if selectNode now has an order by */
- if ((orderByList != null) &&
- (orderByList.size() > 0))
+ if ((orderByLists[0] != null) &&
+ (orderByLists[0].size() > 0))
{
return false;
}
@@ -1456,9 +1519,8 @@ public class SelectNode extends ResultSe
public ResultSetNode genProjectRestrict(int origFromListSize)
throws StandardException
{
- boolean eliminateSort = false;
- PredicateList restrictionList;
- ResultColumnList prRCList;
+ boolean[] eliminateSort = new boolean[orderByLists.length];
+
ResultSetNode prnRSN;
prnRSN = (ResultSetNode) getNodeFactory().getNode(
@@ -1505,7 +1567,9 @@ public class SelectNode extends ResultSe
prnRSN = gbn.getParent();
// Remember whether or not we can eliminate the sort.
- eliminateSort = eliminateSort || gbn.getIsInSortedOrder();
+ for (int i=0; i < eliminateSort.length; i++ ) {
+ eliminateSort[i] = eliminateSort[i] || gbn.getIsInSortedOrder();
+ }
}
@@ -1598,8 +1662,10 @@ public class SelectNode extends ResultSe
getContextManager());
prnRSN.costEstimate = costEstimate.cloneMe();
- // Remember whether or not we can eliminate the sort.
- eliminateSort = eliminateSort || inSortedOrder;
+ // Remember whether or not we can eliminate the sort.
+ for (int i=0; i < eliminateSort.length; i++) {
+ eliminateSort[i] = eliminateSort[i] || inSortedOrder;
+ }
}
}
@@ -1607,66 +1673,71 @@ public class SelectNode extends ResultSe
* the order by.
*/
- if (orderByList != null)
- {
- if (orderByList.getSortNeeded())
- {
- prnRSN = (ResultSetNode) getNodeFactory().getNode(
- C_NodeTypes.ORDER_BY_NODE,
- prnRSN,
- orderByList,
- null,
- getContextManager());
- prnRSN.costEstimate = costEstimate.cloneMe();
- }
-
- // There may be columns added to the select projection list
- // a query like:
- // select a, b from t group by a,b order by a+b
- // the expr a+b is added to the select list.
- int orderBySelect = this.getResultColumns().getOrderBySelect();
- if (orderBySelect > 0)
- {
- // Keep the same RCL on top, since there may be references to
- // its result columns above us, i.e. in this query:
- //
- // select sum(j),i from t group by i having i
- // in (select i from t order by j)
- //
- ResultColumnList topList = prnRSN.getResultColumns();
- ResultColumnList newSelectList = topList.copyListAndObjects();
- prnRSN.setResultColumns(newSelectList);
+ for (int i=0; i < orderByLists.length; i++) {
+ if (orderByLists[i] != null)
+ {
+ if (orderByLists[i].getSortNeeded())
+ {
+ prnRSN = (ResultSetNode) getNodeFactory().getNode(
+ C_NodeTypes.ORDER_BY_NODE,
+ prnRSN,
+ orderByLists[i],
+ null,
+ getContextManager());
+ prnRSN.costEstimate = costEstimate.cloneMe();
+ }
- topList.removeOrderByColumns();
- topList.genVirtualColumnNodes(prnRSN, newSelectList);
- prnRSN = (ResultSetNode) getNodeFactory().getNode(
- C_NodeTypes.PROJECT_RESTRICT_NODE,
- prnRSN,
- topList,
- null,
- null,
- null,
- null,
- null,
- getContextManager());
- }
- }
+ // There may be columns added to the select projection list
+ // a query like:
+ // select a, b from t group by a,b order by a+b
+ // the expr a+b is added to the select list.
+ int orderBySelect = this.getResultColumns().getOrderBySelect();
+ if (orderBySelect > 0)
+ {
+ // Keep the same RCL on top, since there may be references
+ // to its result columns above us, i.e. in this query:
+ //
+ // select sum(j),i from t group by i having i
+ // in (select i from t order by j)
+ //
+ ResultColumnList topList = prnRSN.getResultColumns();
+ ResultColumnList newSelectList =
+ topList.copyListAndObjects();
+ prnRSN.setResultColumns(newSelectList);
+
+ topList.removeOrderByColumns();
+ topList.genVirtualColumnNodes(prnRSN, newSelectList);
+ prnRSN = (ResultSetNode) getNodeFactory().getNode(
+ C_NodeTypes.PROJECT_RESTRICT_NODE,
+ prnRSN,
+ topList,
+ null,
+ null,
+ null,
+ null,
+ null,
+ getContextManager());
+ }
+ }
- 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();
- ResultColumnList newSelectList = topList.copyListAndObjects();
- prnRSN.setResultColumns(newSelectList);
- topList.genVirtualColumnNodes(prnRSN, newSelectList);
- prnRSN = (ResultSetNode)getNodeFactory().getNode(
- C_NodeTypes.ROW_COUNT_NODE,
- prnRSN,
- topList,
- offset,
- fetchFirst,
- Boolean.valueOf( hasJDBClimitClause ),
- 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)) {
+ // Keep the same RCL on top, since there may be references to
+ // its result columns above us.
+ ResultColumnList topList = prnRSN.getResultColumns();
+ ResultColumnList newSelectList = topList.copyListAndObjects();
+ prnRSN.setResultColumns(newSelectList);
+ topList.genVirtualColumnNodes(prnRSN, newSelectList);
+ prnRSN = (ResultSetNode)getNodeFactory().getNode(
+ C_NodeTypes.ROW_COUNT_NODE,
+ prnRSN,
+ topList,
+ offset,
+ fetchFirst,
+ Boolean.valueOf( hasJDBClimitClause ),
+ getContextManager());
+ }
}
@@ -1709,24 +1780,27 @@ public class SelectNode extends ResultSe
getContextManager());
}
- if (!(orderByList != null && orderByList.getSortNeeded()) && orderByQuery)
- {
- // Remember whether or not we can eliminate the sort.
- eliminateSort = true;
- }
+ for (int i=0; i < orderByLists.length; i++) {
+ if (!(orderByLists[i] != null && orderByLists[i].getSortNeeded()) &&
+ orderByQuery)
+ {
+ // Remember whether or not we can eliminate the sort.
+ eliminateSort[i] = true;
+ }
- /* If we were able to eliminate the sort during optimization then
- * we must tell the underlying tree. At minimum, this means no
- * group fetch on an index under an IndexRowToBaseRow since that
- * that could lead to incorrect results. (Bug 2347.)
- */
- if (eliminateSort)
- {
- prnRSN.adjustForSortElimination(orderByList);
- }
+ /* If we were able to eliminate the sort during optimization then
+ * we must tell the underlying tree. At minimum, this means no
+ * group fetch on an index under an IndexRowToBaseRow since that
+ * that could lead to incorrect results. (Bug 2347.)
+ */
+ if (eliminateSort[i])
+ {
+ prnRSN.adjustForSortElimination(orderByLists[i]);
+ }
- /* Set the cost of this node in the generated node */
- prnRSN.costEstimate = costEstimate.cloneMe();
+ /* Set the cost of this node in the generated node */
+ prnRSN.costEstimate = costEstimate.cloneMe();
+ }
return prnRSN;
}
@@ -1834,7 +1908,15 @@ public class SelectNode extends ResultSe
SanityManager.ASSERT(selectSubquerys != null,
"selectSubquerys is expected to be non-null");
- /* If this select node is the child of an outer node that is
+ // 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();
+ }
+ }
+
+ /* If this select node is the child of an outer node that is
* being optimized, we can get here multiple times (once for
* every permutation that is done for the outer node). With
* DERBY-805, we can add optimizable predicates to the WHERE
@@ -1914,7 +1996,7 @@ public class SelectNode extends ResultSe
optimizer = getOptimizer(fromList,
wherePredicates,
dataDictionary,
- orderByList);
+ orderByLists[0]); // use first one
optimizer.setOuterRows(outerRows);
/* Optimize this SelectNode */
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -60,7 +60,14 @@ abstract class SetOperatorNode extends T
*/
boolean all;
- OrderByList orderByList;
+ /**
+ * 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];
+
ValueNode offset; // OFFSET n ROWS
ValueNode fetchFirst; // FETCH FIRST n ROWS ONLY
boolean hasJDBClimitClause; // were OFFSET/FETCH FIRST specified by a JDBC LIMIT clause?
@@ -552,11 +559,22 @@ abstract class SetOperatorNode extends T
{
super.printSubNodes(depth);
- if (orderByList != null) {
- printLabel(depth, "orderByList:");
- orderByList.treePrint(depth + 1);
+ 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);
+ }
}
}
/**
@@ -649,6 +667,8 @@ abstract class SetOperatorNode extends T
// part of the result from the set operation (DERBY-3764).
resultColumns.removeGeneratedGroupingColumns();
+ resultColumns.removeOrderByColumns();
+
/* Create new expressions with the dominant types after verifying
* union compatibility between left and right sides.
*/
@@ -769,6 +789,18 @@ 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);
+ }
+
+ bindOffsetFetch(offset, fetchFirst);
+ super.bindExpressions(fromList);
+ }
+
/**
* Bind the expressions in the target list. This means binding the
* sub-expressions, as well as figuring out what the return type is
@@ -796,7 +828,32 @@ abstract class SetOperatorNode extends T
*/
void pushOrderByList(OrderByList orderByList)
{
- this.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 = (OrderByColumn)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;
+ }
}
/**
@@ -869,28 +926,38 @@ abstract class SetOperatorNode extends T
* above the select so that the shape of the result set
* is as expected.
*/
- if ((! all) && orderByList != null && orderByList.allAscending())
- {
- /* Order by list currently restricted to columns in select
- * list, so we will always eliminate the order by here.
- */
- if (orderByList.isInOrderPrefix(resultColumns))
- {
- orderByList = null;
- }
- /* RESOLVE - We currently only eliminate the order by if it is
- * a prefix of the select list. We do not currently do the
- * elimination if the order by is not a prefix because the code
- * doesn't work. The problem has something to do with the
- * fact that we generate additional nodes between the union
- * and the PRN (for reordering that we would generate here)
- * when modifying the access paths. VCNs under the PRN can be
- * seen as correlated since their source resultset is the Union
- * which is no longer the result set directly under them. This
- * causes the wrong code to get generated. (jerry - 11/3/98)
- * (bug 59)
- */
- }
+ for (int i = 0; i < orderByLists.length; i++) {
+ if ((! all) && orderByLists[i] != null &&
+ orderByLists[i].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))
+ {
+ orderByLists[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
+ * elimination if the order by is not a prefix because the code
+ * doesn't work. The problem has something to do with the
+ * fact that we generate additional nodes between the union
+ * and the PRN (for reordering that we would generate here)
+ * when modifying the access paths. VCNs under the PRN can be
+ * seen as correlated since their source resultset is the Union
+ * which is no longer the result set directly under them. This
+ * causes the wrong code to get generated. (jerry - 11/3/98)
+ * (bug 59)
+ */
+ }
+
+ // 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();
+ }
+ }
return newTop;
}
@@ -1150,5 +1217,4 @@ abstract class SetOperatorNode extends T
return rightOptPredicates;
}
-
}
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -471,30 +471,34 @@ public class UnionNode extends SetOperat
/* Generate the OrderByNode if a sort is still required for
* the order by.
*/
- if (orderByList != null)
- {
- treeTop = (ResultSetNode) getNodeFactory().getNode(
- C_NodeTypes.ORDER_BY_NODE,
- treeTop,
- orderByList,
- tableProperties,
- getContextManager());
- }
-
-
- if (offset != null || fetchFirst != null) {
- ResultColumnList newRcl =
- treeTop.getResultColumns().copyListAndObjects();
- newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
-
- treeTop = (ResultSetNode)getNodeFactory().getNode(
- C_NodeTypes.ROW_COUNT_NODE,
- treeTop,
- newRcl,
- offset,
- fetchFirst,
- Boolean.valueOf( hasJDBClimitClause ),
- getContextManager());
+ for (int i=0; i < orderByLists.length; i++) {
+ if (orderByLists[i] != null)
+ {
+ treeTop = (ResultSetNode) getNodeFactory().getNode(
+ C_NodeTypes.ORDER_BY_NODE,
+ treeTop,
+ orderByLists[i],
+ 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)) {
+ ResultColumnList newRcl =
+ treeTop.getResultColumns().copyListAndObjects();
+ newRcl.genVirtualColumnNodes(treeTop,
+ treeTop.getResultColumns());
+
+ treeTop = (ResultSetNode)getNodeFactory().getNode(
+ C_NodeTypes.ROW_COUNT_NODE,
+ treeTop,
+ newRcl,
+ offset,
+ fetchFirst,
+ Boolean.valueOf( hasJDBClimitClause ),
+ getContextManager());
+ }
}
return treeTop;
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -1978,6 +1978,14 @@ public class SQLParser
}
}
+ private static boolean hasSelectSuffixClause(
+ OrderByList orderBy, ValueNode[] offsetClauses)
+ {
+ return
+ orderBy != null ||
+ offsetClauses[OFFSET_CLAUSE] != null ||
+ offsetClauses[FETCH_FIRST_CLAUSE] != null;
+ }
}
PARSER_END(SQLParser)
@@ -5150,6 +5158,9 @@ ResultSetNode
nonJoinQueryPrimary() throws StandardException :
{
ResultSetNode primary;
+ boolean hasJDBClimitClause = false;
+ OrderByList orderCols = null;
+ ValueNode[] offsetClauses = new ValueNode[OFFSET_CLAUSE_COUNT];
}
{
primary = simpleTable()
@@ -5157,10 +5168,42 @@ nonJoinQueryPrimary() throws StandardExc
return primary;
}
|
- <LEFT_PAREN> primary = queryExpression(null, NO_SET_OP)
+ <LEFT_PAREN>
+ primary = queryExpression(null, NO_SET_OP)
+ [ orderCols = orderByClause() ]
+ hasJDBClimitClause = offsetFetchFirstClause( offsetClauses )
<RIGHT_PAREN>
{
- return primary;
+ if (hasSelectSuffixClause(orderCols, offsetClauses)) {
+ if (primary instanceof SelectNode ||
+ primary instanceof UnionNode || // table value constructor
+ primary instanceof RowResultSetNode) {
+
+ primary.pushOrderByList(orderCols);
+ primary.pushOffsetFetchFirst(
+ offsetClauses[OFFSET_CLAUSE],
+ offsetClauses[FETCH_FIRST_CLAUSE],
+ hasJDBClimitClause);
+ } else {
+ // shouldn't happen
+ String tokenName;
+
+ if (orderCols != null) {
+ tokenName = "ORDER BY";
+ } else if (hasJDBClimitClause) {
+ tokenName = "LIMIT";
+ } else if (offsetClauses[OFFSET_CLAUSE] != null) {
+ tokenName = "OFFSET";
+ } else {
+ tokenName = "FETCH";
+ }
+
+ throw StandardException.newException(
+ SQLState.LANG_SYNTAX_ERROR, tokenName);
+ }
+ }
+
+ return primary;
}
}
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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -1080,4 +1080,154 @@ public class OrderByAndOffsetFetchInSubq
rollback();
}
+
+
+ /**
+ * Test nesting inside set operands, cf. this production in SQL
+ * 2011, section 7.12:
+ * <pre>
+ * <query primary> ::=
+ * <simple table>
+ * | <left paren> <query expression body>
+ * [ <order by clause> ] [ <result offset clause> ]
+ * [ <fetch first clause> ] <right paren>
+ * </pre>
+ * The corresponding production in {@code sqlgrammar.jj} is
+ * {@code nonJoinQueryPrimary}.
+ * Cf. DERBY-6008.
+ */
+ public void testNestingInsideSetOperation() throws SQLException {
+ setAutoCommit(false);
+ Statement s = createStatement();
+
+ s.executeUpdate("create table t1(i int, j int )");
+ s.executeUpdate("create table t2(i int, j int)");
+
+ s.executeUpdate("insert into t1 values (1,1),(4,8),(2,4)");
+ s.executeUpdate("insert into t2 values (10,10),(40,80),(20,40)");
+
+ ResultSet rs = s.executeQuery(
+ "(select i from t1 order by j desc offset 1 row) union " +
+ "(select i from t2 order by j desc offset 1 rows " +
+ "fetch next 1 row only)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"2"}, {"20"}});
+
+ // Without parentheses, expect syntax error
+ assertCompileError("42X01",
+ "select i from t1 order by j desc offset 1 row union " +
+ "(select i from t2 order by j desc offset 2 rows)");
+
+ // With VALUES (single) instead of SELECT:
+ // Single values exercise changes in RowResultSetNode
+ rs = s.executeQuery(
+ "(values 1 order by 1 fetch first 1 row only) union " +
+ "(select i from t2 order by j desc offset 2 rows)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"10"}});
+
+ // With VALUES (single) instead of SELECT and duplicate ordering key
+ rs = s.executeQuery(
+ "(values 1 order by 1,1 fetch first 1 row only) union " +
+ "(select i from t2 order by j desc offset 2 rows)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"10"}});
+
+ // With VALUES (multiple) instead of SELECT
+ // Multiples values exercise changes in SetOperatorNode when used in
+ // table value constructor context (UNION).
+ rs = s.executeQuery(
+ "(values 1,2 order by 1 desc offset 1 row " +
+ " fetch first 1 row only)" +
+ " union (select i from t2 order by j desc offset 2 rows)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"10"}});
+
+ // With VALUES (multiple) instead of SELECT plus duplicate ordering
+ // key
+ rs = s.executeQuery(
+ "(values 1,2 order by 1,1 offset 1 row fetch first 1 row only)" +
+ " union (select i from t2 order by j desc offset 2 rows)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"2"}, {"10"}});
+
+ // Intersect and except
+ s.executeUpdate(
+ "create table countries(name varchar(20), " +
+ " population int, " +
+ " area int)");
+
+ s.executeUpdate("insert into countries values" +
+ "('Norway', 5033675, 385252)," +
+ "('Sweden', 9540065, 449964)," +
+ "('Denmark', 5580413, 42894)," +
+ "('Iceland', 320060, 103001)," +
+ "('Liechtenstein', 36281, 160)");
+
+ rs = s.executeQuery(
+ "(select name from countries " +
+ " order by population desc fetch first 2 rows only)" +
+ " intersect " +
+ "(select name from countries " +
+ " order by area desc fetch first 2 rows only)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"Sweden"}});
+
+ rs = s.executeQuery(
+ "(values ('Norway', 5033675, 385252), " +
+ " ('Sweden', 9540065, 449964)," +
+ " ('Denmark', 5580413, 42894)," +
+ " ('Iceland', 320060, 103001)," +
+ " ('Liechtenstein', 36281, 160)" +
+ " order by 2 desc fetch first 3 rows only)" +
+ " intersect " +
+ "(select * from countries " +
+ " order by area desc fetch first 3 rows only)");
+ // Note: we use 3 rows here to check that both sorts work the way they
+ // should: at the lowest level, the "order by 2 desc", then the
+ // "fetch first" of only three of those rows, then on top the ascending
+ // sort on all columns to get the data ready for the intersect.
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"Norway", "5033675", "385252"},
+ {"Sweden", "9540065", "449964"}});
+
+ rs = s.executeQuery(
+ "(values ('Norway', 5033675, 385252)" +
+ " order by 2 desc fetch first 3 rows only)" +
+ " intersect " +
+ "(values ('Norway', 5033675, 385252))");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"Norway", "5033675", "385252"}});
+
+ rs = s.executeQuery(
+ "(select name from countries " +
+ " order by population desc fetch first 2 rows only)" +
+ " except " +
+ "(select name from countries " +
+ " order by area desc fetch first 2 rows only)");
+
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"Denmark"}});
+
+ rs = s.executeQuery(
+ "(values ('Norway', 5033675, 385252), " +
+ " ('Sweden', 9540065, 449964)," +
+ " ('Denmark', 5580413, 42894)," +
+ " ('Iceland', 320060, 103001)," +
+ " ('Liechtenstein', 36281, 160)" +
+ " order by 2 desc fetch first 3 rows only)" +
+ " except " +
+ "(select * from countries " +
+ " order by area desc fetch first 3 rows only)");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"Denmark", "5580413", "42894"}});
+
+ rollback();
+ }
}