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 2009/12/17 23:19:09 UTC
svn commit: r891952 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Thu Dec 17 22:19:08 2009
New Revision: 891952
URL: http://svn.apache.org/viewvc?rev=891952&view=rev
Log:
DERBY-4397 Allow ORDER BY in subqueries
Patch derby-4397-2 which implements this functionality and also adds a
new test, OrderByInSubqueries. This patch also solves DERBY-4.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.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/NormalizeResultSetNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.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/QueryTreeNode.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/ResultSetNode.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/SubqueryNode.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/master/orderby.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java Thu Dec 17 22:19:08 2009
@@ -286,8 +286,11 @@
*/
public void printSubNodes(int depth) {
if (SanityManager.DEBUG) {
- printLabel(depth, "tableElementList: ");
- tableElementList.treePrint(depth + 1);
+ if (tableElementList != null) {
+ printLabel(depth, "tableElementList: ");
+ tableElementList.treePrint(depth + 1);
+ }
+
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java Thu Dec 17 22:19:08 2009
@@ -68,7 +68,7 @@
int checkOption;
ProviderInfo[] providerInfos;
ColumnInfo[] colInfos;
-
+ private OrderByList orderByList;
/**
* Initializer for a CreateViewNode
@@ -80,6 +80,7 @@
* @param checkOption The type of WITH CHECK OPTION that was specified
* (NONE for now)
* @param qeText The text for the queryExpression
+ * @param orderCols ORDER BY list
*
* @exception StandardException Thrown on error
*/
@@ -88,7 +89,8 @@
Object resultColumns,
Object queryExpression,
Object checkOption,
- Object qeText)
+ Object qeText,
+ Object orderCols)
throws StandardException
{
initAndCheck(newObjectName);
@@ -96,6 +98,7 @@
this.queryExpression = (ResultSetNode) queryExpression;
this.checkOption = ((Integer) checkOption).intValue();
this.qeText = ((String) qeText).trim();
+ this.orderByList = (OrderByList)orderCols;
implicitCreateSchema = true;
}
@@ -408,4 +411,7 @@
}
}
+ public OrderByList getOrderByList() {
+ return orderByList;
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java Thu Dec 17 22:19:08 2009
@@ -677,7 +677,7 @@
*
* @exception StandardException Thrown on failure
*/
- void bindRowScopedExpression
+ static void bindRowScopedExpression
(
NodeFactory nodeFactory,
ContextManager contextManager,
@@ -1915,6 +1915,30 @@
}
/**
+ * Prints the sub-nodes of this object. See QueryTreeNode.java for
+ * how tree printing is supposed to work.
+ *
+ * @param depth The depth of this node in the tree
+ */
+
+ public void printSubNodes(int depth)
+ {
+ if (SanityManager.DEBUG)
+ {
+ super.printSubNodes(depth);
+
+ printLabel(depth, "targetTableName: ");
+ targetTableName.treePrint(depth + 1);
+
+ if (resultColumnList != null)
+ {
+ printLabel(depth, "resultColumnList: ");
+ resultColumnList.treePrint(depth + 1);
+ }
+ }
+ }
+
+ /**
* Accept the visitor for all visitable children of this node.
*
* @param v the visitor
@@ -1931,7 +1955,6 @@
targetTableName.accept(v);
}
}
-
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Thu Dec 17 22:19:08 2009
@@ -2237,12 +2237,14 @@
SanityManager.ASSERT(vd != null,
"vd not expected to be null for " + tableName);
}
-
+
cvn = (CreateViewNode)
parseStatement(vd.getViewText(), false);
rsn = cvn.getParsedQueryExpression();
-
+
+ OrderByList orderByList = cvn.getOrderByList();
+
/* If the view contains a '*' then we mark the views derived column list
* so that the view will still work, and return the expected results,
* if any of the tables referenced in the view have columns added to
@@ -2271,7 +2273,8 @@
fsq = (FromSubquery) getNodeFactory().getNode(
C_NodeTypes.FROM_SUBQUERY,
- rsn,
+ rsn,
+ orderByList,
(correlationName != null) ?
correlationName : getOrigTableName().getTableName(),
resultColumns,
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -46,6 +46,7 @@
public class FromSubquery extends FromTable
{
ResultSetNode subquery;
+ private OrderByList orderByList;
/**
* DERBY-3270: If this subquery represents an expanded view, this holds the
@@ -57,18 +58,21 @@
* Intializer for a table in a FROM list.
*
* @param subquery The subquery
+ * @param orderByList ORDER BY list if any, or null
* @param correlationName The correlation name
* @param derivedRCL The derived column list
* @param tableProperties Properties list associated with the table
*/
public void init(
Object subquery,
+ Object orderByList,
Object correlationName,
Object derivedRCL,
Object tableProperties)
{
super.init(correlationName, tableProperties);
this.subquery = (ResultSetNode) subquery;
+ this.orderByList = (OrderByList)orderByList;
resultColumns = (ResultColumnList) derivedRCL;
}
@@ -89,6 +93,12 @@
printLabel(depth, "subquery: ");
subquery.treePrint(depth + 1);
}
+
+ if (orderByList != null)
+ {
+ printLabel(depth, "orderByList: ");
+ orderByList.treePrint(depth + 1);
+ }
}
}
@@ -200,7 +210,10 @@
/* From subqueries cannot be correlated, so we pass an empty FromList
* to subquery.bindExpressions() and .bindResultColumns()
*/
-
+ if (orderByList != null) {
+ orderByList.pullUpOrderByColumns(subquery);
+ }
+
nestedFromList = emptyFromList;
CompilerContext compilerContext = getCompilerContext();
@@ -219,6 +232,10 @@
}
}
+ if (orderByList != null) {
+ orderByList.bindOrderByColumns(subquery);
+ }
+
/* Now that we've bound the expressions in the subquery, we
* can propagate the subquery's RCL up to the FromSubquery.
* Get the subquery's RCL, assign shallow copy back to
@@ -325,13 +342,27 @@
FromList fromList)
throws StandardException
{
+ // Push the order by list down to the ResultSet
+ if (orderByList != null)
+ {
+ // If we have more than 1 ORDERBY columns, we may be able to
+ // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+ if (orderByList.size() > 1)
+ {
+ orderByList.removeDupColumns();
+ }
+
+ subquery.pushOrderByList(orderByList);
+ orderByList = null;
+ }
+
/* We want to chop out the FromSubquery from the tree and replace it
* with a ProjectRestrictNode. One complication is that there may be
* ColumnReferences above us which point to the FromSubquery's RCL.
* What we want to return is a tree with a PRN with the
* FromSubquery's RCL on top. (In addition, we don't want to be
* introducing any redundant ProjectRestrictNodes.)
- * Another complication is that we want to be able to only only push
+ * Another complication is that we want to be able to only push
* projections and restrictions down to this ProjectRestrict, but
* we want to be able to push them through as well.
* So, we:
@@ -541,7 +572,11 @@
* NOTE: This method will capture any column renaming due to
* a derived column list.
*/
- int rclSize = resultColumns.size();
+
+ // Use visibleSize, because we don't want to propagate any order by
+ // columns not selected.
+ int rclSize = resultColumns.visibleSize();
+
for (int index = 0; index < rclSize; index++)
{
ResultColumn resultColumn = (ResultColumn) resultColumns.elementAt(index);
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -110,6 +110,7 @@
public FKInfo fkInfo;
protected boolean bulkInsert;
private boolean bulkInsertReplace;
+ private OrderByList orderByList;
protected RowLocation[] autoincRowLocation;
/**
@@ -124,13 +125,16 @@
* @param queryExpression The query expression that will generate
* the rows to insert into the given table
* @param targetProperties The properties specified on the target table
+ * @param orderByList The order by list for the source result set, null if
+ * no order by list
*/
public void init(
Object targetName,
Object insertColumns,
Object queryExpression,
- Object targetProperties)
+ Object targetProperties,
+ Object orderByList)
{
/* statementType gets set in super() before we've validated
* any properties, so we've kludged the code to get the
@@ -144,6 +148,7 @@
setTarget((QueryTreeNode) targetName);
targetColumnList = (ResultColumnList) insertColumns;
this.targetProperties = (Properties) targetProperties;
+ this.orderByList = (OrderByList) orderByList;
/* Remember that the query expression is the source to an INSERT */
getResultSetNode().setInsertSource();
@@ -206,6 +211,11 @@
targetColumnList.treePrint(depth + 1);
}
+ if (orderByList != null) {
+ printLabel(depth, "orderByList: ");
+ orderByList.treePrint(depth + 1);
+ }
+
/* RESOLVE - need to print out targetTableDescriptor */
}
}
@@ -421,6 +431,18 @@
}
}
+ // Bind the ORDER BY columns
+ if (orderByList != null)
+ {
+ orderByList.pullUpOrderByColumns(resultSet);
+
+ // The select list may have new columns now, make sure to bind
+ // those.
+ super.bindExpressions();
+
+ orderByList.bindOrderByColumns(resultSet);
+ }
+
resultSet = enhanceAndCheckForAutoincrement(resultSet, inOrder, colMap);
resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
@@ -796,6 +818,32 @@
}
/**
+ * {@inheritDoc}
+ * <p>
+ * Remove any duplicate ORDER BY columns and push an ORDER BY if present
+ * down to the source result set, before calling super.optimizeStatement.
+ * </p>
+ */
+
+ public void optimizeStatement() throws StandardException
+ {
+ // Push the order by list down to the ResultSet
+ if (orderByList != null)
+ {
+ // If we have more than 1 ORDERBY columns, we may be able to
+ // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+ if (orderByList.size() > 1)
+ {
+ orderByList.removeDupColumns();
+ }
+
+ resultSet.pushOrderByList(orderByList);
+ orderByList = null;
+ }
+ super.optimizeStatement();
+ }
+
+ /**
* Code generation for insert
* creates an expression for:
* ResultSetFactory.getInsertResultSet(resultSet.generate(ps), generationClausesResult, checkConstrainResult, this )
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -686,5 +686,16 @@
dependentScan);
}
+ /**
+ * 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
+ * sort avoidance.
+ *
+ * @param orderByList The order by list
+ */
+ void pushOrderByList(OrderByList orderByList)
+ {
+ childResult.pushOrderByList(orderByList);
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Thu Dec 17 22:19:08 2009
@@ -76,6 +76,7 @@
private ColumnOrdering[] columnOrdering;
private int estimatedRowSize;
private boolean sortNeeded = true;
+ private int resultSetNumber = -1;
/**
Add a column to the list
@@ -112,12 +113,11 @@
}
/**
- Bind the update columns by their names to the target resultset
- of the cursor specification.
-
- @param target The underlying result set
-
- @exception StandardException Thrown on error
+ * Bind the update columns by their names to the target resultset of the
+ * cursor specification. This variant is used by InsertNode.
+ *
+ * @param target The underlying result set
+ * @exception StandardException Thrown on error
*/
public void bindOrderByColumns(ResultSetNode target)
throws StandardException {
@@ -149,7 +149,7 @@
}
}
}
-
+
/**
* Adjust addedColumnOffset values due to removal of a duplicate column
*
@@ -387,7 +387,7 @@
child.generate(acb, mb);
- int resultSetNumber = cc.getNextResultSetNumber();
+ resultSetNumber = cc.getNextResultSetNumber();
// is a distinct query
mb.push(false);
@@ -842,4 +842,8 @@
buff.toString() + "\n" +
super.toString();
}
+
+ public int getResultSetNumber() {
+ return resultSetNumber;
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java Thu Dec 17 22:19:08 2009
@@ -128,5 +128,19 @@
}
orderByList.generate(acb, mb, childResult);
+
+ // We need to take note of result set number if ORDER BY is used in a
+ // subquery for the case where a PRN is inserted in top of the select's
+ // PRN to project away a sort column that is not part of the select
+ // list, e.g.
+ //
+ // select * from (select i from t order by j desc) s
+ //
+ // If the resultSetNumber is not correctly set in our resultColumns,
+ // code generation for the PRN above us will fail when calling
+ // resultColumns.generateCore -> VCN.generateExpression, cf. the Sanity
+ // assert in VCN.generateExpression on sourceResultSetNumber >= 0.
+ resultSetNumber = orderByList.getResultSetNumber();
+ resultColumns.setResultSetNumber(resultSetNumber);
}
}
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1865,4 +1865,16 @@
this.restriction = restriction;
}
+ /**
+ * 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
+ * sort avoidance.
+ *
+ * @param orderByList The order by list
+ */
+ void pushOrderByList(OrderByList orderByList)
+ {
+ childResult.pushOrderByList(orderByList);
+ }
+
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Thu Dec 17 22:19:08 2009
@@ -343,6 +343,25 @@
}
/**
+ * Print call stack for debug purposes
+ */
+
+ public void stackPrint()
+ {
+ if (SanityManager.DEBUG)
+ {
+ debugPrint("Stacktrace:\n");
+ Exception e = new Exception("dummy");
+ StackTraceElement[] st= e.getStackTrace();
+ for (int i=0; i<st.length; i++) {
+ debugPrint(st[i] + "\n");
+ }
+
+ debugFlush();
+ }
+ }
+
+ /**
* Print this tree for debugging purposes. This recurses through
* all the sub-nodes and prints them indented by their depth in
* the tree, starting with the given indentation.
@@ -371,6 +390,10 @@
debugPrint(thisStr);
}
+ if (thisStr.charAt(thisStr.length()-1) != '\n') {
+ debugPrint("\n");
+ }
+
printSubNodes(depth);
}
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -646,15 +646,16 @@
*/
if (SanityManager.DEBUG)
{
- if ((! countMismatchAllowed) && size() != nameList.size())
+ if ((! countMismatchAllowed) && visibleSize() != nameList.size())
{
SanityManager.THROWASSERT(
- "The size of the 2 lists is expected to be the same. size() = " +
- size() + ", nameList.size() = " + nameList.size());
+ "The size of the 2 lists is expected to be the same. " +
+ "visibleSize() = " + visibleSize() +
+ ", nameList.size() = " + nameList.size());
}
}
- int size = (countMismatchAllowed) ? nameList.size() : size();
+ int size = (countMismatchAllowed) ? nameList.size() : visibleSize();
for (int index = 0; index < size; index++)
{
ResultColumn thisResultColumn = (ResultColumn) elementAt(index);
@@ -4227,6 +4228,7 @@
{
if (SanityManager.DEBUG) {
return "indexRow: " + indexRow + "\n" +
+ "orderBySelect: " + orderBySelect + "\n" +
(indexRow ? "conglomerateId: " + conglomerateId + "\n"
: "") +
super.toString();
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -935,7 +935,8 @@
InsertNode target, boolean inOrder, int[] colMap)
throws StandardException
{
- if (!inOrder || resultColumns.size() < target.resultColumnList.size()) {
+ if (!inOrder ||
+ resultColumns.visibleSize() < target.resultColumnList.size()) {
return generateProjectRestrictForInsert(target, colMap);
}
return this;
@@ -1001,7 +1002,7 @@
*
* @exception StandardException Thrown on error
*/
- ResultColumn genNewRCForInsert(TableDescriptor targetTD,
+ private ResultColumn genNewRCForInsert(TableDescriptor targetTD,
FromVTI targetVTI,
int columnNumber,
DataDictionary dataDictionary)
@@ -1901,4 +1902,15 @@
return null;
}
+ /**
+ * Override this for nodes that can take an ORDER BY
+ */
+ public void setOrderBy(OrderByList orderCols) throws StandardException {
+ if (SanityManager.DEBUG)
+ {
+ SanityManager.THROWASSERT(
+ "ORDER BY attempted set for wrong node type:" +
+ getClass().getName());
+ }
+ }
}
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1342,6 +1342,7 @@
* o It does not contain a group by or having clause
* o It does not contain aggregates.
* o It is not a DISTINCT.
+ * o It does not have an ORDER BY clause (pushed from FromSubquery).
*
* @param fromList The outer from list
*
@@ -1385,6 +1386,13 @@
return false;
}
+ /* Don't flatten if selectNode now has an order by */
+ if ((orderByList != null) &&
+ (orderByList.size() > 0))
+ {
+ return false;
+ }
+
return true;
}
@@ -1573,14 +1581,22 @@
int orderBySelect = this.getResultColumns().getOrderBySelect();
if (orderBySelect > 0)
{
- ResultColumnList selectRCs = prnRSN.getResultColumns().copyListAndObjects();
-
- selectRCs.removeOrderByColumns();
- selectRCs.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());
+ // 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,
- selectRCs,
+ topList,
null,
null,
null,
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -166,6 +166,8 @@
private boolean foundVariant;
private boolean doneInvariantCheck;
+ private OrderByList orderByList;
+
/* Subquery types.
* NOTE: FROM_SUBQUERY only exists for a brief second in the parser. It
* should never appear in a query tree.
@@ -201,15 +203,18 @@
* @param resultSet The ResultSetNode for the subquery
* @param subqueryType The type of the subquery
* @param leftOperand The left operand, if any, of the subquery
+ * @param orderCols ORDER BY list
*/
public void init(
Object resultSet,
Object subqueryType,
- Object leftOperand)
+ Object leftOperand,
+ Object orderCols)
{
this.resultSet = (ResultSetNode) resultSet;
this.subqueryType = ((Integer) subqueryType).intValue();
+ this.orderByList = (OrderByList)orderCols;
/* Subqueries are presumed not to be under a top level AndNode by
* default. This is because expression normalization only recurses
@@ -269,6 +274,12 @@
printLabel(depth, "leftOperand: ");
leftOperand.treePrint(depth + 1);
}
+
+ if (orderByList != null)
+ {
+ printLabel(depth, "orderByList: ");
+ orderByList.treePrint(depth + 1);
+ }
}
}
@@ -504,11 +515,19 @@
aggregateVector);
}
+ if (orderByList != null) {
+ orderByList.pullUpOrderByColumns(resultSet);
+ }
+
/* bind the expressions in the underlying subquery */
resultSet.bindExpressions(fromList);
resultSet.bindResultColumns(fromList);
+ if (orderByList != null) {
+ orderByList.bindOrderByColumns(resultSet);
+ }
+
/* reject any untyped nulls in the subquery */
resultSet.bindUntypedNullsToResultColumns(null);
@@ -625,6 +644,7 @@
*/
flattenable = (resultSet instanceof RowResultSetNode) &&
underTopAndNode && !havingSubquery &&
+ orderByList == null &&
!isWhereExistsAnyInWithWhereSubquery() &&
parentComparisonOperator instanceof BinaryComparisonOperatorNode;
@@ -694,6 +714,7 @@
flattenable = (resultSet instanceof SelectNode) &&
!((SelectNode)resultSet).hasWindows() &&
+ orderByList == null &&
underTopAndNode && !havingSubquery &&
!isWhereExistsAnyInWithWhereSubquery() &&
(isIN() || isANY() || isEXISTS() || flattenableNotExists ||
@@ -788,6 +809,20 @@
}
}
+ // Push the order by list down to the ResultSet
+ if (orderByList != null) {
+ // If we have more than 1 ORDERBY columns, we may be able to
+ // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+ if (orderByList.size() > 1)
+ {
+ orderByList.removeDupColumns();
+ }
+
+ resultSet.pushOrderByList(orderByList);
+ orderByList = null;
+ }
+
+
/* We transform the leftOperand and the select list for quantified
* predicates that have a leftOperand into a new predicate and push it
* down to the subquery after we preprocess the subquery's resultSet.
@@ -2463,4 +2498,14 @@
return false;
}
}
+
+ /**
+ * Get ORDER BY list (used to construct FROM_SUBQUERY only), cf.
+ * FromSubquery, for which this node is transient.
+ *
+ * @return order by list if specified, else null.
+ */
+ public OrderByList getOrderByList() {
+ return orderByList;
+ }
}
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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1746,6 +1746,15 @@
autoIncrementInfo,
getContextManager());
}
+
+ private boolean
+ isTableValueConstructor(ResultSetNode expression)
+ throws StandardException {
+
+ return expression instanceof RowResultSetNode ||
+ (expression instanceof UnionNode &&
+ ((UnionNode)expression).tableConstructor());
+ }
}
PARSER_END(SQLParser)
@@ -4903,6 +4912,7 @@
nonJoinQueryPrimary() throws StandardException :
{
ResultSetNode primary;
+ OrderByList orderCols = null;
}
{
primary = simpleTable()
@@ -4910,8 +4920,14 @@
return primary;
}
|
- <LEFT_PAREN> primary = queryExpression(null, NO_SET_OP) <RIGHT_PAREN>
+ <LEFT_PAREN> primary = queryExpression(null, NO_SET_OP)
+ [ orderCols = orderByClause() ]
+ <RIGHT_PAREN>
{
+ if (orderCols != null) {
+ primary.setOrderBy(orderCols);
+ }
+
return primary;
}
}
@@ -8295,6 +8311,7 @@
Properties targetProperties = null;
ResultSetNode queryExpression;
ResultColumnList columnList = null;
+ OrderByList orderCols = null;
}
{
[
@@ -8308,13 +8325,25 @@
]
[ targetProperties = propertyList(false) <CHECK_PROPERTIES>]
queryExpression = queryExpression(null, NO_SET_OP)
+ [ orderCols = orderByClause() ]
{
+ if (orderCols != null && isTableValueConstructor(queryExpression)) {
+ // Not allowed by the standard since this is a <contextually typed
+ // table value constructor> according SQL 2008, vol2, section 14.11
+ // "<insert statement>, SR 17. (I.e. it is not a <subquery> and
+ // can't have an ORDER BY).
+
+ throw StandardException.newException(
+ SQLState.LANG_SYNTAX_ERROR, "ORDER BY");
+ }
+
return (StatementNode) nodeFactory.getNode(
C_NodeTypes.INSERT_NODE,
targetTable,
columnList,
queryExpression,
targetProperties,
+ orderCols,
getContextManager());
}
/* RESOLVE: Ignoring default values for now
@@ -8552,15 +8581,18 @@
{
ResultSetNode queryExpression;
SubqueryNode subqueryNode;
+ OrderByList orderCols = null;
}
{
queryExpression = queryExpression(null, NO_SET_OP)
+ [ orderCols = orderByClause() ]
{
subqueryNode = (SubqueryNode) nodeFactory.getNode(
C_NodeTypes.SUBQUERY_NODE,
queryExpression,
ReuseFactory.getInteger(subqueryType),
leftOperand,
+ orderCols,
getContextManager());
return subqueryNode;
}
@@ -8964,7 +8996,7 @@
FromTable tableReference;
Object[] optionalTableClauses = new Object[OPTIONAL_TABLE_CLAUSES_SIZE];
Properties tableProperties = null;
- ResultSetNode derivedTable;
+ SubqueryNode derivedTable;
}
{
/* NOTE: this rule has to come first in order to avoid making NEW
@@ -9033,7 +9065,8 @@
{
fromTable = (FromTable) nodeFactory.getNode(
C_NodeTypes.FROM_SUBQUERY,
- derivedTable,
+ derivedTable.getResultSet(),
+ derivedTable.getOrderByList(),
correlationName,
derivedRCL,
((optionalTableClauses != null) ?
@@ -9141,7 +9174,7 @@
/*
* <A NAME="derivedTable">derivedTable</A>
*/
-ResultSetNode
+SubqueryNode
derivedTable() throws StandardException :
{
SubqueryNode tableSubquery;
@@ -9151,7 +9184,7 @@
tableSubquery = tableSubquery(SubqueryNode.FROM_SUBQUERY, null)
<RIGHT_PAREN>
{
- return tableSubquery.getResultSet();
+ return tableSubquery;
}
}
@@ -10618,11 +10651,13 @@
TableName tableName;
Token checkTok = null;
Token endToken;
+ OrderByList orderCols = null;
}
{
<VIEW> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
[ <LEFT_PAREN> resultColumns = viewColumnList() <RIGHT_PAREN> ]
<AS> queryExpression = queryExpression(null, NO_SET_OP)
+ [ orderCols = orderByClause() ]
{
checkOptionType = ViewDescriptor.NO_CHECK_OPTION;
endToken = getToken(0);
@@ -10643,6 +10678,7 @@
StringUtil.slice(statementSQLText,
beginToken.beginOffset,
endToken.endOffset,false),
+ orderCols,
getContextManager());
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out Thu Dec 17 22:19:08 2009
@@ -742,22 +742,31 @@
----------------------------------------
ij> -- rollback should release the prepared statements
rollback ;
-ij> -- . order by only allowed on cursor spec, not subquerys (error)
-select v from obt where i in (select i from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 2, column 50.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select v from obt where i = (select i from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 1, column 49.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select v from (select i,v from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 1, column 37.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
+ij> select v from obt where i in (select i from obt2 order by i);
+V
+----------------------------------------
+hello
+planet
+world
+hello
+ij> select v from obt where i in (select i2 from obt2 order by i2);
+V
+----------------------------------------
+hello
+world
+hello
+ij> -- more than one row in subquery: error
+select v from obt where i = (select i from obt2 order by i);
+V
+----------------------------------------
+ERROR 21000: Scalar subquery is only allowed to return a single row.
+ij> select v from (select i2,v from obt2 order by i2)s;
+V
+----------------------------------------
+shoe
+planet
+hello
+planet
ij> -- rollback should release the prepared statements
rollback ;
ij> -- order by allowed on datatypes,
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java?rev=891952&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java Thu Dec 17 22:19:08 2009
@@ -0,0 +1,502 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.OrderByInSubqueries
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.ResultSet;
+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.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+
+/**
+ * Tests for DERBY-4397: allow ORDER BY in subqueries.
+ */
+public class OrderByInSubqueries extends BaseJDBCTestCase {
+
+ public OrderByInSubqueries(String name) {
+ super(name);
+ }
+
+ /**
+ * Construct top level suite in this JUnit test
+ *
+ * @return A suite containing embedded and client suites.
+ */
+ public static Test suite()
+ {
+ TestSuite suite = new TestSuite("OrderByInSubqueries");
+
+ suite.addTest(makeSuite());
+ suite.addTest(
+ TestConfiguration.clientServerDecorator(makeSuite()));
+
+ return suite;
+ }
+
+ /**
+ * Construct suite of tests
+ *
+ * @return A suite containing the test cases.
+ */
+ private static Test makeSuite()
+ {
+ return new CleanDatabaseTestSetup(
+ new TestSuite(OrderByInSubqueries.class)) {
+ protected void decorateSQL(Statement s)
+ throws SQLException {
+ getConnection().setAutoCommit(false);
+
+ s.execute("create table temp1(s varchar(10))");
+
+ // GENERATED ALWAYS AS IDENTITY
+ s.execute("create table temp2(" +
+ "i integer not null " +
+ " generated always as identity," +
+ "s varchar(10))");
+ // DEFAULT value
+ s.execute("create table temp3(" +
+ "i integer not null " +
+ " generated always as identity," +
+ "s varchar(10)," +
+ "j integer not null " +
+ " default 66," +
+ "t varchar(10))");
+
+ // GENERATED ALWAYS AS (expression)
+ s.execute("create table temp4(" +
+ "i integer not null " +
+ " generated always as identity," +
+ "s varchar(10)," +
+ "j integer not null " +
+ " generated always as (2*i)," +
+ "t varchar(10))");
+
+
+ s.execute("create table t01(c1 int)");
+ s.execute("create table t02(c2 int)");
+
+ s.execute("create table t_source(c1 int, c2 varchar(10))");
+ s.execute("create table t(i int not null, " +
+ " constraint c unique (i), " +
+ " j int, k int)");
+
+ getConnection().commit();
+ }
+ };
+ }
+
+ /**
+ * Test INSERT INTO t SELECT .. FROM .. ORDER BY ..
+ */
+ public void testInsertSelectOrderBy() throws SQLException {
+ //
+ // Shows that DERBY-4 is now solved.
+ //
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+ ResultSet rs = null;
+
+ s.execute("insert into temp1 values 'x','a','c','b','a'");
+ s.execute("insert into temp2(s) select s from temp1 order by s");
+ s.execute("insert into temp2(s) select s as a1 from temp1 order by a1");
+ s.execute("insert into temp2(s) select * from temp1 order by s");
+
+ rs = s.executeQuery("select * from temp2");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1", "a"},
+ {"2", "a"},
+ {"3", "b"},
+ {"4", "c"},
+ {"5", "x"},
+ {"6", "a"},
+ {"7", "a"},
+ {"8", "b"},
+ {"9", "c"},
+ {"10", "x"},
+ {"11", "a"},
+ {"12", "a"},
+ {"13", "b"},
+ {"14", "c"},
+ {"15", "x"}});
+
+ rs = s.executeQuery("select * from temp2 order by i");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1", "a"},
+ {"2", "a"},
+ {"3", "b"},
+ {"4", "c"},
+ {"5", "x"},
+ {"6", "a"},
+ {"7", "a"},
+ {"8", "b"},
+ {"9", "c"},
+ {"10", "x"},
+ {"11", "a"},
+ {"12", "a"},
+ {"13", "b"},
+ {"14", "c"},
+ {"15", "x"}});
+
+ s.execute("insert into temp2(s) select s as a1 from temp1 order by s");
+
+ // This should be rejected as "no such column" errors:
+ assertStatementError(
+ "42X04", s,
+ "insert into temp2(s) select s as a1 from temp1 order by no_such");
+
+ // A similar example, but with integers rather than strings, and some
+ // intermediate select statements to show that the ordering is working.
+ //
+ s.execute("insert into t01 values (50), (10), (1000), (15), (51)");
+
+ rs = s.executeQuery("select * from t01");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"50"},
+ {"10"},
+ {"1000"},
+ {"15"},
+ {"51"}});
+
+
+ s.execute("insert into t02 select * from t01 order by c1");
+ s.execute("insert into t02 select * from t01");
+ s.execute("insert into t02 select * from t01 order by c1");
+ rs = s.executeQuery("select * from t02");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"10"},
+ {"15"},
+ {"50"},
+ {"51"},
+ {"1000"},
+ {"50"},
+ {"10"},
+ {"1000"},
+ {"15"},
+ {"51"},
+ {"10"},
+ {"15"},
+ {"50"},
+ {"51"},
+ {"1000"}});
+
+ // Combining ORDER BY and VALUES is not legal SQL, cf. SQL 2008,
+ // section 14.11, Syntactic Rule 17: "A <query expression> simply
+ // contained in a <from subquery> shall not be a <table value
+ // constructor>. See also discussion in JIRA on DERBY-4413
+ // (2009-OCT-23).
+ //
+ assertStatementError(
+ "42X01", s,
+ "insert into t02 values 66 order by 1");
+ assertStatementError(
+ "42X01", s,
+ "insert into t02 values (901), (920), (903) order by 1");
+
+ // But this should work:
+ s.executeUpdate("delete from t02");
+ s.executeUpdate("insert into t02 select 900 from sys.systables " +
+ " union values 66 order by 1");
+ rs = s.executeQuery("select * from t02");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"66"},
+ {"900"}});
+
+ // other way around:
+ s.executeUpdate("delete from t02");
+ s.executeUpdate(
+ "insert into t02 values 66 " +
+ " union select 900 from sys.systables order by 1");
+ rs = s.executeQuery("select * from t02");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"66"},
+ {"900"}});
+
+ // and, somewhat perversely (since a plain "values 66 order by 1" is
+ // illegal), this:
+ s.executeUpdate("delete from t02");
+ s.executeUpdate("insert into t02 values 66 " +
+ " union values 66 order by 1");
+ rs = s.executeQuery("select * from t02");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"66"}});
+
+
+ // UNION
+ //
+ // ok:
+ s.execute("delete from t02");
+ s.execute("insert into t02 select * from t01 union all " +
+ " select * from t01 order by c1");
+ rs = s.executeQuery("select * from t02");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"10"},
+ {"10"},
+ {"15"},
+ {"15"},
+ {"50"},
+ {"50"},
+ {"51"},
+ {"51"},
+ {"1000"},
+ {"1000"}});
+
+ // Not ok (c1 is not a column in the union result set, since t02 has
+ // column c02.
+ assertStatementError(
+ "42X78", s,
+ "insert into t02 select * from t01 union all " +
+ " select * from t02 order by c1");
+
+
+ // Complication: project away sort column
+ s.execute("delete from t02");
+ s.execute("insert into t_source " +
+ " values (1, 'one'), (2, 'two'), (8, 'three')");
+ s.execute("insert into t_source(c1) " +
+ " select c1 from t_source order by c2 desc");
+ rs = s.executeQuery("select * from t_source");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1", "one"},
+ {"2", "two"},
+ {"8", "three"},
+ {"2", null},
+ {"8", null},
+ {"1", null}});
+
+ rollback();
+ }
+
+
+ /**
+ * SELECT subqueries with ORDER BY
+ */
+ public void testSelectSubqueriesOrderBy() throws SQLException {
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+ ResultSet rs;
+
+ s.execute(
+ "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+ /*
+ * Simple SELECT FromSubquery
+ */
+ rs = s.executeQuery(
+ "select * from (select c1 from t_source order by c1 desc) s");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"8"}, {"2"}, {"1"}});
+
+ rs = s.executeQuery(
+ "select * from (select c1+1 from t_source order by c1+1 desc) s");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"9"}, {"3"}, {"2"}});
+
+ rs = s.executeQuery(
+ "select * from (select c1,c2 from t_source order by c1 desc,2) s");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+ // Complication: project away sort column
+ rs = s.executeQuery(
+ "select * from (select c2 from t_source order by c1 desc) s");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"three"}, {"two"}, {"one"}});
+
+ rs = s.executeQuery(
+ "select * from " +
+ " (select c2 from t_source order by c1 desc) s order by 1");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"one"}, {"three"}, {"two"}});
+
+ /*
+ * Simple VALUES FromSubquery
+ */
+ rs = s.executeQuery(
+ "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+ " order by 1 desc) s");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+
+ /*
+ * ORDER BY in EXISTS subquery
+ */
+ rs = s.executeQuery(
+ "select c1 from t_source where exists " +
+ " (select c1 from t_source order by c1)");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"2"}, {"8"}});
+
+ rs = s.executeQuery(
+ "select c1 from t_source where exists " +
+ " (select c1 from t_source order by c1 desc)");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"2"}, {"8"}});
+
+ rs = s.executeQuery(
+ "select c1 from t_source where exists " +
+ " (select c1 from t_source order by c2 desc)");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"}, {"2"}, {"8"}});
+
+ rs = s.executeQuery(
+ "select c1 from t_source where exists " +
+ " (select c1 from t_source order by c2 desc) order by 1 desc");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"8"}, {"2"}, {"1"}});
+
+ /*
+ * NOT EXISTS
+ */
+ rs = s.executeQuery(
+ "select c1 from t_source where not exists " +
+ " (select c1 from t_source order by c2 desc) order by 1 desc");
+ JDBC.assertEmpty(rs);
+
+ rs = s.executeQuery(
+ "select c1 from t_source ot where not exists " +
+ " (select c1 from t_source where ot.c1=(c1/2) order by c2 desc)" +
+ " order by 1 desc");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"8"}, {"2"}});
+
+ /*
+ * IN subquery
+ */
+ s.executeUpdate("insert into t values (1,10,1), (2,40,1)," +
+ " (3,45,1), (4,46,1), (5,90,1)");
+ rs = s.executeQuery(
+ "select * from t where i in (select i/5 from t order by 1 desc)");
+ JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+ rs = s.executeQuery(
+ "select * from t where i in (select i/5 from t order by i/5 desc)");
+ JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+ rs = s.executeQuery(
+ "select * from t where i in (select i/5 from t order by j)");
+ JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+
+ /*
+ * Scalar subquery inside ALL subquery with correlation
+ */
+ String[][] expected = new String[][]{
+ {"1", "10", "1"},
+ {"2", "40", "1"}};
+
+ // First without any ORDER BYs
+ rs = s.executeQuery(
+ "select * from t t_o where i <= all (" +
+ " select i+1 from t where i = t_o.k + (" +
+ " select count(*) from t) - 5)");
+ JDBC.assertFullResultSet(rs, expected);
+
+ // Then with ORDER BY at both subquery levels; should be the same result
+ rs = s.executeQuery(
+ "select * from t t_o where i <= all (" +
+ " select i+1 from t where i = t_o.k + (" +
+ " select count(*) from t order by 1) - 5 " +
+ " order by 1 desc)");
+ JDBC.assertFullResultSet(rs, expected);
+
+ rollback();
+ }
+
+
+ /**
+ * Test ORDER BY in a view definition
+ */
+ public void testView() throws SQLException {
+
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+ ResultSet rs;
+
+ s.executeUpdate(
+ "create view v1 as select i from t order by j desc");
+ s.executeUpdate(
+ "create view v2 as select i from t order by i");
+ s.executeUpdate(
+ "insert into t values (1,10,1), (2,40,1)," +
+ " (3,45,1), (4,46,1), (5,90,1)");
+ rs = s.executeQuery(
+ "select i from v1");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"5"},{"4"},{"3"},{"2"},{"1"}});
+
+ rs = s.executeQuery(
+ "select i from v2");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1"},{"2"},{"3"},{"4"},{"5"}});
+
+ rollback();
+ }
+
+ /**
+ * SELECT subqueries with ORDER BY - negative tests
+ */
+ public void testSelectSubqueriesOrderByNegative() throws SQLException {
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+ ResultSet rs;
+
+ s.execute(
+ "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+ /*
+ * Simple SELECT FromSubquery
+ */
+ assertStatementError(
+ "42X04", s,
+ "select * from (select c1 from t_source order by c3 desc) s");
+
+ assertStatementError(
+ "42X77", s,
+ "select * from (select c1 from t_source order by 3 desc) s");
+
+
+ /*
+ * Simple VALUES FromSubquery
+ */
+ assertStatementError(
+ "42X77", s,
+ "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+ " order by 3 desc) s");
+
+ /*
+ * ORDER BY in EXISTS subquery:
+ */
+ assertStatementError(
+ "42X04", s,
+ "select c1 from t_source where exists " +
+ " (select c1 from t_source order by c4)");
+
+ rollback();
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql Thu Dec 17 22:19:08 2009
@@ -300,12 +300,13 @@
-- rollback should release the prepared statements
rollback ;
--- . order by only allowed on cursor spec, not subquerys (error)
select v from obt where i in (select i from obt2 order by i);
+select v from obt where i in (select i2 from obt2 order by i2);
+-- more than one row in subquery: error
select v from obt where i = (select i from obt2 order by i);
-select v from (select i,v from obt2 order by i);
+select v from (select i2,v from obt2 order by i2)s;
-- rollback should release the prepared statements
rollback ;