数据库系统

开发平台：

Unix_Linux

internals.ps：源码内容

n 2343 2402 m 2262 2495 l 2289 2375 l 2316 2389 l 2343 2402 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
3693 2402 m 3612 2495 l 3639 2375 l 3566 2520 l 3620 2547 l cp clip
n 3915.0 1800.0 m 4050.0 1800.0 l
4050.0 1800.0 4185.0 1800.0 4185.0 2070.0 DrawSplineSection
4185.0 2070.0 4185.0 2340.0 3937.5 2340.0 DrawSplineSection
3937.5 2340.0 3690.0 2340.0 3645.0 2430.0 DrawSplineSection
3600.0 2520.0 l gs col-1 s gr
gr
% arrowhead
n 3693 2402 m 3612 2495 l 3639 2375 l 3666 2389 l 3693 2402 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
3712 3230 m 3616 3308 l 3664 3194 l 3567 3324 l 3615 3360 l cp clip
n 3915.0 2070.0 m 4117.5 2070.0 l
4117.5 2070.0 4320.0 2070.0 4320.0 2632.5 DrawSplineSection
4320.0 2632.5 4320.0 3195.0 4027.5 3172.5 DrawSplineSection
4027.5 3172.5 3735.0 3150.0 3667.5 3240.0 DrawSplineSection
3600.0 3330.0 l gs col-1 s gr
gr
% arrowhead
n 3712 3230 m 3616 3308 l 3664 3194 l 3688 3212 l 3712 3230 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
3693 4292 m 3612 4385 l 3639 4265 l 3566 4410 l 3620 4437 l cp clip
n 4005.0 3960.0 m 3982.5 4072.5 l
3982.5 4072.5 3960.0 4185.0 3825.0 4207.5 DrawSplineSection
3825.0 4207.5 3690.0 4230.0 3645.0 4320.0 DrawSplineSection
3600.0 4410.0 l gs col-1 s gr
gr
% arrowhead
n 3693 4292 m 3612 4385 l 3639 4265 l 3666 4279 l 3693 4292 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
2362 3230 m 2266 3308 l 2314 3194 l 2217 3324 l 2265 3360 l cp clip
n 2565.0 2070.0 m 2767.5 2070.0 l
2767.5 2070.0 2970.0 2070.0 2970.0 2632.5 DrawSplineSection
2970.0 2632.5 2970.0 3195.0 2677.5 3172.5 DrawSplineSection
2677.5 3172.5 2385.0 3150.0 2317.5 3240.0 DrawSplineSection
2250.0 3330.0 l gs col-1 s gr
gr
% arrowhead
n 2362 3230 m 2266 3308 l 2314 3194 l 2338 3212 l 2362 3230 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
3363 330 m 3483 360 l 3363 390 l 3525 390 l 3525 330 l cp clip
n 1260.0 585.0 m 1687.5 585.0 l
1687.5 585.0 2115.0 585.0 2407.5 472.5 DrawSplineSection
2407.5 472.5 2700.0 360.0 3105.0 360.0 DrawSplineSection
3510.0 360.0 l gs col-1 s gr
gr
% arrowhead
n 3363 330 m 3483 360 l 3363 390 l 3363 360 l 3363 330 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
4277 3628 m 4161 3674 l 4241 3579 l 4110 3675 l 4146 3723 l cp clip
n 3645.0 360.0 m 3645.0 585.0 l
3645.0 585.0 3645.0 810.0 4140.0 1102.5 DrawSplineSection
4140.0 1102.5 4635.0 1395.0 4635.0 2362.5 DrawSplineSection
4635.0 2362.5 4635.0 3330.0 4387.5 3510.0 DrawSplineSection
4140.0 3690.0 l gs col-1 s gr
gr
% arrowhead
n 4277 3628 m 4161 3674 l 4241 3579 l 4259 3604 l 4277 3628 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
2608 5282 m 2679 5382 l 2569 5327 l 2692 5433 l 2731 5387 l cp clip
n 1665.0 5130.0 m 2025.0 5130.0 l
2025.0 5130.0 2385.0 5130.0 2542.5 5265.0 DrawSplineSection
2700.0 5400.0 l gs col-1 s gr
gr
% arrowhead
n 2608 5282 m 2679 5382 l 2569 5327 l 2588 5304 l 2608 5282 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
1290 4353 m 1260 4473 l 1230 4353 l 1230 4515 l 1290 4515 l cp clip
n 1260.0 1125.0 m 1417.5 1125.0 l
1417.5 1125.0 1575.0 1125.0 1552.5 1935.0 DrawSplineSection
1552.5 1935.0 1530.0 2745.0 1395.0 3127.5 DrawSplineSection
1395.0 3127.5 1260.0 3510.0 1260.0 4005.0 DrawSplineSection
1260.0 4500.0 l gs col-1 s gr
gr
% arrowhead
n 1290 4353 m 1260 4473 l 1230 4353 l 1260 4353 l 1290 4353 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
2238 4695 m 2358 4725 l 2238 4755 l 2400 4755 l 2400 4695 l cp clip
n 1665.0 4860.0 m 1845.0 4860.0 l
1845.0 4860.0 2025.0 4860.0 2092.5 4792.5 DrawSplineSection
2092.5 4792.5 2160.0 4725.0 2272.5 4725.0 DrawSplineSection
2385.0 4725.0 l gs col-1 s gr
gr
% arrowhead
n 2238 4695 m 2358 4725 l 2238 4755 l 2238 4725 l 2238 4695 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
3678 5595 m 3798 5625 l 3678 5655 l 3840 5655 l 3840 5595 l cp clip
n 3105.0 5760.0 m 3285.0 5760.0 l
3285.0 5760.0 3465.0 5760.0 3532.5 5692.5 DrawSplineSection
3532.5 5692.5 3600.0 5625.0 3712.5 5625.0 DrawSplineSection
3825.0 5625.0 l gs col-1 s gr
gr
% arrowhead
n 3678 5595 m 3798 5625 l 3678 5655 l 3678 5625 l 3678 5595 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
5118 6495 m 5238 6525 l 5118 6555 l 5280 6555 l 5280 6495 l cp clip
n 4545.0 6660.0 m 4725.0 6660.0 l
4725.0 6660.0 4905.0 6660.0 4972.5 6592.5 DrawSplineSection
4972.5 6592.5 5040.0 6525.0 5152.5 6525.0 DrawSplineSection
5265.0 6525.0 l gs col-1 s gr
gr
% arrowhead
n 5118 6495 m 5238 6525 l 5118 6555 l 5118 6525 l 5118 6495 l cp gs 0.00 setgray ef gr col-1 s
% Open spline
gs clippath
4048 6182 m 4119 6282 l 4009 6227 l 4132 6333 l 4171 6287 l cp clip
n 3105.0 6030.0 m 3465.0 6030.0 l
3465.0 6030.0 3825.0 6030.0 3982.5 6165.0 DrawSplineSection
4140.0 6300.0 l gs col-1 s gr
gr
% arrowhead
n 4048 6182 m 4119 6282 l 4009 6227 l 4028 6204 l 4048 6182 l cp gs 0.00 setgray ef gr col-1 s
/Times-Roman ff 150.00 scf sf
3375 2700 m
gs 1 -1 sc (Resdom) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3150 2925 m
gs 1 -1 sc (resname: count) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1845 2925 m
gs 1 -1 sc (resname: sno) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2025 2700 m
gs 1 -1 sc (Resdom) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3240 4815 m
gs 1 -1 sc (varno: 1) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3240 5085 m
gs 1 -1 sc (varattno: 2) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3375 3510 m
gs 1 -1 sc (Aggreg) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3105 4005 m
gs 1 -1 sc (target) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3105 3735 m
gs 1 -1 sc (aggname: count) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1890 3735 m
gs 1 -1 sc (varno: 1) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1890 4005 m
gs 1 -1 sc (varattno: 1) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2070 3510 m
gs 1 -1 sc (VAR) col-1 sh gr
/Times-Roman ff 150.00 scf sf
810 1530 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
720 405 m
gs 1 -1 sc (AGG) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3915 5445 m
gs 1 -1 sc (Pointer to ) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3915 5835 m
gs 1 -1 sc (SORT node) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3915 5640 m
gs 1 -1 sc (targetlist of) col-1 sh gr
/Times-Roman ff 150.00 scf sf
405 900 m
gs 1 -1 sc (qptargetlist) col-1 sh gr
/Times-Roman ff 150.00 scf sf
405 630 m
gs 1 -1 sc (aggs) col-1 sh gr
/Times-Roman ff 150.00 scf sf
810 4905 m
gs 1 -1 sc (qptargetlist) col-1 sh gr
/Times-Roman ff 150.00 scf sf
810 5175 m
gs 1 -1 sc (lefttree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1170 5535 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2655 6435 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3690 6975 m
gs 1 -1 sc (lefttree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2250 5805 m
gs 1 -1 sc (qptargetlist) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2250 6075 m
gs 1 -1 sc (lefttree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3915 6480 m
gs 1 -1 sc (SeqScan) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4095 7290 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
405 1170 m
gs 1 -1 sc (lefttree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3690 6705 m
gs 1 -1 sc (qptargetlist) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1125 4680 m
gs 1 -1 sc (GRP) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3465 4590 m
gs 1 -1 sc (VAR) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2520 5580 m
gs 1 -1 sc (SORT) col-1 sh gr
showpage
$F2psEnd
rs
%%EndDocument
@endspecial 679 2950 a Ft(Figure)f(3.8:)16 b Fp(Plantr)n(ee)d
Ft(for)e(the)i(query)f(of)g(e)o(xample)g(3.2)p eop
%%Page: 65 65
65 64 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(65)198
234 y Fn(Executor)198 329 y Ft(The)13 b Fp(e)o(xecutor)f
Ft(uses)h(the)f(function)g Fr(execAgg())f Ft(to)h(e)o(x)o(ecute)g
Fr(AGG)g Ft(nodes.)k(As)d(described)f(earlier)f(it)198
389 y(uses)k(one)g(main)f(function)f Fr(ExecProcNode)h
Ft(which)g(is)h(called)f(recursi)o(v)o(ely)g(to)g(e)o(x)o(ecute)h
(subtrees.)198 449 y(The)e(follo)o(wing)e(steps)i(are)f(performed)f(by)
h Fr(execAgg())p Ft(:)273 552 y Fo(17)25 b Ft(The)15
b(list)g(attached)h(to)f(the)g(02eld)g Fr(aggs)f Ft(of)h(the)g
Fr(AGG)g Ft(node)g(is)h(e)o(xamined)f(and)g(for)f(e)o(v)o(ery)h
Fp(ag-)323 612 y(gr)n(e)n(gate)f(function)f Ft(included)h(the)g
Fp(tr)o(ansition)g(functions)f Ft(are)h(fetched)g(from)f(a)h
Fp(function)f(table)p Ft(.)323 671 y(Calculating)f(the)g(v)o(alue)g(of)
g(an)h Fp(aggr)n(e)n(gate)e(function)h Ft(is)h(done)f(using)g(three)g
(functions:)382 775 y Fn(226)25 b Ft(The)18 b Fp(02rst)g(tr)o
(ansition)g(function)f Fr(xfn1)g Ft(is)h(called)g(with)g(the)f(current)
g(v)o(alue)g(of)h(the)f(at-)432 835 y(trib)o(ute)c(the)h
Fp(aggr)n(e)n(gate)f(function)g Ft(is)h(applied)f(to)h(and)g(changes)g
(its)g(internal)f(state)h(using)432 895 y(the)e(attrib)o(ute')m(s)g(v)o
(alue)g(gi)o(v)o(en)g(as)h(an)g(ar)o(gument.)382 976
y Fn(226)25 b Ft(The)e Fp(second)f(tr)o(ansition)g(function)f
Fr(xfn2)h Ft(is)g(called)g(without)f(an)o(y)h(ar)o(guments)g(and)432
1036 y(changes)13 b(its)g(internal)e(state)i(only)f(according)g(to)g
(internal)g(rules.)382 1118 y Fn(226)25 b Ft(The)13
b Fp(02nal)g(function)f Fr(finalfn)g Ft(takes)h(the)g(02nal)f
(states)h(of)g Fr(xfn1)f Ft(and)h Fr(xfn2)f Ft(as)i(ar)o(gu-)432
1178 y(ments)f(and)f(02nishes)h(the)f Fp(aggr)n(e)n(gation)p
Ft(.)323 1302 y Fn(Example)g(3.3)25 b Ft(Recall)19 b(the)f(functions)g
(necessary)h(to)f(implement)g(the)g Fp(aggr)n(e)n(gate)g(function)323
1362 y Fr(avg)12 b Ft(b)o(uilding)g(the)h(a)o(v)o(erage)g(o)o(v)o(er)f
(all)h(v)o(alues)g(of)f(an)h(attrib)o(ute)f(in)g(a)h(group)f((see)h
(section)g(2.5.5)323 1422 y Fp(Extending)f(Aggr)n(e)n(gates)p
Ft():)382 1526 y Fn(226)25 b Ft(The)10 b(02rst)f(transition)g
(function)g Fr(xfn1)g Ft(has)h(to)f(be)g(a)h(function)f(that)g(takes)g
(the)h(actual)f(v)o(alue)432 1585 y(of)15 b(the)h(attrib)o(ute)e
Fr(avg)h Ft(is)h(applied)f(to)h(as)g(an)f(ar)o(gument)g(and)g(adds)h
(it)f(to)h(the)f(internally)432 1645 y(stored)d(sum)h(of)f(pre)o(vious)
g(calls.)382 1727 y Fn(226)25 b Ft(The)15 b(second)h(transition)d
(function)h Fr(xfn2)h Ft(only)f(increases)h(an)g(internal)f(counter)g
(e)o(v)o(ery)432 1787 y(time)e(it)g(is)h(called.)382
1868 y Fn(226)25 b Ft(The)10 b(02nal)g(function)f
Fr(finalfn)g Ft(di)o(vides)g(the)h(result)g(of)f Fr(xfn1)g
Ft(by)h(the)g(counter)f(of)g Fr(xfn2)432 1928 y Ft(to)j(calculate)h
(the)f(a)o(v)o(erage.)323 2053 y(Note)h(that)h Fr(xfn2)f
Ft(and)h Fr(finalfn)f Ft(may)h(be)f(absent)i((e.g.)20
b(for)13 b(the)g Fp(aggr)n(e)n(gate)g(function)g Fr(sum)323
2113 y Ft(which)f(simply)g(sums)h(up)f(all)g(v)o(alues)h(of)f(the)g(gi)
o(v)o(en)g(attrib)o(ute)g(within)f(a)i(group.)323 2232
y Fr(execAgg())25 b Ft(creates)i(an)g(array)f(containing)g(one)h
(entry)f(for)g(e)o(v)o(ery)h Fp(aggr)n(e)n(gate)f(func-)323
2292 y(tion)16 b Ft(found)h(in)g(the)g(list)g(attached)g(to)g(the)g
(02eld)g Fr(aggs)p Ft(.)30 b(The)18 b(array)e(will)h(hold)g
(information)323 2352 y(needed)25 b(for)f(the)i(e)o(x)o(ecution)f(of)g
(e)o(v)o(ery)g Fp(aggr)n(e)n(gate)g(function)g Ft((including)f(the)h
Fp(tr)o(ansition)323 2412 y(functions)12 b Ft(described)g(abo)o(v)o
(e).)273 2515 y Fo(17)25 b Ft(The)15 b(follo)o(wing)f(steps)i(are)f
(e)o(x)o(ecuted)h(in)f(a)g(loop)g(as)g(long)g(as)h(there)f(are)g(still)
g(tuples)g(returned)323 2575 y(by)e(the)h(subplan)g((i.e.)20
b(as)15 b(long)e(as)i(there)e(are)h(still)g(tuples)g(left)f(in)h(the)g
(current)f(group).)19 b(When)323 2635 y(there)12 b(are)h(no)g(tuples)g
(left)f(in)h(the)g(group)f(a)h Fr(NULL)g Ft(pointer)f(is)h(returned)f
(indicating)h(the)g(end)g(of)323 2695 y(the)f(group.)382
2798 y Fn(226)25 b Ft(A)15 b(ne)o(w)f(tuple)h(from)e(the)i(subplan)g
((i.e.)22 b(the)15 b Fp(plan)f Ft(attached)h(to)f(the)h(02eld)f
Fr(lefttree)p Ft())432 2858 y(is)e(fetched)f(by)g(recursi)o(v)o(ely)f
(calling)h Fr(ExecProcNode())f Ft(with)h(the)g(subplan)g(as)h(ar)o
(gu-)432 2918 y(ment.)382 3000 y Fn(226)25 b Ft(F)o(or)14
b(e)o(v)o(ery)g Fp(aggr)n(e)n(gate)f(function)h Ft((contained)f(in)h
(the)g(array)g(created)g(before))f(apply)h(the)432 3059
y(transition)e(functions)h Fr(xfn1)f Ft(and)h Fr(xfn2)f
Ft(to)h(the)g(v)o(alues)g(of)f(the)h(appropriate)f(attrib)o(utes)432
3119 y(of)g(the)g(current)g(tuple.)273 3223 y Fo(17)25
b Ft(When)13 b(we)g(get)g(here,)g(all)g(tuples)g(of)g(the)g(current)f
(group)g(ha)o(v)o(e)i(been)f(processed)g(and)g(the)g
Fp(tr)o(an-)323 3283 y(sition)18 b(functions)h Ft(of)g(all)g
Fp(aggr)n(e)n(gate)g(functions)f Ft(ha)o(v)o(e)i(been)f(applied)g(to)g
(the)g(v)o(alues)g(of)g(the)323 3342 y(attrib)o(utes.)c(W)l(e)d(are)g
(no)o(w)g(ready)f(to)h(complete)g(the)g Fp(aggr)n(e)n(gation)f
Ft(by)h(applying)f(the)h Fp(02nal)g(func-)323 3402
y(tion)f Ft(()p Fr(finalfn)p Ft())g(for)h(e)o(v)o(ery)g
Fp(aggr)n(e)n(gate)g(function)p Ft(.)p eop
%%Page: 66 66
66 65 bop 270 60 a Ft(66)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Ft(Store)10 b(the)i(tuple)f(containing)g(the)h(ne)o
(w)g(v)o(alues)f((the)h(results)f(of)h(the)f Fp(aggr)n(e)n(gate)g
(functions)p Ft())g(and)395 294 y(hand)h(it)g(back.)270
405 y(Note)j(that)g(the)g(procedure)f(described)i(abo)o(v)o(e)f(only)g
(returns)g(one)g(tuple)g((i.e.)24 b(it)15 b(processes)h(just)f(one)270
465 y(group)f(and)h(when)f(the)h(end)g(of)f(the)h(group)f(is)h
(detected)g(it)f(processes)i(the)f Fp(aggr)n(e)n(gate)f(functions)g
Ft(and)270 524 y(hands)21 b(back)f(one)h(tuple).)39
b(T)l(o)20 b(retrie)o(v)o(e)g(all)g(tuples)h((i.e.)40
b(to)20 b(process)h(all)f(groups))g(the)g(function)270
584 y Fr(execAgg())15 b Ft(has)i(to)f(be)g(called)h((returning)d(a)i
(ne)o(w)h(tuple)e(e)o(v)o(ery)h(time))g(until)f(it)h(returns)g(a)g
Fr(NULL)270 644 y Ft(pointer)c(indicating)f(that)h(there)g(are)h(no)f
(groups)g(left)g(to)g(process.)270 807 y Fh(3.7.2)59
b(Ho)o(w)15 b(the)g(Ha)o(ving)e(Clause)i(is)g(Implemented)270
908 y Ft(The)k(basic)g(idea)f(of)g(the)g(implementation)f(is)i(to)f
(attach)h(the)f Fp(oper)o(ator)h(tr)n(ee)g Ft(b)o(uilt)f(for)f(the)h
Fp(having)270 968 y(clause)e Ft(to)f(the)g(02eld)g
Fr(qpqual)f Ft(of)h(node)g Fr(AGG)g Ft((which)g(is)h(the)f(top)g(node)
g(of)g(the)g(query)g(tree).)23 b(No)o(w)270 1028 y(the)17
b(e)o(x)o(ecutor)g(has)h(to)f(e)o(v)o(aluate)g(the)g(ne)o(w)g
Fp(oper)o(ator)h(tr)n(ee)g Ft(attached)f(to)g Fr(qpqual)g
Ft(for)f(e)o(v)o(ery)h(group)270 1087 y(processed.)26
b(If)15 b(the)h(e)o(v)o(aluation)e(returns)h Fr(true)h
Ft(the)f(group)g(is)h(taken)f(into)g(account)h(otherwise)f(it)h(is)270
1147 y(ignored)c(and)g(the)g(ne)o(xt)h(group)e(will)h(be)h(e)o
(xamined.)270 1267 y(In)21 b(order)g(to)g(implement)g(the)g
Fp(having)g(clause)h Ft(a)f(v)o(ariety)g(of)g(changes)h(ha)o(v)o(e)g
(been)f(made)h(to)f(the)270 1326 y(follo)o(wing)11 b(stages:)345
1437 y Fo(17)25 b Ft(The)15 b Fp(parser)h(stage)f Ft(has)h(been)f
(modi02ed)g(slightly)g(to)g(b)o(uild)g(up)g(and)h(transform)e(an)h
Fp(oper)o(ator)395 1497 y(tr)n(ee)e Ft(for)e(the)h Fp(having)h(clause)p
Ft(.)345 1612 y Fo(17)25 b Ft(The)12 b Fp(r)n(e)o(write)i(system)f
Ft(has)g(been)g(adapted)f(to)g(be)h(able)f(to)h(use)f
Fp(vie)o(ws)i Ft(with)e(the)g Fp(having)g(logic)p Ft(.)345
1727 y Fo(17)25 b Ft(The)12 b Fp(planner/optimizer)g
Ft(no)o(w)g(takes)g(the)g Fp(oper)o(ator)h(tr)n(ee)g
Ft(of)f(the)g Fp(having)g(clause)g Ft(and)h(attaches)395
1787 y(it)f(to)g(the)g Fr(AGG)g Ft(node)h((which)e(is)i(the)f(top)h
(node)f(of)g(the)g Fp(queryplan)p Ft().)345 1901 y Fo(17)25
b Ft(The)13 b Fp(e)o(xecutor)g Ft(has)g(been)g(modi02ed)e(to)i(e)o(v)
o(aluate)f(the)h Fp(oper)o(ator)g(tr)n(ee)g Ft((i.e.)k(the)12
b(internal)g(repre-)395 1961 y(sentation)g(of)f(the)i
Fp(having)e(quali02cation)p Ft())g(attached)h(to)g(the)h
Fr(AGG)f Ft(node)g(and)g(the)g(results)g(of)g(the)395
2021 y Fp(aggr)n(e)n(gation)f Ft(are)h(only)g(considered)g(if)g(the)g
(e)o(v)o(aluation)g(returns)g Fr(true)p Ft(.)270 2132
y(In)g(the)g(follo)o(wing)e(sections)j(we)f(will)g(describe)g(the)g
(changes)h(made)f(to)g(e)o(v)o(ery)g(single)g(stage)h(in)f(detail.)270
2285 y Fn(The)g(Parser)i(Stage)270 2386 y Ft(The)g(grammar)f(rules)g
(of)g(the)h Fp(parser)g Ft(de02ned)g(in)f Fr(gram.y)g
Ft(did)g(not)h(require)e(an)o(y)i(changes)h((i.e.)k(the)270
2446 y(rules)14 b(had)g(already)g(been)g(prepared)g(for)f(the)h
Fp(having)g(clause)p Ft().)21 b(The)15 b Fp(oper)o(ator)f(tr)n(ee)h
Ft(b)o(uilt)f(up)g(for)f(the)270 2506 y Fp(having)f(clause)h
Ft(is)f(attached)h(to)f(the)g(02eld)g Fr(havingClause)f
Ft(of)h(the)h Fr(SelectStmt)e Ft(node)h(handed)270 2565
y(back)h(by)f(the)g Fp(parser)p Ft(.)270 2685 y(The)18
b Fp(tr)o(ansformation)g Ft(procedures)f(applied)h(to)f(the)h(tree)f
(handed)h(back)g(by)f(the)h Fp(parser)g Ft(transform)270
2745 y(the)d Fp(oper)o(ator)g(tr)n(ee)g Ft(attached)g(to)g(the)f
(02eld)h Fr(havingClause)e Ft(using)i(e)o(xactly)g(the)f(same)i
(functions)270 2804 y(used)j(for)f(the)h(transformation)e(of)i(the)g
Fp(oper)o(ator)g(tr)n(ee)h Ft(for)e(the)h Fp(wher)n(e)g(clause)p
Ft(.)36 b(This)20 b(is)f(possible)270 2864 y(because)c(both)e(trees)h
(are)g(b)o(uilt)g(up)f(by)h(the)g(same)h(grammar)d(rules)i(of)g(the)g
Fp(parser)g Ft(and)g(are)g(therefore)270 2924 y(compatible.)h
(Additional)9 b(checks)j(which)e(make)g(sure)h(that)f(the)h
Fp(having)f(clause)h Ft(in)n(v)o(olv)o(es)f(at)h(least)g(one)270
2984 y Fp(aggr)n(e)n(gate)f(function)g Ft(etc.)15 b(are)c(performed)e
(at)h(a)h(later)f(point)g(in)g(time)h(in)f(the)h Fp(planner/optimizer)f
Ft(stage.)270 3103 y(The)24 b(necessary)h(changes)f(ha)o(v)o(e)g(been)g
(applied)g(to)g(the)f(follo)o(wing)g(functions)g(included)g(in)h(the)
270 3163 y(02le)19 b Fk(:)8 b(:)g(:)p Fr
(/src/backend/parser/analyze.c)o Ft(.)33 b(Note,)21 b(that)d(only)h
(the)g(rele)o(v)o(ant)f(parts)h(of)270 3223 y(the)e(af)o(fected)f(code)
g(are)h(presented)f(instead)h(of)g(the)f(whole)h(functions.)28
b(Ev)o(ery)17 b(added)g(source)f(line)270 3283 y(will)f(be)h(marked)e
(by)h(a)h Fr('+')f Ft(at)h(the)f(be)o(ginning)g(of)g(the)g(line)h(and)f
(e)o(v)o(ery)g(changed)h(source)f(line)g(will)270 3342
y(be)f(marked)f(by)h(a)g Fr('!')19 b Ft(throughout)13
b(the)h(follo)o(wing)e(code)i(listings.)21 b(Whene)o(v)o(er)13
b(a)h(part)g(of)f(the)h(code)270 3402 y(which)e(is)h(not)f(rele)o(v)o
(ant)f(at)i(the)f(moment)g(is)h(skipped,)f(three)g(v)o(ertical)h(dots)f
(are)g(inserted)g(instead.)p eop
%%Page: 67 67
67 66 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(67)273
234 y Fo(17)25 b Fr(transformInsertStmt())323 294
y Ft(This)14 b(function)e(becomes)i(is)g(in)n(v)o(oked)e(e)o(v)o(ery)i
(time)f(a)g(SQL)h Fr(insert)f Ft(statement)g(in)n(v)o(olving)g(a)323
354 y Fr(select)e Ft(is)i(used)g(like)e(the)i(follo)o(wing)e(e)o
(xample)h(illustrates:)382 467 y Fr(insert)30 b(into)f(t2)382
527 y(select)h(x,)f(y)382 587 y(from)h(t1;)323 700 y
Ft(T)l(wo)10 b(statements)h(ha)o(v)o(e)h(been)f(added)f(to)h(this)g
(function.)j(The)e(02rst)e(one)h(performs)f(the)g(transfor)o(-)323
760 y(mation)g(of)h(the)h Fp(oper)o(ator)g(tr)n(ee)g
Ft(attached)f(to)h(the)f(02eld)g Fr(havingClause)f
Ft(using)i(the)f(function)323 819 y Fr(transformWhereClause())h
Ft(as)j(done)f(for)g(the)h Fp(wher)n(e)g(clause)p Ft(.)23
b(It)14 b(is)h(possible)g(to)g(use)323 879 y(the)c(same)h(function)f
(for)g(both)g(clauses,)j(because)e(the)o(y)g(are)f(both)h(b)o(uilt)f
(up)h(by)f(the)h(same)g Fp(gr)o(am-)323 939 y(mar)g(rules)h
Ft(gi)o(v)o(en)f(in)h Fr(gram.y)e Ft(and)i(are)f(therefore)f
(compatible.)323 1017 y(The)h(second)h(statement)f(makes)g(sure,)h
(that)f Fp(aggr)n(e)n(gate)f(functions)h Ft(are)g(in)n(v)o(olv)o(ed)g
(in)g(the)h(query)323 1077 y(whene)o(v)o(er)h(a)h Fp(having)f(clause)h
Ft(is)g(used,)i(otherwise)d(the)h(query)f(could)g(ha)o(v)o(e)i(been)e
(formulated)323 1137 y(using)e(only)g(a)h Fp(wher)n(e)g(clause)p
Ft(.)382 1250 y Fr(static)30 b(Query)f(*)382 1310 y
(transformInsertStmt(ParseState)e(*pstate,)980 1370
y(InsertStmt)i(*stmt))382 1430 y({)442 1489 y(/*)h(make)f(a)h(new)g
(query)f(tree)g(*/)442 1549 y(Query)g(*qry)h(=)g(makeNode(Query);)
1159 1609 y(.)1159 1669 y(.)1159 1729 y(.)442 1788 y(/*)g(fix)f(where)h
(clause)f(*/)442 1848 y(qry->qual)g(=)h(transformWhereClause(pstate,)
1428 1908 y(stmt->whereClause);)323 2027 y(+)89 b(/*)30
b(The)f(havingQual)g(has)h(a)f(similar)g(meaning)h(as)f("qual")g(in)323
2087 y(+)119 b(*)30 b(the)f(where)h(statement.)e(So)i(we)g(can)f
(easily)g(use)h(the)323 2147 y(+)119 b(*)30 b(code)f(from)h(the)f
("where)g(clause")g(with)h(some)f(additional)323 2207
y(+)119 b(*)30 b(traversals)f(done)g(in)h(.../optimizer/plan/planner.c)
323 2267 y(+)119 b(*/)323 2326 y(+)89 b(qry->havingQual)28
b(=)i(transformWhereClause(pstate,)323 2386 y(+)1045
b(stmt->havingClause);)1159 2446 y(.)1159 2506 y(.)1159
2565 y(.)323 2625 y(+)89 b(/*)30 b(If)f(there)h(is)f(a)h(havingQual)f
(but)g(there)h(are)f(no)323 2685 y(+)119 b(*)30 b(aggregates,)e(then)i
(there)f(is)h(something)f(wrong)g(with)323 2745 y(+)119
b(*)30 b(the)f(query)h(because)f(having)g(must)g(contain)g(aggregates)
323 2804 y(+)119 b(*)30 b(in)f(its)h(expressions!)f(Otherwise)f(the)i
(query)f(could)323 2864 y(+)119 b(*)30 b(have)f(been)h(formulated)e
(using)i(the)f(where)h(clause.)323 2924 y(+)119 b(*/)323
2984 y(+)89 b(if((qry->hasAggs)28 b(==)i(false))f(&&)323
3044 y(+)179 b((qry->havingQual)28 b(!=)i(NULL)))323
3103 y(+)89 b({)323 3163 y(+)149 b(elog(ERROR,"This)28
b(is)i(not)f(a)h(valid)f(having)g(query!");)323 3223
y(+)149 b(return)29 b((Query)g(*)NIL;)323 3283 y(+)89
b(})442 3342 y(return)29 b((Query)h(*))f(qry;)382 3402
y(})p eop
%%Page: 68 68
68 67 bop 270 60 a Ft(68)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Fr(transformSelectStmt())395 294
y Ft(Exactly)17 b(the)g(same)h(statements)f(added)g(to)g(the)g
(function)f Fr(transformInsertStmt())395 354 y Ft(abo)o(v)o(e)c(ha)o
(v)o(e)h(been)g(added)f(here)g(as)h(well.)454 555 y Fr(static)30
b(Query)f(*)454 614 y(transformSelectStmt(ParseState)e(*pstate,)1052
674 y(SelectStmt)i(*stmt))454 734 y({)514 794 y(Query)59
b(*qry)30 b(=)g(makeNode(Query);)514 913 y(qry->commandType)e(=)i
(CMD_SELECT;)1231 973 y(.)1231 1033 y(.)1231 1093 y(.)514
1152 y(qry->qual)f(=)h(transformWhereClause(pstate,)1500
1212 y(stmt->whereClause);)395 1332 y(+)89 b(/*)30 b(The)f(havingQual)
g(has)h(a)f(similar)g(meaning)h(as)f("qual")g(in)395
1391 y(+)119 b(*)30 b(the)f(where)h(statement.)e(So)i(we)g(can)f
(easily)g(use)h(the)395 1451 y(+)119 b(*)30 b(code)f(from)h(the)f
("where)g(clause")g(with)h(some)f(additional)395 1511
y(+)119 b(*)30 b(traversals)f(done)g(in)h(.../optimizer/plan/planner.c)
395 1571 y(+)119 b(*/)395 1631 y(+)89 b(qry->havingQual)28
b(=)i(transformWhereClause(pstate,)395 1690 y(+)1045
b(stmt->havingClause);)1231 1750 y(.)1231 1810 y(.)1231
1870 y(.)395 1929 y(+)89 b(/*)30 b(If)f(there)h(is)f(a)h(havingQual)f
(but)g(there)h(are)f(no)395 1989 y(+)119 b(*)30 b(aggregates,)e(then)i
(there)f(is)h(something)f(wrong)g(with)395 2049 y(+)119
b(*)30 b(the)f(query)h(because)f(having)g(must)g(contain)g(aggregates)
395 2109 y(+)119 b(*)30 b(in)f(its)h(expressions!)f(Otherwise)f(the)i
(query)f(could)395 2169 y(+)119 b(*)30 b(have)f(been)h(formulated)e
(using)i(the)f(where)h(clause.)395 2228 y(+)119 b(*/)395
2288 y(+)89 b(if((qry->hasAggs)28 b(==)i(false))f(&&)395
2348 y(+)179 b((qry->havingQual)28 b(!=)i(NULL)))395
2408 y(+)89 b({)395 2467 y(+)149 b(elog(ERROR,"This)28
b(is)i(not)f(a)h(valid)f(having)g(query!");)395 2527
y(+)149 b(return)29 b((Query)g(*)NIL;)395 2587 y(+)89
b(})514 2647 y(return)29 b((Query)h(*))f(qry;)454 2707
y(})270 2951 y Fn(The)12 b(Rewrite)h(System)270 3083
y Ft(This)19 b(section)g(describes)g(the)g(changes)g(to)g(the)f
Fp(r)n(e)o(write)i(system)g Ft(of)e(PostgreSQL)g(that)h(ha)o(v)o(e)g
(been)270 3143 y(necessary)c(to)f(support)g(the)g(use)h(of)f
Fp(vie)o(ws)h Ft(within)f(queries)g(using)g(a)h Fp(having)f(clause)g
Ft(and)g(to)h(support)270 3203 y(the)d(de02nition)g(of)g
Fp(vie)o(ws)h Ft(by)f(queries)h(using)f(a)h Fp(having)f(clause)p
Ft(.)345 3283 y(As)f(described)h(in)f(section)g(3.4.1)h
Fp(T)-5 b(ec)o(hniques)12 b(T)-5 b(o)11 b(Implement)g(V)l(ie)o(ws)i
Ft(a)e(query)g(re)o(write)f(technique)270 3342 y(is)i(used)h(to)f
(implement)f Fp(vie)o(ws)p Ft(.)17 b(There)12 b(are)g(two)f(cases)j(to)
e(be)g(handled)g(within)f(the)h Fp(r)n(e)o(write)i(system)f
Ft(as)270 3402 y(far)e(as)i(the)g Fp(having)f(clause)g
Ft(is)h(concerned:)p eop
%%Page: 69 69
69 68 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(69)273
234 y Fo(17)25 b Ft(The)10 b Fp(vie)o(w)h(de02nition)f
Ft(does)h(not)f(contain)g(a)h Fp(having)f(clause)g Ft(b)o(ut)h(the)f
(queries)g(e)o(v)o(aluated)g(against)323 294 y(this)i(vie)o(w)g(may)g
(contain)g Fp(having)g(clauses)p Ft(.)273 423 y Fo(17)25
b Ft(The)15 b Fp(vie)o(w)g(de02nition)f Ft(contains)g(a)h
Fp(having)g(clause)p Ft(.)22 b(In)14 b(this)h(case)h(queries)e(e)o(v)o
(aluated)g(against)323 483 y(this)e(vie)o(w)g(must)g(meet)h(some)f
(restrictions)g(as)h(we)g(will)f(describe)g(later)m(.)198
655 y Fn(No)h(ha)o(ving)f(clause)i(in)f(the)g(view)g(de02nition:)47
b Ft(First)13 b(we)h(will)f(look)g(at)g(the)g(changes)h(necessary)g(to)
198 715 y(support)e(queries)g(using)h(a)f Fp(having)g(clause)h
Ft(against)f(a)h Fp(vie)o(w)g Ft(de02ned)f(without)g(a)g
Fp(having)g(clause)p Ft(.)198 835 y(Let)h(the)f(follo)o(wing)f(vie)o(w)
h(de02nition)g(be)g(gi)o(v)o(en:)258 964 y Fr(create)29
b(view)g(test_view)258 1023 y(as)g(select)h(sno,)f(pno)347
1083 y(from)h(sells)347 1143 y(where)g(sno)f(>)h(2;)198
1264 y Ft(and)12 b(the)h(follo)o(wing)e(query)h(made)g(against)g
Fr(test)p 1083 1264 15 2 v 18 w(view)p Ft(:)258 1393
y Fr(select)29 b(*)258 1453 y(from)g(testview)258 1513
y(where)g(sno)h(<>)f(5;)198 1634 y Ft(The)13 b(query)f(will)g(be)g(re)o
(written)f(to:)258 1763 y Fr(select)29 b(sno,)g(pno)258
1823 y(from)g(sells)258 1883 y(where)g(sno)h(>)f(2)h(and)437
1943 y(sno)g(<>)f(5;)198 2064 y Ft(The)13 b(query)g(gi)o(v)o(en)g(in)f
(the)h(de02nition)f(of)h(the)g Fp(vie)o(w)g Fr(test)p
1221 2064 V 18 w(view)f Ft(is)h(the)g Fp(bac)o(kbone)g
Ft(of)g(the)f(re)o(written)198 2124 y(query)m(.)k(The)d
Fp(tar)n(getlist)f Ft(is)h(taken)f(from)g(the)h(user')m(s)g(query)f
(and)g(also)h(the)g Fp(wher)n(e)h(quali02cation)24
b Ft(of)12 b(the)198 2184 y(user')m(s)e(query)f(is)h(added)f(to)h(the)f
Fp(wher)n(e)i(quali02cation)d Ft(of)h(the)h(ne)o(w)f(query)g(by)h
(using)f(an)h Fr(AND)f Ft(operation.)198 2303 y(No)o(w)j(consider)g
(the)h(follo)o(wing)e(query:)258 2432 y Fr(select)29
b(sno,)g(count(pno))258 2492 y(from)g(testview)258
2552 y(where)g(sno)h(<>)f(5)258 2611 y(group)g(by)h(sno)258
2671 y(having)f(count(pno))g(>)h(1;)198 2793 y Ft(From)12
b(no)o(w)g(on)h(it)g(is)g(no)f(longer)h(suf)o(02cient)f(to)g(add)h
(just)g(the)g Fp(wher)n(e)h(clause)f Ft(and)g(the)f Fp(tar)n(getlist)h
Ft(of)f(the)198 2853 y(user')m(s)i(query)f(to)h(the)g(ne)o(w)f(query)m
(.)19 b(The)c Fp(gr)n(oup)e(clause)h Ft(and)g(the)g Fp(having)f
(quali02cation)g Ft(also)h(ha)o(v)o(e)g(to)198 2912
y(be)e(added)h(to)f(the)g(re)o(written)f(query:)258 3041
y Fr(select)29 b(sno,)g(count(pno))258 3101 y(from)g(sells)258
3161 y(where)g(sno)h(>)f(2)h(and)437 3221 y(sno)g(<>)f(5)258
3280 y(group)g(by)h(sno)258 3340 y(having)f(count(pno))g(>)h(1;)p
eop
%%Page: 70 70
70 69 bop 270 60 a Ft(70)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)270
234 y Ft(Se)o(v)o(eral)i(changes)g(that)h(ha)o(v)o(e)f(already)g(been)g
(applied)g(to)g(the)h Fp(tar)n(getlist)f Ft(and)g(the)g
Fp(wher)n(e)h(clause)g Ft(also)270 294 y(ha)o(v)o(e)c(to)g(be)g
(applied)f(to)h(the)g Fp(having)f(clause)p Ft(.)16 b(Here)10
b(is)h(a)g(collection)g(of)f(all)h Fp(additional)f Ft(steps)h(that)g
(ha)o(v)o(e)270 354 y(to)g(be)g(performed)f(in)h(order)g(to)g(re)o
(write)f(a)h(query)g(using)g(a)h Fp(having)e(clause)i
Ft(against)f(a)h(simple)f Fp(vie)o(w)h Ft((i.e.)270
413 y(a)h Fp(vie)o(w)g Ft(whose)f(de02nition)g(does)h(not)f(use)g(an)
o(y)h Fp(gr)n(oup)f Ft(and)h Fp(having)f(clauses)p Ft():)345
523 y Fo(17)25 b Ft(Re)o(write)11 b(the)i(subselects)g(contained)f
(in)h(the)f Fp(having)g(clause)h Ft(if)f(an)o(y)g(are)g(present.)345
635 y Fo(17)25 b Ft(Adapt)12 b(the)g Fr(varno)g Ft(and)g
Fr(varattno)f Ft(02elds)i(of)e(all)i Fr(VAR)f Ft(nodes)g(contained)g
(in)g(the)g Fp(oper)o(ator)395 695 y(tr)n(ee)i Ft(representing)g(the)f
Fp(having)h(clause)h Ft(in)e(the)h(same)h(way)e(as)i(it)f(has)g(been)g
(done)g(for)f(the)h(tree)395 755 y(representing)e(the)g
Fp(wher)n(e)i(clause)p Ft(.)j(The)d Fr(varno)e Ft(02elds)h(are)f
(changed)h(to)g(use)g(the)g Fp(base)g(tables)395 814
y Ft(gi)o(v)o(en)g(in)g(the)h Fp(vie)o(w)g(de02nition)e
Ft((which)h(ha)o(v)o(e)h(been)g(inserted)f(into)g(the)g
Fp(r)o(ange)h(table)f(entry)h(list)395 874 y Ft(in)h(the)h(meantime))f
(instead)i(of)e(the)h Fp(virtual)g(tables)p Ft(.)27 b(The)17
b(positions)f(of)f(the)h(attrib)o(utes)g(used)395 934
y(in)d(the)g Fp(vie)o(w)h Ft(may)g(dif)o(fer)e(from)g(the)h(positions)h
(of)f(the)g(corresponding)g(attrib)o(utes)g(in)g(the)h
Fp(base)395 994 y(tables)p Ft(.)h(That')m(s)e(why)f(the)h
Fr(varattno)e Ft(02elds)i(also)f(ha)o(v)o(e)h(to)f(be)h(adapted.)345
1106 y Fo(17)25 b Ft(Adapt)i(the)h Fr(varno)f Ft(and)h
Fr(varattno)f Ft(02elds)h(of)f(all)h Fr(VAR)f Ft(nodes)h(contained)g
(in)f(the)395 1166 y Fr(groupClause)11 b Ft(of)h(the)g(user')m(s)h
(query)f(in)g(the)g(way)g(and)g(for)g(the)g(reasons)h(described)f(abo)o
(v)o(e.)345 1278 y Fo(17)25 b Ft(Attach)19 b(the)g(tree)g
(representing)g(the)h Fp(having)f(quali02cation)f Ft((which)h(is)h
(currently)e(attached)395 1338 y(to)d(the)i(02eld)e
Fr(havingClause)g Ft(of)h(the)g Fr(Query)f Ft(node)i(for)e(the)h(user')
m(s)g(query))f(to)h(the)g(02eld)395 1398 y Fr(havingClause)11
b Ft(of)h(the)g Fr(Query)g Ft(node)g(for)g(the)g(ne)o(w)g((re)o
(written))e(query)m(.)345 1510 y Fo(17)25 b Ft(Attach)j(the)h(list)g
(representing)f(the)h Fp(gr)n(oup)g(clause)g Ft((currently)f(attached)
h(to)g(the)f(02eld)395 1570 y Fr(groupClause)10 b Ft(of)h(the)g
Fr(Query)g Ft(node)g(for)f(the)h(user')m(s)h(query))e(to)i(the)f
(02eld)g Fp(gr)n(oup)g(clause)h Ft(of)395 1630 y(the)g(node)g(for)g
(the)g(ne)o(w)g((re)o(written))e(query)m(.)270 1780
y Fn(The)15 b(view)h(de02nition)d(contains)i(a)g(ha)o(ving)f(clause:)
50 b Ft(No)o(w)15 b(we)g(will)g(look)g(at)g(the)g(problems)g(that)270
1840 y(can)e(arise)f(using)g Fp(vie)o(ws)i Ft(that)e(are)g(de02ned)g
(using)h(a)f(query)g(in)n(v)o(olving)g(a)g Fp(having)g(clause)p
Ft(.)270 1959 y(Let)h(the)f(follo)o(wing)f Fp(vie)o(w)i(de02nition)f
Ft(be)g(gi)o(v)o(en:)330 2072 y Fr(create)29 b(view)g(test_view)330
2132 y(as)g(select)h(sno,)f(count(pno))g(as)h(number)419
2191 y(from)g(sells)419 2251 y(where)g(sno)f(>)h(2)419
2311 y(group)g(by)f(sno)419 2371 y(having)h(count(pno))e(>)i(1;)270
2480 y Ft(Simple)12 b(queries)g(against)g(this)h Fp(vie)o(w)g
Ft(will)f(not)g(cause)h(an)o(y)f(troubles:)330 2592 y
Fr(select)29 b(*)330 2652 y(from)g(test_view)330 2712
y(where)g(sno)h(<>)f(5;)270 2821 y Ft(This)17 b(query)g(can)g(easily)g
(be)g(re)o(written)f(by)g(adding)h(the)g Fp(wher)n(e)g(quali02cation)
f Ft(of)h(the)f(user')m(s)i(query)270 2881 y(()p Fr(sno)29
b Fk(<>)h Fr(5)p Ft())11 b(to)i(the)f Fp(wher)n(e)h(quali02cation)f
Ft(of)g(the)g Fp(vie)o(w)h(de02nition')n(s)24 b Ft(query)m(.)270
3000 y(The)f(ne)o(xt)g(query)f(is)h(also)g(simple)g(b)o(ut)f(it)g(will)
h(cause)g(troubles)f(when)h(it)f(is)h(e)o(v)o(aluated)f(against)270
3060 y(the)12 b(abo)o(v)o(e)h(gi)o(v)o(en)f Fp(vie)o(w)h(de02nition)p
Ft(:)330 3173 y Fr(select)29 b(*)330 3232 y(from)g(test_view)330
3292 y(where)g(number)g(>)h(1;)g(/*)f(count(pno))g(in)h(the)f(view)h
(def.)898 3352 y(*)f(is)h(called)59 b(number)29 b(here)h(*/)p
eop
%%Page: 71 71
71 70 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(71)198
234 y(The)13 b(currently)e(implemented)h(techniques)g(for)g(query)g(re)
o(writing)e(will)i(re)o(write)g(the)g(query)g(to:)258
368 y Fr(select)29 b(*)258 428 y(from)g(sells)258 487
y(where)g(sno)h(>)f(2)h(and)437 547 y(count(pno))f(>)h(1)258
607 y(group)f(by)h(sno)258 667 y(having)f(count(pno))g(>)h(1;)198
792 y Ft(which)12 b(is)h(an)f(in)n(v)o(alid)g(query)g(because)h(an)f
Fp(aggr)n(e)n(gate)g(function)g Ft(appears)g(in)h(the)f
Fp(wher)n(e)h(clause)p Ft(.)198 912 y(Also)g(the)f(ne)o(xt)g(query)g
(will)g(cause)h(troubles:)258 1045 y Fr(select)29 b(pno,)g
(count(sno))258 1105 y(from)g(test_view)258 1165 y(group)g(by)h(pno;)
198 1290 y Ft(As)16 b(you)f(can)h(see)g(this)g(query)f(does)h(neither)f
(in)n(v)o(olv)o(e)g(a)h Fp(wher)n(e)g(clause)g Ft(nor)f(a)h
Fp(having)f(clause)h Ft(b)o(ut)f(it)198 1350 y(contains)i(a)g
Fp(gr)n(oup)g(clause)g Ft(which)g(groups)g(by)g(the)g(attrib)o(ute)f
Fr(pno)p Ft(.)29 b(The)18 b(query)e(in)h(the)g(de02nition)198
1410 y(of)d(the)g Fp(vie)o(w)h Ft(also)g(contains)f(a)h
Fp(gr)n(oup)f(clause)h Ft(that)f(groups)g(by)g(the)h(attrib)o(ute)e
Fr(sno)p Ft(.)22 b(The)15 b(two)e Fp(gr)n(oup)198 1469
y(clauses)19 b Ft(are)f(in)h(con03ict)e(with)i(each)f(other)g(and)g
(therefore)g(the)g(query)g(cannot)g(be)h(re)o(written)e(to)h(a)198
1529 y(form)11 b(that)h(would)g(make)g(sense.)198 1649
y Fn(Note:)38 b Ft(There)24 b(is)g(no)g(solution)g(to)g(the)g(abo)o(v)o
(e)g(mentioned)g(problems)f(at)h(the)g(moment)f(and)h(it)198
1709 y(does)12 b(not)f(make)g(sense)h(to)f(put)g(much)h(ef)o(fort)d
(into)i(that)h(because)g(the)f(implementation)f(of)h(the)h(support)198
1768 y(for)g(queries)g(like:)258 1902 y Fr(select)29
b(pno_count,)g(count(sno))258 1962 y(from)g(()h(select)f(sno,)h
(count(pno))e(as)i(pno_count)467 2022 y(from)f(sells)467
2081 y(where)g(sno)h(>)g(2)467 2141 y(group)f(by)h(sno)467
2201 y(having)f(count(pno))g(>)h(1))258 2261 y(group)f(by)h
(pno_count;)198 2386 y Ft((which)12 b(is)g(part)g(of)g(the)h(SQL92)f
(standard))g(will)g(automatically)f(also)i(solv)o(e)g(these)g
(problems.)198 2506 y(In)26 b(the)h(ne)o(xt)g(part)f(of)h(the)g
(current)f(section)h(we)g(will)f(present)h(the)g(changes)g(applied)f
(to)h(the)198 2565 y(source)15 b(code)f(in)h(order)f(to)g(realize)h
(the)f(abo)o(v)o(e)h(described)g(items.)22 b(Note)15
b(that)f(it)h(is)g(not)f(necessary)h(to)198 2625 y(understand)e(the)h
(meaning)g(of)f(e)o(v)o(ery)h(single)g(source)f(line)h(here)g(and)f
(therefore)g(we)h(will)g(not)f(discuss)198 2685 y(detailed)j(questions)
g(like)g(224Why)g(has)g(the)g(v)o(ariable)g Fr(varno)f
Ft(to)h(be)g(increased)h(by)f(3?224.)27 b(Questions)198
2745 y(like)13 b(that)h(belong)f(to)h(a)g(chapter)f(dealing)g(with)h
(the)g(implementation)e(of)h Fp(vie)o(ws)i Ft(in)f(PostgreSQL)f(and)198
2804 y(to)k(be)g(able)g(to)g(answer)g(them)g(it)g(would)f(be)h
(necessary)h(to)f(kno)o(w)f(all)h(the)g(functions)g(and)g(not)f(only)
198 2864 y(those)g(described)g(here.)25 b(The)17 b(fact)e(important)f
(for)h(us)h(is)g(to)g(make)f(sure,)i(that)f(whate)o(v)o(er)f(is)h
(applied)198 2924 y(to)d(the)g Fp(tar)n(getlist)g Ft(and)g(the)g(data)h
(structures)f(representing)f(the)h Fp(wher)n(e)i(clause)e
Ft(is)h(also)f(applied)g(to)g(the)198 2984 y(data)f(structures)h(for)e
(the)h Fp(having)g(clause)p Ft(.)17 b(There)12 b(are)h(three)f(02les)
g(af)o(fected:)273 3103 y Fk(:)c(:)g(:)p Fr
(/src/backend/rewrite/rewriteHa)o(ndler.c)273 3163 y
Fk(:)g(:)g(:)p Fr(/src/backend/rewrite/rewriteMa)o(nip.c)273
3223 y Fk(:)g(:)g(:)p Fr(/src/backend/commands/view.c)198
3342 y Ft(Here)29 b(is)h(a)g(description)f(of)g(the)g(changes)h(made)g
(to)f(the)g(functions)g(contained)h(in)f(the)g(02le)198
3402 y Fk(:)8 b(:)g(:)p Fr(/src/backend/rewrite/rewriteHandl)o(er.c)p
Ft(:)p eop
%%Page: 72 72
72 71 bop 270 60 a Ft(72)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Fr(ApplyRetrieveRule())395 294 y
Ft(This)g(function)g(becomes)g(in)n(v)o(oked)f(whene)o(v)o(er)h(a)g
Fr(select)g Ft(statement)g(against)g(a)g Fp(vie)o(w)395
354 y Ft(is)f(recognized)g(and)g(applies)g(the)g Fp(r)n(e)o(write)h
(rule)g Ft(stored)f(in)f(the)h Fp(system)h(catalogs)p
Ft(.)51 b(The)395 413 y(additional)23 b(source)h(lines)g(gi)o(v)o(en)f
(in)h(the)g(listing)f(belo)o(w)h(make)f(sure)h(that)g(the)g(functions)
395 473 y Fr(OffsetVarNodes())12 b Ft(and)j Fr(ChangeVarNodes())d
Ft(that)i(are)h(in)n(v)o(oked)e(for)h(the)g Fp(wher)n(e)395
533 y(clause)g Ft(and)h(the)f Fp(tar)n(getlist)g Ft(of)g(the)g(query)g
(gi)o(v)o(en)g(in)h(the)f Fp(vie)o(w)h(de02nition)f
Ft(are)g(also)h(called)f(for)395 593 y(the)i Fp(having)h(clause)g
Ft(and)g(the)g Fp(gr)n(oup)g(clause)g Ft(of)f(the)h(query)f(in)h(the)g
Fp(vie)o(w)g(de02nition)p Ft(.)29 b(These)395 653 y(functions)11
b(adapt)i(the)f Fr(varno)g Ft(and)g Fr(varattno)g Ft(02elds)g(of)g
(the)h Fr(VAR)f Ft(nodes)g(in)n(v)o(olv)o(ed.)395 730
y(The)g(additional)g(source)g(lines)g(at)g(the)g(end)g(of)g
Fr(ApplyRetrieveRule())e Ft(attach)i(the)g(data)395
790 y(structures)k(representing)g(the)h Fp(having)f(clause)i
Ft(and)e(the)h Fp(gr)n(oup)g(clause)g Ft(of)f(the)h(query)f(in)h(the)
395 850 y Fp(vie)o(w)h(de02nition)g Ft(to)g(the)g(re)o(written)e
Fp(parsetr)n(ee)p Ft(.)34 b(As)19 b(mentioned)e(earlier)n(,)j(a)e
Fp(vie)o(w)h(de02nition)395 910 y Ft(in)n(v)o(olving)8
b(a)i Fp(gr)n(oup)g(clause)g Ft(will)g(cause)g(troubles)f(whene)o(v)o
(er)h(a)g(query)f(using)h(a)g(dif)o(ferent)e Fp(gr)n(oup)395
969 y(clause)i Ft(against)f(this)h Fp(vie)o(w)h Ft(is)f(e)o(x)o
(ecuted.)16 b(There)10 b(is)g(no)f(mechanism)h(pre)o(v)o(enting)f
(these)i(troubles)395 1029 y(included)h(at)g(the)g(moment.)395
1107 y(Note)30 b(that)g(the)g(functions)g Fr(OffsetVarNodes())e
Ft(,)36 b Fr(ChangeVarNodes())28 b Ft(and)395 1167
y Fr(AddHavingQual())14 b Ft(appearing)i(in)g Fr
(ApplyRetrieveRule())e Ft(are)i(described)h(at)f(a)395
1227 y(later)c(point)f(in)i(time.)454 1337 y Fr(static)30
b(void)454 1397 y(ApplyRetrieveRule(Query)e(*parsetree,)g(RewriteRule)
h(*rule,)992 1456 y(int)h(rt_index,)f(int)g(relation_level,)992
1516 y(Relation)g(relation,)g(int)h(*modified))454 1576
y({)514 1636 y(Query)59 b(*rule_action)29 b(=)h(NULL;)514
1696 y(Node)89 b(*rule_qual;)514 1755 y(List)g(*rtable,)1231
1815 y(.)1231 1875 y(.)1231 1935 y(.)514 1994 y(OffsetVarNodes((Node)
28 b(*))h(rule_action->targetList,)962 2054 y(rt_length);)514
2114 y(OffsetVarNodes(rule_qual,)e(rt_length);)395
2233 y(+)89 b(OffsetVarNodes((Node)28 b(*))h
(rule_action->groupClause,)395 2293 y(+)537 b(rt_length);)395
2353 y(+)89 b(OffsetVarNodes((Node)28 b(*))h
(rule_action->havingQual,)395 2413 y(+)537 b(rt_length);)1231
2473 y(.)1231 2532 y(.)1231 2592 y(.)514 2652 y
(ChangeVarNodes(rule_qual,)962 2712 y(PRS2_CURRENT_VARNO)28
b(+)i(rt_length,)962 2771 y(rt_index,)f(0);)395 2891
y(+)89 b(ChangeVarNodes((Node)28 b(*))h(rule_action->groupClause,)
395 2951 y(+)537 b(PRS2_CURRENT_VARNO)28 b(+)i(rt_length,)395
3011 y(+)537 b(rt_index,)29 b(0);)395 3070 y(+)89 b
(ChangeVarNodes((Node)28 b(*))h(rule_action->havingQual,)395
3130 y(+)537 b(PRS2_CURRENT_VARNO)28 b(+)i(rt_length,)395
3190 y(+)537 b(rt_index,)29 b(0);)1231 3250 y(.)1231
3309 y(.)1231 3369 y(.)p eop
%%Page: 73 73
73 72 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(73)442
234 y Fr(if)30 b((*modified)f(&&)g(!badsql))442 294
y({)502 354 y(AddQual(parsetree,)f(rule_action->qual);)323
413 y(+)149 b(/*)30 b(This)f(will)g(only)h(work)f(if)h(the)f(query)h
(made)f(to)h(the)323 473 y(+)179 b(*)30 b(view)f(defined)g(by)h(the)f
(following)g(groupClause)323 533 y(+)179 b(*)30 b(groups)f(by)g(the)h
(same)f(attributes)g(or)h(does)f(not)h(use)323 593 y(+)179
b(*)30 b(groups)f(at)g(all!)323 653 y(+)179 b(*/)323
712 y(+)g(if)29 b((parsetree->groupClause)f(==)h(NULL))323
772 y(+)268 b(parsetree->groupClause)28 b(=)323 832 y(+)597
b(rule_action->groupClause;)323 892 y(+)179 b
(AddHavingQual(parsetree,)323 951 y(+)597 b
(rule_action->havingQual);)323 1011 y(+)179 b(parsetree->hasAggs)28
b(=)323 1071 y(+)268 b((rule_action->hasAggs)28 b(||)i
(parsetree->hasAggs);)323 1131 y(+)179 b(parsetree->hasSubLinks)27
b(=)323 1191 y(+)268 b((rule_action->hasSubLinks)27
b(||)323 1250 y(+)298 b(parsetree->hasSubLinks);)442
1310 y(})382 1370 y(})273 1529 y Fo(17)25 b Fr
(QueryRewriteSubLink())323 1588 y Ft(This)17 b(function)e(is)i
(called)g(by)g Fr(QueryRewrite())e Ft(to)h(process)h(possibly)g
(contained)f(sub-)323 1648 y(queries)d(02rst.)k(It)c(searches)h(for)e
(nested)i(queries)f(by)g(recursi)o(v)o(ely)f(tracing)h(through)f(the)h
Fp(parse-)323 1708 y(tr)n(ee)h Ft(gi)o(v)o(en)g(as)g(ar)o(gument.)20
b(The)15 b(additional)e(statement)h(makes)g(sure)g(that)f(the)h
Fp(having)g(clause)323 1768 y Ft(is)e(also)h(e)o(xamined.)382
1911 y Fr(static)30 b(void)382 1970 y(QueryRewriteSubLink(Node)e
(*node))382 2030 y({)442 2090 y(if)i((node)f(==)h(NULL))532
2150 y(return;)442 2209 y(switch)f((nodeTag(node)))442
2269 y({)502 2329 y(case)g(T_SubLink:)502 2389 y({)1159
2449 y(.)1159 2508 y(.)1159 2568 y(.)592 2628 y
(QueryRewriteSubLink((Node)e(*))i(query->qual);)323
2688 y(+)239 b(QueryRewriteSubLink((Node)27 b(*))323
2747 y(+)836 b(query->havingQual);)1159 2807 y(.)1159
2867 y(.)1159 2927 y(.)502 2987 y(})1159 3046 y(.)1159
3106 y(.)1159 3166 y(.)442 3226 y(})442 3285 y(return;)382
3345 y(})p eop
%%Page: 74 74
74 73 bop 270 60 a Ft(74)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Fr(QueryRewrite())395 294 y Ft(This)c(function)g
(takes)g(the)g Fp(parsetr)n(ee)h Ft(of)f(a)h(query)f(and)g(re)o(writes)
f(it)i(using)f(PostgreSQL)-5 b(')m(s)395 354 y Fp(r)n(e)o(write)27
b(system)p Ft(.)58 b(Before)26 b(the)g(query)g(itself)g(can)h(be)f(re)o
(written,)j(subqueries)d(that)g(are)395 413 y(possibly)33
b(part)f(of)g(the)h(query)f(ha)o(v)o(e)i(to)e(be)h(processed.)78
b(Therefore)32 b(the)h(function)395 473 y Fr(QueryRewriteSubLink())17
b Ft(is)k(called)f(for)g(the)g Fp(wher)n(e)h(clause)g
Ft(and)f(for)f(the)h Fp(having)395 533 y(clause)p Ft(.)454
648 y Fr(List)30 b(*)454 708 y(QueryRewrite(Query)e(*parsetree))454
768 y({)514 828 y(QueryRewriteSubLink(parsetree->q)o(ual);)395
887 y(+)89 b(QueryRewriteSubLink(parsetree->h)o(avingQual)o();)514
947 y(return)29 b(QueryRewriteOne(parsetree);)454 1007
y(})270 1122 y Ft(Here)c(we)h(present)f(the)g(changes)h(applied)f(to)g
(the)h(functions)e(that)i(are)f(contained)g(in)g(the)g(02le)270
1182 y Fk(:)8 b(:)g(:)p Fr(/src/backend/rewrite/rewriteManip)o(.c)p
Ft(:)345 1278 y Fo(17)25 b Fr(OffsetVarNodes())395
1338 y Ft(Recursi)o(v)o(ely)15 b(steps)h(through)f(the)g
Fp(parsetr)n(ee)i Ft(gi)o(v)o(en)e(as)h(the)g(02rst)f(ar)o(gument)g
(and)g(increments)395 1398 y(the)h Fr(varno)f Ft(and)h
Fr(varnoold)f Ft(02elds)h(of)g(e)o(v)o(ery)g Fr(VAR)f
Ft(node)h(found)g(by)g(the)g Fp(of)o(fset)g Ft(gi)o(v)o(en)g(as)395
1457 y(the)f(second)h(ar)o(gument.)24 b(The)16 b(additional)f
(statements)g(are)h(necessary)g(to)f(be)g(able)h(to)f(handle)395
1517 y Fr(GroupClause)e Ft(nodes)h(and)g Fr(Sublink)g
Ft(nodes)h(that)f(may)g(appear)g(in)g(the)g Fp(parsetr)n(ee)h
Ft(from)395 1577 y(no)o(w)d(on.)454 1692 y Fr(void)454
1752 y(OffsetVarNodes(Node)28 b(*node,)h(int)h(offset))454
1812 y({)544 1871 y(if)g((node)f(==)h(NULL))634 1931
y(return;)544 1991 y(switch)f((nodeTag(node)))544
2051 y({)1231 2111 y(.)1231 2170 y(.)1231 2230 y(.)395
2290 y(+)209 b(/*)29 b(This)h(has)f(to)h(be)g(done)f(to)h(make)f
(queries)g(using)395 2350 y(+)239 b(*)29 b(groupclauses)g(work)g(on)h
(views)395 2409 y(+)239 b(*/)395 2469 y(+)g(case)29 b(T_GroupClause:)
395 2529 y(+)239 b({)395 2589 y(+)298 b(GroupClause)29
b(*group)g(=)h((GroupClause)e(*))i(node;)395 2649 y(+)395
2708 y(+)298 b(OffsetVarNodes((Node)28 b(*)(group->entry),)395
2768 y(+)747 b(offset);)395 2828 y(+)239 b(})395 2888
y(+)g(break;)1231 2947 y(.)1231 3007 y(.)1231 3067 y(.)395
3127 y(+)g(case)29 b(T_SubLink:)395 3187 y(+)239 b({)395
3246 y(+)298 b(SubLink)29 b(*sublink)g(=)h((SubLink)f(*))h(node;)395
3306 y(+)298 b(List)30 b(*tmp_oper,)f(*tmp_lefthand;)395
3366 y(+)p eop
%%Page: 75 75
75 74 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(75)323
234 y Fr(+)298 b(/*)30 b(We)g(also)f(have)h(to)f(adapt)g(the)h
(variables)f(used)323 294 y(+)328 b(*)30 b(in)g(sublink->lefthand)e
(and)h(sublink->oper)323 354 y(+)328 b(*/)323 413 y(+)298
b(OffsetVarNodes((Node)28 b(*)(sublink->lefthand),)323
473 y(+)747 b(offset);)323 533 y(+)323 593 y(+)298 b(/*)30
b(Make)f(sure)h(the)f(first)h(argument)f(of)323 653 y(+)328
b(*)30 b(sublink->oper)e(points)i(to)f(the)h(same)f(var)h(as)323
712 y(+)328 b(*)30 b(sublink->lefthand)e(does)h(otherwise)g(we)h(will)
323 772 y(+)328 b(*)30 b(run)g(into)f(troubles)g(using)g(aggregates)g
((aggno)323 832 y(+)328 b(*)30 b(will)f(not)h(be)g(set)f(correctly))
323 892 y(+)328 b(*/)323 951 y(+)298 b(tmp_lefthand)29
b(=)h(sublink->lefthand;)323 1011 y(+)298 b(foreach(tmp_oper,)28
b(sublink->oper))323 1071 y(+)298 b({)323 1131 y(+)358
b(lfirst(((Expr)29 b(*)lfirst(tmp_oper))->args))d(=)323
1191 y(+)1016 b(lfirst(tmp_lefthand);)323 1250 y(+)358
b(tmp_lefthand)29 b(=)g(lnext(tmp_lefthand);)323 1310
y(+)298 b(})323 1370 y(+)239 b(})323 1430 y(+)g(break;)1159
1489 y(.)1159 1549 y(.)1159 1609 y(.)472 1669 y(})382
1729 y(})273 1838 y Fo(17)25 b Fr(ChangeVarNodes())323
1898 y Ft(This)d(function)f(is)h(similar)f(to)h(the)g(abo)o(v)o(e)g
(described)g(function)f Fr(OffsetVarNodes())323 1958
y Ft(b)o(ut)e(instead)h(of)f(incrementing)g(the)h(02elds)g
Fr(varno)f Ft(and)h Fr(varnoold)f Ft(of)g Fp(all)g Fr(VAR)h
Ft(nodes)323 2017 y(found,)j(it)e(processes)h(only)f(those)g
Fr(VAR)g Ft(nodes)h(whose)f Fr(varno)g Ft(v)o(alue)g(matches)g(the)g
(pa-)323 2077 y(rameter)13 b Fr(old)p 582 2077 15 2 v
18 w(varno)h Ft(gi)o(v)o(en)g(as)h(ar)o(gument)f(and)g(whose)h
Fr(varlevelsup)f Ft(v)o(alue)g(matches)323 2137 y(the)i(parameter)g
Fr(sublevels)p 889 2137 V 17 w(up)p Ft(.)29 b(Whene)o(v)o(er)17
b(such)g(a)g(node)g(is)g(found,)h(the)f Fr(varno)f Ft(and)323
2197 y Fr(varnoold)10 b Ft(02elds)h(are)g(set)g(to)g(the)g(v)o(alue)g
(gi)o(v)o(en)g(in)g(the)g(parameter)f Fr(new)p 1615 2197
V 18 w(varno)p Ft(.)15 b(The)d(addi-)323 2257 y(tional)g(statements)i
(are)f(necessary)h(to)f(be)h(able)f(to)g(handle)g Fr(GroupClause)f
Ft(and)i Fr(Sublink)323 2316 y Ft(nodes.)382 2419 y Fr(void)382
2479 y(ChangeVarNodes(Node)28 b(*node,)h(int)h(old_varno,)831
2539 y(int)f(new_varno,)g(int)g(sublevels_up))382 2599
y({)442 2658 y(if)h((node)f(==)h(NULL))532 2718 y(return;)442
2778 y(switch)f((nodeTag(node)))442 2838 y({)1159
2897 y(.)1159 2957 y(.)1159 3017 y(.)323 3077 y(+)149
b(/*)30 b(This)f(has)h(to)f(be)h(done)f(to)h(make)f(queries)g(using)323
3137 y(+)179 b(*)30 b(groupclauses)e(work)i(on)f(views)g(*/)323
3196 y(+)149 b(case)29 b(T_GroupClause:)323 3256 y(+)149
b({)323 3316 y(+)209 b(GroupClause)58 b(*group)29 b(=)h((GroupClause)f
(*))g(node;)323 3376 y(+)p eop
%%Page: 76 76
76 75 bop 270 60 a Ft(76)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)395
234 y Fr(+)209 b(ChangeVarNodes((Node)27 b(*)(group->entry),)395
294 y(+)657 b(old_varno,)29 b(new_varno,)395 354 y(+)657
b(sublevels_up);)395 413 y(+)149 b(})395 473 y(+)g(break;)1231
533 y(.)1231 593 y(.)1231 653 y(.)574 712 y(case)29 b(T_Var:)574
772 y({)1231 832 y(.)1231 892 y(.)1231 951 y(.)634 1011
y(/*)g(This)h(is)f(a)h(hack:)g(Whenever)e(an)i(attribute)664
1071 y(*)f(from)h(the)f("outside")g(query)g(is)h(used)g(within)664
1131 y(*)f(a)h(nested)f(subquery,)g(the)h(varlevelsup)e(will)664
1191 y(*)h(be)h(>0.)g(Nodes)f(having)g(varlevelsup)g(>)g(0)h(are)664
1250 y(*)f(forgotten)g(to)h(be)g(processed.)e(The)i(call)f(to)664
1310 y(*)g(OffsetVarNodes())f(should)i(really)f(be)g(done)h(at)664
1370 y(*)f(another)g(place)h(but)f(this)h(hack)f(makes)g(sure)664
1430 y(*)g(that)h(also)f(those)h(VAR)f(nodes)g(are)h(processed.)664
1489 y(*/)395 1549 y(+)209 b(if)29 b((var->varlevelsup)f(>)i(0))395
1609 y(+)298 b(OffsetVarNodes((Node)28 b(*)var,3);)574
1669 y(})574 1729 y(break;)1231 1788 y(.)1231 1848 y(.)1231
1908 y(.)574 1968 y(case)h(T_SubLink:)574 2027 y({)1231
2087 y(.)1231 2147 y(.)1231 2207 y(.)395 2267 y(+)209
b(ChangeVarNodes((Node)27 b(*))j(query->havingQual,)395
2326 y(+)657 b(old_varno,)29 b(new_varno,)395 2386 y(+)657
b(sublevels_up);)395 2446 y(+)209 b(ChangeVarNodes((Node)27
b(*))j(query->targetList,)395 2506 y(+)657 b(old_varno,)29
b(new_varno,)395 2565 y(+)657 b(sublevels_up);)395 2625
y(+)395 2685 y(+)209 b(/*)29 b(We)h(also)f(have)h(to)g(adapt)f(the)g
(variables)g(used)h(in)395 2745 y(+)239 b(*)29 b(sublink->lefthand)f
(and)i(sublink->oper)395 2804 y(+)239 b(*/)395 2864 y(+)209
b(ChangeVarNodes((Node)27 b(*))j((sublink->lefthand),)395
2924 y(+)657 b(old_varno,)29 b(new_varno,)395 2984 y(+)657
b(sublevels_up);)574 3044 y(})574 3103 y(break;)1231
3163 y(.)1231 3223 y(.)1231 3283 y(.)514 3342 y(})454
3402 y(})p eop
%%Page: 77 77
77 76 bop 198 60 a Fm(3.7.)26 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)625 b Ft(77)273
234 y Fo(17)25 b Fr(AddHavingQual())323 294 y Ft(This)17
b(function)f(adds)h(the)g Fp(oper)o(ator)g(tr)n(ee)h
Ft(gi)o(v)o(en)e(by)h(the)g(parameter)f Fr(havingQual)g
Ft(to)g(the)323 354 y(one)k(attached)g(to)g(the)g(02eld)g
Fr(havingQual)f Ft(of)h(the)g Fp(parsetr)n(ee)i Ft(gi)o(v)o(en)e(by)g
(the)g(parameter)323 413 y Fr(parsetree)p Ft(.)28 b(This)17
b(is)g(done)g(by)g(adding)f(a)h(ne)o(w)g Fr(AND)g Ft(node)f(and)h
(attaching)g(the)g(old)f(and)323 473 y(the)d(ne)o(w)h
Fp(oper)o(ator)h(tr)n(ee)f Ft(as)h(ar)o(guments)f(to)f(it.)21
b Fr(AddHavingQual())12 b Ft(has)i(not)g(been)g(e)o(xist-)323
533 y(ing)e(until)f(v6.3.2.)17 b(It)12 b(has)h(been)f(created)g(for)g
(the)g Fp(having)g(logic)p Ft(.)382 667 y Fr(void)382
727 y(AddHavingQual(Query)28 b(*parsetree,)h(Node)g(*havingQual))382
787 y({)442 847 y(Node)59 b(*copy,)30 b(*old;)442 966
y(if)g((havingQual)e(==)i(NULL))532 1026 y(return;)442
1146 y(copy)g(=)f(havingQual;)442 1265 y(old)h(=)f
(parsetree->havingQual;)442 1325 y(if)h((old)f(==)h(NULL))562
1385 y(parsetree->havingQual)d(=)j(copy;)442 1445 y(else)562
1504 y(parsetree->havingQual)d(=)681 1564 y((Node)j(*))f
(make_andclause()1010 1624 y(makeList(parsetree->havingQual,)1279
1684 y(copy,)g(-1));)382 1743 y(})273 1878 y Fo(17)c
Fr(AddNotHavingQual())323 1938 y Ft(This)17 b(function)f(is)i
(similar)e(to)h(the)g(abo)o(v)o(e)h(described)f(function)f
Fr(AddHavingQual())p Ft(.)29 b(It)323 1998 y(also)14
b(adds)g(the)f Fp(oper)o(ator)i(tr)n(ee)f Ft(gi)o(v)o(en)g(by)f(the)h
(parameter)f Fr(havingQual)f Ft(b)o(ut)i(pre02x)o(es)g(it)f(by)323
2057 y(a)h Fr(NOT)g Ft(node.)22 b Fr(AddNotHavingQual())13
b Ft(has)i(also)g(not)f(been)h(e)o(xisting)f(until)g(v6.3.2)h(and)323
2117 y(has)d(been)h(created)f(for)g(the)g Fp(having)g(logic)p
Ft(.)382 2252 y Fr(void)382 2311 y(AddNotHavingQual(Query)28
b(*parsetree,)890 2371 y(Node)i(*havingQual))382 2431
y({)442 2491 y(Node)g(*copy;)442 2610 y(if)g((havingQual)e(==)i
(NULL))532 2670 y(return;)442 2790 y(copy)g(=)f((Node)h(*))f
(make_notclause((Expr)f(*)havingQual);)442 2849 y
(AddHavingQual(parsetree,)f(copy);)323 2909 y(})273
3044 y Fo(17)e Fr(nodeHandleViewRule())323 3103 y
Ft(This)20 b(function)f(is)h(called)g(by)g Fr(HandleViewRule())p
Ft(.)36 b(It)20 b(replaces)g(all)g Fr(VAR)f Ft(nodes)i(of)323
3163 y(the)16 b Fp(user)i(query)f Ft(e)o(v)o(aluated)f(against)g(the)h
Fp(vie)o(w)g Ft((the)f(02elds)h(of)f(these)h Fr(VAR)g
Ft(nodes)f(represent)323 3223 y(the)g(positions)h(of)f(the)g(attrib)o
(utes)g(in)h(the)f Fp(virtual)h Ft(table))f(by)g Fr(VAR)g
Ft(nodes)h(that)f(ha)o(v)o(e)h(already)323 3283 y(been)c(prepared)g(to)
h(represent)f(the)h(positions)g(of)f(the)h(corresponding)f(attrib)o
(utes)g(in)g(the)h Fp(phys-)323 3342 y(ical)h Ft(tables)h((gi)o(v)o
(en)f(in)h(the)g Fp(vie)o(w)g(de02nition)p Ft().)25
b(The)16 b(additional)f(statements)h(make)f(sure)h(that)323
3402 y Fr(GroupClause)11 b Ft(nodes)h(and)h Fr(Sublink)e
Ft(nodes)i(are)f(handled)h(correctly)m(.)p eop
%%Page: 78 78
78 77 bop 270 60 a Ft(78)85 b Fm(CHAPTER)14 b(3.)25 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)454
234 y Fr(static)30 b(void)454 294 y(nodeHandleViewRule(Node)e
(**nodePtr,)g(List)i(*rtable,)1022 354 y(List)g(*targetlist,)e(int)i
(rt_index,)1022 413 y(int)g(*modified,)e(int)i(sublevels_up))454
473 y({)514 533 y(Node)g(*node)f(=)h(*nodePtr;)514 593
y(if)g((node)f(==)h(NULL))604 653 y(return;)514 712
y(switch)f((nodeTag(node)))514 772 y({)1231 832 y(.)1231
892 y(.)1231 951 y(.)395 1011 y(+)149 b(/*)30 b(This)f(has)h(to)f(be)h
(done)f(to)h(make)f(queries)g(using)395 1071 y(+)179
b(*)30 b(groupclauses)e(work)i(on)f(views)395 1131 y(+)179
b(*/)395 1191 y(+)149 b(case)29 b(T_GroupClause:)395
1250 y(+)149 b({)395 1310 y(+)209 b(GroupClause)58 b(*group)29
b(=)h((GroupClause)f(*))g(node;)395 1370 y(+)209 b
(nodeHandleViewRule((Node)27 b(**))i((&(group->entry)),)395
1430 y(+)777 b(rtable,)29 b(targetlist,)f(rt_index,)395
1489 y(+)777 b(modified,)28 b(sublevels_up);)395 1549
y(+)149 b(})395 1609 y(+)g(break;)1231 1669 y(.)1231
1729 y(.)1231 1788 y(.)574 1848 y(case)29 b(T_Var:)574
1908 y({)1231 1968 y(.)1231 2027 y(.)1231 2087 y(.)634
2147 y(if)g((n)h(==)g(NULL))634 2207 y({)693 2267 y(*nodePtr)f(=)h
(make_null(((Var)e(*)node)->vartype);)634 2326
y(})634 2386 y(else)395 2446 y(+)209 b(/*)29 b(This)h(is)f(a)h(hack:)g
(The)f(varlevelsup)g(of)g(the)395 2506 y(+)239 b(*)29
b(original)g(variable)g(and)h(the)f(new)h(one)f(should)395
2565 y(+)239 b(*)29 b(be)h(the)g(same.)f(Normally)g(we)g(adapt)h(the)f
(node)395 2625 y(+)239 b(*)29 b(by)h(changing)f(a)h(pointer)f(to)g
(point)h(to)f(a)h(var)395 2685 y(+)239 b(*)29 b(contained)g(in)h
('targetlist'.)e(In)i(the)395 2745 y(+)239 b(*)29 b(targetlist)g(all)h
(varlevelsups)e(are)i(0)f(so)h(if)395 2804 y(+)239 b(*)29
b(we)h(want)f(to)h(change)f(it)h(to)g(the)f(original)395
2864 y(+)239 b(*)29 b(value)h(we)f(have)h(to)f(copy)h(the)f(node)h
(before!)395 2924 y(+)239 b(*)29 b((Maybe)h(this)f(will)g(cause)h
(troubles)f(with)g(some)395 2984 y(+)239 b(*)29 b(sophisticated)g
(queries)g(on)g(views?))395 3044 y(+)239 b(*/)395 3103
y(+)209 b({)395 3163 y(+)268 b(if(this_varlevelsup>0))395
3223 y(+)g({)395 3283 y(+)358 b(*nodePtr)29 b(=)h(copyObject(n);)395
3342 y(+)268 b(})395 3402 y(+)g(else)p eop
%%Page: 79 79
79 78 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(79)323
234 y Fr(+)268 b({)323 294 y(+)328 b(*nodePtr)29 b(=)h(n;)323
354 y(+)268 b(})323 413 y(+)g(((Var)30 b(*)*nodePtr)->varlevelsup)d
(=)323 473 y(+)836 b(this_varlevelsup;)323 533 y(+)209
b(})562 593 y(*modified)29 b(=)g(TRUE;)502 653 y(})502
712 y(break;)1159 772 y(.)1159 832 y(.)1159 892 y(.)502
951 y(case)g(T_SubLink:)502 1011 y({)1159 1071 y(.)1159
1131 y(.)1159 1191 y(.)323 1250 y(+)209 b(nodeHandleViewRule()323
1310 y(+)508 b((Node)29 b(**))g(&(query->havingQual),)323
1370 y(+)508 b(rtable,)29 b(targetlist,)f(rt_index,)323
1430 y(+)508 b(modified,)28 b(sublevels_up);)323 1489
y(+)209 b(nodeHandleViewRule()323 1549 y(+)508 b((Node)29
b(**))g(&(query->targetList),)323 1609 y(+)508 b(rtable,)29
b(targetlist,)f(rt_index,)323 1669 y(+)508 b(modified,)28
b(sublevels_up);)323 1729 y(+)209 b(/*)29 b(We)h(also)f(have)h(to)g
(adapt)f(the)g(variables)g(used)323 1788 y(+)239 b(*)29
b(in)h(sublink->lefthand)e(and)h(sublink->oper)323 1848
y(+)239 b(*/)323 1908 y(+)209 b(nodeHandleViewRule()323
1968 y(+)508 b((Node)29 b(**))g(&(sublink->lefthand),)323
2027 y(+)508 b(rtable,)29 b(targetlist,)f(rt_index,)323
2087 y(+)508 b(modified,)28 b(sublevels_up);)323 2147
y(+)209 b(/*)29 b(Make)h(sure)f(the)h(first)f(argument)g(of)323
2207 y(+)239 b(*)29 b(sublink->oper)g(points)g(to)h(the)f(same)h(var)f
(as)323 2267 y(+)239 b(*)29 b(sublink->lefthand)f(does)i(otherwise)f
(we)g(will)323 2326 y(+)239 b(*)29 b(run)h(into)f(troubles)g(using)h
(aggregates)323 2386 y(+)239 b(*)29 b((aggno)h(will)f(not)h(be)f(set)h
(correctly!))323 2446 y(+)239 b(*/)323 2506 y(+)209
b(pfree(lfirst(((Expr)27 b(*))323 2565 y(+)597 b
(lfirst(sublink->oper))->args));)323 2625 y(+)209
b(tmp_lefthand)28 b(=)i(sublink->lefthand;)323 2685 y(+)209
b(foreach(tmp_oper,)28 b(sublink->oper))323 2745 y(+)209
b({)323 2804 y(+)298 b(lfirst(((Expr)29 b(*))g
(lfirst(tmp_oper))->args))f(=)323 2864 y(+)1016 b
(lfirst(tmp_lefthand);)323 2924 y(+)298 b(tmp_lefthand)29
b(=)h(lnext(tmp_lefthand);)323 2984 y(+)209 b(})502
3044 y(})502 3103 y(break;)1159 3163 y(.)1159 3223 y(.)1159
3283 y(.)442 3342 y(})382 3402 y(})p eop
%%Page: 80 80
80 79 bop 270 60 a Ft(80)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Fr(HandleViewRule())395 294 y Ft(This)11
b(function)g(calls)g Fr(nodeHandleViewRule())f Ft(for)g(the)h
Fp(wher)n(e)h(clause)p Ft(,)h(the)e Fp(tar)n(getlist)p
Ft(,)395 354 y(the)h Fp(gr)n(oup)g(clause)h Ft(and)f(the)h
Fp(having)f(clause)g Ft(of)g(the)h Fp(user)g(query)g
Ft(e)o(v)o(aluated)f(against)g(the)g(gi)o(v)o(en)395
413 y Fp(vie)o(w)p Ft(.)454 537 y Fr(void)454 597 y
(HandleViewRule(Query)28 b(*parsetree,)h(List)g(*rtable,)903
656 y(List)g(*targetlist,)g(int)g(rt_index,)903 716 y(int)g
(*modified))454 776 y({)1231 836 y(.)1231 895 y(.)1231
955 y(.)395 1015 y(+)89 b(/*)30 b(The)f(variables)g(in)h(the)f
(havingQual)g(and)395 1075 y(+)119 b(*)30 b(groupClause)e(also)i(have)f
(to)h(be)g(adapted)395 1135 y(+)119 b(*/)395 1194 y(+)89
b(nodeHandleViewRule(&parsetree->h)o(avingQual)o(,)27
b(rtable,)395 1254 y(+)657 b(targetlist,)29 b(rt_index,)395
1314 y(+)657 b(modified,)29 b(0);)395 1374 y(+)89 b
(nodeHandleViewRule()395 1433 y(+)328 b((Node)30 b
(**)(&(parsetree->groupClaus)o(e)),)395 1493 y(+)328
b(rtable,)29 b(targetlist,)g(rt_index,)g(modified,)g(0);)454
1553 y(})270 1676 y Ft(The)13 b(follo)o(wing)e(function)g(is)i
(contained)f(in)g Fk(:)c(:)g(:)p Fr(/src/backend/commands/view.c)p
Ft(:)345 1790 y Fo(17)25 b Fr(UpdateRangeTableOfViewParse()o())395
1849 y Ft(This)f(function)f(updates)h(the)g Fp(r)o(ange)g(table)f
Ft(of)g(the)h Fp(parsetr)n(ee)h Ft(gi)o(v)o(en)e(by)h(the)g(parameter)
395 1909 y Fr(viewParse)p Ft(.)14 b(The)e(additional)e(statement)h
(makes)g(sure)g(that)g(the)g Fr(VAR)g Ft(nodes)g(of)f(the)h
Fp(having)395 1969 y(clause)h Ft(are)h(modi02ed)e(in)h(the)h(same)f
(way)g(as)h(the)g Fr(VAR)f Ft(nodes)g(of)g(the)h Fp(wher)n(e)g(clause)g
Ft(are.)454 2092 y Fr(static)30 b(void)454 2152 y
(UpdateRangeTableOfViewParse(char)d(*viewName,)1291
2212 y(Query)i(*viewParse))454 2272 y({)1231 2331 y(.)1231
2391 y(.)1231 2451 y(.)514 2511 y(OffsetVarNodes(viewParse->qual,)d
(2);)395 2630 y(+)89 b(OffsetVarNodes(viewParse->having)o(Qual,)27
b(2);)1231 2690 y(.)1231 2750 y(.)1231 2810 y(.)454
2869 y(})270 3007 y Fn(Planner/Optimizer)270 3102 y Ft(The)18
b Fp(planner)g Ft(b)o(uilds)f(a)h Fp(queryplan)g Ft(like)e(the)i(one)f
(sho)o(wn)h(in)f(02gure)g(3.8)h(and)g(in)f(addition)g(to)g(that)270
3162 y(it)f(takes)g(the)g Fp(oper)o(ator)h(tr)n(ee)g
Ft(attached)f(to)g(the)g(02eld)g Fr(havingClause)f
Ft(of)g(the)h Fr(Query)g Ft(node)g(and)270 3222 y(attaches)d(is)g(to)f
(the)g Fr(qpqual)g Ft(02eld)g(of)g(the)g Fr(AGG)g Ft(node.)345
3283 y(Unfortunately)18 b(this)i(is)g(not)f(the)g(only)h(thing)f(to)g
(do.)38 b(Remember)19 b(from)g(section)h(3.7.1)g Fp(How)270
3342 y(Aggr)n(e)n(gate)11 b(Functions)g(ar)n(e)g(Implemented)f
Ft(that)g(the)h Fp(tar)n(getlist)f Ft(is)h(searched)g(for)f
Fp(aggr)n(e)n(gate)f(functions)270 3402 y Ft(which)k(are)g(appended)g
(to)g(a)g(list)g(that)g(will)g(be)g(attached)g(to)g(the)g(02eld)g
Fr(aggs)g Ft(of)g(the)g Fr(AGG)f Ft(node.)18 b(This)p
eop
%%Page: 81 81
81 80 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(81)198
234 y(was)14 b(suf)o(02cient)f(as)i(long)e(as)i Fp(aggr)n(e)n(gate)e
(functions)h Ft(ha)o(v)o(e)g(only)g(been)g(allo)o(wed)f(to)h(appear)g
(within)f(the)198 294 y Fp(tar)n(getlist)p Ft(.)24 b(No)o(w)16
b(the)f Fp(having)g(clause)h Ft(is)f(another)g(source)h(of)f
Fp(aggr)n(e)n(gate)f(functions)p Ft(.)25 b(Consider)15
b(the)198 354 y(follo)o(wing)c(e)o(xample:)258 459 y
Fr(select)29 b(sno,)g(max(pno))258 519 y(from)g(sells)258
579 y(group)g(by)h(sno)258 638 y(having)f(count(pno))g(>)h(1;)198
742 y Ft(Here)14 b(the)g Fp(aggr)n(e)n(gate)f(functions)h
Fr(max)g Ft(and)g Fr(count)g Ft(are)g(in)g(use.)21 b(If)14
b(only)f(the)h Fp(tar)n(getlist)g Ft(is)h(scanned)198
802 y((as)f(it)g(was)g(the)f(case)i(before)e(the)h Fp(having)g(clause)
g Ft(had)g(been)g(implemented))f(we)h(will)f(only)h(02nd)f(and)198
862 y(process)18 b(the)g Fp(aggr)n(e)n(gate)g(function)f
Fr(max)p Ft(.)32 b(The)19 b(second)f(function)f Fr(count)h
Ft(is)g(not)g(processed)g(and)198 922 y(therefore)f(an)o(y)i(reference)
f(to)g(the)g(result)g(of)g Fr(count)g Ft(from)g(within)f(the)i
Fp(having)f(clause)h Ft(will)f(fail.)198 981 y(The)g(solution)g(to)f
(this)h(problem)f(is)h(to)g(scan)g(the)g(whole)g Fp(oper)o(ator)g(tr)n
(ee)h Ft(representing)e(the)g Fp(having)198 1041 y(clause)12
b Ft(for)g Fp(aggr)n(e)n(gate)f(functions)h Ft(not)f(contained)h(in)g
(the)g Fp(tar)n(getlist)g Ft(yet)g(and)g(add)g(them)g(to)f(the)h(list)h
(of)198 1101 y Fp(aggr)n(e)n(gate)g(functions)g Ft(attached)h(to)f(the)
h(02eld)f Fr(aggs)g Ft(of)h(the)f Fr(AGG)g Ft(node.)20
b(The)14 b(scanning)g(is)g(done)f(by)198 1161 y(the)18
b(function)f Fr(check)p 614 1161 15 2 v 18 w(having)p
812 1161 V 17 w(qual)p 949 1161 V 17 w(for)p 1056 1161
V 18 w(aggs())h Ft(which)g(steps)g(recursi)o(v)o(ely)g(through)f(the)
198 1220 y(tree.)198 1340 y(While)25 b(scanning)h(the)g
Fp(having)f(clause)h Ft(for)f Fp(aggr)n(e)n(gate)g(functions)g
Ft(not)h(contained)f(in)h(the)f Fp(tar)o(-)198 1400 y(getlist)18
b Ft(yet,)j(an)e(additional)f(check)h(is)g(made)g(to)g(make)f(sure)h
(that)f Fp(aggr)n(e)n(gate)g(functions)h Ft(are)g(used)198
1460 y(within)12 b(the)g Fp(having)g(clause)g Ft((otherwise)g(the)g
(query)g(could)g(ha)o(v)o(e)g(been)h(formulated)e(using)h(the)g
Fp(wher)n(e)198 1519 y(clause)p Ft().)k(Consider)c(the)g(follo)o(wing)
f(query)h(which)g(is)h(not)f(a)h(v)o(alid)f(SQL92)g(query:)258
1625 y Fr(testdb=>)29 b(select)g(sno,)g(max(pno))258
1684 y(testdb->)g(from)g(sells)258 1744 y(testdb->)g(group)g(by)h(sno)
258 1804 y(testdb->)f(having)g(sno)h(>)f(1;)258 1864
y(ERROR:)59 b(This)29 b(could)h(have)f(been)h(done)f(in)h(a)f(where)h
(clause!!)258 1924 y(testdb=>)198 2027 y Ft(There)19
b(is)f(no)g(need)h(to)f(e)o(xpress)h(this)f(query)g(using)h(a)f
Fp(having)g(clause)p Ft(,)j(this)d(kind)g(of)g(quali02cation)198
2087 y(belongs)12 b(to)h(the)f Fp(wher)n(e)h(clause)p
Ft(:)258 2192 y Fr(select)29 b(sno,)g(max(pno))258
2252 y(from)g(sells)258 2312 y(where)g(sno)h(>)f(1)258
2372 y(group)g(by)h(sno;)198 2476 y Ft(There)17 b(is)g(still)f(an)g
(unsolv)o(ed)h(problem)f(left.)27 b(Consider)17 b(the)f(follo)o(wing)f
(query)h(where)h(we)f(want)g(to)198 2535 y(kno)o(w)c(just)g(the)h
(supplier)f(numbers)g(()p Fr(sno)p Ft())f(of)h(all)g(suppliers)g
(selling)h(more)f(than)g(one)g(part:)258 2641 y Fr(select)29
b(sno)258 2701 y(from)g(sells)258 2760 y(group)g(by)h(sno)258
2820 y(having)f(count(pno))g(>)h(1;)198 2924 y Ft(The)14
b Fp(planner)f Ft(creates)g(a)h Fp(queryplan)f Ft((like)f(the)h(one)g
(sho)o(wn)g(in)g(02gure)f(3.8))h(where)g(the)g Fp(tar)n(getlists)h
Ft(of)198 2984 y(all)e(nodes)f(in)n(v)o(olv)o(ed)h(contain)f(only)g
(entries)h(of)f(those)h(attrib)o(utes)f(listed)h(after)f(the)g
Fr(select)g Ft(ke)o(yword)198 3044 y(of)18 b(the)h(query)m(.)35
b(Looking)19 b(at)f(the)h(e)o(xample)g(abo)o(v)o(e)h(this)f(means)g
(that)f(the)h Fp(tar)n(getlists)g Ft(of)g(the)f Fr(AGG)198
3103 y Ft(node,)c(the)f Fr(GRP)f Ft(node)h(the)g Fr(SORT)g
Ft(node)g(and)g(the)g Fr(SeqScan)f Ft(node)h(contain)g(only)g(the)f
(entry)h(for)f(the)198 3163 y(attrib)o(ute)d Fr(sno)p
Ft(.)15 b(As)c(described)f(earlier)g(the)g Fp(aggr)n(e)n(gation)f
(logic)h Ft(operates)g(on)g(attrib)o(utes)g(of)g(the)g(tuples)198
3223 y(returned)h(by)g(the)h(subplan)g(of)f(the)h Fr(AGG)f
Ft(node)h((i.e.)j(the)d(result)g(of)f(the)g Fr(GRP)h
Ft(node).)j(Which)d(attrib)o(utes)198 3283 y(are)17
b(contained)f(in)g(the)h(tuples)g(handed)f(back)h(by)g(a)g(subplan)f
(is)h(determined)f(by)h(the)f Fp(tar)n(getlist)p Ft(.)29
b(In)198 3342 y(the)13 b(case)h(of)e(our)h(e)o(xample)g(the)f(attrib)o
(ute)g Fr(pno)h Ft(needed)g(for)f(the)h Fp(aggr)n(e)n(gate)f(function)h
Fr(count)f Ft(is)h(not)198 3402 y(included)f(and)g(therefore)g(the)g
Fp(aggr)n(e)n(gation)f Ft(will)h(fail.)p eop
%%Page: 82 82
82 81 bop 270 60 a Ft(82)85 b Fm(CHAPTER)14 b(3.)25 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)270
234 y Ft(The)h(solution)f(to)g(this)h(problem)e(is)i(gi)o(v)o(en)f(in)g
(the)g(follo)o(wing)f(steps:)345 334 y Fo(17)25 b Ft(Make)12
b(a)g(copy)g(of)g(the)h(actual)f Fp(tar)n(getlist)g Ft(of)g(the)g
Fr(AGG)g Ft(node.)345 433 y Fo(17)25 b Ft(Search)17
b(the)h Fp(oper)o(ator)h(tr)n(ee)g Ft(representing)e(the)h
Fp(having)g(clause)h Ft(for)e(attrib)o(utes)g(that)h(are)g(not)395
493 y(contained)12 b(in)h(the)g Fp(tar)n(getlist)f Ft(of)h(the)g
Fr(AGG)f Ft(node)h(yet)g(and)g(add)g(them)f(to)h(the)g(pre)o(viously)f
(made)395 553 y(copy)m(.)345 653 y Fo(17)25 b Ft(The)12
b(e)o(xtended)f Fp(tar)n(getlist)g Ft(is)h(used)g(to)f(create)h(the)f
(subplan)h(attached)f(to)g(the)h Fr(lefttree)e Ft(02eld)395
712 y(of)18 b(the)h Fr(AGG)f Ft(node.)35 b(That)19 b(means)g(that)g
(the)f Fp(tar)n(getlists)h Ft(of)g(the)f Fr(GRP)h Ft(node,)h(of)f(the)f
Fr(SORT)395 772 y Ft(node)g(and)g(of)f(the)h Fr(SeqScan)g
Ft(node)g(will)f(no)o(w)h(contain)g(an)g(entry)f(for)h(the)g(attrib)o
(ute)f Fr(pno)p Ft(.)395 832 y(The)c Fp(tar)n(getlist)g
Ft(of)g(the)g Fr(AGG)g Ft(node)g(itself)g(will)g(not)g(be)h(changed)f
(because)h(we)f(do)h(not)f(want)f(to)395 892 y(include)g(the)g(attrib)o
(ute)f Fr(pno)i Ft(into)f(the)g(result)g(returned)f(by)i(the)f(whole)g
(query)m(.)270 991 y(Care)17 b(has)g(to)f(be)h(taken)f(that)h(the)f
Fr(varattno)g Ft(02elds)h(of)f(the)h Fr(VAR)f Ft(nodes)h(used)g(in)f
(the)h Fp(tar)n(getlists)270 1051 y Ft(contain)e(the)h(position)f(of)g
(the)h(corresponding)e(attrib)o(ute)h(in)g(the)h Fp(tar)n(getlist)f
Ft(of)g(the)h(subplan)f((i.e)h(the)270 1111 y(subplan)c(deli)o(v)o
(ering)g(the)g(tuples)h(for)e(further)g(processing)i(by)f(the)g(actual)
h(node).)270 1230 y(The)23 b(follo)o(wing)e(part)h(deals)g(with)g(the)
h(source)f(code)h(of)f(the)g(ne)o(w)g(and)g(changed)h(functions)f(in-)
270 1290 y(v)o(olv)o(ed)12 b(in)h(the)f(planner/optimizer)e(stage.)17
b(The)c(02les)f(af)o(fected)g(are:)345 1410 y Fk(:)c(:)g(:)p
Fr(/src/backend/optimizer/plan/se)o(trefs.c)345 1469
y Fk(:)g(:)g(:)p Fr(/src/backend/optimizer/plan/pl)o(anner.c)270
1589 y Ft(Since)25 b(all)f(of)h(the)g(functions)f(presented)h(here)g
(are)f(v)o(ery)h(long)g(and)g(would)f(need)h(v)o(ery)f(much)270
1649 y(space)13 b(if)f(presented)g(as)h(a)g(whole,)f(we)h(just)f(list)h
(the)f(most)g(important)g(parts.)270 1768 y(The)23 b(follo)o(wing)e
(two)g(functions)h(are)g(ne)o(w)g(and)g(ha)o(v)o(e)h(been)f(introduced)
g(for)f(the)h Fp(having)g(logic)p Ft(.)270 1828 y(The)o(y)13
b(are)f(contained)g(in)h(the)f(02le)g Fk(:)c(:)g(:)p
Fr(/src/backend/optimizer/plan/setref)o(s.c)p Ft(:)345
1928 y Fo(17)25 b Fr(check)p 548 1928 15 2 v 17 w(having)p
745 1928 V 17 w(qual)p 882 1928 V 18 w(for)p 990 1928
V 17 w(aggs())395 1988 y Ft(This)19 b(function)g(takes)g(the)g
(representation)f(of)h(a)g Fp(having)g(clause)h Ft(gi)o(v)o(en)f(by)g
(the)g(parameter)395 2047 y Fr(clause)p Ft(,)14 b(a)h
Fp(tar)n(getlist)f Ft(gi)o(v)o(en)h(by)f(the)h(parameter)e
Fr(subplanTargetList)g Ft(and)i(a)f Fp(gr)n(oup)395 2107
y(clause)j Ft(gi)o(v)o(en)h(by)f(the)g(parameter)g Fr(groupClause)f
Ft(as)j(ar)o(guments)e(and)g(scans)i(the)e(repre-)395
2167 y(sentation)e(of)g(the)h Fp(having)g(clause)g Ft(recursi)o(v)o
(ely)f(for)g Fp(aggr)n(e)n(gate)g(functions)p Ft(.)25
b(If)15 b(an)h Fp(aggr)n(e)n(gate)395 2227 y(function)i
Ft(is)h(found)f(it)g(is)h(attached)g(to)g(a)g(list)f((internally)g
(called)g Fr(agg)p 1682 2227 V 18 w(list)p Ft())g(and)h(02nally)395
2286 y(returned)11 b(by)h(the)h(function.)395 2366 y(Additionally)g
(the)i Fr(varno)f Ft(02eld)g(of)g(e)o(v)o(ery)h Fr(VAR)f
Ft(node)h(found)f(is)h(set)g(to)f(the)h(position)f(of)g(the)395
2426 y(corresponding)d(attrib)o(ute)g(in)i(the)f Fp(tar)n(getlist)g
Ft(gi)o(v)o(en)g(by)g Fr(subplanTargetList)p Ft(.)395
2506 y(If)17 b(the)h Fp(having)f(clause)h Ft(contains)g(a)g(subquery)g
(the)g(function)f(also)h(makes)g(sure,)i(that)d(e)o(v)o(ery)395
2565 y(attrib)o(ute)f(from)f(the)i Fp(main)g(query)g
Ft(that)g(is)g(used)g(within)g(the)f(subquery)h(also)g(appears)g(in)g
(the)395 2625 y Fp(gr)n(oup)11 b(clause)i Ft(gi)o(v)o(en)e(by)h
Fr(groupClause)p Ft(.)j(If)c(the)h(attrib)o(ute)f(cannot)h(be)g(found)f
(in)h(the)g Fp(gr)n(oup)395 2685 y(clause)g Ft(an)h(error)e(message)i
(is)g(printed)e(to)i(the)f(screen)h(and)f(the)g(query)g(processing)h
(is)f(aborted.)454 2804 y Fr(List)30 b(*)454 2864 y
(check_having_qual_for_aggs(Node)d(*clause,)1261 2924
y(List)j(*subplanTargetList,)1261 2984 y(List)g(*groupClause))454
3044 y({)514 3103 y(List)g(*t,)f(*l1;)514 3163 y(List)h(*agg_list)e(=)i
(NIL;)514 3223 y(int)60 b(contained_in_group_clause)27
b(=)i(0;)514 3342 y(if)h((IsA(clause,)e(Var)))514
3402 y({)p eop
%%Page: 83 83
83 82 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(83)502
234 y Fr(TargetEntry)29 b(*subplanVar;)502 354 y(subplanVar)g(=)g
(match_varid((Var)f(*))i(clause,)1249 413 y(subplanTargetList);)502
473 y(/*)g(Change)f(the)g(varattno)g(fields)g(of)h(the)532
533 y(*)g(var)f(node)h(to)f(point)h(to)f(the)h(resdom->resnofields)532
593 y(*)g(of)f(the)h(subplan)f((lefttree))532 653 y(*/)502
712 y(((Var)g(*))h(clause)->varattno)e(=)562 772
y(subplanVar->resdom->resno;)502 832 y(return)h(NIL;)442
892 y(})442 951 y(else)502 1011 y(if)h((is_funcclause(clause))d(||)j
(not_clause(clause))621 1071 y(||)g(or_clause(clause))e(||)i
(and_clause(clause)))502 1131 y({)562 1191 y(int)f(new_length=0,)g
(old_length=0;)562 1310 y(/*)g(This)h(is)f(a)h(function.)f(Recursively)
g(call)g(this)592 1370 y(*)g(routine)g(for)h(its)g(arguments...)e
((i.e.)h(for)h(AND,)592 1430 y(*)f(OR,)h(...)f(clauses!))592
1489 y(*/)562 1549 y(foreach(t,)f(((Expr)i(*))f(clause)->args))
562 1609 y({)621 1669 y(old_length=length((List)f(*)agg_list);)621
1729 y(agg_list)h(=)h(nconc(agg_list,)741 1788 y
(check_having_qual_for_aggs(lfir)o(st(t),)1309 1848
y(subplanTargetList,)1309 1908 y(groupClause));)621
1968 y(if(((new_length=length((List)d(*)agg_list)))i(==)741
2027 y(old_length))g(||)g((new_length)g(==)h(0)))621
2087 y({)681 2147 y(elog(ERROR,"This)e(could)i(have)f(been)g(done)1309
2207 y(in)g(a)h(where)g(clause!!");)681 2267 y(return)f(NIL;)621
2326 y(})562 2386 y(})562 2446 y(return)g(agg_list;)502
2506 y(})502 2565 y(else)562 2625 y(if)g((IsA(clause,)g(Aggreg)))
562 2685 y({)621 2745 y(return)h(lcons(clause,)1040
2804 y(check_having_qual_for_aggs()1159 2864 y(((Aggreg)f
(*)clause)->target,)1159 2924 y(subplanTargetList,)1159
2984 y(groupClause));)562 3044 y(})562 3103 y(else)1159
3163 y(.)1159 3223 y(.)1159 3283 y(.)382 3342 y(})p eop
%%Page: 84 84
84 83 bop 270 60 a Ft(84)85 b Fm(CHAPTER)14 b(3.)25 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)345
234 y Fo(17)25 b Fr(check)p 548 234 15 2 v 17 w(having)p
745 234 V 17 w(qual)p 882 234 V 18 w(for)p 990 234 V
17 w(vars())395 294 y Ft(This)19 b(function)g(takes)g(the)g
(representation)f(of)h(a)g Fp(having)g(clause)h Ft(gi)o(v)o(en)f(by)g
(the)g(parameter)395 354 y Fr(clause)11 b Ft(and)h(the)g(actual)h
Fp(tar)n(getlist)e Ft(gi)o(v)o(en)h(by)h(the)f(parameter)f
Fr(targetlist)p 1835 354 V 17 w(so)p 1912 354 V 18 w(far)h
Ft(as)395 413 y(ar)o(guments)e(and)h(recursi)o(v)o(ely)f(scans)i(the)e
(representation)g(of)h(the)f Fp(having)h(clause)g Ft(for)f(attrib)o
(utes)395 473 y(that)i(are)g(not)h(included)f(in)g(the)h(actual)f
Fp(tar)n(getlist)g Ft(yet.)k(Whene)o(v)o(er)d(such)g(an)f(attrib)o(ute)
g(is)h(found)395 533 y(it)f(is)g(added)h(to)f(the)g(actual)h
Fp(tar)n(getlist)f Ft(which)g(is)h(02nally)e(returned)h(by)g(the)g
(function.)395 611 y(Attrib)o(utes)20 b(contained)h(in)g(the)g
Fp(having)g(clause)g Ft(b)o(ut)g(not)g(in)g(the)g Fp(tar)n(getlist)f
Ft(sho)o(w)h(up)g(with)395 671 y(queries)12 b(like:)454
785 y Fr(select)30 b(sid)454 844 y(from)60 b(part)454
904 y(group)30 b(by)f(sid)454 964 y(having)h(min(pid))f(>)g(1;)395
1077 y Ft(In)9 b(the)g(abo)o(v)o(e)h(query)f(the)g(attrib)o(ute)g
Fr(pid)g Ft(is)h(used)f(in)h(the)f Fp(having)g(clause)h
Ft(b)o(ut)f(it)g(does)h(not)f(appear)395 1137 y(in)j(the)h
Fp(tar)n(getlist)f Ft((i.e.)17 b(the)c(list)f(of)h(attrib)o(utes)f
(after)g(the)g(ke)o(yword)g Fr(select)p Ft().)k(Unfortunately)395
1197 y(only)f(those)g(attrib)o(utes)g(are)g(deli)o(v)o(ered)g(by)g(the)
h(subplan)f(and)h(can)f(therefore)f(be)i(used)g(within)395
1257 y(the)e Fp(having)g(clause)p Ft(.)22 b(T)l(o)15
b(become)f(able)h(to)f(handle)g(queries)h(like)e(that)i(correctly)m(,)f
(we)g(ha)o(v)o(e)h(to)395 1316 y(e)o(xtend)c(the)h(actual)g
Fp(tar)n(getlist)f Ft(by)g(those)h(attrib)o(utes)g(used)g(in)f(the)h
Fr(having)29 b(clause)11 b Ft(b)o(ut)g(not)395 1376 y(already)h
(appearing)f(in)i(the)f Fp(tar)n(getlist)p Ft(.)454 1489
y Fr(List)30 b(*)454 1549 y(check_having_qual_for_vars(Node)d
(*clause,)1261 1609 y(List)j(*targetlist_so_far))454
1669 y({)514 1729 y(List)149 b(*t;)514 1848 y(if)30 b((IsA(clause,)e
(Var)))514 1908 y({)574 1968 y(Rel)269 b(tmp_rel;)574
2087 y(tmp_rel.targetlist)28 b(=)i(targetlist_so_far;)574
2147 y(/*)g(Check)f(if)h(the)f(VAR)h(is)f(already)g(contained)g(in)h
(the)604 2207 y(*)g(targetlist)604 2267 y(*/)574 2326
y(if)g((tlist_member((Var)d(*)clause,)933 2386 y((List)i
(*)targetlist_so_far))e(==)j(NULL))574 2446 y({)634
2506 y(add_tl_element(&tmp_rel,)d((Var)i(*)clause);)574
2565 y(})574 2625 y(return)g(tmp_rel.targetlist;)514
2685 y(})514 2745 y(else)574 2804 y(if)h((is_funcclause(clause))d
(||)j(not_clause(clause))693 2864 y(||)g(or_clause(clause))e(||)i
(and_clause(clause)))574 2924 y({)634 2984 y(/*)f(This)h(is)f(a)h
(function.)f(Recursively)g(call)g(this)664 3044 y(*)g(routine)g(for)h
(its)g(arguments...)664 3103 y(*/)634 3163 y(foreach(t,)e(((Expr)i
(*))f(clause)->args))634 3223 y({)693 3283 y(targetlist_so_far)f(=)
753 3342 y(check_having_qual_for_vars(lfirst)o((t),)1231
3402 y(targetlist_so_far);)p eop
%%Page: 85 85
85 84 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(85)562
234 y Fr(})562 294 y(return)29 b(targetlist_so_far;)502
354 y(})502 413 y(else)562 473 y(if)g((IsA(clause,)g(Aggreg)))562
533 y({)621 593 y(targetlist_so_far)f(=)681 653 y
(check_having_qual_for_vars()1159 712 y(((Aggreg)h
(*)clause)->target,)1159 772 y(targetlist_so_far);)621
832 y(return)h(targetlist_so_far;)562 892 y(})1159 951
y(.)1159 1011 y(.)1159 1071 y(.)382 1131 y(})198 1271
y Ft(The)13 b(ne)o(xt)f(function)g(is)h(found)e(in)h
Fk(:)c(:)g(:)q Fr(/src/backend/optimizer/plan/)o(planner.c)o
Ft(:)273 1386 y Fo(17)25 b Fr(union)p 476 1386 15 2
v 17 w(planner())323 1446 y Ft(This)14 b(function)f(creates)h(a)g
Fp(plan)f Ft(from)g(the)h Fp(parsetr)n(ee)g Ft(gi)o(v)o(en)g(to)g(it)f
(by)h(the)g(parameter)f Fr(parse)323 1505 y Ft(that)f(can)g(be)h(e)o(x)
o(ecuted)g(by)f(the)g Fp(e)o(xecutor)p Ft(.)323 1595
y(If)g Fp(aggr)n(e)n(gate)g(functions)g Ft(are)h(present)f((indicated)
h(by)f Fr(parse->hasAggs)f Ft(set)j(to)e(true))g(the)323
1655 y(02rst)f(step)i(is)g(to)f(e)o(xtend)g(the)g Fp(tar)n(getlist)g
Ft(by)g(those)h(attrib)o(utes)f(that)g(are)g(used)h(within)e(the)h
Fp(having)323 1715 y(clause)h Ft((if)e(an)o(y)i(is)h(present))e(b)o
(ut)g(do)h(not)f(appear)h(in)g(the)f Fp(select)i(list)e
Ft((Refer)g(to)h(the)g(description)323 1775 y(of)e Fr(check)p
529 1775 V 18 w(having)p 727 1775 V 17 w(qual)p 864 1775
V 18 w(for)p 972 1775 V 17 w(vars())h Ft(abo)o(v)o(e).)323
1865 y(The)17 b(ne)o(xt)g(step)g(is)g(to)g(call)f(the)h(function)f
Fr(query)p 1224 1865 V 17 w(planner())g Ft(creating)g(a)h
Fp(plan)g Ft(without)323 1924 y(taking)c(the)g Fp(gr)n(oup)h(clause)p
Ft(,)h(the)e Fp(aggr)n(e)n(gate)g(functions)g Ft(and)h(the)g
Fp(having)f(clause)h Ft(into)f(account)323 1984 y(for)e(the)h(moment.)
323 2074 y(Ne)o(xt)j(insert)h(a)f Fr(GRP)h Ft(node)f(at)h(the)f(top)h
(of)f(the)g Fp(plan)h Ft(according)f(to)g(the)h Fp(gr)n(oup)f(clause)h
Ft(of)f(the)323 2134 y Fp(parsetr)n(ee)e Ft(if)f(an)o(y)g(is)h
(present.)323 2224 y(Add)d(an)h Fr(AGG)g Ft(node)f(to)h(the)g(top)f(of)
h(the)f(current)g Fp(plan)h Ft(if)f Fp(aggr)n(e)n(gate)g(functions)h
Ft(are)f(present)h(and)323 2284 y(if)g(a)i Fp(having)f(clause)h
Ft(is)f(present)h(additionally)e(perform)g(the)h(follo)o(wing)f(steps:)
382 2404 y Fn(226)25 b Ft(Perform)9 b(v)o(arious)i(transformations)e
(to)i(the)g(representation)f(of)g(the)h Fp(having)f(clause)h
Ft((e.g.)432 2463 y(transform)g(it)h(to)h(CNF)l(,)f
Fk(:)c(:)g(:)q Ft().)382 2553 y Fn(226)25 b Ft(Attach)30
b(the)f(transformed)f(representation)h(of)g(the)h Fp(having)f(clause)h
Ft(to)f(the)g(02eld)432 2613 y Fr(plan.qual)12 b Ft(of)g(the)g(just)g
(created)h Fr(AGG)f Ft(node.)382 2703 y Fn(226)25 b
Ft(Examine)15 b(the)g(whole)g Fp(having)f(clause)h Ft(and)g(search)g
(for)f Fp(aggr)n(e)n(gate)g(functions)p Ft(.)23 b(This)16
b(is)432 2763 y(done)10 b(using)g(the)g(function)f Fr(check)p
1057 2763 V 17 w(having)p 1254 2763 V 18 w(qual)p 1392
2763 V 17 w(for)p 1499 2763 V 18 w(aggs())g Ft(which)h(appends)432
2823 y(e)o(v)o(ery)i Fp(aggr)n(e)n(gate)g(function)g
Ft(found)f(to)h(a)h(list)f(that)h(is)f(02nally)g(returned.)382
2913 y Fn(226)25 b Ft(Append)12 b(the)h(list)f(just)h(created)f(to)g
(the)g(list)h(already)f(attached)g(to)h(the)f(02eld)g
Fr(aggs)g Ft(of)g(the)432 2972 y Fr(AGG)g Ft(node)h((this)f(list)g
(contains)g(the)h Fp(aggr)n(e)n(gate)e(functions)h Ft(found)g(in)g(the)
h Fp(tar)n(getlist)p Ft().)382 3062 y Fn(226)25 b Ft(Make)10
b(sure)h(that)f Fp(aggr)n(e)n(gate)f(functions)h Ft(do)h(appear)f(in)g
(the)g Fp(having)g(clause)p Ft(.)16 b(This)11 b(is)f(done)432
3122 y(by)15 b(comparing)f(the)g(length)g(of)h(the)f(list)h(attached)g
(to)f Fr(aggs)h Ft(before)f(and)g(after)g(the)h(call)432
3182 y(to)h Fr(check)p 640 3182 V 17 w(having)p 837 3182
V 18 w(qual)p 975 3182 V 17 w(for)p 1082 3182 V 18 w(aggs())p
Ft(.)26 b(If)15 b(the)h(length)g(has)h(not)e(changed,)j(we)432
3242 y(kno)o(w)e(that)h(no)g Fp(aggr)n(e)n(gate)f(function)g
Ft(has)h(been)g(detected)g(and)g(that)f(this)h(query)f(could)432
3302 y(ha)o(v)o(e)d(been)g(formulated)f(using)g(only)h(a)g
Fp(wher)n(e)h(clause)p Ft(.)j(In)12 b(this)h(case)h(an)f(error)e
(message)432 3361 y(is)i(printed)e(to)i(the)f(screen)h(and)f(the)g
(processing)h(is)f(aborted.)p eop
%%Page: 86 86
86 85 bop 270 60 a Ft(86)85 b Fm(CHAPTER)14 b(3.)25 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)454
234 y Fr(Plan)30 b(*)454 294 y(union_planner(Query)e(*parse))454
354 y({)544 413 y(List)209 b(*tlist)29 b(=)h(parse->targetList;)395
533 y(+)119 b(/*)30 b(copy)f(the)h(original)f(tlist,)g(we)g(will)h
(need)f(the)395 593 y(+)149 b(*)30 b(original)f(one)g(for)h(the)f(AGG)h
(node)f(later)h(on)f(*/)395 653 y(+)149 b(List)29 b(*new_tlist)g(=)h
(new_unsorted_tlist(tlist);)1231 712 y(.)1231 772 y(.)1231
832 y(.)395 892 y(+)268 b(if)30 b((parse->hasAggs))395
951 y(+)268 b({)395 1011 y(+)328 b(/*)30 b(extend)f(targetlist)g(by)g
(variables)g(not)395 1071 y(+)358 b(*)30 b(contained)f(already)g(but)g
(used)h(in)f(the)395 1131 y(+)358 b(*)30 b(havingQual.)395
1191 y(+)358 b(*/)395 1250 y(+)328 b(if)30 b((parse->havingQual)e(!=)h
(NULL))395 1310 y(+)388 b({)395 1370 y(+)448 b(new_tlist)29
b(=)395 1430 y(+)508 b(check_having_qual_for_vars()395
1489 y(+)896 b(parse->havingQual,)395 1549 y(+)g(new_tlist);)395
1609 y(+)388 b(})395 1669 y(+)268 b(})1231 1729 y(.)1231
1788 y(.)1231 1848 y(.)693 1908 y(/*)30 b(Call)g(the)f(planner)g(for)h
(everything)723 1968 y(*)g(but)g(groupclauses)e(and)i(aggregate)f
(funcs.)723 2027 y(*/)693 2087 y(result_plan)g(=)h
(query_planner(parse,)1411 2147 y(parse->commandType,)1411
2207 y(new_tlist,)1411 2267 y((List)f(*))h(parse->qual);)1231
2326 y(.)1231 2386 y(.)1231 2446 y(.)634 2506 y(/*)f(If)h(aggregate)f
(is)h(present,)e(insert)i(the)f(AGG)h(node)664 2565 y(*/)634
2625 y(if)f((parse->hasAggs))634 2685 y({)693 2745
y(int)h(old_length=0,)e(new_length=0;)693 2804 y(/*)i(Create)f(the)h
(AGG)f(node)h(but)f(use)h('tlist')f(not)723 2864 y(*)h('new_tlist')f
(as)g(target)h(list)f(because)g(we)723 2924 y(*)h(don't)f(want)h(the)f
(additional)g(attributes)723 2984 y(*)h((only)f(used)h(for)f(the)h
(havingQual,)f(see)723 3044 y(*)h(above))f(to)h(show)f(up)h(in)g(the)f
(result.)723 3103 y(*/)693 3163 y(result_plan)g(=)h((Plan)f(*))h
(make_agg(tlist,)1560 3223 y(result_plan);)1231 3283
y(.)1231 3342 y(.)1231 3402 y(.)p eop
%%Page: 87 87
87 86 bop 198 60 a Fm(3.7.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(THE)h(HA)-7 b(VING)12 b(CLA)m(USE)622 b Ft(87)323
234 y Fr(+)268 b(/*)30 b(Check)f(every)h(clause)f(of)h(the)f
(havingQual)g(for)323 294 y(+)298 b(*)30 b(aggregates)f(used)g(and)h
(append)f(them)g(to)323 354 y(+)298 b(*)30 b(the)g(list)f(in)h
(result_plan->aggs)323 413 y(+)298 b(*/)323 473 y(+)268
b(foreach(clause,)323 533 y(+)508 b(((Agg)29 b(*))h
(result_plan)->plan.qual))323 593 y(+)268 b({)323 653
y(+)328 b(/*)30 b(Make)f(sure)h(there)f(are)h(aggregates)e(in)i(the)323
712 y(+)358 b(*)30 b(havingQual)f(if)g(so,)h(the)f(list)h(must)f(be)323
772 y(+)358 b(*)30 b(longer)f(after)g(check_having_qual_for_aggs)323
832 y(+)358 b(*/)323 892 y(+)328 b(old_length)29 b(=)323
951 y(+)388 b(length(((Agg)29 b(*))g(result_plan)->aggs);)323
1011 y(+)323 1071 y(+)328 b(((Agg)30 b(*))f(result_plan)->aggs)f(=)
323 1131 y(+)418 b(nconc(((Agg)29 b(*))g(result_plan)->aggs,)323
1191 y(+)597 b(check_having_qual_for_aggs()323 1250
y(+)657 b((Node)29 b(*))h(lfirst(clause),)323 1310
y(+)657 b(((Agg)29 b(*)result_plan)->)323 1370 y(+)777
b(plan.lefttree->targetlist,)323 1430 y(+)657 b(((List)29
b(*))h(parse->groupClause)));)323 1489 y(+)328 b(/*)30
b(Have)f(a)h(look)g(at)f(the)h(length)f(of)h(the)f(returned)323
1549 y(+)358 b(*)30 b(list.)f(If)h(there)f(is)h(no)f(difference,)g(no)
323 1609 y(+)358 b(*)30 b(aggregates)f(have)g(been)g(found)h(and)f
(that)h(means)323 1669 y(+)358 b(*)30 b(that)f(the)h(Qual)f(belongs)g
(to)h(the)g(where)f(clause)323 1729 y(+)358 b(*/)323
1788 y(+)328 b(if)30 b((((new_length)e(=)323 1848
y(+)508 b(length(((Agg)28 b(*))i(result_plan)->aggs))==)323
1908 y(+)508 b(old_length))28 b(||)i((new_length)e(==)i(0)))323
1968 y(+)328 b({)323 2027 y(+)388 b(elog(ERROR,"This)28
b(could)h(have)h(been)f(done)h(in)f(a)323 2087 y(+)1135
b(where)29 b(clause!!");)323 2147 y(+)388 b(return)29
b((Plan)h(*)NIL;)323 2207 y(+)328 b(})323 2267 y(+)268
b(})1159 2326 y(.)1159 2386 y(.)1159 2446 y(.)382 2506
y(})198 2647 y Fn(Executor)198 2743 y Ft(The)17 b Fp(e)o(xecutor)g
Ft(takes)g(the)f Fp(queryplan)h Ft(produced)f(by)g(the)h
Fp(planner/optimizer)g Ft(in)f(the)h(way)f(just)g(de-)198
2803 y(scribed)c(and)g(processes)h(all)e Fp(aggr)n(e)n(gate)g
(functions)h Ft(in)g(the)g(way)f(described)h(in)g(section)g(3.7.1)g
Fp(The)g(Im-)198 2863 y(plementation)g(of)g(Aggr)n(e)n(gate)g
(Functions)h Ft(b)o(ut)f(before)g(the)g(tuple)g(deri)o(v)o(ed)g(is)h
(handed)f(back)h(the)f Fp(oper)o(-)198 2922 y(ator)h(tr)n(ee)g
Ft(attached)f(to)g(the)h(02eld)f Fr(qpqual)g Ft(is)g(e)o(v)o(aluated)
g(by)h(calling)f(the)g(function)g Fr(ExecQual())p Ft(.)198
2982 y(This)i(function)e(recursi)o(v)o(ely)g(steps)i(through)e(the)h
Fp(oper)o(ator)h(tr)n(ee)f Ft((i.e.)18 b(the)13 b Fp(having)g(clause)p
Ft())g(and)g(e)o(v)o(al-)198 3042 y(uates)k(the)g(predicates)f
(appearing)g(there.)29 b(Thanks)17 b(to)g(our)f(changes)h(that)f(ha)o
(v)o(e)h(been)g(made)g(to)f(the)198 3102 y Fp(planner)i
Ft(the)g(v)o(alues)f(of)h(all)g(operands)f(needed)h(to)g(e)o(v)o
(aluate)f(the)h(predicates)g((e.g.)32 b(the)18 b(v)o(alues)g(of)198
3162 y(all)13 b Fp(aggr)n(e)n(gate)f(functions)p Ft())g(are)h(already)
f(present)h(and)g(can)g(be)g(accessed)i(throughout)c(the)i(e)o(v)o
(aluation)198 3221 y(without)f(an)o(y)g(problems.)273
3283 y(If)i(the)h(e)o(v)o(aluation)e(of)i(the)f Fp(having)h
(quali02cation)f Ft(returns)g Fr(true)g Ft(the)h(tuple)g(is)g
(returned)e(by)i(the)198 3342 y(function)c Fr(execAgg())h
Ft(otherwise)g(it)g(is)h(ignored)f(and)g(the)g(ne)o(xt)h(group)e(is)i
(processed.)p eop
%%Page: 88 88
88 87 bop 270 60 a Ft(88)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)270
234 y Ft(The)26 b(necessary)h(changes)f(and)g(enhancements)g(ha)o(v)o
(e)g(been)g(applied)f(to)h(the)g(follo)o(wing)e(func-)270
294 y(tion)12 b(in)g(the)g(02le)h Fk(:)8 b(:)g(:)p
Fr(/src/backend/executor/nodeAgg)o(.c)p Ft(:)345 391
y Fo(17)25 b Fr(execAgg())17 b Ft(Whene)o(v)o(er)g(the)h
Fp(e)o(xecutor)h Ft(gets)f(to)g(an)g Fr(AGG)g Ft(node)g(this)g
(function)f(is)i(called.)395 451 y(Before)d(the)g Fp(having)g(logic)g
Ft(had)g(been)h(implemented,)g(all)g(the)f Fp(tuples)h
Ft(of)f(the)g(current)g(group)395 511 y(were)j(fetched)f(from)g(the)h
Fp(subplan)g Ft(and)h(all)f Fp(aggr)n(e)n(gate)f(functions)h
Ft(were)g(applied)g(to)g(these)395 571 y(tuples.)c(After)d(that,)h(the)
f(results)g(were)h(handed)f(back)h(to)f(the)g(calling)g(function.)395
650 y(Since)i(the)g Fp(having)h(logic)f Ft(has)h(been)f(implemented)g
(there)h(is)f(one)h(additional)f(step)h(e)o(x)o(ecuted.)395
710 y(Before)d(the)h(results)g(of)f(applying)g(the)h
Fp(aggr)n(e)n(gate)f(functions)g Ft(are)h(handed)g(back,)g(the)g
(function)395 769 y Fr(ExecQual())d Ft(is)i(called)f(with)h(the)f
(representation)g(of)g(the)g Fp(having)h(clause)g Ft(as)g(an)f(ar)o
(gument.)395 829 y(If)h Fr(true)h Ft(is)h(returned,)f(the)g(results)g
(are)g(handed)h(back,)g(otherwise)f(the)o(y)g(are)g(ignored)g(and)g(we)
395 889 y(start)18 b(from)f(the)i(be)o(ginning)f(for)f(the)i(ne)o(xt)f
(group)g(until)g(a)g(group)g(meeting)g(the)h(restrictions)395
949 y(gi)o(v)o(en)12 b(in)g(the)g Fp(having)g(clause)h
Ft(is)g(found.)454 1066 y Fr(TupleTableSlot)29 b(*)454
1125 y(ExecAgg(Agg)g(*node))454 1185 y({)1231 1245
y(.)1231 1305 y(.)1231 1364 y(.)634 1424 y(/*)g(We)h(loop)f(retrieving)
g(groups)g(until)h(we)f(find)h(one)664 1484 y(*)f(matching)g
(node->plan.qual)664 1544 y(*/)395 1603 y(+)209 b(do)395
1663 y(+)g({)1231 1723 y(.)1231 1783 y(.)1231 1843 y(.)693
1902 y(/*)30 b(Apply)f(*all*)h(aggregate)f(function)g(to)g(the)723
1962 y(*)h(tuples)f(of)h(the)g(*current*)e(group)723
2022 y(*/)1231 2082 y(.)1231 2141 y(.)1231 2201 y(.)693
2261 y(econtext->ecxt_scantuple)g(=)962 2321 y
(aggstate->csstate.css_ScanTupleSlot)o(;)693 2381 y(resultSlot)h(=)h
(ExecProject(projInfo,)d(&isDone);)395 2500 y(+)268
b(/*)30 b(As)g(long)f(as)h(the)f(retrieved)g(group)h(does)f(not)395
2560 y(+)298 b(*)30 b(match)f(the)h(qualifications)e(it)i(is)g(ignored)
f(and)395 2620 y(+)298 b(*)30 b(the)g(next)f(group)g(is)h(fetched)395
2679 y(+)298 b(*/)395 2739 y(+)268 b(if(node->plan.qual)28
b(!=)i(NULL))395 2799 y(+)268 b({)395 2859 y(+)328 b(qual_result)29
b(=)395 2919 y(+)448 b(ExecQual(fix_opids(node->plan)o(.qual),)395
2978 y(+)717 b(econtext);)395 3038 y(+)268 b(})395 3098
y(+)g(if)30 b((oneTuple))f(pfree(oneTuple);)395 3158
y(+)209 b(})395 3217 y(+)g(while((node->plan.qual!=NULL)o())27
b(&&)395 3277 y(+)926 b((qual_result!=true));)634
3337 y(return)29 b(resultSlot;)454 3397 y(})p eop
%%Page: 89 89
89 88 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(89)198
234 y Fi(3.8)71 b(The)18 b(Realization)f(of)g(Union,)g(Intersect)g(and)
h(Except)198 352 y Ft(SQL92)12 b(supports)g(the)h(well)f(kno)o(wn)g
(set)g(theoretic)g(operations)g Fp(union)p Ft(,)g Fp(intersect)h
Ft(and)f Fp(set)h(dif)o(fer)n(ence)198 411 y Ft((the)j
Fp(set)h(dif)o(fer)n(ence)f Ft(is)h(called)f Fp(e)o(xcept)h
Ft(in)f(SQL92).)27 b(The)17 b(operators)f(are)g(used)h(to)f(connect)g
(two)g(or)198 471 y(more)c Fr(select)g Ft(statements.)18
b(Ev)o(ery)13 b Fr(select)f Ft(statement)h(returns)f(a)h(set)g(of)f
(tuples)h(and)g(the)g(opera-)198 531 y(tors)f(between)h(the)f
Fr(select)g Ft(statements)g(tell)h(ho)o(w)f(to)g(mer)o(ge)g(the)g
(returned)g(sets)h(of)f(tuples)g(into)g(one)198 591 y(result)g
(relation.)198 729 y Fn(Example)h(3.4)25 b Ft(Let)13
b(the)f(follo)o(wing)f(tables)i(be)f(gi)o(v)o(en:)616
842 y Fr(A)60 b(C1|C2|C3)268 b(B)60 b(C1|C2|C3)706 902
y(--+--+--)358 b(--+--+--)736 961 y(1|)30 b(a|)f(b)389
b(1|)29 b(a|)h(b)736 1021 y(2|)g(a|)f(b)389 b(5|)29 b(a|)h(b)736
1081 y(3|)g(c|)f(d)389 b(3|)29 b(c|)h(d)736 1141 y(4|)g(e|)f(f)389
b(8|)29 b(e|)h(f)945 1260 y(C)60 b(C1|C2|C3)1035 1320
y(--+--+--)1065 1380 y(4|)29 b(e|)h(f)1065 1440 y(8|)f(e|)h(f)198
1549 y Ft(No)o(w)12 b(let')m(s)h(ha)o(v)o(e)f(a)h(look)f(at)g(the)h
(results)f(of)g(the)g(follo)o(wing)f(queries:)258 1661
y Fr(select)29 b(*)h(from)f(A)258 1721 y(union)258 1781
y(select)g(*)h(from)f(B;)198 1890 y Ft(deri)o(v)o(es)12
b(the)h(set)f(theoretic)g Fp(union)g Ft(of)g(the)g(two)g(tables:)1035
2002 y Fr(C1|C2|C3)1035 2062 y(--+--+--)1065 2122 y(1|)29
b(a|)h(b)1065 2182 y(2|)f(a|)h(b)1065 2242 y(3|)f(c|)h(d)1065
2301 y(4|)f(e|)h(f)1065 2361 y(5|)f(a|)h(b)1065 2421
y(8|)f(e|)h(f)198 2530 y Ft(The)13 b Fr(select)f Ft(statements)g(used)h
(may)f(be)h(more)f(comple)o(x:)258 2643 y Fr(select)29
b(C1,)h(C3)f(from)h(A)318 2702 y(where)f(C2)h(=)f('a')258
2762 y(union)258 2822 y(select)g(C1,)h(C2)f(from)h(B)318
2882 y(where)f(C3)h(=)f('b';)198 2991 y Ft(will)12 b(return)f(the)i
(follo)o(wing)e(table:)1065 3103 y Fr(C1|C3)1065 3163
y(--+--)1095 3223 y(1|)29 b(b)1095 3283 y(2|)g(b)1095
3342 y(1|)g(a)1095 3402 y(5|)g(a)p eop
%%Page: 90 90
90 89 bop 270 60 a Ft(90)85 b Fm(CHAPTER)14 b(3.)25 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)270
234 y Ft(Note)j(that)f(the)h(selected)g(columns)f(do)h(not)f(need)h(to)
g(ha)o(v)o(e)g(identical)f(names,)i(the)o(y)f(only)f(ha)o(v)o(e)h(to)g
(be)270 294 y(of)e(the)g(same)h(type.)k(In)13 b(the)g(pre)o(vious)g(e)o
(xample)g(we)h(selected)g(for)e Fr(C1)h Ft(and)g Fr(C3)g
Ft(in)h(the)f(02rst)g Fr(select)270 354 y Ft(statement)i(and)h(for)e
Fr(C1)h Ft(and)g Fr(C2)g Ft(in)h(the)f(second)g(one.)25
b(The)16 b(names)f(of)g(the)g(resulting)g(columns)g(are)270
413 y(taken)d(from)f(the)h(02rst)g Fr(select)g Ft(statement.)270
533 y(Let')m(s)h(ha)o(v)o(e)g(a)f(look)g(at)h(a)f(query)g(using)h
Fr(intersect)p Ft(:)330 634 y Fr(select)29 b(*)h(from)f(A)330
694 y(intersect)330 754 y(select)g(*)h(from)f(B;)270
855 y Ft(will)12 b(return:)1107 956 y Fr(C1|C2|C3)1107
1016 y(--+--+--)1137 1076 y(1|)29 b(a|)h(b)1137 1136
y(3|)f(c|)h(d)270 1236 y Ft(Here)12 b(is)h(an)f(e)o(xample)h(using)f
Fr(except)p Ft(:)330 1338 y Fr(select)29 b(*)h(from)f(A)330
1398 y(except)330 1457 y(select)g(*)h(from)f(B;)270 1558
y Ft(will)12 b(return:)1107 1660 y Fr(C1|C2|C3)1107 1719
y(--+--+--)1137 1779 y(2|)29 b(a|)h(b)1137 1839 y(4|)f(e|)h(f)270
1940 y Ft(The)11 b(last)g(e)o(xamples)g(were)f(rather)g(simple)g
(because)h(the)o(y)g(only)f(used)h(one)g(set)g(operator)e(at)i(a)f
(time)g(with)270 2000 y(only)h(two)f(operands.)15 b(No)o(w)c(we)g(look)
g(at)g(some)g(more)f(comple)o(x)h(queries)g(in)n(v)o(olving)f(more)h
Fp(oper)o(ators)p Ft(:)330 2101 y Fr(select)29 b(*)h(from)f(A)330
2161 y(union)330 2221 y(select)g(*)h(from)f(B)330 2280
y(intersect)330 2340 y(select)g(*)h(from)f(C;)270 2441
y Ft(will)12 b(return:)1107 2542 y Fr(C1|C2|C3)1107 2602
y(--+--+--)1137 2662 y(4|)29 b(e|)h(f)1137 2722 y(8|)f(e|)h(f)270
2823 y Ft(The)11 b(abo)o(v)o(e)g(query)f(performs)f(the)i(set)g
(theoretic)f(computation)f Fl(()p Fk(A)t Fo([)t Fk(B)r
Fl())t Fo(\)t Fk(C)t Ft(.)17 b(When)10 b(no)h(parentheses)270
2882 y(are)j(used,)h(the)f(operations)g(are)g(considered)g(to)g(be)g
(left)f(associati)o(v)o(e,)j(i.e.)21 b Fk(A)13 b Fo([)f
Fk(B)j Fo([)e Fk(C)i Fo([)e Fk(D)i Ft(will)f(be)270 2942
y(treated)e(as)h Fl((()p Fk(A)f Fo([)f Fk(B)r Fl())h
Fo([)f Fk(C)t Fl())g Fo([)g Fk(D)q Ft(.)270 3062 y(The)i(same)g(query)
f(using)g(parenthesis)h(can)f(lead)h(to)f(a)g(completely)g(dif)o
(ferent)f(result:)330 3163 y Fr(select)29 b(*)h(from)f(A)330
3223 y(union)330 3283 y((select)g(*)h(from)f(B)360 3342
y(intersect)360 3402 y(select)g(*)h(from)f(C);)p eop
%%Page: 91 91
91 90 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(91)198
234 y(performs)11 b Fk(A)h Fo([)f Fl(()p Fk(B)j Fo(\)d
Fk(C)t Fl())h Ft(and)g(will)g(return:)1035 333 y Fr(C1|C2|C3)1035
393 y(--+--+--)1065 453 y(1|)29 b(a|)h(b)1065 513 y(2|)f(a|)h(b)1065
572 y(3|)f(c|)h(d)1065 632 y(4|)f(e|)h(f)1065 692 y(8|)f(e|)h(f)198
835 y Fh(3.8.1)59 b(Ho)o(w)15 b(Unions)h(ha)o(v)o(e)e(been)g(Realized)g
(Until)g(V)-6 b(ersion)14 b(6.3.2)198 928 y Ft(First)d(we)h(gi)o(v)o(e)
g(a)g(description)f(of)h(the)f(implementation)g(of)g
Fp(union)h Ft(and)f Fp(union)h(all)f Ft(until)h(v)o(ersion)f(6.3.2)198
988 y(because)g(we)f(need)g(it)g(to)g(understand)g(the)g
(implementation)f(of)g Fp(intersect)h Ft(and)g Fp(e)o(xcept)h
Ft(described)f(later)m(.)198 1107 y(A)i Fp(union)g Ft(query)g(is)h
(passed)g(through)f(the)g(usual)h(stages:)273 1206 y
Fo(17)25 b Ft(parser)273 1306 y Fo(17)g Ft(re)o(write)11
b(system)273 1405 y Fo(17)25 b Ft(planner/optimizer)273
1505 y Fo(17)g Ft(e)o(x)o(ecutor)198 1604 y(and)11
b(we)g(will)f(no)o(w)g(describe)h(what)g(e)o(v)o(ery)f(single)h(stage)g
(does)g(to)g(the)f(query)m(.)15 b(F)o(or)10 b(our)g(e)o(xplanation)h
(we)198 1664 y(assume)j(to)f(process)g(a)h(simple)f(query)f((i.e.)19
b(a)13 b(query)g(without)f Fp(subselects)p Ft(,)j Fp(aggr)n(e)n(gates)e
Ft(and)g(without)198 1724 y(in)n(v)o(olving)e Fp(vie)o(ws)p
Ft())198 1856 y Fn(The)h(Parser)i(Stage)198 1949 y Ft(As)f(described)f
(earlier)g(the)g Fp(parser)h(stage)f Ft(can)g(be)h(di)o(vided)e(into)h
(two)g(parts:)273 2049 y Fo(17)25 b Ft(the)12 b Fp(parser)h
Ft(b)o(uilt)f(up)g(by)g(the)h(grammar)e(rules)h(gi)o(v)o(en)g(in)g
Fr(gram.y)g Ft(and)273 2148 y Fo(17)25 b Ft(the)12
b Fp(tr)o(ansformation)g(r)n(outines)g Ft(performing)e(a)i(lot)g(of)g
(changes)g(and)g(analysis)h(to)f(the)g(tree)g(b)o(uilt)323
2208 y(up)g(by)g(the)g(parser)m(.)k(Most)d(of)f(these)h(routines)f
(reside)g(in)g Fr(analyze.c)p Ft(.)198 2307 y(A)18 b
Fp(union)f Ft(statement)h(consists)h(of)e(two)h(or)f(more)g
Fp(select)i Ft(statements)f(connected)g(by)g(the)f(ke)o(yword)198
2367 y Fr(union)12 b Ft(as)h(the)f(follo)o(wing)f(e)o(xample)i(sho)o
(ws:)258 2466 y Fr(select)29 b(*)h(from)f(A)318 2526
y(where)g(C1=1)258 2586 y(union)258 2645 y(select)g(*)h(from)f(B)318
2705 y(where)g(C2)h(=)f('a')258 2765 y(union)258 2825
y(select)g(*)h(from)f(C)318 2885 y(where)g(C3)h(=)f('f')198
2984 y Ft(The)17 b(abo)o(v)o(e)f Fp(union)g Ft(statement)g(consists)g
(of)g(three)g Fp(select)g Ft(statements)h(connected)f(by)f(the)h(ke)o
(yword)198 3044 y Fr(union)p Ft(.)i(W)l(e)13 b(will)g(refer)f(to)h(the)
h(02rst)e Fp(select)i Ft(statement)f(by)g(A,)h(to)f(the)g(second)h
(one)f(by)g(B)h(and)f(to)g(the)198 3103 y(third)i(one)g(by)h(C)g(for)e
(our)h(further)g(e)o(xplanation)g((in)g(the)g(ne)o(w)h(notation)f(our)
g(query)g(looks)g(like)g(this:)198 3163 y Fr(A)30 b(union)f(B)h(union)f
(C)p Ft().)198 3283 y(The)g Fp(parser)g Ft((gi)o(v)o(en)f(by)h
Fr(gram.y)p Ft())e(processes)j(all)e(three)h Fr(select)e
Ft(statements,)34 b(creates)29 b(a)198 3342 y Fr(SelectStmt)13
b Ft(node)g(for)g(e)o(v)o(ery)g Fr(select)g Ft(and)h(attaches)g(the)g
Fr(where)f Ft(quali02cations,)h Fp(tar)n(getlists)198
3402 y Ft(etc.)48 b(to)23 b(the)g(corresponding)g(nodes.)48
b(Then)23 b(it)g(creates)h(a)f(list)g(of)g(the)g(second)h(and)f(the)g
(third)p eop
%%Page: 92 92
92 91 bop 270 60 a Ft(92)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)275
1049 y @beginspecial 127 @llx 309 @lly 485 @urx 482 @ury
3580 @rwi @setspecial
%%BeginDocument: figures/parser_union_back.ps
%Magnification: 1.05
/$F2psDict 200 dict def
$F2psDict begin
$F2psDict /mtrx matrix put
/col-1 {0 setgray} bind def
/col0 {0.000 0.000 0.000 srgb} bind def
/col1 {0.000 0.000 1.000 srgb} bind def
/col2 {0.000 1.000 0.000 srgb} bind def
/col3 {0.000 1.000 1.000 srgb} bind def
/col4 {1.000 0.000 0.000 srgb} bind def
/col5 {1.000 0.000 1.000 srgb} bind def
/col6 {1.000 1.000 0.000 srgb} bind def
/col7 {1.000 1.000 1.000 srgb} bind def
/col8 {0.000 0.000 0.560 srgb} bind def
/col9 {0.000 0.000 0.690 srgb} bind def
/col10 {0.000 0.000 0.820 srgb} bind def
/col11 {0.530 0.810 1.000 srgb} bind def
/col12 {0.000 0.560 0.000 srgb} bind def
/col13 {0.000 0.690 0.000 srgb} bind def
/col14 {0.000 0.820 0.000 srgb} bind def
/col15 {0.000 0.560 0.560 srgb} bind def
/col16 {0.000 0.690 0.690 srgb} bind def
/col17 {0.000 0.820 0.820 srgb} bind def
/col18 {0.560 0.000 0.000 srgb} bind def
/col19 {0.690 0.000 0.000 srgb} bind def
/col20 {0.820 0.000 0.000 srgb} bind def
/col21 {0.560 0.000 0.560 srgb} bind def
/col22 {0.690 0.000 0.690 srgb} bind def
/col23 {0.820 0.000 0.820 srgb} bind def
/col24 {0.500 0.190 0.000 srgb} bind def
/col25 {0.630 0.250 0.000 srgb} bind def
/col26 {0.750 0.380 0.000 srgb} bind def
/col27 {1.000 0.500 0.500 srgb} bind def
/col28 {1.000 0.630 0.630 srgb} bind def
/col29 {1.000 0.750 0.750 srgb} bind def
/col30 {1.000 0.880 0.880 srgb} bind def
/col31 {1.000 0.840 0.000 srgb} bind def
end
save
100.0 495.5 translate
1 -1 scale
/cp {closepath} bind def
/ef {eofill} bind def
/gr {grestore} bind def
/gs {gsave} bind def
/sa {save} bind def
/rs {restore} bind def
/l {lineto} bind def
/m {moveto} bind def
/rm {rmoveto} bind def
/n {newpath} bind def
/s {stroke} bind def
/sh {show} bind def
/slc {setlinecap} bind def
/slj {setlinejoin} bind def
/slw {setlinewidth} bind def
/srgb {setrgbcolor} bind def
/rot {rotate} bind def
/sc {scale} bind def
/sd {setdash} bind def
/ff {findfont} bind def
/sf {setfont} bind def
/scf {scalefont} bind def
/sw {stringwidth} bind def
/tr {translate} bind def
/tnt {dup dup currentrgbcolor
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add srgb}
bind def
/shd {dup dup currentrgbcolor 4 -2 roll mul 4 -2 roll mul
4 -2 roll mul srgb} bind def
/$F2psBegin {$F2psDict begin /$F2psEnteredState save def} def
/$F2psEnd {$F2psEnteredState restore end} def
$F2psBegin
10 setmiterlimit
n 0 792 m 0 0 l 612 0 l 612 792 l cp clip
0.06299 0.06299 sc
7.500 slw
% Polyline
n 450 450 m 1800 450 l gs col-1 s gr
% Polyline
n 450 225 m 1800 225 l 1800 1350 l 450 1350 l cp gs col-1 s gr
% Polyline
n 450 1080 m 1800 1080 l gs col-1 s gr
% Polyline
n 450 810 m 1800 810 l gs col-1 s gr
% Polyline
n 1530 450 m 1530 1350 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
540 405 m
gs 1 -1 sc (SelectStmt node (A)) col-1 sh gr
/Times-Roman ff 150.00 scf sf
900 720 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
540 990 m
gs 1 -1 sc (qual) col-1 sh gr
/Times-Roman ff 150.00 scf sf
540 1260 m
gs 1 -1 sc (unionclause) col-1 sh gr
% Polyline
n 4365 2655 m 4635 2925 l gs col-1 s gr
% Polyline
n 4635 2655 m 4365 2925 l gs col-1 s gr
% Polyline
n 3285 2025 m 4635 2025 l gs col-1 s gr
% Polyline
n 3285 1800 m 4635 1800 l 4635 2925 l 3285 2925 l cp gs col-1 s gr
% Polyline
n 3285 2655 m 4635 2655 l gs col-1 s gr
% Polyline
n 3285 2385 m 4635 2385 l gs col-1 s gr
% Polyline
n 4365 2025 m 4365 2925 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
3375 1980 m
gs 1 -1 sc (SelectStmt node (C)) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3735 2295 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3375 2565 m
gs 1 -1 sc (qual) col-1 sh gr
/Times-Roman ff 150.00 scf sf
3375 2835 m
gs 1 -1 sc (unionclause) col-1 sh gr
% Polyline
gs clippath
2103 1185 m 2223 1215 l 2103 1245 l 2265 1245 l 2265 1185 l cp clip
n 1665 1215 m 2250 1215 l gs col-1 s gr gr
% arrowhead
n 2103 1185 m 2223 1215 l 2103 1245 l 2103 1215 l 2103 1185 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
3453 1185 m 3573 1215 l 3453 1245 l 3615 1245 l 3615 1185 l cp clip
n 2655 1215 m 3600 1215 l gs col-1 s gr gr
% arrowhead
n 3453 1185 m 3573 1215 l 3453 1245 l 3453 1215 l 3453 1185 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
n 2250 1080 m 2790 1080 l 2790 1350 l 2250 1350 l cp gs col-1 s gr
% Polyline
n 3600 1080 m 4140 1080 l 4140 1350 l 3600 1350 l cp gs col-1 s gr
% Polyline
n 2520 1080 m 2520 1350 l gs col-1 s gr
% Polyline
n 3870 1080 m 3870 1350 l gs col-1 s gr
% Polyline
n 3870 1080 m 4140 1350 l gs col-1 s gr
% Polyline
n 3870 1350 m 4140 1080 l gs col-1 s gr
% Polyline
gs clippath
3935 1652 m 3950 1774 l 3879 1674 l 3937 1825 l 3993 1803 l cp clip
n 3735 1215 m 3960 1800 l gs col-1 s gr gr
% arrowhead
n 3935 1652 m 3950 1774 l 3879 1674 l 3907 1663 l 3935 1652 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
1878 2490 m 1998 2520 l 1878 2550 l 2040 2550 l 2040 2490 l cp clip
n 1665 2520 m 2025 2520 l gs col-1 s gr gr
% arrowhead
n 1878 2490 m 1998 2520 l 1878 2550 l 1878 2520 l 1878 2490 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
n 1530 2655 m 1800 2925 l gs col-1 s gr
% Polyline
n 1800 2655 m 1530 2925 l gs col-1 s gr
% Polyline
n 450 2025 m 1800 2025 l gs col-1 s gr
% Polyline
n 450 1800 m 1800 1800 l 1800 2925 l 450 2925 l cp gs col-1 s gr
% Polyline
n 450 2655 m 1800 2655 l gs col-1 s gr
% Polyline
n 450 2385 m 1800 2385 l gs col-1 s gr
% Polyline
n 1530 2025 m 1530 2925 l gs col-1 s gr
% Polyline
gs clippath
4713 2490 m 4833 2520 l 4713 2550 l 4875 2550 l 4875 2490 l cp clip
n 4500 2520 m 4860 2520 l gs col-1 s gr gr
% arrowhead
n 4713 2490 m 4833 2520 l 4713 2550 l 4713 2520 l 4713 2490 l cp gs 0.00 setgray ef gr col-1 s
% Interp Spline
gs clippath
2114 874 m 2228 826 l 2150 922 l 2280 825 l 2244 777 l cp clip
n 1665 945 m
1820.9 950.7 1888.4 950.7 1935 945 curveto
1977.7 939.7 2075.0 917.7 2115 900 curveto
2141.2 888.4 2175.0 865.9 2250 810 curveto
gs col-1 s gr
gr
% arrowhead
n 2114 874 m 2228 826 l 2150 922 l 2132 898 l 2114 874 l cp gs 0.00 setgray ef gr col-1 s
% Interp Spline
gs clippath
1259 1733 m 1146 1783 l 1222 1686 l 1095 1786 l 1132 1833 l cp clip
n 2385 1215 m
2315.4 1408.3 2270.4 1487.0 2205 1530 curveto
2009.1 1658.7 1581.4 1552.1 1395 1620 curveto
1339.3 1640.3 1271.8 1685.3 1125 1800 curveto
gs col-1 s gr
gr
% arrowhead
n 1259 1733 m 1146 1783 l 1222 1686 l 1241 1709 l 1259 1733 l cp gs 0.00 setgray ef gr col-1 s
/Times-Roman ff 150.00 scf sf
2340 720 m
gs 1 -1 sc (Pointer to qualtree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2340 915 m
gs 1 -1 sc (of 1st Select) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2070 2475 m
gs 1 -1 sc (Pointer to qualtree) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2070 2670 m
gs 1 -1 sc (of 2nd Select) col-1 sh gr
/Times-Roman ff 150.00 scf sf
540 1980 m
gs 1 -1 sc (SelectStmt node (B)) col-1 sh gr
/Times-Roman ff 150.00 scf sf
900 2295 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
540 2565 m
gs 1 -1 sc (qual) col-1 sh gr
/Times-Roman ff 150.00 scf sf
540 2835 m
gs 1 -1 sc (unionclause) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4905 2670 m
gs 1 -1 sc (of 3rd Select) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4905 2475 m
gs 1 -1 sc (Pointer to qualtree) col-1 sh gr
showpage
$F2psEnd
rs
%%EndDocument
@endspecial 642 1159 a Ft(Figure)g(3.9:)j(Data)e(structure)f(handed)g
(back)g(by)h(the)f Fp(parser)270 1401 y Fr(SelectStmt)j
Ft(node)g((of)g(B)h(and)f(C))g(and)h(attaches)g(it)f(to)h(the)f
(02eld)g Fr(unionClause)g Ft(of)g(the)g(02rst)270
1460 y(node)i((of)e(A).)i(Finally)f(it)h(hands)g(back)f(the)h
(02rst)f(node)h((node)f(A))g(with)h(the)f(list)h(of)f(the)h
(remaining)270 1520 y(nodes)c(attached)f(as)h(sho)o(wn)f(in)h(02gure)
e(3.9.)270 1640 y(The)24 b(follo)o(wing)d Fp(tr)o(ansformation)j(r)n
(outines)f Ft(process)g(the)g(data)g(structure)g(handed)g(back)g(by)g
(the)270 1700 y Fp(parser)p Ft(.)45 b(First)21 b(the)h(top)g(node)g
((node)f(A))h(is)g(transformed)f(from)f(a)i Fr(SelectStmt)f
Ft(node)h(to)g(a)270 1759 y Fr(Query)15 b Ft(node.)26
b(The)16 b Fp(tar)n(getlist)p Ft(,)h(the)f Fr(where)f
Ft(quali02cation)g(etc.)26 b(attached)16 b(to)f(it)h(are)f
(transformed)270 1819 y(as)h(well.)26 b(Ne)o(xt)16 b(the)g(list)g(of)f
(the)h(remaining)f(nodes)h((attached)f(to)h Fr(unionClause)e
Ft(of)i(node)f(A))h(is)270 1879 y(transformed)c(and)h(in)g(this)g
(step)h(also)f(a)h(check)f(is)h(made)f(if)f(the)h(types)h(and)f
(lengths)g(of)g(the)g Fp(tar)n(getlists)270 1939 y Ft(of)g(the)g(in)n
(v)o(olv)o(ed)g(nodes)h(are)f(equal.)19 b(The)14 b(ne)o(w)f
Fr(Query)g Ft(nodes)g(are)h(no)o(w)e(handed)i(back)f(in)g(the)h(same)
270 1998 y(way)e(as)h(the)g Fr(SelectStmt)e Ft(nodes)i(were)g(before)e
((i.e.)17 b(the)12 b Fr(Query)h Ft(nodes)f(B)h(and)g(C)g(are)f
(collected)270 2058 y(in)g(a)h(list)f(which)g(is)h(attached)f(to)h
Fr(unionClause)e Ft(of)h Fr(Query)g Ft(node)g(A).)270
2278 y Fn(The)g(Rewrite)h(System)270 2401 y Ft(If)18
b(an)o(y)g Fp(r)n(e)o(write)i(rules)f Ft(are)f(present)g(for)f(the)i
Fr(Query)e Ft(nodes)i((i.e.)34 b(one)18 b(of)g(the)g
Fp(select)h Ft(statements)270 2461 y(uses)d(a)g Fp(vie)o(w)p
Ft())f(the)g(necessary)i(changes)e(to)g(the)h Fr(Query)f
Ft(nodes)g(are)g(performed)f((see)i(section)f(3.4.1)270
2521 y Fp(T)-5 b(ec)o(hniques)11 b(T)-5 b(o)11 b(Implement)e(V)l(ie)o
(ws)p Ft().)17 b(Otherwise)10 b(no)g(changes)h(are)g(made)f(to)g(the)h
(nodes)f(in)h(this)f(stage.)270 2741 y Fn(Planner/Optimizer)270
2864 y Ft(This)15 b(stage)g(has)g(to)f(create)g(a)h Fp(plan)f
Ft(out)g(of)g(the)g Fp(querytr)n(ee)i Ft(produced)d(by)i(the)f
Fp(parser)h(stage)f Ft(that)g(can)270 2924 y(be)j(e)o(x)o(ecuted)g(by)f
(the)g Fp(e)o(xecutor)p Ft(.)29 b(In)16 b(most)g(cases)i(there)e(are)g
(se)o(v)o(eral)h(ways)f((paths))g(with)g(dif)o(ferent)270
2984 y(cost)d(to)f(get)h(to)f(the)h(same)g(result.)j(It')m(s)c(the)g
Fp(planner/optimizer')n(s)h Ft(task)g(to)f(02nd)g(out)h(which)f(path)
g(is)h(the)270 3044 y(cheapest)f(and)f(to)g(create)h(a)f
Fp(plan)g Ft(using)h(this)f(path.)k(The)d(implementation)e(of)h
Fp(unions)g Ft(in)g(PostgreSQL)270 3103 y(is)i(based)g(on)f(the)g
(follo)o(wing)f(idea:)270 3223 y(The)26 b(set)g(deri)o(v)o(ed)e(by)h(e)
o(v)o(aluating)g Fk(A)c Fo([)g Fk(B)27 b Ft(must)f(contain)f(e)o(v)o
(ery)g(member)f(of)h Fk(A)h Fn(and)e Ft(e)o(v)o(ery)270
3283 y(member)16 b(of)h Fk(B)r Ft(.)30 b(So)17 b(if)f(we)h(append)g
(the)g(members)g(of)f Fk(B)k Ft(to)c(the)h(members)g(of)g
Fk(A)g Ft(we)g(are)g(almost)270 3342 y(done.)f(If)11
b(there)h(e)o(xist)h(members)f(common)f(to)h Fk(A)h Ft(and)f
Fk(B)j Ft(these)d(members)g(are)g(no)o(w)g(contained)g(twice)270
3402 y(in)g(our)g(ne)o(w)g(set,)i(so)e(the)h(only)f(thing)f(left)h(to)h
(do)f(is)g(to)h(remo)o(v)o(e)f(these)h(duplicates.)p
eop
%%Page: 93 93
93 92 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(93)198
234 y(In)11 b(the)h(case)h(of)e(our)h(e)o(xample)g(the)g
Fp(planner)g Ft(would)f(b)o(uild)g(up)h(the)g Fp(tr)n(ee)g
Ft(sho)o(wn)g(in)g(02gure)f(3.10.)16 b(Ev)o(ery)198
294 y Fr(Query)f Ft(node)h(is)g(planned)f(separately)h(and)f(results)h
(in)f(a)h Fr(SeqScan)f Ft(node)g(in)h(our)f(e)o(xample.)26
b(The)198 354 y(three)12 b Fr(SeqScan)f Ft(nodes)h(are)g(put)f
(together)h(into)f(a)h(list)g(which)g(is)g(attached)g(to)g
Fr(unionplans)f Ft(of)g(an)198 413 y Fr(Append)h Ft(node.)283
1835 y @beginspecial 143 @llx 261 @lly 469 @urx 530 @ury
3260 @rwi @setspecial
%%BeginDocument: figures/union_plan.ps
%Magnification: 1.05
/$F2psDict 200 dict def
$F2psDict begin
$F2psDict /mtrx matrix put
/col-1 {0 setgray} bind def
/col0 {0.000 0.000 0.000 srgb} bind def
/col1 {0.000 0.000 1.000 srgb} bind def
/col2 {0.000 1.000 0.000 srgb} bind def
/col3 {0.000 1.000 1.000 srgb} bind def
/col4 {1.000 0.000 0.000 srgb} bind def
/col5 {1.000 0.000 1.000 srgb} bind def
/col6 {1.000 1.000 0.000 srgb} bind def
/col7 {1.000 1.000 1.000 srgb} bind def
/col8 {0.000 0.000 0.560 srgb} bind def
/col9 {0.000 0.000 0.690 srgb} bind def
/col10 {0.000 0.000 0.820 srgb} bind def
/col11 {0.530 0.810 1.000 srgb} bind def
/col12 {0.000 0.560 0.000 srgb} bind def
/col13 {0.000 0.690 0.000 srgb} bind def
/col14 {0.000 0.820 0.000 srgb} bind def
/col15 {0.000 0.560 0.560 srgb} bind def
/col16 {0.000 0.690 0.690 srgb} bind def
/col17 {0.000 0.820 0.820 srgb} bind def
/col18 {0.560 0.000 0.000 srgb} bind def
/col19 {0.690 0.000 0.000 srgb} bind def
/col20 {0.820 0.000 0.000 srgb} bind def
/col21 {0.560 0.000 0.560 srgb} bind def
/col22 {0.690 0.000 0.690 srgb} bind def
/col23 {0.820 0.000 0.820 srgb} bind def
/col24 {0.500 0.190 0.000 srgb} bind def
/col25 {0.630 0.250 0.000 srgb} bind def
/col26 {0.750 0.380 0.000 srgb} bind def
/col27 {1.000 0.500 0.500 srgb} bind def
/col28 {1.000 0.630 0.630 srgb} bind def
/col29 {1.000 0.750 0.750 srgb} bind def
/col30 {1.000 0.880 0.880 srgb} bind def
/col31 {1.000 0.840 0.000 srgb} bind def
end
save
130.0 543.5 translate
1 -1 scale
/cp {closepath} bind def
/ef {eofill} bind def
/gr {grestore} bind def
/gs {gsave} bind def
/sa {save} bind def
/rs {restore} bind def
/l {lineto} bind def
/m {moveto} bind def
/rm {rmoveto} bind def
/n {newpath} bind def
/s {stroke} bind def
/sh {show} bind def
/slc {setlinecap} bind def
/slj {setlinejoin} bind def
/slw {setlinewidth} bind def
/srgb {setrgbcolor} bind def
/rot {rotate} bind def
/sc {scale} bind def
/sd {setdash} bind def
/ff {findfont} bind def
/sf {setfont} bind def
/scf {scalefont} bind def
/sw {stringwidth} bind def
/tr {translate} bind def
/tnt {dup dup currentrgbcolor
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add srgb}
bind def
/shd {dup dup currentrgbcolor 4 -2 roll mul 4 -2 roll mul
4 -2 roll mul srgb} bind def
/$F2psBegin {$F2psDict begin /$F2psEnteredState save def} def
/$F2psEnd {$F2psEnteredState restore end} def
$F2psBegin
10 setmiterlimit
n 0 792 m 0 0 l 612 0 l 612 792 l cp clip
0.06299 0.06299 sc
7.500 slw
% Polyline
n 225 450 m 1575 450 l gs col-1 s gr
% Polyline
n 1305 450 m 1305 1080 l gs col-1 s gr
% Polyline
n 225 225 m 1575 225 l 1575 1080 l 225 1080 l cp gs col-1 s gr
% Polyline
n 225 810 m 1575 810 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
675 720 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
315 405 m
gs 1 -1 sc (Unique node) col-1 sh gr
/Times-Roman ff 150.00 scf sf
315 990 m
gs 1 -1 sc (lefttree) col-1 sh gr
% Polyline
n 225 4095 m 1575 4095 l gs col-1 s gr
% Polyline
n 1305 4095 m 1305 4455 l gs col-1 s gr
% Polyline
n 225 3870 m 1575 3870 l 1575 4455 l 225 4455 l cp gs col-1 s gr
/Times-Roman ff 150.00 scf sf
675 4365 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
315 4050 m
gs 1 -1 sc (SeqScan node (A)) col-1 sh gr
% Polyline
n 2115 4095 m 3465 4095 l gs col-1 s gr
% Polyline
n 3195 4095 m 3195 4455 l gs col-1 s gr
% Polyline
n 2115 3870 m 3465 3870 l 3465 4455 l 2115 4455 l cp gs col-1 s gr
/Times-Roman ff 150.00 scf sf
2565 4365 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2205 4050 m
gs 1 -1 sc (SeqScan node (B)) col-1 sh gr
% Polyline
n 4005 4095 m 5355 4095 l gs col-1 s gr
% Polyline
n 5085 4095 m 5085 4455 l gs col-1 s gr
% Polyline
n 4005 3870 m 5355 3870 l 5355 4455 l 4005 4455 l cp gs col-1 s gr
/Times-Roman ff 150.00 scf sf
4455 4365 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4095 4050 m
gs 1 -1 sc (SeqScan node (C)) col-1 sh gr
% Polyline
n 765 3285 m 1305 3285 l 1305 3555 l 765 3555 l cp gs col-1 s gr
% Polyline
n 1035 3285 m 1035 3555 l gs col-1 s gr
% Polyline
n 2655 3285 m 3195 3285 l 3195 3555 l 2655 3555 l cp gs col-1 s gr
% Polyline
n 2925 3285 m 2925 3555 l gs col-1 s gr
% Polyline
n 4545 3285 m 5085 3285 l 5085 3555 l 4545 3555 l cp gs col-1 s gr
% Polyline
n 4815 3285 m 4815 3555 l gs col-1 s gr
% Polyline
n 4815 3285 m 5085 3555 l gs col-1 s gr
% Polyline
n 4815 3555 m 5085 3285 l gs col-1 s gr
% Polyline
gs clippath
930 3723 m 900 3843 l 870 3723 l 870 3885 l 930 3885 l cp clip
n 900 3870 m 900 3420 l gs col-1 s gr gr
% arrowhead
n 930 3723 m 900 3843 l 870 3723 l 900 3723 l 930 3723 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
2820 3723 m 2790 3843 l 2760 3723 l 2760 3885 l 2820 3885 l cp clip
n 2790 3870 m 2790 3420 l gs col-1 s gr gr
% arrowhead
n 2820 3723 m 2790 3843 l 2760 3723 l 2790 3723 l 2820 3723 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
2508 3390 m 2628 3420 l 2508 3450 l 2670 3450 l 2670 3390 l cp clip
n 1170 3420 m 2655 3420 l gs col-1 s gr gr
% arrowhead
n 2508 3390 m 2628 3420 l 2508 3450 l 2508 3420 l 2508 3390 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
4710 3723 m 4680 3843 l 4650 3723 l 4650 3885 l 4710 3885 l cp clip
n 4680 3870 m 4680 3420 l gs col-1 s gr gr
% arrowhead
n 4710 3723 m 4680 3843 l 4650 3723 l 4680 3723 l 4710 3723 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
gs clippath
4398 3390 m 4518 3420 l 4398 3450 l 4560 3450 l 4560 3390 l cp clip
n 3060 3420 m 4545 3420 l gs col-1 s gr gr
% arrowhead
n 4398 3390 m 4518 3420 l 4398 3450 l 4398 3420 l 4398 3390 l cp gs 0.00 setgray ef gr col-1 s
% Polyline
n 4005 2250 m 5355 2250 l gs col-1 s gr
% Polyline
n 5085 2250 m 5085 2880 l gs col-1 s gr
% Polyline
n 4005 2025 m 5355 2025 l 5355 2880 l 4005 2880 l cp gs col-1 s gr
% Polyline
n 4005 2610 m 5355 2610 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
4455 2520 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4095 2790 m
gs 1 -1 sc (unionplans) col-1 sh gr
/Times-Roman ff 150.00 scf sf
4095 2205 m
gs 1 -1 sc (Append node) col-1 sh gr
% Polyline
n 2115 1350 m 3465 1350 l gs col-1 s gr
% Polyline
n 3195 1350 m 3195 1980 l gs col-1 s gr
% Polyline
n 2115 1125 m 3465 1125 l 3465 1980 l 2115 1980 l cp gs col-1 s gr
% Polyline
n 2115 1710 m 3465 1710 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
2565 1620 m
gs 1 -1 sc 90.0 rot (. . .) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2205 1305 m
gs 1 -1 sc (Sort node) col-1 sh gr
/Times-Roman ff 150.00 scf sf
2205 1890 m
gs 1 -1 sc (lefttree) col-1 sh gr
% Interp Spline
gs clippath
2702 1004 m 2770 1106 l 2661 1048 l 2781 1157 l 2821 1113 l cp clip
n 1440 945 m
1674.5 943.0 1775.8 943.0 1845 945 curveto
2019.7 950.1 2429.3 923.8 2610 990 curveto
2649.8 1004.6 2694.8 1038.3 2790 1125 curveto
gs col-1 s gr
gr
% arrowhead
n 2702 1004 m 2770 1106 l 2661 1048 l 2682 1026 l 2702 1004 l cp gs 0.00 setgray ef gr col-1 s
% Interp Spline
gs clippath
4592 1904 m 4660 2006 l 4551 1948 l 4671 2057 l 4711 2013 l cp clip
n 3330 1845 m
3564.5 1843.0 3665.8 1843.0 3735 1845 curveto
3909.7 1850.1 4319.3 1823.8 4500 1890 curveto
4539.8 1904.6 4584.8 1938.3 4680 2025 curveto
gs col-1 s gr
gr
% arrowhead
n 4592 1904 m 4660 2006 l 4551 1948 l 4572 1926 l 4592 1904 l cp gs 0.00 setgray ef gr col-1 s
% Interp Spline
gs clippath
617 3396 m 738 3421 l 619 3456 l 781 3449 l 779 3389 l cp clip
n 5220 2745 m
5223.4 2874.8 5212.2 2931.0 5175 2970 curveto
5094.8 3054.1 4900.0 3048.4 4815 3060 curveto
4423.3 3113.5 3494.7 3099.9 3105 3105 curveto
2694.9 3110.4 1715.1 3105.0 1305 3105 curveto
1202.5 3105.0 957.7 3100.3 855 3105 curveto
741.6 3110.1 477.3 3095.1 360 3150 curveto
330.0 3164.1 274.4 3195.4 270 3240 curveto
264.4 3296.9 324.9 3350.2 360 3375 curveto
400.2 3403.3 497.3 3414.7 540 3420 curveto
578.9 3424.8 635.1 3424.8 765 3420 curveto
gs col-1 s gr
gr
% arrowhead
n 617 3396 m 738 3421 l 619 3456 l 618 3426 l 617 3396 l cp gs 0.00 setgray ef gr col-1 s
showpage
$F2psEnd
rs
%%EndDocument
@endspecial 743 1945 a(Figure)g(3.10:)j Fp(Plan)e Ft(for)e(a)i(union)f
(query)198 2225 y Fn(Executor)198 2327 y Ft(The)k Fp(e)o(xecutor)h
Ft(will)e(process)h(all)g(the)f Fr(SeqScan)g Ft(nodes)i(and)e(append)h
(all)g(the)f(deli)o(v)o(ered)g(tuples)h(to)198 2387 y(a)e(single)g
Fp(r)n(esult)h(r)n(elation)p Ft(.)21 b(No)o(w)13 b(it)h(is)g(possible)h
(that)e(duplicate)h(tuples)g(are)g(contained)g(in)f(the)h
Fp(r)n(esult)198 2447 y(r)n(elation)e Ft(which)g(ha)o(v)o(e)g(to)g(be)g
(remo)o(v)o(ed.)j(The)e(remo)o(v)o(al)e(is)i(done)e(by)h(the)g
Fr(Unique)g Ft(node)f(and)h(the)g(sort)198 2506 y(is)h(just)f
(performed)f(to)h(make)g(its)h(work)e(easier)m(.)198
2674 y Fh(3.8.2)59 b(Ho)o(w)15 b(Intersect,)f(Except)g(and)h(Union)g(W)
l(ork)f(T)-5 b(ogether)198 2777 y Ft(The)20 b(last)f(section)g(sho)o
(wed)h(that)f(e)o(v)o(ery)f(stage)i(()p Fp(parser)f(stage)p
Ft(,)i Fp(planner/optimizer)p Ft(,)g Fp(e)o(xecutor)p
Ft())f(of)198 2836 y(PostgreSQL)15 b(has)h(to)g(pro)o(vide)f(features)
h(in)f(order)g(to)h(support)f Fp(union)g Ft(statements.)26
b(F)o(or)15 b(the)h(imple-)198 2896 y(mentation)f(of)h
Fp(intersect)h Ft(and)f Fp(e)o(xcept)g Ft(statements)h((and)f
(statements)g(in)n(v)o(olving)f(all)h Fp(set)h(oper)o(ators)p
Ft())198 2956 y(we)c(choose)f(a)h(dif)o(ferent)e(approach)h(based)g
(on)h Fp(query)g(r)n(e)o(writing)p Ft(.)198 3076 y(The)26
b(idea)f(is)h(based)g(on)f(the)g(fact)f(that)i Fp(intersect)f
Ft(and)g Fp(e)o(xcept)h Ft(statements)g(are)f(redundant)f(in)198
3135 y(SQL,)15 b(i.e.)21 b(for)14 b(e)o(v)o(ery)g Fp(intersect)g
Ft(or)g Fp(e)o(xcept)g Ft(statement)g(it)g(is)g(possible)h(to)f
(formulate)f(a)h(semantically)198 3195 y(equi)o(v)o(alent)d(statement)i
(without)f(using)g Fp(intersect)g Ft(or)g Fp(e)o(xcept)p
Ft(.)198 3342 y Fn(Example)h(3.5)25 b Ft(This)18 b(e)o(xample)g(sho)o
(ws)h(ho)o(w)e(a)h(query)g(using)f Fp(intersect)h Ft(can)h(be)f
(transformed)e(to)i(a)198 3402 y(semantically)12 b(equi)o(v)o(alent)g
(query)g(without)f(an)i Fp(intersect)p Ft(:)p eop
%%Page: 94 94
94 93 bop 270 60 a Ft(94)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)330
234 y Fr(select)29 b(C1,)h(C3)f(from)h(A)330 294 y(where)f(C1)h(=)g(1)
330 354 y(intersect)330 413 y(select)f(C1,)h(C2)f(from)h(B)330
473 y(where)f(C2)h(=)g('c';)270 564 y Ft(is)13 b(equi)o(v)o(alent)e
(to:)330 656 y Fr(select)29 b(C1,)h(C3)330 715 y(from)f(A)330
775 y(where)g(C1)h(=)g(1)f(and)509 835 y((C1,)h(C3))f(in)h((select)f
(C1,)g(C2)898 895 y(from)g(B)898 955 y(where)g(C2)h(=)f('c');)270
1046 y Ft(This)13 b(e)o(xample)f(sho)o(ws)h(ho)o(w)f(an)h
Fp(e)o(xcept)f Ft(query)g(can)h(be)f(transformed)f(to)i(an)f
Fp(e)o(xcept)p Ft(-less)h(form:)330 1137 y Fr(select)29
b(C1,)h(C2)f(from)h(A)330 1197 y(where)f(C2)h(=)g('c')330
1257 y(except)330 1316 y(select)f(C1,)h(C3)f(from)h(B)330
1376 y(where)f(C3)h(=)g('f';)270 1467 y Ft(is)13 b(equi)o(v)o(alent)e
(to:)330 1558 y Fr(select)29 b(C1,)h(C2)330 1618 y(from)f(A)330
1678 y(where)g(C2)h(=)g('c')f(and)509 1738 y((C1,)h(C2))f(not)h(in)f
((select)g(C1,)h(C3)1017 1798 y(from)g(B)1017 1857 y(where)g(C3)f(=)h
('f');)270 1949 y Ft(The)13 b(transformations)e(used)i(in)g(e)o
(xample)f(3.5)h(are)g(always)f(v)o(alid)g(because)h(the)o(y)g(just)f
(implement)g(the)270 2008 y(set)h(theoretic)f(de02nition)f(of)h
Fp(intersect)g Ft(and)h Fp(e)o(xcept)p Ft(:)270 2122
y Fn(De02nition)e(3.1)270 2181 y Ft(The)i Fp(intersection)f
Ft(of)g(two)g(sets)h Fk(A)g Ft(and)f Fk(B)j Ft(is)d(de02ned)h(as:)828
2280 y Fl(()p Fk(A)f Fo(\)f Fk(B)r Fl())k(:=)e Fo(f)p
Fk(x)h Fo(j)g Fk(x)g Fo(2)g Fk(A)e Fo(^)f Fk(x)k Fo(2)f
Fk(B)r Fo(g)270 2380 y Ft(The)f Fp(intersection)f Ft(of)g
Fk(n)g Ft(sets)i Fk(A)828 2387 y Fg(1)850 2380 y Fk(;)8
b(:)g(:)g(:)h(;)f(A)998 2387 y Ff(n)1038 2380 y Ft(is)13
b(de02ned)f(as:)918 2453 y Ff(n)903 2468 y Fb(\)899
2575 y Ff(i)p Fg(=1)971 2516 y Fk(A)1008 2523 y Ff(i)1039
2516 y Fl(:=)i Fo(f)p Fk(x)g Fo(j)1220 2453 y Ff(n)1205
2468 y Fb(^)1200 2575 y Ff(i)p Fg(=1)1273 2516 y Fk(x)h
Fo(2)f Fk(A)1400 2523 y Ff(i)1417 2516 y Fo(g)270 2672
y Fn(De02nition)d(3.2)270 2732 y Ft(The)i Fp(dif)o(fer)n(ence)g
Ft(of)f(two)f(sets)j Fk(A)e Ft(and)h Fk(B)i Ft(is)d(de02ned)g(as:)843
2831 y Fl(()p Fk(A)p Fo(n)p Fk(B)r Fl())k(:=)d Fo(f)p
Fk(x)h Fo(j)g Fk(x)g Fo(2)g Fk(A)e Fo(^)f Fk(x)j Fo(62)g
Fk(B)r Fo(g)270 2944 y Fn(De02nition)d(3.3)270 3004
y Ft(The)i Fp(union)f Ft(of)g(two)f(sets)j Fk(A)e Ft(and)h
Fk(B)i Ft(is)d(de02ned)g(as:)828 3103 y Fl(()p Fk(A)g
Fo([)f Fk(B)r Fl())k(:=)e Fo(f)p Fk(x)h Fo(j)g Fk(x)g
Fo(2)g Fk(A)e Fo(_)f Fk(x)k Fo(2)f Fk(B)r Fo(g)270 3202
y Ft(The)f Fp(union)f Ft(of)g Fk(n)g Ft(sets)h Fk(A)706
3209 y Fg(1)729 3202 y Fk(;)8 b(:)g(:)g(:)h(;)f(A)877
3209 y Ff(n)917 3202 y Ft(is)k(de02ned)g(as:)918 3276
y Ff(n)903 3291 y Fb([)899 3397 y Ff(i)p Fg(=1)971 3338
y Fk(A)1008 3345 y Ff(i)1039 3338 y Fl(:=)i Fo(f)p Fk(x)g
Fo(j)1220 3276 y Ff(n)1205 3291 y Fb(_)1200 3397 y Ff(i)p
Fg(=1)1273 3338 y Fk(x)h Fo(2)f Fk(A)1400 3345 y Ff(i)1417
3338 y Fo(g)p eop
%%Page: 95 95
95 94 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(95)198
234 y Fn(De02nition)11 b(3.4)25 b Ft(Disjuncti)o(v)o(e)12
b(Normal)g(F)o(orm)f((DNF))198 294 y(Let)k Fk(F)24
b Fl(=)17 b Fk(C)427 301 y Fg(1)461 294 y Fo(_)c Fk(:)8
b(:)g(:)13 b Fo(_)f Fk(C)659 301 y Ff(n)700 294 y Ft(be)j(gi)o(v)o(en)f
(where)g(e)o(v)o(ery)g Fk(C)1180 301 y Ff(i)1210 294
y Ft(is)h(of)f(the)g(form)f Fl(()p Fk(L)1553 276 y Fg(1)1553
307 y Ff(i)1588 294 y Fo(^)g Fk(:)8 b(:)g(:)k Fo(^)h
Fk(L)1784 272 y Ff(k)1805 277 y Fa(i)1784 308 y Ff(i)1823
294 y Fl())i Ft(and)f Fk(L)1977 270 y Ff(j)1977 308
y(i)198 354 y Ft(is)g(a)f(propositional)g(v)o(ariable)f(or)h(the)h(ne)o
(gation)f(of)g(a)g(propositional)g(v)o(ariable.)18 b(No)o(w)13
b(we)g(say)h Fk(F)21 b Ft(is)13 b(in)198 413 y(DNF)l(.)198
549 y Fn(Example)g(3.6)25 b Ft(In)12 b(the)g(follo)o(wing)f(e)o(xample)
h(the)h Fk(L)1106 525 y Ff(j)1106 563 y(i)1139 549 y
Ft(are)f(of)g(the)h(form)e Fk(x)j Fo(2)g Fk(X)j Ft(or)12
b Fo(:)p Fl(()p Fk(x)j Fo(2)f Fk(X)t Fl())p Ft(:)273
609 y Fl((()p Fk(x)h Fo(2)f Fk(A)d Fo(^)h(:)p Fl(()p
Fk(x)i Fo(2)g Fk(B)r Fl())e Fo(^)f Fk(x)k Fo(2)f Fk(C)t
Fl())c Fo(_)i Fl(()p Fk(x)i Fo(2)g Fk(D)f Fo(^)e Fk(x)k
Fo(2)f Fk(E)s Fl()))f Ft(is)f(a)h(formula)e(in)h(DNF)273
669 y Fl((()p Fk(x)j Fo(2)f Fk(A)d Fo(_)h Fk(x)i Fo(2)g
Fk(B)r Fl())e Fo(^)f Fl(()p Fk(x)k Fo(2)f Fk(C)g Fo(_)d(:)p
Fl(()p Fk(x)k Fo(2)f Fk(D)q Fl())))g Ft(is)f(not)f(in)g(DNF)l(.)198
779 y(The)g(transformation)e(of)i(an)o(y)g(formula)e(in)i
(propositional)e(logic)i(into)f(DNF)h(is)g(done)f(by)h(successi)o(v)o
(ely)198 839 y(applying)g(the)g(follo)o(wing)f(rules:)273
959 y Fl(()p Fk(R)p Fl(1))25 b Fo(:)p Fl(()p Fk(F)483
966 y Fg(1)518 959 y Fo(_)11 b Fk(F)594 966 y Fg(2)616
959 y Fl())j Fo())g Fl(()p Fo(:)p Fk(F)797 966 y Fg(1)831
959 y Fo(^)d(:)p Fk(F)940 966 y Fg(2)963 959 y Fl())273
1018 y(()p Fk(R)p Fl(2))25 b Fo(:)p Fl(()p Fk(F)483
1025 y Fg(1)518 1018 y Fo(^)11 b Fk(F)594 1025 y Fg(2)616
1018 y Fl())j Fo())g Fl(()p Fo(:)p Fk(F)797 1025 y
Fg(1)831 1018 y Fo(_)d(:)p Fk(F)940 1025 y Fg(2)963 1018
y Fl())273 1078 y(()p Fk(R)p Fl(3))25 b Fk(F)431 1085
y Fg(1)465 1078 y Fo(^)11 b Fl(()p Fk(F)560 1085 y Fg(2)594
1078 y Fo(_)g Fk(F)670 1085 y Fg(3)693 1078 y Fl())j
Fo())g Fl(()p Fk(F)841 1085 y Fg(1)874 1078 y Fo(^)e
Fk(F)951 1085 y Fg(2)973 1078 y Fl())f Fo(_)h Fl(()p
Fk(F)1099 1085 y Fg(1)1133 1078 y Fo(^)f Fk(F)1209 1085
y Fg(3)1231 1078 y Fl())273 1138 y(()p Fk(R)p Fl(4))25
b(()p Fk(F)450 1145 y Fg(1)484 1138 y Fo(_)12 b Fk(F)561
1145 y Fg(2)583 1138 y Fl())f Fo(^)h Fk(F)690 1145 y
Fg(3)726 1138 y Fo())i Fl(()p Fk(F)841 1145 y Fg(1)874
1138 y Fo(^)e Fk(F)951 1145 y Fg(3)973 1138 y Fl())f
Fo(_)h Fl(()p Fk(F)1099 1145 y Fg(2)1133 1138 y Fo(^)f
Fk(F)1209 1145 y Fg(3)1231 1138 y Fl())198 1257 y Ft(It)24
b(can)i(be)f(sho)o(wn)f(that)h(the)g(transformation)e(using)i(the)g
(rules)g((R1))f(to)h((R4))f(always)g(termi-)198 1317
y(nates)13 b(after)f(a)g(02nite)g(number)g(of)g(steps.)198
1465 y Fn(Set)g(Operations)f(as)i(Pr)o(opositional)f(Logic)g(F)o
(ormulas)198 1563 y Ft(Using)j(the)f(de02nitions)h(from)e(abo)o(v)o
(e)i(we)g(can)g(treat)g(formulas)e(in)n(v)o(olving)h(set)h(theoretic)f
(operations)198 1623 y(as)d(formulas)f(of)h Fp(pr)n(opositional)f
(logic)p Ft(.)k(As)d(we)g(will)f(see)i(later)e(these)h(formulas)f(can)h
(easily)g(be)g(used)g(in)198 1683 y(the)h Fr(where-)g
Ft(and)g Fr(having)g Ft(quali02cations)g(of)g(the)g
Fr(select)g Ft(statements)h(in)n(v)o(olv)o(ed)f(in)g(the)h(query)m(.)
198 1819 y Fn(Example)g(3.7)25 b Ft(Here)12 b(are)g(some)h(e)o
(xamples:)273 1938 y Fl((()p Fk(A)f Fo([)f Fk(B)r Fl())h
Fo(\)f Fk(C)t Fl())j(:=)f Fo(f)p Fk(x)h Fo(j)g Fl(()p
Fk(x)h Fo(2)f Fk(A)d Fo(_)g Fk(x)k Fo(2)f Fk(B)r Fl())d
Fk(A)e Fo([)f Fk(B)r Fl())h Fo(\)f Fl(()p Fk(C)k Fo([)c
Fk(D)q Fl()))k(:=)f Fo(f)p Fk(x)g Fo(j)g Fl(()p Fk(x)g
Fo(2)g Fk(A)e Fo(_)f Fk(x)j Fo(2)g Fk(B)r Fl())e Fo(^)f
Fl(()p Fk(x)k Fo(2)f Fk(C)g Fo(_)e Fk(x)i Fo(2)g Fk(D)q
Fl())p Fo(g)273 2058 y Fl((()p Fk(A)e Fo(\)f Fk(B)r
Fl())p Fo(n)p Fk(C)t Fl())j(:=)g Fo(f)p Fk(x)g Fo(j)f
Fl(()p Fk(x)i Fo(2)f Fk(A)e Fo(^)f Fk(x)j Fo(2)g Fk(B)r
Fl())e Fo(^)f Fk(x)k Fo(62)f Fk(C)t Fo(g)273 2117 y
Fl(()p Fk(A)p Fo(n)p Fl(()p Fk(B)g Fo([)d Fk(C)t Fl()))j(:=)g
Fo(f)p Fk(x)g Fo(j)f Fk(x)i Fo(2)f Fk(A)d Fo(^)g(:)p
Fl(()p Fk(x)k Fo(2)f Fk(B)g Fo(_)d Fk(x)j Fo(2)g Fk(C)t
Fl())p Fo(g)273 2177 y Fl(((()p Fk(A)e Fo(\)g Fk(B)r
Fl())f Fo([)h Fl(()p Fk(C)t Fo(n)p Fk(D)q Fl()))f
Fo(\)h Fk(E)s Fl())i(:=)f Fo(f)p Fl((()p Fk(x)i Fo(2)f
Fk(A)d Fo(^)h Fk(x)i Fo(2)g Fk(B)r Fl())e Fo(_)f Fl(()p
Fk(x)k Fo(2)f Fk(C)g Fo(^)d Fk(x)k Fo(62)f Fk(D)q Fl()))e
Fo(^)f Fk(x)k Fo(2)f Fk(E)s Fo(g)198 2335 y Fh(3.8.3)59
b(Implementing)19 b(Intersect)h(and)g(Except)g(Using)g(the)g(Union)h
(Capabili-)377 2404 y(ties)198 2503 y Ft(W)l(e)14 b(want)g(to)g(be)g
(able)g(to)g(use)h(queries)f(in)n(v)o(olving)f(more)h(than)g(just)g
(one)g(type)g(of)g(set)g(operation)f((e.g.)198 2563
y(only)18 b Fp(union)g Ft(or)g(only)g Fp(intersect)p
Ft())g(at)g(a)h(time,)h(so)f(we)f(ha)o(v)o(e)h(to)f(look)g(for)g(a)g
(solution)g(that)g(supports)198 2622 y(correct)12 b(handling)h(of)g
(queries)g(like)f(that.)18 b(As)13 b(described)g(abo)o(v)o(e)h(there)e
(is)i(a)f(solution)g(for)f(pure)h Fp(union)198 2682 y
Ft(statements)19 b(implemented)f(already)m(,)i(so)f(we)g(ha)o(v)o(e)g
(to)g(de)o(v)o(elop)f(an)h(approach)f(that)h(makes)f(use)i(of)198
2742 y(these)13 b Fp(union)f Ft(capabilities.)198 2862
y(As)27 b(02gure)g(3.9)g(illustrates,)k(the)c(operands)g(of)f(a)i
Fp(union)e Ft(operation)g(are)h(just)g Fr(Query)g Ft(nodes)198
2921 y((the)14 b(02rst)h(operand)f(is)h(the)g(top)g(node)f(and)h
(all)g(further)e(operands)i(form)f(a)h(list)g(which)f(is)h(attached)g
(to)198 2981 y(the)h(02eld)f Fr(unionClause)g Ft(of)g(the)g(top)h
(node).)25 b(So)15 b(our)h(goal)f(will)g(be)h(to)g(transform)e(e)o(v)o
(ery)i(query)198 3041 y(in)n(v)o(olving)11 b(set)i(operations)f(into)f
(this)i(form.)h((Note)e(that)g(the)g(operands)g(to)g(the)g
Fp(union)g Ft(operation)f(may)198 3101 y(be)h(comple)o(x,)h(i.e.)k
Fp(subselects)p Ft(,)c Fp(gr)n(ouping)p Ft(,)g Fp(aggr)n(e)n(gates)f
Ft(etc.)k(are)c(allo)o(wed.))273 3163 y(The)k(transformation)e(of)i(a)
g(query)f(in)n(v)o(olving)g(set)i(operations)e(in)h(an)o(y)g(order)f
(into)h(a)g(query)f(that)198 3223 y(can)g(be)f(accepted)h(by)g(the)f
Fp(union)g Ft(logic)g(is)h(equi)o(v)o(alent)f(to)g(transforming)f(the)i
Fp(membership)f(formula)198 3283 y Ft((see)d(de02nitions)g(3.1,)h
(3.2)f(and)g(3.3))g(in)g(propositional)f(logic)g(into)h
Fp(disjunctive)g(normal)g(form)g Ft((DNF).)198 3342
y(The)j(transformation)d(of)i(an)o(y)g(formula)f(in)h(propositional)g
(logic)g(into)f(DNF)h(is)h(always)e(possible)i(in)f(a)198
3402 y(02nite)f(number)g(of)f(steps.)p eop
%%Page: 96 96
96 95 bop 270 60 a Ft(96)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)270
234 y Ft(The)18 b(adv)o(antage)f(of)g(this)g Fp(tr)o(ansformation)h
(tec)o(hnique)f Ft(is)g(the)h(little)e(impact)h(on)g(the)h(whole)f
(system)270 294 y(and)12 b(the)f(implicit)g(in)n(v)o(ocation)g(of)g
(the)h Fp(planner/optimizer)p Ft(.)k(The)c(only)f(changes)h(necessary)h
(are)e(made)270 354 y(to)h(the)h Fp(parser)g(stage)e
Ft(and)i(the)f Fp(r)n(e)o(write)i(system)p Ft(.)270 473
y(Here)24 b(are)f(some)h(changes)g(that)g(had)f(to)h(be)g(applied)f(to)
h(the)f(source)h(code)g(before)f(the)g Fp(parser)270
533 y(stage)12 b Ft(and)g(the)g Fp(r)n(e)o(write)i(system)f
Ft(could)f(be)h(adapted:)345 636 y Fo(17)25 b Ft(Add)19
b(the)g(additional)g(02eld)g Fr(intersectClause)f Ft(to)h(the)g(data)
h(structures)f Fr(Query)g Ft(and)395 695 y Fr(InsertStmt)11
b Ft(de02ned)h(in)g(the)g(02le)h Fk(:)8 b(:)g(:)p
Fr(/src/include/nodes/parsenodes)o(.h)p Ft(:)454 819
y Fr(typedef)29 b(struct)h(Query)454 878 y({)514 938
y(NodeTag)119 b(type;)514 998 y(CmdType)g(commandType;)753
1058 y(.)753 1118 y(.)753 1177 y(.)514 1237 y(Node)209
b(*havingQual;)395 1297 y(+)89 b(List)209 b(*intersectClause;)514
1357 y(List)g(*unionClause;)514 1416 y(List)g(*base_relation_list_;)514
1476 y(List)g(*join_relation_list_;)454 1536 y(})30 b(Query;)454
1656 y(typedef)f(struct)h(InsertStmt)454 1715 y({)514
1775 y(NodeTag)119 b(type;)753 1835 y(.)753 1895 y(.)753
1954 y(.)514 2014 y(bool)209 b(unionall;)395 2074 y(+)89
b(List)209 b(*intersectClause;)454 2134 y(})30 b(InsertStmt;)345
2257 y Fo(17)25 b Ft(Add)63 b(the)g(ne)o(w)g(ke)o(ywords)g
Fr(EXCEPT)g Ft(and)g Fr(INTERSECT)g Ft(to)g(the)g(02le)395
2317 y Fk(:)8 b(:)g(:)p Fr(/src/backend/parser/keywords.)o(c)p
Ft(:)454 2440 y Fr(static)30 b(ScanKeyword)e(ScanKeywords[])g(=)i({)514
2500 y({"abort",)f(ABORT_TRANS},)514 2560 y({"action",)g(ACTION},)813
2620 y(.)813 2679 y(.)813 2739 y(.)514 2799 y({"end",)g(END_TRANS},)395
2859 y(+)89 b({"except",)29 b(EXCEPT},)813 2919 y(.)813
2978 y(.)813 3038 y(.)514 3098 y({"instead",)g(INSTEAD},)395
3158 y(+)89 b({"intersect",)29 b(INTERSECT},)813 3217
y(.)813 3277 y(.)813 3337 y(.)454 3397 y(};)p eop
%%Page: 97 97
97 96 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(97)273
234 y Fo(17)25 b Ft(PostgreSQL)14 b(contains)h(functions)g(to)f(con)n
(v)o(ert)h(the)g(internal)f(representation)g(of)h(a)g
Fp(parsetr)n(ee)323 294 y Ft(or)j Fp(plantr)n(ee)g Ft(into)g(an)h
(ASCII)f(representation)f((that)h(can)h(easily)g(be)f(printed)g(to)g
(the)h(screen)323 354 y((for)f(deb)o(ugging)g(purposes))i(or)f(be)g
(stored)g(in)h(a)f(02le))g(and)g(vice)h(v)o(ersa.)37
b(These)21 b(functions)323 413 y(ha)o(v)o(e)g(to)g(be)h(adapted)f(to)g
(be)g(able)g(to)g(deal)h(with)f Fp(intersects)g Ft(and)h
Fp(e)o(xcepts)p Ft(.)43 b(These)22 b(func-)323 473 y(tions)e(can)h(be)g
(found)f(in)h(the)f(02les)h Fk(:)8 b(:)g(:)p Fr
(/src/backend/nodes/outfuncs.c)18 b Ft(and)323 533 y
Fk(:)8 b(:)g(:)p Fr(/src/backend/nodes/readfuncs.)o(c)p
Ft(:)382 675 y Fr(static)30 b(void)382 735 y(_outQuery(StringInfo)e
(str,)h(Query)h(*node))382 795 y({)1159 854 y(.)1159
914 y(.)1159 974 y(.)442 1034 y(appendStringInfo(str,)e(")h
(:unionClause)g(");)442 1094 y(_outNode(str,)g(node->unionClause);)
323 1153 y(+)89 b(appendStringInfo(str,)28 b(")h(:intersectClause)f
(");)323 1213 y(+)89 b(_outNode(str,)29 b(node->intersectClause);)
382 1273 y(})382 1392 y(static)h(Query)f(*)382 1452 y(_readQuery())
382 1512 y({)1159 1572 y(.)1159 1632 y(.)1159 1691 y(.)442
1751 y(token)g(=)h(lsptok(NULL,)f(&length);)442 1811
y(local_node->unionClause)e(=)j(nodeRead(true);)323
1871 y(+)89 b(token)29 b(=)h(lsptok(NULL,)f(&length);)323
1930 y(+)89 b(local_node->intersectClause)27 b(=)j(nodeRead(true);)
442 2050 y(return)f((local_node);)382 2110 y(})273
2252 y Fo(17)c Ft(The)14 b(function)g Fr(ExecReScan())f
Ft(is)h(called)h(whene)o(v)o(er)f(a)g(ne)o(w)g(e)o(x)o(ecution)h(of)f
(a)g(gi)o(v)o(en)g Fp(plan)323 2312 y Ft(has)j(to)f(be)h(started)g
((i.e.)29 b(whene)o(v)o(er)16 b(we)h(ha)o(v)o(e)g(to)f(start)h(from)f
(the)g(be)o(ginning)g(with)h(the)f(02rst)323 2371 y(tuple)k(again).)
41 b(The)21 b(call)g(to)f(this)h(function)f(happens)h(implicitly)m(.)40
b(F)o(or)20 b(the)h(special)g(kind)323 2431 y(of)16 b(subqueries)h(we)g
(are)g(using)f(for)g(the)h(re)o(written)f(queries)g((see)h(e)o(xample)
g(3.5))g(we)g(ha)o(v)o(e)g(to)323 2491 y(take)g(that)g(also)i
Fr(Group)e Ft(nodes)h(are)g(processed.)32 b(The)18 b(function)f(can)h
(be)g(found)f(in)h(the)f(02le)323 2551 y Fk(l)q(dots)p
Fr(/backend/executor/execAmi.c)p Ft(.)382 2693 y Fr(void)382
2753 y(ExecReScan(Plan)29 b(*node,)g(ExprContext)f(*exprCtxt,)711
2812 y(Plan)i(*parent))382 2872 y({)1159 2932 y(.)1159
2992 y(.)1159 3052 y(.)472 3111 y(switch)f((nodeTag(node)))472
3171 y({)1159 3231 y(.)1159 3291 y(.)1159 3350 y(.)p
eop
%%Page: 98 98
98 97 bop 270 60 a Ft(98)82 b Fm(CHAPTER)14 b(3.)28 b(POSTGRESQL)13
b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)395
234 y Fr(+)179 b(case)29 b(T_Group:)395 294 y(+)388 b
(ExecReScanGroup((Group)27 b(*))j(node,)395 354 y(+)896
b(exprCtxt,)29 b(parent);)395 413 y(+)388 b(break;)1231
473 y(.)1231 533 y(.)1231 593 y(.)544 653 y(})454 712
y(})345 933 y Fo(17)25 b Ft(The)g(function)e Fr(ExecReScanGroup())g
Ft(is)i(called)f(by)h Fr(ExecReScan())e Ft(described)395
993 y(abo)o(v)o(e)33 b(whene)o(v)o(er)g(a)g Fr(Group)g
Ft(node)g(is)g(detected)g(and)g(can)h(be)f(found)f(in)h(the)g(02le)
395 1053 y Fk(:)8 b(:)g(:)p Fr(/src/backend/executor/nodeGro)o(up.c)i
Ft(.)18 b(It)13 b(has)g(been)g(created)g(for)g(the)g
Fp(in-)395 1112 y(tersect)e Ft(and)f Fp(e)o(xcept)h Ft(logic)f
(although)g(it)h(is)g(actually)f(needed)h(by)f(the)h(special)g(kind)f
(of)g(subselect)395 1172 y((see)i(abo)o(v)o(e).)454
1365 y Fr(void)454 1424 y(ExecReScanGroup(Group)28 b(*node,)h
(ExprContext)g(*exprCtxt,)933 1484 y(Plan)g(*parent))454
1544 y({)514 1604 y(GroupState)g(*grpstate)g(=)h(node->grpstate;)514
1723 y(grpstate->grp_useFirstTuple)d(=)j(FALSE;)514 1783
y(grpstate->grp_done)e(=)i(FALSE;)514 1843 y(grpstate->grp_firstTuple)d
(=)j((HeapTupleData)e(*)NIL;)514 1962 y(/*)544 2022
y(*)i(if)f(chgParam)g(of)h(subnode)f(is)h(not)f(null)h(then)f(plan)544
2082 y(*)h(will)f(be)h(re-scanned)f(by)g(first)h(ExecProcNode.)544
2142 y(*/)514 2201 y(if)g((((Plan)f(*))h
(node)->lefttree->chgParam)d(==)i(NULL))574 2261 y
(ExecReScan(((Plan)f(*))i(node)->lefttree,)903 2321
y(exprCtxt,)f((Plan)g(*))g(node);)454 2381 y(})270
2614 y Fn(Parser)270 2742 y Ft(The)12 b Fp(parser)g Ft(de02ned)f(in)g
(the)g(02le)g Fk(:)d(:)g(:)p Fr(/src/backend/parser/gram.y)g
Ft(had)j(to)h(be)f(modi02ed)270 2801 y(in)h(two)g(ways:)345
2956 y Fo(17)25 b Ft(The)17 b(grammar)f(had)g(to)h(be)g(adapted)g(to)
f(support)h(the)g(usage)g(of)f(parenthesis)h((to)f(be)h(able)g(to)395
3016 y(specify)12 b(the)g(order)g(of)g(e)o(x)o(ecution)g(of)g(the)h
(set)f(operators).)345 3188 y Fo(17)25 b Ft(The)10
b(code)f(b)o(uilding)g(up)h(the)f(data)h(structures)f(handed)h(back)g
(by)f(the)h Fp(parser)g Ft(had)g(to)f(be)h(inserted.)270
3342 y(Here)h(is)h(a)f(part)g(of)f(the)i(grammar)e(which)h(is)g
(responsible)g(for)g Fr(select)f Ft(statements)i(ha)o(ving)f(the)g
(code)270 3402 y(b)o(uilding)h(up)g(the)g(data)h(structures)f
(inserted:)p eop
%%Page: 99 99
99 98 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6 b(TION)14
b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)349 b Ft(99)258
234 y Fr(SelectStmt)29 b(:)59 b(select_w_o_sort)28 b(sort_clause)377
294 y({)1035 354 y(.)1035 413 y(.)1035 473 y(.)467 533
y(/*)i($1)f(holds)h(the)f(tree)h(built)f(up)h(by)f(the)497
593 y(*)h(rule)f('select_w_o_sort')497 653 y(*/)467 712
y(Node)g(*op)h(=)g((Node)f(*))h($1;)467 832 y(if)g(IsA($1,)f
(SelectStmt))467 892 y({)527 951 y(SelectStmt)g(*n)g(=)h((SelectStmt)
f(*)$1;)527 1011 y(n->sortClause)f(=)i($2;)527 1071
y($$)f(=)h((Node)g(*)n;)467 1131 y(})467 1191 y(else)467
1250 y({)527 1310 y(/*)f(Create)h(a)f("flat)h(list")f(of)h(the)f
(operator)557 1370 y(*)g(tree)h(built)f(up)h(by)g('select_w_o_sort')e
(and)557 1430 y(*)h(let)h(select_list)f(point)g(to)h(it)557
1489 y(*/)527 1549 y(create_select_list((Node)d(*)op,)1095
1609 y(&select_list,)1095 1669 y(&unionall_present);)527
1729 y(/*)i(Replace)h(all)f(the)h(A_Expr)f(nodes)g(in)h(the)557
1788 y(*)f(operator)g(tree)h(by)g(Expr)f(nodes.)557 1848
y(*/)557 1908 y(op)g(=)h(A_Expr_to_Expr(op,)e(&intersect_present);)
1035 1968 y(.)1035 2027 y(.)1035 2087 y(.)557 2147 y(/*)h(Get)h(the)f
(leftmost)g(SelectStmt)g(node)h((which)587 2207 y(*)f(automatically)g
(represents)g(the)g(first)g(Select)587 2267 y(*)g(Statement)g(of)h(the)
f(query!))h(*/)557 2326 y(first_select)e(=)766 2386
y((SelectStmt)h(*)lfirst(select_list);)557 2446 y(/*)g(Attach)h
(the)f(list)h(of)f(all)h(SelectStmt)f(nodes)587 2506
y(*)g(to)h(unionClause)587 2565 y(*/)557 2625 y
(first_select->unionClause)d(=)j(select_list;)557 2745
y(/*)f(Attach)h(the)f(whole)g(operator)g(tree)h(to)587
2804 y(*)f(intersectClause)g(*/)557 2864 y
(first_select->intersectClause)d(=)1423 2924 y((List)k(*))f(op;)557
2984 y(/*)g(finally)g(attach)h(the)f(sort)h(clause)f(*/)557
3044 y(first_select->sortClause)e(=)j($2;)557 3163 y(/*)f(Now)h(hand)f
(it)h(back!)f(*/)557 3223 y($$)g(=)h((Node)f(*)first_select;)497
3283 y(})377 3342 y(})258 3402 y(;)p eop
%%Page: 100 100
100 99 bop 270 60 a Ft(100)57 b Fm(CHAPTER)14 b(3.)28
b(POSTGRESQL)13 b(FR)n(OM)f(THE)i(PR)n(OGRAMMER'S)f(POINT)f(OF)g(VIEW)
330 234 y Fr(select_w_o_sort)28 b(:)60 b('(')29 b(select_w_o_sort)f
(')')449 294 y({)509 354 y($$)i(=)g($2;)449 413 y(})330
473 y(|)59 b(SubSelect)449 533 y({)509 593 y($$)30 b(=)g($1;)449
653 y(})330 712 y(|)59 b(select_w_o_sort)29 b(EXCEPT)g(select_w_o_sort)
449 772 y({)509 832 y($$)h(=)g((Node)f(*)makeA_Expr(AND,NULL,$1,)
1017 892 y(makeA_Expr(NOT,NULL,NULL,$3));)449 951
y(})330 1011 y(|)59 b(select_w_o_sort)29 b(UNION)g(opt_union)g
(select_w_o_sort)449 1071 y({)509 1131 y(if)h((IsA($4,)f
(SelectStmt)))509 1191 y({)569 1250 y(SelectStmt)g(*n)g(=)h
((SelectStmt)f(*)$4;)569 1310 y(n->unionall)g(=)g($3;)509
1370 y(})509 1430 y($$)h(=)g((Node)f(*)makeA_Expr(OR,NULL,$1,$4);)
449 1489 y(})330 1549 y(|)59 b(select_w_o_sort)29 b(INTERSECT)g
(select_w_o_sort)449 1609 y({)509 1669 y($$)h(=)g((Node)f
(*)makeA_Expr(AND,NULL,$1,$3);)449 1729 y(})330 1788
y(;)330 1896 y(SubSelect)g(:)59 b(SELECT)30 b(opt_unique)e
(res_target_list2)718 1956 y(result)i(from_clause)e(where_clause)718
2016 y(group_clause)h(having_clause)390 2076 y({)449
2135 y(SelectStmt)g(*n)h(=)g(makeNode(SelectStmt);)449
2195 y(n->unique)f(=)h($2;)688 2255 y(.)688 2315 y(.)688
2375 y(.)449 2434 y(n->havingClause)f(=)g($8;)449 2494
y($$)h(=)g((Node)f(*)n;)390 2554 y(})330 2614 y(;)270
2722 y Ft(The)e(ke)o(ywords)f Fr(SELECT)p Ft(,)h Fr(EXCEPT)p
Ft(,)g Fr(UNION)p Ft(,)g Fr(INTERSECT)p Ft(,)f Fr('(')h
Ft(and)g Fr(')')f Ft(are)h Fp(termi-)270 2782 y(nal)f(symbols)h
Ft(and)f Fr(SelectStmt)p Ft(,)k Fr(select)p 1170 2782
15 2 v 17 w(w)p 1217 2782 V 18 w(o)p 1265 2782 V 17 w(sort)p
Ft(,)g Fr(sort)p 1564 2782 V 18 w(clause)p Ft(,)g Fr(opt)p
1894 2782 V 17 w(union)p Ft(,)270 2841 y Fr(SubSelect)p
Ft(,)57 b Fr(opt)p 702 2841 V 18 w(unique)p Ft(,)h Fr(res)p
1060 2841 V 17 w(target)p 1257 2841 V 17 w(list2)p Ft(,)g
Fr(result)p Ft(,)g Fr(from)p 1864 2841 V 17 w(clause)p
Ft(,)270 2901 y Fr(where)p 423 2901 V 17 w(clause)p Ft(,)23
b Fr(group)p 805 2901 V 18 w(clause)p Ft(,)g Fr(having)p
1218 2901 V 17 w(clause)d Ft(are)h Fp(nonterminal)g(symbols)p
Ft(.)42 b(The)270 2961 y(symbols)13 b Fr(EXCEPT)p Ft(,)h
Fr(UNION)e Ft(and)i Fr(INTERSECT)e Ft(are)h Fp(left)g(associative)h
Ft(meaning)f(that)g(a)g(statement)270 3021 y(like:)330
3119 y Fr(select)29 b(*)h(from)f(A)330 3179 y(union)330
3239 y(select)g(*)h(from)f(B)330 3298 y(union)330 3358
y(select)g(*)h(from)f(C;)p eop
%%Page: 101 101
101 100 bop 198 60 a Fm(3.8.)29 b(THE)13 b(REALIZA)-6
b(TION)14 b(OF)e(UNION,)g(INTERSECT)i(AND)f(EXCEPT)324
b Ft(101)198 234 y(will)12 b(be)g(treated)g(as:)258 343
y Fr(((select)29 b(*)h(from)f(A)318 403 y(union)318
463 y(select)g(*)h(from)f(B))318 523 y(union)318 583
y(select)g(*)h(from)f(C))198 689 y Ft(The)13 b Fr(select)p
471 689 15 2 v 17 w(w)p 518 689 V 18 w(o)p 566 689 V
18 w(sort)f Ft(rule)g(b)o(uilds)g(up)h(an)f Fp(oper)o(ator)h(tr)n(ee)g
Ft(using)g(nodes)g(of)f(type)g Fr(A)p 1767 689 V 18 w(Expr)p
Ft(.)k(F)o(or)198 749 y(e)o(v)o(ery)f Fp(union)g Ft(an)h
Fr(OR)f Ft(node)g(is)h(created,)g(for)f(e)o(v)o(ery)g
Fp(intersect)h Ft(an)f Fr(AND)g Ft(node)h(and)f(for)g(e)o(v)o(ery)g
Fp(e)o(xcept)198 809 y Ft(and)d Fr(AND)30 b(NOT)12 b
Ft(node)g(b)o(uilding)f(up)h(a)g(representation)f(of)h(a)g(formula)f
(in)h(propositional)f(logic.)16 b(If)11 b(the)198 869
y(query)h(parsed)h(did)g(not)g(contain)f(an)o(y)i(set)f(operations)g
(the)f(rule)h(hands)g(back)g(a)g Fr(SelectStmt)f Ft(node)198
929 y(representing)g(the)h(query)f(otherwise)h(the)f(top)h(node)g(of)f
(the)h Fp(oper)o(ator)g(tr)n(ee)h Ft(is)f(returned.)j(Figure)c(3.11)198
988 y(sho)o(ws)h(a)f(typical)g Fp(oper)o(ator)h(tr)n(ee)g
Ft(returned)f(by)g(the)g Fr(select)p 1287 988 V 18 w(w)p
1335 988 V 18 w(o)p 1383 988 V 17 w(sort)g Ft(rule.)388
2579 y @beginspecial 145 @llx 244 @lly 467 @urx 547 @ury
3220 @rwi @setspecial
%%BeginDocument: figures/union_intersect_select_w_o_sort.ps
%Magnification: 1.05
/$F2psDict 200 dict def
$F2psDict begin
$F2psDict /mtrx matrix put
/col-1 {0 setgray} bind def
/col0 {0.000 0.000 0.000 srgb} bind def
/col1 {0.000 0.000 1.000 srgb} bind def
/col2 {0.000 1.000 0.000 srgb} bind def
/col3 {0.000 1.000 1.000 srgb} bind def
/col4 {1.000 0.000 0.000 srgb} bind def
/col5 {1.000 0.000 1.000 srgb} bind def
/col6 {1.000 1.000 0.000 srgb} bind def
/col7 {1.000 1.000 1.000 srgb} bind def
/col8 {0.000 0.000 0.560 srgb} bind def
/col9 {0.000 0.000 0.690 srgb} bind def
/col10 {0.000 0.000 0.820 srgb} bind def
/col11 {0.530 0.810 1.000 srgb} bind def
/col12 {0.000 0.560 0.000 srgb} bind def
/col13 {0.000 0.690 0.000 srgb} bind def
/col14 {0.000 0.820 0.000 srgb} bind def
/col15 {0.000 0.560 0.560 srgb} bind def
/col16 {0.000 0.690 0.690 srgb} bind def
/col17 {0.000 0.820 0.820 srgb} bind def
/col18 {0.560 0.000 0.000 srgb} bind def
/col19 {0.690 0.000 0.000 srgb} bind def
/col20 {0.820 0.000 0.000 srgb} bind def
/col21 {0.560 0.000 0.560 srgb} bind def
/col22 {0.690 0.000 0.690 srgb} bind def
/col23 {0.820 0.000 0.820 srgb} bind def
/col24 {0.500 0.190 0.000 srgb} bind def
/col25 {0.630 0.250 0.000 srgb} bind def
/col26 {0.750 0.380 0.000 srgb} bind def
/col27 {1.000 0.500 0.500 srgb} bind def
/col28 {1.000 0.630 0.630 srgb} bind def
/col29 {1.000 0.750 0.750 srgb} bind def
/col30 {1.000 0.880 0.880 srgb} bind def
/col31 {1.000 0.840 0.000 srgb} bind def
end
save
143.0 574.5 translate
1 -1 scale
/cp {closepath} bind def
/ef {eofill} bind def
/gr {grestore} bind def
/gs {gsave} bind def
/sa {save} bind def
/rs {restore} bind def
/l {lineto} bind def
/m {moveto} bind def
/rm {rmoveto} bind def
/n {newpath} bind def
/s {stroke} bind def
/sh {show} bind def
/slc {setlinecap} bind def
/slj {setlinejoin} bind def
/slw {setlinewidth} bind def
/srgb {setrgbcolor} bind def
/rot {rotate} bind def
/sc {scale} bind def
/sd {setdash} bind def
/ff {findfont} bind def
/sf {setfont} bind def
/scf {scalefont} bind def
/sw {stringwidth} bind def
/tr {translate} bind def
/tnt {dup dup currentrgbcolor
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add
4 -2 roll dup 1 exch sub 3 -1 roll mul add srgb}
bind def
/shd {dup dup currentrgbcolor 4 -2 roll mul 4 -2 roll mul
4 -2 roll mul srgb} bind def
/$F2psBegin {$F2psDict begin /$F2psEnteredState save def} def
/$F2psEnd {$F2psEnteredState restore end} def
$F2psBegin
10 setmiterlimit
n 0 792 m 0 0 l 612 0 l 612 792 l cp clip
0.06299 0.06299 sc
7.500 slw
% Polyline
n 990 1575 m 1890 1575 l 1890 2295 l 990 2295 l cp gs col-1 s gr
% Polyline
n 990 2025 m 1890 2025 l gs col-1 s gr
% Polyline
n 1440 2025 m 1440 2295 l gs col-1 s gr
% Polyline
n 990 1800 m 1890 1800 l gs col-1 s gr
/Times-Roman ff 150.00 scf sf
1215 1755 m
gs 1 -1 sc (A_Expr) col-1 sh gr
/Times-Roman ff 150.00 scf sf
1350 1980 m
gs 1 -1 sc (OR) col-1 sh gr
% Polyline
n 2025 450 m 2925 450 l 2925 1170 l 2025 1170 l cp gs col-1 s gr
% Polyline