I started adding the Having Clause and it works quite fine for

sequential scans! (I think it will also work with hash, index, etc
but I did not check it out! I made some High level changes which
should work for all access methods, but maybe I'm wrong. Please
let me know.)

Now it is possible to make queries like:

select s.sname, max(p.pid), min(p.pid) from part p, supplier s
where s.sid=p.sid group by s.sname having max(pid)=6 and min(pid)=1
or avg(pid)=4;

Having does not work yet for queries that contain a subselect
statement in the Having clause, I'll try to fix this in the next
days.

If there are some bugs, please let me know, I'll start to read the
mailinglists now!

Now here is the patch against the original 6.3 version (no snapshot!!):

Stefan

9 files changed, 173 insertions, 16 deletions

diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index 55a12bd8317..f1ff8bbbb4c 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/execQual.c,v 1.26 1998/02/26 04:31:13 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/execQual.c,v 1.27 1998/03/30 16:35:50 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -203,8 +203,7 @@ ExecEvalArrayRef(ArrayRef *arrayRef,
 static Datum
 ExecEvalAggreg(Aggreg *agg, ExprContext *econtext, bool *isNull)
 {
-
-	*isNull = econtext->ecxt_nulls[agg->aggno];
+	*isNull = econtext->ecxt_nulls[agg->aggno];	
 	return econtext->ecxt_values[agg->aggno];
 }
 
@@ -648,6 +647,8 @@ ExecEvalFuncArgs(FunctionCachePtr fcache,
 						 econtext,
 						 &argIsNull,
 						 argIsDone);
+
+
 		if (!(*argIsDone))
 		{
 			Assert(i == 0);
@@ -1356,8 +1357,11 @@ ExecQual(List *qual, ExprContext *econtext)
 	 * ----------------
 	 */
 	result = false;
+
 	foreach(clause, qual)
 	{
+	 
+	  
 		result = ExecQualClause((Node *) lfirst(clause), econtext);
 		if (result == true)
 			break;
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 848bd616bf0..c2b77c6e050 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -29,6 +29,7 @@
 #include "storage/bufmgr.h"
 #include "utils/palloc.h"
 #include "utils/syscache.h"
+#include "optimizer/clauses.h"
 
 /*
  * AggFuncInfo -
@@ -109,10 +110,16 @@ ExecAgg(Agg *node)
 				isNull1 = FALSE,
 				isNull2 = FALSE;
 
+
+	/***S*H***/
+	do { 
+
+
 	/* ---------------------
 	 *	get state info from node
 	 * ---------------------
 	 */
+
 	aggstate = node->aggstate;
 	if (aggstate->agg_done)
 		return NULL;
@@ -229,6 +236,7 @@ ExecAgg(Agg *node)
 			}
 		}
 	}
+	  
 
 	/* ----------------
 	 *	 for each tuple from the the outer plan, apply all the aggregates
@@ -477,11 +485,19 @@ ExecAgg(Agg *node)
 	 *	slot and return it.
 	 * ----------------
 	 */
+
+        /***S*H***/
+	}
+	while((ExecQual(fix_opids(node->plan.qual),econtext)!=true) && 
+	      (node->plan.qual!=NULL));
+
+	 
 	ExecStoreTuple(oneTuple,
 				   aggstate->csstate.css_ScanTupleSlot,
 				   InvalidBuffer,
 				   false);
 	econtext->ecxt_scantuple = aggstate->csstate.css_ScanTupleSlot;
+
 	resultSlot = ExecProject(projInfo, &isDone);
 
 	if (oneTuple)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 824c0d29b35..35c19d6c44f 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.23 1998/02/26 04:32:51 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.24 1998/03/30 16:36:04 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -59,6 +59,115 @@ make_groupPlan(List **tlist, bool tuplePerGroup,
  *
  *****************************************************************************/
 
+
+/***S*H***/ /* Anfang */
+
+static List *
+check_having_qual_for_aggs(Node *clause, List *subplanTargetList)
+{
+	List	   *t;
+	List	   *agg_list = NIL;
+
+	if (IsA(clause, Var))
+	{
+	  TargetEntry *subplanVar;
+	  
+	  /*
+	   * Ha! A Var node!
+	   */
+	  subplanVar = match_varid((Var *) clause, subplanTargetList);
+	  
+	  /*
+	   * Change the varno & varattno fields of the var node.
+	   *
+	   */
+	  ((Var *) clause)->varattno = subplanVar->resdom->resno;
+	  return NIL;
+	}
+        /***S*H***/
+	else if (is_funcclause(clause) || not_clause(clause) || 
+		 or_clause(clause) || and_clause(clause))
+	{
+
+		/*
+		 * This is a function. Recursively call this routine for its
+		 * arguments...
+		 */
+		foreach(t, ((Expr *) clause)->args)
+		{
+			agg_list = nconc(agg_list,
+					   check_having_qual_for_aggs(lfirst(t), subplanTargetList));
+		}
+		return agg_list;
+	}
+	else if (IsA(clause, Aggreg))
+	{
+		return lcons(clause,
+		    check_having_qual_for_aggs(((Aggreg *) clause)->target, subplanTargetList));
+		
+	}
+	else if (IsA(clause, ArrayRef))
+	{
+		ArrayRef   *aref = (ArrayRef *) clause;
+
+		/*
+		 * This is an arrayref. Recursively call this routine for its
+		 * expression and its index expression...
+		 */
+		foreach(t, aref->refupperindexpr)
+		{
+			agg_list = nconc(agg_list,
+					 check_having_qual_for_aggs(lfirst(t), subplanTargetList));
+		}
+		foreach(t, aref->reflowerindexpr)
+		{
+			agg_list = nconc(agg_list,
+					 check_having_qual_for_aggs(lfirst(t), subplanTargetList));
+		}
+		agg_list = nconc(agg_list,
+				 check_having_qual_for_aggs(aref->refexpr, subplanTargetList));
+		agg_list = nconc(agg_list,
+				 check_having_qual_for_aggs(aref->refassgnexpr, subplanTargetList));
+
+		return agg_list;
+	}
+	else if (is_opclause(clause))
+	{
+
+		/*
+		 * This is an operator. Recursively call this routine for both its
+		 * left and right operands
+		 */
+		Node	   *left = (Node *) get_leftop((Expr *) clause);
+		Node	   *right = (Node *) get_rightop((Expr *) clause);
+
+		if (left != (Node *) NULL)
+			agg_list = nconc(agg_list,
+					 check_having_qual_for_aggs(left, subplanTargetList));
+		if (right != (Node *) NULL)
+			agg_list = nconc(agg_list,
+					 check_having_qual_for_aggs(right, subplanTargetList));
+
+		return agg_list;
+	}
+	else if (IsA(clause, Param) ||IsA(clause, Const))
+	{
+		/* do nothing! */
+		return NIL;
+	}
+	else
+	{
+
+		/*
+		 * Ooops! we can not handle that!
+		 */
+		elog(ERROR, "check_having_qual_for_aggs: Can not handle this having_qual!\n");
+		return NIL;
+	}
+}
+/***S*H***/ /* Ende */
+
+
 Plan *
 planner(Query *parse)
 {
@@ -181,7 +290,22 @@ union_planner(Query *parse)
 		 * the result tuple of the subplans.
 		 */
 		((Agg *) result_plan)->aggs =
-			set_agg_tlist_references((Agg *) result_plan);
+			set_agg_tlist_references((Agg *) result_plan); 
+
+		/***S*H***/
+		if(parse->havingQual!=NULL) {
+		  List	   *clause;
+
+		  /***S*H***/ /* set qpqual of having clause */
+		  ((Agg *) result_plan)->plan.qual=cnfify((Expr *)parse->havingQual,true);
+
+		  foreach(clause, ((Agg *) result_plan)->plan.qual)
+		    {
+		      ((Agg *) result_plan)->aggs = nconc(((Agg *) result_plan)->aggs,
+			 check_having_qual_for_aggs((Node *) lfirst(clause),
+				       ((Agg *) result_plan)->plan.lefttree->targetlist));
+		    }
+		}
 	}
 
 	/*
@@ -429,3 +553,6 @@ pg_checkretval(Oid rettype, QueryTreeList *queryTreeList)
 	/* success */
 	return;
 }
+
+
+
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index c82a711637b..de4bbf95198 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.19 1998/02/26 04:32:53 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.20 1998/03/30 16:36:14 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -839,6 +839,7 @@ replace_agg_clause(Node *clause, List *subplanTargetList)
 	}
 }
 
+
 /*
  * del_agg_tlist_references
  *	  Remove the Agg nodes from the target list
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 64ec5fa37d9..6dfdd8c26fe 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.71 1998/02/26 04:33:26 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.72 1998/03/30 16:36:23 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -781,6 +781,10 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->targetList = transformTargetList(pstate, stmt->targetList);
 
 	qry->qual = transformWhereClause(pstate, stmt->whereClause);
+
+        /***S*H***/
+	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);
+
 	qry->hasSubLinks = pstate->p_hasSubLinks;
 
 	qry->sortClause = transformSortClause(pstate,
diff --git a/src/backend/parser/gram.c b/src/backend/parser/gram.c
index 9c4cca1a40f..298852387dd 100644
--- a/src/backend/parser/gram.c
+++ b/src/backend/parser/gram.c
@@ -216,7 +216,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/Attic/gram.c,v 2.4 1998/03/18 16:50:15 thomas Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/Attic/gram.c,v 2.5 1998/03/30 16:36:32 momjian Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -6700,7 +6700,7 @@ case 463:
 case 464:
 #line 2529 "gram.y"
 {
-					elog(NOTICE, "HAVING not yet supported; ignore clause");
+					/***S*H***/ /* elog(NOTICE, "HAVING not yet supported; ignore clause");*/
 					yyval.node = yyvsp[0].node;
 				;
     break;}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e9808c91027..71812de58b4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.7 1998/03/18 16:50:19 thomas Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.8 1998/03/30 16:36:35 momjian Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -2527,7 +2527,7 @@ groupby:  ColId
 
 having_clause:  HAVING a_expr
 				{
-					elog(NOTICE, "HAVING not yet supported; ignore clause");
+					/***S*H***/ /* elog(NOTICE, "HAVING not yet supported; ignore clause");*/
 					$$ = $2;
 				}
 		| /*EMPTY*/								{ $$ = NULL; }
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index e7a88101960..083d4ee15df 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_agg.c,v 1.9 1998/02/26 04:33:28 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_agg.c,v 1.10 1998/03/30 16:36:36 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -331,7 +331,7 @@ ParseAgg(ParseState *pstate, char *aggname, Oid basetype,
 	aggreg->target = lfirst(target);
 	if (usenulls)
 		aggreg->usenulls = true;
-
+	
 	pstate->p_hasAggs = true;
 
 	return aggreg;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index f3ccf54c23e..5bb24720f8f 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -6,7 +6,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.14 1998/02/26 04:35:16 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.15 1998/03/30 16:36:43 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -195,7 +195,7 @@ FireRetrieveRulesAtQuery(Query *parsetree,
 	if ((rt_entry_locks = relation->rd_rules) == NULL)
 		return NIL;
 
-	locks = matchLocks(CMD_SELECT, rt_entry_locks, rt_index, parsetree);
+	locks = matchLocks(CMD_SELECT, rt_entry_locks, rt_index, parsetree);	
 
 	/* find all retrieve instead */
 	foreach(i, locks)
@@ -375,6 +375,7 @@ ProcessRetrieveQuery(Query *parsetree,
 	List	   *product_queries = NIL;
 	int			rt_index = 0;
 
+
 	foreach(rt, rtable)
 	{
 		RangeTblEntry *rt_entry = lfirst(rt);
@@ -384,6 +385,8 @@ ProcessRetrieveQuery(Query *parsetree,
 		rt_index++;
 		rt_entry_relation = heap_openr(rt_entry->relname);
 
+
+
 		if (rt_entry_relation->rd_rules != NULL)
 		{
 			result =
@@ -414,6 +417,7 @@ ProcessRetrieveQuery(Query *parsetree,
 		rt_entry_locks = rt_entry_relation->rd_rules;
 		heap_close(rt_entry_relation);
 
+
 		if (rt_entry_locks)
 		{
 			locks =
@@ -683,7 +687,6 @@ static int	numQueryRewriteInvoked = 0;
 List *
 QueryRewrite(Query *parsetree)
 {
-
 	QueryRewriteSubLink(parsetree->qual);
 	return QueryRewriteOne(parsetree);
 }
@@ -780,6 +783,8 @@ deepRewriteQuery(Query *parsetree)
 	bool		instead;
 	List	   *qual_products = NIL;
 
+
+
 	if (++numQueryRewriteInvoked > REWRITE_INVOKE_MAX)
 	{
 		elog(ERROR, "query rewritten %d times, may contain cycles",