Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit460b20a

Browse files
committed
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 subselects1.3) Subselects used in the where clause and in the having clause simultaneously 1.4) Union Selects using having 1.5) Indexeson the base relations are used correctly 1.6) Unallowed Queriesare prevented (e.g. qualifications in the having clause that belong to the where clause) 1.7) Insertinto as select2) 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 inthe 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) Bugin ExecMergeJoin ??Regards Stefan
1 parent916710f commit460b20a

File tree

13 files changed

+998
-538
lines changed

13 files changed

+998
-538
lines changed

‎src/backend/commands/view.c

Lines changed: 4 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/commands/view.c,v 1.22 1998/06/15 19:28:17 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.23 1998/07/19 05:49:12 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -222,6 +222,9 @@ UpdateRangeTableOfViewParse(char *viewName, Query *viewParse)
222222
OffsetVarNodes((Node*)viewParse->targetList,2);
223223
OffsetVarNodes(viewParse->qual,2);
224224

225+
OffsetVarNodes(viewParse->havingQual,2);
226+
227+
225228
/*
226229
* find the old range table...
227230
*/

‎src/backend/executor/nodeAgg.c

Lines changed: 13 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -109,22 +109,24 @@ ExecAgg(Agg *node)
109109
boolisNull= FALSE,
110110
isNull1= FALSE,
111111
isNull2= FALSE;
112-
113-
114-
do {
115-
112+
boolqual_result;
113+
116114

117115
/* ---------------------
118116
*get state info from node
119117
* ---------------------
120118
*/
121119

120+
/* We loop retrieving groups until we find one matching node->plan.qual */
121+
do {
122+
122123
aggstate=node->aggstate;
123124
if (aggstate->agg_done)
124125
returnNULL;
125126

126127
estate=node->plan.state;
127128
econtext=aggstate->csstate.cstate.cs_ExprContext;
129+
128130
nagg=length(node->aggs);
129131

130132
aggregates= (Aggreg**)palloc(sizeof(Aggreg*)*nagg);
@@ -235,8 +237,7 @@ ExecAgg(Agg *node)
235237
}
236238
}
237239
}
238-
239-
240+
240241
/* ----------------
241242
* for each tuple from the the outer plan, apply all the aggregates
242243
* ----------------
@@ -474,11 +475,6 @@ ExecAgg(Agg *node)
474475
*slot and return it.
475476
* ----------------
476477
*/
477-
478-
}
479-
while((ExecQual(fix_opids(node->plan.qual),econtext)!=true)&&
480-
(node->plan.qual!=NULL));
481-
482478

483479
ExecStoreTuple(oneTuple,
484480
aggstate->csstate.css_ScanTupleSlot,
@@ -488,8 +484,13 @@ ExecAgg(Agg *node)
488484

489485
resultSlot=ExecProject(projInfo,&isDone);
490486

487+
/* As long as the retrieved group does not match the qualifications it is ignored and
488+
* the next group is fetched */
489+
qual_result=ExecQual(fix_opids(node->plan.qual),econtext);
491490
if (oneTuple)
492-
pfree(oneTuple);
491+
pfree(oneTuple);
492+
}
493+
while((node->plan.qual!=NULL)&& (qual_result!=true));
493494

494495
returnresultSlot;
495496
}

‎src/backend/executor/nodeMergejoin.c

Lines changed: 4 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/executor/nodeMergejoin.c,v 1.16 1998/06/15 19:28:22 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/executor/nodeMergejoin.c,v 1.17 1998/07/19 05:49:13 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -365,7 +365,9 @@ CleanUpSort(Plan *plan)
365365
{
366366
Sort*sort= (Sort*)plan;
367367

368-
psort_end(sort);
368+
/* This may need to be fixed or moved somewhere else, bjm */
369+
/* psort_end(sort); */
370+
369371
}
370372
}
371373

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

Lines changed: 133 additions & 44 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.27 1998/04/15 15:29:41 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.28 1998/07/19 05:49:14 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -95,6 +95,11 @@ Plan *
9595
union_planner(Query*parse)
9696
{
9797
List*tlist=parse->targetList;
98+
99+
/* copy the original tlist, we will need the original one
100+
* for the AGG node later on */
101+
List*new_tlist=new_unsorted_tlist(tlist);
102+
98103
List*rangetable=parse->rtable;
99104

100105
Plan*result_plan= (Plan*)NULL;
@@ -104,46 +109,77 @@ union_planner(Query *parse)
104109

105110
if (parse->unionClause)
106111
{
107-
result_plan= (Plan*)plan_union_queries(parse);
108-
/* XXX do we need to do this? bjm 12/19/97 */
109-
tlist=preprocess_targetlist(tlist,
110-
parse->commandType,
111-
parse->resultRelation,
112-
parse->rtable);
112+
result_plan= (Plan*)plan_union_queries(parse);
113+
/* XXX do we need to do this? bjm 12/19/97 */
114+
tlist=preprocess_targetlist(tlist,
115+
parse->commandType,
116+
parse->resultRelation,
117+
parse->rtable);
113118
}
114119
elseif ((rt_index=
115120
first_inherit_rt_entry(rangetable))!=-1)
116121
{
117122
result_plan= (Plan*)plan_inherit_queries(parse,rt_index);
118123
/* XXX do we need to do this? bjm 12/19/97 */
119124
tlist=preprocess_targetlist(tlist,
120-
parse->commandType,
121-
parse->resultRelation,
122-
parse->rtable);
125+
parse->commandType,
126+
parse->resultRelation,
127+
parse->rtable);
123128
}
124129
else
125130
{
126-
List**vpm=NULL;
127-
128-
tlist=preprocess_targetlist(tlist,
129-
parse->commandType,
130-
parse->resultRelation,
131-
parse->rtable);
132-
if (parse->rtable!=NULL)
131+
List**vpm=NULL;
132+
133+
/* This is only necessary if aggregates are in use in queries like:
134+
* SELECT sid
135+
* FROM part
136+
* GROUP BY sid
137+
* HAVING MIN(pid) > 1; (pid is used but never selected for!!!)
138+
* because the function 'query_planner' creates the plan for the lefttree
139+
* of the 'GROUP' node and returns only those attributes contained in 'tlist'.
140+
* The original 'tlist' contains only 'sid' here and that's why we have to
141+
* to extend it to attributes which are not selected but are used in the
142+
* havingQual. */
143+
144+
/* 'check_having_qual_for_vars' takes the havingQual and the actual 'tlist'
145+
* as arguments and recursively scans the havingQual for attributes
146+
* (VAR nodes) that are not contained in 'tlist' yet. If so, it creates
147+
* a new entry and attaches it to the list 'new_tlist' (consisting of the
148+
* VAR node and the RESDOM node as usual with tlists :-) ) */
149+
if (parse->hasAggs)
150+
{
151+
if (parse->havingQual!=NULL)
133152
{
134-
vpm= (List**)palloc(length(parse->rtable)*sizeof(List*));
135-
memset(vpm,0,length(parse->rtable)*sizeof(List*));
153+
new_tlist=check_having_qual_for_vars(parse->havingQual,new_tlist);
136154
}
137-
PlannerVarParam=lcons(vpm,PlannerVarParam);
138-
result_plan=query_planner(parse,
139-
parse->commandType,
140-
tlist,
141-
(List*)parse->qual);
142-
PlannerVarParam=lnext(PlannerVarParam);
143-
if (vpm!=NULL)
144-
pfree(vpm);
155+
}
156+
157+
new_tlist=preprocess_targetlist(new_tlist,
158+
parse->commandType,
159+
parse->resultRelation,
160+
parse->rtable);
161+
162+
/* Here starts the original (pre having) code */
163+
tlist=preprocess_targetlist(tlist,
164+
parse->commandType,
165+
parse->resultRelation,
166+
parse->rtable);
167+
168+
if (parse->rtable!=NULL)
169+
{
170+
vpm= (List**)palloc(length(parse->rtable)*sizeof(List*));
171+
memset(vpm,0,length(parse->rtable)*sizeof(List*));
172+
}
173+
PlannerVarParam=lcons(vpm,PlannerVarParam);
174+
result_plan=query_planner(parse,
175+
parse->commandType,
176+
new_tlist,
177+
(List*)parse->qual);
178+
PlannerVarParam=lnext(PlannerVarParam);
179+
if (vpm!=NULL)
180+
pfree(vpm);
145181
}
146-
182+
147183
/*
148184
* If we have a GROUP BY clause, insert a group node (with the
149185
* appropriate sort node.)
@@ -160,43 +196,96 @@ union_planner(Query *parse)
160196
*/
161197
tuplePerGroup=parse->hasAggs;
162198

199+
/* Use 'new_tlist' instead of 'tlist' */
163200
result_plan=
164-
make_groupPlan(&tlist,
201+
make_groupPlan(&new_tlist,
165202
tuplePerGroup,
166203
parse->groupClause,
167204
result_plan);
168-
169205
}
170206

171207
/*
172208
* If aggregate is present, insert the agg node
173209
*/
174210
if (parse->hasAggs)
175211
{
212+
intold_length=0,new_length=0;
213+
214+
/* Create the AGG node but use 'tlist' not 'new_tlist' as target list because we
215+
* don't want the additional attributes (only used for the havingQual, see above)
216+
* to show up in the result */
176217
result_plan= (Plan*)make_agg(tlist,result_plan);
177218

178219
/*
179220
* set the varno/attno entries to the appropriate references to
180221
* the result tuple of the subplans.
181222
*/
182223
((Agg*)result_plan)->aggs=
183-
set_agg_tlist_references((Agg*)result_plan);
224+
set_agg_tlist_references((Agg*)result_plan);
184225

185-
if(parse->havingQual!=NULL) {
186-
List*clause;
187226

188-
/* set qpqual of having clause */
189-
((Agg*)result_plan)->plan.qual=cnfify((Expr*)parse->havingQual,true);
190-
191-
foreach(clause, ((Agg*)result_plan)->plan.qual)
192-
{
193-
((Agg*)result_plan)->aggs=nconc(((Agg*)result_plan)->aggs,
194-
check_having_qual_for_aggs((Node*)lfirst(clause),
195-
((Agg*)result_plan)->plan.lefttree->targetlist));
196-
}
197-
}
198-
}
227+
if(parse->havingQual!=NULL)
228+
{
229+
List*clause;
230+
List**vpm=NULL;
231+
232+
233+
/* stuff copied from above to handle the use of attributes from outside
234+
* in subselects */
199235

236+
if (parse->rtable!=NULL)
237+
{
238+
vpm= (List**)palloc(length(parse->rtable)*sizeof(List*));
239+
memset(vpm,0,length(parse->rtable)*sizeof(List*));
240+
}
241+
PlannerVarParam=lcons(vpm,PlannerVarParam);
242+
243+
/* There is a subselect in the havingQual, so we have to process it
244+
* using the same function as for a subselect in 'where' */
245+
if (parse->hasSubLinks)
246+
{
247+
(List*)parse->havingQual=
248+
(List*)SS_process_sublinks((Node*)parse->havingQual);
249+
}
250+
251+
/* convert the havingQual to conjunctive normal form (cnf) */
252+
(List*)parse->havingQual=cnfify((Expr*)(Node*)parse->havingQual,true);
253+
254+
/* Calculate the opfids from the opnos (=select the correct functions for
255+
* the used VAR datatypes) */
256+
(List*)parse->havingQual=fix_opids((List*)parse->havingQual);
257+
258+
((Agg*)result_plan)->plan.qual=(List*)parse->havingQual;
259+
260+
/* Check every clause of the havingQual for aggregates used and append
261+
* them to result_plan->aggs */
262+
foreach(clause, ((Agg*)result_plan)->plan.qual)
263+
{
264+
/* Make sure there are aggregates in the havingQual
265+
* if so, the list must be longer after check_having_qual_for_aggs */
266+
old_length=length(((Agg*)result_plan)->aggs);
267+
268+
((Agg*)result_plan)->aggs=nconc(((Agg*)result_plan)->aggs,
269+
check_having_qual_for_aggs((Node*)lfirst(clause),
270+
((Agg*)result_plan)->plan.lefttree->targetlist,
271+
((List*)parse->groupClause)));
272+
273+
/* Have a look at the length of the returned list. If there is no
274+
* difference, no aggregates have been found and that means, that
275+
* the Qual belongs to the where clause */
276+
if (((new_length=length(((Agg*)result_plan)->aggs))==old_length)||
277+
(new_length==0))
278+
{
279+
elog(ERROR,"This could have been done in a where clause!!");
280+
return (Plan*)NIL;
281+
}
282+
}
283+
PlannerVarParam=lnext(PlannerVarParam);
284+
if (vpm!=NULL)
285+
pfree(vpm);
286+
}
287+
}
288+
200289
/*
201290
* For now, before we hand back the plan, check to see if there is a
202291
* user-specified sort that needs to be done. Eventually, this will

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp