1) Queries using the having clause on base tables should work well

   now. Here some tested features, (examples included in the patch):

1.1) Subselects in the having clause 1.2) Double nested subselects
1.3) Subselects used in the where clause and in the having clause
     simultaneously 1.4) Union Selects using having 1.5) Indexes
on the base relations are used correctly 1.6) Unallowed Queries
are prevented (e.g. qualifications in the
     having clause that belong to the where clause) 1.7) Insert
into as select

2) Queries using the having clause on view relations also work
   but there are some restrictions:

2.1) Create View as Select ... Having ...; using base tables in
the select 2.1.1) The Query rewrite system:

2.1.2) Why are only simple queries allowed against a view from 2.1)
? 2.2) Select ... from testview1, testview2, ... having...; 3) Bug
in ExecMergeJoin ??


Regards Stefan

1 files changed, 25 insertions, 1 deletions

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 95e19e8cb59..4865d2de6da 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.76 1998/05/29 13:39:30 thomas Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.77 1998/07/19 05:49:17 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -327,6 +327,9 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	/* fix where clause */
 	qry->qual = transformWhereClause(pstate, stmt->whereClause);
 
+	/* The havingQual has a similar meaning as "qual" in the where statement. 
+	 * So we can easily use the code from the "where clause" with some additional
+         * traversals done in .../optimizer/plan/planner.c */
 	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);
 
 	qry->hasSubLinks = pstate->p_hasSubLinks;
@@ -356,6 +359,15 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	qry->unionall = stmt->unionall;
 	qry->unionClause = transformUnionClause(stmt->unionClause, qry->targetList);
 
+	/* If there is a havingQual but there are no aggregates, then there is something wrong
+	 * with the query because having must contain aggregates in its expressions! 
+	 * Otherwise the query could have been formulated using the where clause. */
+	if((qry->hasAggs == false) && (qry->havingQual != NULL))
+	  {
+	    elog(ERROR,"This is not a valid having query!");
+	    return (Query *)NIL;
+	  }
+
 	return (Query *) qry;
 }
 
@@ -795,6 +807,9 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 
 	qry->qual = transformWhereClause(pstate, stmt->whereClause);
 
+	/* The havingQual has a similar meaning as "qual" in the where statement. 
+	 * So we can easily use the code from the "where clause" with some additional
+         * traversals done in .../optimizer/plan/planner.c */
 	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);
 
 	qry->hasSubLinks = pstate->p_hasSubLinks;
@@ -820,6 +835,15 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->unionall = stmt->unionall;
 	qry->unionClause = transformUnionClause(stmt->unionClause, qry->targetList);
 
+	/* If there is a havingQual but there are no aggregates, then there is something wrong
+	 * with the query because having must contain aggregates in its expressions! 
+	 * Otherwise the query could have been formulated using the where clause. */
+	if((qry->hasAggs == false) && (qry->havingQual != NULL))
+	  {
+	    elog(ERROR,"This is not a valid having query!");
+	    return (Query *)NIL;
+	  }
+
 	return (Query *) qry;
 }