• create a new RC in the GROUPBY RCL and set it to * point to the bottom RC
• *
• reset the top PR ref to point to the new GROUPBY - * RC + * RC
• * * For each aggregate in aggregateVector
*
• create RC in FROM TABLE. Fill it with @@ -400,8 +475,41 @@ * to point to FROM TABLE RC
• *
• create RC in GROUPBY for agg result
• *
• create RC in GROUPBY for aggregator
• - *
• replace Agg with reference to RC for agg result
• - * + *
• replace Agg with reference to RC for agg result
. + *

+ * For a query like, + *

```+	  select c1, sum(c2), max(c3)
+	  from t1
+	  group by c1;
+	  ```
+ * the query tree ends up looking like this: +
```+	    ProjectRestrictNode RCL -> (ptr to GBN(column[0]), ptr to GBN(column[1]), ptr to GBN(column[4]))
+	              |
+	    GroupByNode RCL->(C1, SUM(C2), , , MAX(C3), , )
+	              |
+	    ProjectRestrict RCL->(C1, C2, C3)
+	              |
+	    FromBaseTable
+	    ```
+ * + * The RCL of the GroupByNode contains all the unagg (or grouping columns) + * followed by 3 RC's for each aggregate in this order: the final computed + * aggregate value, the aggregate input and the aggregator function. + *

+ * The Aggregator function puts the results in the first of the 3 RC's + * and the PR resultset in turn picks up the value from there. + *

+ * The notation (ptr to GBN(column[0])) basically means that it is + * a pointer to the 0th RC in the RCL of the GroupByNode. + *

+ * The addition of these unagg and agg columns to the GroupByNode and + * to the PRN is performed in addUnAggColumns and addAggregateColumns. + *

+ * Note that that addition of the GroupByNode is done after the + * query is optimized (in SelectNode#modifyAccessPaths) which means a + * fair amount of patching up is needed to account for generated group by columns. * @exception standard exception */ private void addNewColumnsForAggregation() @@ -412,6 +520,32 @@ { addUnAggColumns(); } + if (havingClause != null) { + // we have replaced group by expressions in the having clause. + // there should be no column references in the having clause + // referencing this table. Skip over aggregate nodes. + // select a, sum(b) from t group by a having a+c > 1 + // is not valid because of column c. + // + // it is allright to have columns from parent or child subqueries; + // select * from p where p.p1 in + // (select c.c1 from c group by c.c1 having count(*) = p.p2 + CollectNodesVisitor collectNodesVisitor = + new CollectNodesVisitor(ColumnReference.class, AggregateNode.class); + havingClause.accept(collectNodesVisitor); + for (Iterator it = collectNodesVisitor.getList().iterator(); + it.hasNext(); ) + { + ColumnReference cr = (ColumnReference)it.next(); + + if (!cr.getGeneratedToReplaceAggregate() && + cr.getSourceLevel() == level) { + throw StandardException.newException( + SQLState.LANG_INVALID_COL_HAVING_CLAUSE, + cr.getSQLColumnName()); + } + } + } addAggregateColumns(); } @@ -449,9 +583,27 @@ (ResultColumnList) getNodeFactory().getNode( C_NodeTypes.RESULT_COLUMN_LIST, getContextManager()), - ((FromTable) childResult).getTableNumber()); + ((FromTable) childResult).getTableNumber(), + ResultSetNode.class); parent.getResultColumns().accept(replaceAggsVisitor); + + if (havingClause != null) + { + // replace aggregates in the having clause with column references. + replaceAggsVisitor = new ReplaceAggregatesWithCRVisitor( + (ResultColumnList) getNodeFactory().getNode( + C_NodeTypes.RESULT_COLUMN_LIST, + getContextManager()), + ((FromTable)childResult).getTableNumber()); + havingClause.accept(replaceAggsVisitor); + // make having clause a restriction list in the parent + // project restrict node. + ProjectRestrictNode parentPRSN = (ProjectRestrictNode)parent; + parentPRSN.setRestriction(havingClause); + } + + /* ** For each aggregate */ 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?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- 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 Fri Mar 9 08:37:20 2007 @@ -320,14 +320,14 @@ */ if (targetColumnList != null) { - if (resultSet.getResultColumns().size() > targetColumnList.size()) + if (resultSet.getResultColumns().visibleSize() > targetColumnList.size()) throw StandardException.newException(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED); resultSet.bindUntypedNullsToResultColumns(targetColumnList); resultSet.setTableConstructorTypes(targetColumnList); } else { - if (resultSet.getResultColumns().size() > resultColumnList.size()) + if (resultSet.getResultColumns().visibleSize() > resultColumnList.size()) throw StandardException.newException(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED); resultSet.bindUntypedNullsToResultColumns(resultColumnList); resultSet.setTableConstructorTypes(resultColumnList); @@ -336,7 +336,7 @@ /* Bind the columns of the result set to their expressions */ resultSet.bindResultColumns(fromList); - int resCols = resultSet.getResultColumns().size(); + int resCols = resultSet.getResultColumns().visibleSize(); DataDictionary dd = getDataDictionary(); if (targetColumnList != null) { @@ -404,7 +404,7 @@ ** table. */ for (int position = 0; - position < resultSet.getResultColumns().size(); + position < resultSet.getResultColumns().visibleSize(); position++) { colMap[position] = position; 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?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- 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 Fri Mar 9 08:37:20 2007 @@ -266,6 +266,7 @@ /* And ends with the non-ordering columns */ newRCL.destructiveAppend(resultColumns); newRCL.resetVirtualColumnIds(); + newRCL.copyOrderBySelect(resultColumns); return newRCL; } 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?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- 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 Fri Mar 9 08:37:20 2007 @@ -1838,4 +1838,8 @@ dependentScan); } + public void setRestriction(ValueNode restriction) { + this.restriction = restriction; + } + } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java Fri Mar 9 08:37:20 2007 @@ -47,8 +47,14 @@ */ public ReplaceAggregatesWithCRVisitor(ResultColumnList rcl, int tableNumber) { + this(rcl, tableNumber, null); + } + + public ReplaceAggregatesWithCRVisitor(ResultColumnList rcl, int tableNumber, Class skipOverClass) + { this.rcl = rcl; this.tableNumber = tableNumber; + this.skipOverClass = skipOverClass; } /** Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Fri Mar 9 08:37:20 2007 @@ -1434,6 +1434,10 @@ newResultColumn.setAutoincrement(); if (isGroupingColumn()) newResultColumn.markAsGroupingColumn(); + + if (isGenerated()) { + newResultColumn.markGenerated(); + } return newResultColumn; } 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?view=diff&rev=516454&r1=516453&r2=516454 ============================================================================== --- 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 Fri Mar 9 08:37:20 2007 @@ -340,9 +340,12 @@ * Only FromSubquery needs to call this flavor since * it can have ambiguous references in its own list. * - * @param cr The ColumnReference to resolve - * @param exposedTableName Exposed table name for FromTable - * + * @param cr The ColumnReference to resolve + * @param exposedTableName Exposed table name for FromTable + * @param considerGeneratedColumns Also consider columns that are generated. + * One example of this is group by where columns are added to the select list + * if they are referenced in the group by but are not present in the select + * list. * @return the column that matches that name. * * @exception StandardException Thrown on error @@ -350,7 +353,8 @@ public ResultColumn getAtMostOneResultColumn( ColumnReference cr, - String exposedTableName) + String exposedTableName, + boolean considerGeneratedColumns) throws StandardException { int size = size(); @@ -361,8 +365,11 @@ { ResultColumn resultColumn = (ResultColumn) elementAt(index); - if (columnName.equals( resultColumn.getName()) ) + if (columnName.equals( resultColumn.getName())) { + if (resultColumn.isGenerated() && !considerGeneratedColumns) { + continue; + } /* We should get at most 1 match */ if (retRC != null) { @@ -1570,7 +1577,7 @@ public void copyTypesAndLengthsToSource(ResultColumnList sourceRCL) throws StandardException { /* Source and target can have different lengths. */ - int size = (size() > sourceRCL.size()) ? size() : sourceRCL.size(); + int size = Math.min(size(), sourceRCL.size()); for (int index = 0; index < size; index++) { ResultColumn sourceRC = (ResultColumn) sourceRCL.elementAt(index); @@ -1747,11 +1754,28 @@ newList.addResultColumn(newResultColumn); } - + newList.copyOrderBySelect(this); return newList; } /** + * Remove any columns that may have been added for an order by clause. + * In a query like: + *

`select a from t order by b`