Skip to content

Commit c579ce0

Browse files
bmomjianbmomjian
committed
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
1 parent 9c93fa2 commit c579ce0

File tree

9 files changed

+173
-16
lines changed

9 files changed

+173
-16
lines changed

‎src/backend/executor/execQual.c

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/executor/execQual.c,v 1.26 1998/02/26 04:31:13 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/executor/execQual.c,v 1.27 1998/03/30 16:35:50 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -203,8 +203,7 @@ ExecEvalArrayRef(ArrayRef *arrayRef,
203203
static Datum
204204
ExecEvalAggreg(Aggreg *agg, ExprContext *econtext, bool *isNull)
205205
{
206-
207-
*isNull = econtext->ecxt_nulls[agg->aggno];
206+
*isNull = econtext->ecxt_nulls[agg->aggno];
208207
return econtext->ecxt_values[agg->aggno];
209208
}
210209

@@ -648,6 +647,8 @@ ExecEvalFuncArgs(FunctionCachePtr fcache,
648647
econtext,
649648
&argIsNull,
650649
argIsDone);
650+
651+
651652
if (!(*argIsDone))
652653
{
653654
Assert(i == 0);
@@ -1356,8 +1357,11 @@ ExecQual(List *qual, ExprContext *econtext)
13561357
* ----------------
13571358
*/
13581359
result = false;
1360+
13591361
foreach(clause, qual)
13601362
{
1363+
1364+
13611365
result = ExecQualClause((Node *) lfirst(clause), econtext);
13621366
if (result == true)
13631367
break;

‎src/backend/executor/nodeAgg.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include "storage/bufmgr.h"
3030
#include "utils/palloc.h"
3131
#include "utils/syscache.h"
32+
#include "optimizer/clauses.h"
3233

3334
/*
3435
* AggFuncInfo -
@@ -109,10 +110,16 @@ ExecAgg(Agg *node)
109110
isNull1 = FALSE,
110111
isNull2 = FALSE;
111112

113+
114+
/***S*H***/
115+
do {
116+
117+
112118
/* ---------------------
113119
* get state info from node
114120
* ---------------------
115121
*/
122+
116123
aggstate = node->aggstate;
117124
if (aggstate->agg_done)
118125
return NULL;
@@ -229,6 +236,7 @@ ExecAgg(Agg *node)
229236
}
230237
}
231238
}
239+
232240

233241
/* ----------------
234242
* for each tuple from the the outer plan, apply all the aggregates
@@ -477,11 +485,19 @@ ExecAgg(Agg *node)
477485
* slot and return it.
478486
* ----------------
479487
*/
488+
489+
/***S*H***/
490+
}
491+
while((ExecQual(fix_opids(node->plan.qual),econtext)!=true) &&
492+
(node->plan.qual!=NULL));
493+
494+
480495
ExecStoreTuple(oneTuple,
481496
aggstate->csstate.css_ScanTupleSlot,
482497
InvalidBuffer,
483498
false);
484499
econtext->ecxt_scantuple = aggstate->csstate.css_ScanTupleSlot;
500+
485501
resultSlot = ExecProject(projInfo, &isDone);
486502

487503
if (oneTuple)

‎src/backend/optimizer/plan/planner.c

Lines changed: 129 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.23 1998/02/26 04:32:51 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.24 1998/03/30 16:36:04 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -59,6 +59,115 @@ make_groupPlan(List **tlist, bool tuplePerGroup,
5959
*
6060
*****************************************************************************/
6161

62+
63+
/***S*H***/ /* Anfang */
64+
65+
static List *
66+
check_having_qual_for_aggs(Node *clause, List *subplanTargetList)
67+
{
68+
List *t;
69+
List *agg_list = NIL;
70+
71+
if (IsA(clause, Var))
72+
{
73+
TargetEntry *subplanVar;
74+
75+
/*
76+
* Ha! A Var node!
77+
*/
78+
subplanVar = match_varid((Var *) clause, subplanTargetList);
79+
80+
/*
81+
* Change the varno & varattno fields of the var node.
82+
*
83+
*/
84+
((Var *) clause)->varattno = subplanVar->resdom->resno;
85+
return NIL;
86+
}
87+
/***S*H***/
88+
else if (is_funcclause(clause) || not_clause(clause) ||
89+
or_clause(clause) || and_clause(clause))
90+
{
91+
92+
/*
93+
* This is a function. Recursively call this routine for its
94+
* arguments...
95+
*/
96+
foreach(t, ((Expr *) clause)->args)
97+
{
98+
agg_list = nconc(agg_list,
99+
check_having_qual_for_aggs(lfirst(t), subplanTargetList));
100+
}
101+
return agg_list;
102+
}
103+
else if (IsA(clause, Aggreg))
104+
{
105+
return lcons(clause,
106+
check_having_qual_for_aggs(((Aggreg *) clause)->target, subplanTargetList));
107+
108+
}
109+
else if (IsA(clause, ArrayRef))
110+
{
111+
ArrayRef *aref = (ArrayRef *) clause;
112+
113+
/*
114+
* This is an arrayref. Recursively call this routine for its
115+
* expression and its index expression...
116+
*/
117+
foreach(t, aref->refupperindexpr)
118+
{
119+
agg_list = nconc(agg_list,
120+
check_having_qual_for_aggs(lfirst(t), subplanTargetList));
121+
}
122+
foreach(t, aref->reflowerindexpr)
123+
{
124+
agg_list = nconc(agg_list,
125+
check_having_qual_for_aggs(lfirst(t), subplanTargetList));
126+
}
127+
agg_list = nconc(agg_list,
128+
check_having_qual_for_aggs(aref->refexpr, subplanTargetList));
129+
agg_list = nconc(agg_list,
130+
check_having_qual_for_aggs(aref->refassgnexpr, subplanTargetList));
131+
132+
return agg_list;
133+
}
134+
else if (is_opclause(clause))
135+
{
136+
137+
/*
138+
* This is an operator. Recursively call this routine for both its
139+
* left and right operands
140+
*/
141+
Node *left = (Node *) get_leftop((Expr *) clause);
142+
Node *right = (Node *) get_rightop((Expr *) clause);
143+
144+
if (left != (Node *) NULL)
145+
agg_list = nconc(agg_list,
146+
check_having_qual_for_aggs(left, subplanTargetList));
147+
if (right != (Node *) NULL)
148+
agg_list = nconc(agg_list,
149+
check_having_qual_for_aggs(right, subplanTargetList));
150+
151+
return agg_list;
152+
}
153+
else if (IsA(clause, Param) ||IsA(clause, Const))
154+
{
155+
/* do nothing! */
156+
return NIL;
157+
}
158+
else
159+
{
160+
161+
/*
162+
* Ooops! we can not handle that!
163+
*/
164+
elog(ERROR, "check_having_qual_for_aggs: Can not handle this having_qual!\n");
165+
return NIL;
166+
}
167+
}
168+
/***S*H***/ /* Ende */
169+
170+
62171
Plan *
63172
planner(Query *parse)
64173
{
@@ -181,7 +290,22 @@ union_planner(Query *parse)
181290
* the result tuple of the subplans.
182291
*/
183292
((Agg *) result_plan)->aggs =
184-
set_agg_tlist_references((Agg *) result_plan);
293+
set_agg_tlist_references((Agg *) result_plan);
294+
295+
/***S*H***/
296+
if(parse->havingQual!=NULL) {
297+
List *clause;
298+
299+
/***S*H***/ /* set qpqual of having clause */
300+
((Agg *) result_plan)->plan.qual=cnfify((Expr *)parse->havingQual,true);
301+
302+
foreach(clause, ((Agg *) result_plan)->plan.qual)
303+
{
304+
((Agg *) result_plan)->aggs = nconc(((Agg *) result_plan)->aggs,
305+
check_having_qual_for_aggs((Node *) lfirst(clause),
306+
((Agg *) result_plan)->plan.lefttree->targetlist));
307+
}
308+
}
185309
}
186310

187311
/*
@@ -429,3 +553,6 @@ pg_checkretval(Oid rettype, QueryTreeList *queryTreeList)
429553
/* success */
430554
return;
431555
}
556+
557+
558+

‎src/backend/optimizer/plan/setrefs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.19 1998/02/26 04:32:53 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.20 1998/03/30 16:36:14 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -839,6 +839,7 @@ replace_agg_clause(Node *clause, List *subplanTargetList)
839839
}
840840
}
841841

842+
842843
/*
843844
* del_agg_tlist_references
844845
* Remove the Agg nodes from the target list

‎src/backend/parser/analyze.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.71 1998/02/26 04:33:26 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.72 1998/03/30 16:36:23 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -781,6 +781,10 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
781781
qry->targetList = transformTargetList(pstate, stmt->targetList);
782782

783783
qry->qual = transformWhereClause(pstate, stmt->whereClause);
784+
785+
/***S*H***/
786+
qry->havingQual = transformWhereClause(pstate, stmt->havingClause);
787+
784788
qry->hasSubLinks = pstate->p_hasSubLinks;
785789

786790
qry->sortClause = transformSortClause(pstate,

‎src/backend/parser/gram.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -216,7 +216,7 @@
216216
*
217217
*
218218
* IDENTIFICATION
219-
* $Header: /cvsroot/pgsql/src/backend/parser/Attic/gram.c,v 2.4 1998/03/18 16:50:15 thomas Exp $
219+
* $Header: /cvsroot/pgsql/src/backend/parser/Attic/gram.c,v 2.5 1998/03/30 16:36:32 momjian Exp $
220220
*
221221
* HISTORY
222222
* AUTHOR DATE MAJOR EVENT
@@ -6700,7 +6700,7 @@ case 463:
67006700
case 464:
67016701
#line 2529 "gram.y"
67026702
{
6703-
elog(NOTICE, "HAVING not yet supported; ignore clause");
6703+
/***S*H***/ /* elog(NOTICE, "HAVING not yet supported; ignore clause");*/
67046704
yyval.node = yyvsp[0].node;
67056705
;
67066706
break;}

‎src/backend/parser/gram.y

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.7 1998/03/18 16:50:19 thomas Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.8 1998/03/30 16:36:35 momjian Exp $
1414
*
1515
* HISTORY
1616
* AUTHOR DATE MAJOR EVENT
@@ -2527,7 +2527,7 @@ groupby: ColId
25272527

25282528
having_clause: HAVING a_expr
25292529
{
2530-
elog(NOTICE, "HAVING not yet supported; ignore clause");
2530+
/***S*H***/ /* elog(NOTICE, "HAVING not yet supported; ignore clause");*/
25312531
$$ = $2;
25322532
}
25332533
| /*EMPTY*/ { $$ = NULL; }

‎src/backend/parser/parse_agg.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/parser/parse_agg.c,v 1.9 1998/02/26 04:33:28 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/parser/parse_agg.c,v 1.10 1998/03/30 16:36:36 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -331,7 +331,7 @@ ParseAgg(ParseState *pstate, char *aggname, Oid basetype,
331331
aggreg->target = lfirst(target);
332332
if (usenulls)
333333
aggreg->usenulls = true;
334-
334+
335335
pstate->p_hasAggs = true;
336336

337337
return aggreg;

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
*
77
*
88
* IDENTIFICATION
9-
* $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.14 1998/02/26 04:35:16 momjian Exp $
9+
* $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.15 1998/03/30 16:36:43 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -195,7 +195,7 @@ FireRetrieveRulesAtQuery(Query *parsetree,
195195
if ((rt_entry_locks = relation->rd_rules) == NULL)
196196
return NIL;
197197

198-
locks = matchLocks(CMD_SELECT, rt_entry_locks, rt_index, parsetree);
198+
locks = matchLocks(CMD_SELECT, rt_entry_locks, rt_index, parsetree);
199199

200200
/* find all retrieve instead */
201201
foreach(i, locks)
@@ -375,6 +375,7 @@ ProcessRetrieveQuery(Query *parsetree,
375375
List *product_queries = NIL;
376376
int rt_index = 0;
377377

378+
378379
foreach(rt, rtable)
379380
{
380381
RangeTblEntry *rt_entry = lfirst(rt);
@@ -384,6 +385,8 @@ ProcessRetrieveQuery(Query *parsetree,
384385
rt_index++;
385386
rt_entry_relation = heap_openr(rt_entry->relname);
386387

388+
389+
387390
if (rt_entry_relation->rd_rules != NULL)
388391
{
389392
result =
@@ -414,6 +417,7 @@ ProcessRetrieveQuery(Query *parsetree,
414417
rt_entry_locks = rt_entry_relation->rd_rules;
415418
heap_close(rt_entry_relation);
416419

420+
417421
if (rt_entry_locks)
418422
{
419423
locks =
@@ -683,7 +687,6 @@ static int numQueryRewriteInvoked = 0;
683687
List *
684688
QueryRewrite(Query *parsetree)
685689
{
686-
687690
QueryRewriteSubLink(parsetree->qual);
688691
return QueryRewriteOne(parsetree);
689692
}
@@ -780,6 +783,8 @@ deepRewriteQuery(Query *parsetree)
780783
bool instead;
781784
List *qual_products = NIL;
782785

786+
787+
783788
if (++numQueryRewriteInvoked > REWRITE_INVOKE_MAX)
784789
{
785790
elog(ERROR, "query rewritten %d times, may contain cycles",

0 commit comments

Comments
 (0)