Table Of Content

Provenance for Aggregate Queries Yael Amsterdamer Daniel Deutch Val Tannen UniversityofPennsylvania UniversityofPennsylvania UniversityofPennsylvania andTelAvivUniversity andBenGurionUniversity 1 ABSTRACT 1 R 0 We study in this paper provenance information for queries EmpId Dept Sal 2 withaggregation. Provenanceinformationwasstudiedinthe 1 d1 20 p1 Dept n contextofvariousquerylanguagesthatdonotallowforag- 2 d1 10 p2 d1 p1+p2+p3 a gregation, and recent work has suggested to capture prove- 3 d1 15 p3 d2 r1+r2 J nance by annotating the different database tuples with ele- 4 d2 10 r1 5 mentsofacommutativesemiringandpropagatingtheanno- 5 d2 15 r2 (b) tations through query evaluation. We show that aggregate queries pose novel challenges rendering this approach inap- ] (a) B plicable. Consequently, we propose a new approach, where Figure 1: Projection on annotated relations weannotatewithprovenanceinformationnotjusttuplesbut D alsotheindividualvalueswithintuples,usingprovenanceto atethepolynomialsinthesecurity semiring,andpropagate s. describe the values computation. We realize this approach thesecurity annotationsthroughqueryevaluation (seeSec- c in a concrete construction, first for“simple”queries where tion 2.1),assigning security levels to queryresults. [ theaggregationoperatoristhelastoneapplied,andthenfor Let us briefly illustrate deletion propagation as an appli- arbitrary (positive) relational algebra querieswith aggrega- cation of provenance. Consider a simple example of an em- 1 tion; the latter queriesare shown to bemore challenging in ployee/department/salary relation R shown in Figure 1(a). v this context. Finally, we use aggregation to encode queries The variables p ,p ,p ,r ,r can be thought of as tuple 0 1 2 3 1 2 1 with difference, and study the semantics obtained for such identifiers and in the framework of provenance polynomi- 1 queries on provenanceannotated databases. als[24]theyarethe“provenancetokens”or“indeterminates” 1 out of which provenance is built. We denote by N[X] the . 1. INTRODUCTION setofprovenancepolynomials(hereX ={p1,p2,p3,r1,r2}). 1 R can be seen as an N[X]-annotated relation; as defined in 10 wiTthhepraonvneontaanticoenionffotrhmearteisounltshaosfdqautiatbeaasefterwanaspfoprlmicaattiioonnss a[2n4o]tthheer eNv[aXlu]-aatnionnotoafteqduerreyl,aftoiorne,xianmtphleisΠeDxaepmtRp,leptrhodeuocnees 1 [19, 6, 35, 10, 11, 38, 34, 25, 27, 39, 37, 2, 4]. Recent shown in Figure 1(b). Intuitively, in this simple example, : v work [24,17, 21] hasproposed a framework of semiring an- the summation in the annotation of every result tuple is i notationsthatallowsustostateformallywhatisexpectedof overthe identifiersof its alternative origins 1. X such provenanceinformation. These papers havedeveloped Now,theresultofpropagatingthedeletionsoftupleswith r the framework for the positive fragment of the relational EmpId 3 and 5 in R is obtained by simply setting p = a 3 algebra (as well as for Datalog, the positive Nested Rela- r = 0 in the answer. We get the same two tuples in the 2 tional Calculus, and some query languages on trees/XML). query answer but their provenances change to p +p and 1 2 The main goal of this paper is to extend the framework to r , respectively. If the tuple with EmpId 4 is also deleted 1 aggregate operations. from R thenwealso set r =0,andthesecond tupleinthe 1 In the perspective promoted by these papers, provenance answer is deleted because its provenance has now become isageneralformofannotationinformationthatcanbespe- 0. This algebraic treatment of deletions is related to the cialized for different purposes, such as multiplicity, trust, counting algorithm for view maintenance [26], but is more cost, security, or identification of “possible worlds” which general as it incrementally maintains not just the data but in turn applies to incomplete databases, deletion propaga- also theprovenance. tion, and probabilistic databases. In fact, the introduction An intuitive way of understanding what happens is that oftheframeworkin[24]wasmotivatedbytheneedtotrack provenance-aware evaluation of queries conveniently“com- trustanddeletionpropagationintheOrchestrasystem[23]. mutes”with deletions. In fact, in [24, 17] this intuition is Whatmakessuchadiversityofapplicationspossibleisthat captured formally by theorems that state that query eval- eachiscapturedbyadifferentsemiring,whileprovenanceis uation commutes with semiring homomorphisms. The fac- represented by elements of a semiring of polynomials. One torization throughprovenancerelies onthisandonthefact then relies on the property that any semiring-annotation that the polynomial provenance semiring is “freely gener- semantics factors through the provenance polynomials se- ated”. All applications of provenance polynomials we have mantics. This means that storing provenance polynomials listed, for trust,security,etc., are based on these theorems. allows for many other practical applications. For example, tocaptureaccesscontrol,wheretheaccesstodifferenttuples 1we explain how the annotations of query results are com- require different security credentials, we can simply evalu- putedin Section 2.1 Dept SalMass d1 45 p1p2p3 rather than multiplicities? Then, the aggregate value does d1 30 p1p2p3 Dept SalMass not correspond to any number. d1 35 p1p2pb3 d1 30 p1p2 Wewillmakep1×20intoanabstractconstruction,p1⊗20 d1 25 p1pb2p3 d1 20 p1p2 and the aggregate value will be the formal expression p1⊗ d1 20 pb1p2p3 d1 10 p1pb2 20+p2⊗10+p3⊗15. dd11 1105 ppb11ppb22ppbb33 ··· ··· ·b·· i.eI.,nttuhietivreelayl,swRe,arienetmobaedladringegrthdeodmoaminainofoffosurmma-algegxrpegreast-es, sions that capture how the sum-aggregates are computed ··· ··· ··· (b) b b from values annotated with provenance. We do the same forotherkindsof aggregation, forinstancemin-aggregation (a) givesp ⊗20minp ⊗10minp ⊗15. Wecalltheseannotated 1 2 3 Figure 2: A naive approach to aggregation aggregate expressions. In this paper we consider only aggregations defined by Thus, commutation with homomorphisms is an essential commutative-associativeoperations3. Specifically,ourframe- criterionforourproposedframeworkextensiontoaggregate workcanaccommodateaggregationbasedonanycommuta- operations. However,inSection3.1weprovethattheframe- tivemonoid. Forexamplethecommutativemonoidforsum- workofsemiring-annotatedrelationsintroducedin[24]can- mation is (R,+,0) while theone for min is (R∞,min,∞)4. notbeextendedtohandleaggregationwhilebothsatisfying TocombineanaggregationmonoidM withanannotation commutationwithhomomorphismsandworkingasusualon commutativesemiringK,inawaycapturingaggregatesover set or bag relations. K-relations,weproposetheuseofthealgebraicstructureof If the semiring operations are not enough then perhaps K-semimodules(seeSection2.2). Semimodulesareawayof wecanaddothers? Thisisanaturalideasoweillustrateit generalizing(alot!) theoperationsconsideredinlinearalge- on the same R in Figure 1(a) and we use again the neces- bra. Its“vectors”form only a commutative monoid (rather sity to supportdeletion propagation to guidetheapproach. than an abelian group) and its “scalars” are the elements Consider thequerythat groupsbyDept andsumsSal. The of K which is only a commutative semiring (rather than a result of the summation depends on which tuples partici- field). pate in it. To provide enough information to obtain all the In general, a commutative monoid M does not have an possible summation results for all possible sets of deletions, obvious structure of K-semimodule. To make it such we wecouldusetherepresentationinFigure2(a)whereweadd may need to add new elements corresponding to the scalar to the semiring operations an unary operation with the multiplicationofelementsofM withelementsfromK,thus property that p = 1 whenever p = 0. This will indeed sat- b endingupwiththeformalexpressionsthatrepresentaggre- isfythedeletioncriterion. Forexamplewhenthetuplewith b gatecomputations,motivatedabove,aselementsofatensor Id 3 is deleted we get the relation in Figure 2(b). In fact, productconstructionK⊗M. Weshowthattheuseoftensor thereexistsemiringswiththeadditionalstructureneededto product expressions as a formal representation of aggrega- define . For example in the semiring of polynomials with integer coefficients, Z[X], we can take p = 1−p while in tionresultiseffectiveinmanagingtheprovenanceof“simple b aggregate” queries, namely queries where the aggregation the semiring of boolean expressions with variables from X, b operators are thelast ones applied. BoolExp(X),wecantakep=¬p2. Thelatterisessentially the approach taken in [31]. However, whether we use Z[X] We show that certain semirings are “compatible” with b certain monoids, in the sense that the results of compu- or BoolExp(X), we still have, in the worst case, exponen- tation done in K ⊗ M may be mapped to M, faithfully tially many different results to account for, at least in the representing the aggregation results. Interestingly, compat- caseofsummation(alowerboundrecognizedalsoin[31]). It ibility is aligned with common wisdom: it is known that followsthatsummationinparticular(andthereforeanyuni- some (idempotent) aggregation functions such as MIN and form approach to aggregation) cannot be represented with MAXworkwellforsetrelations,whileSUMandPRODre- a feasible amount of annotation as long as the annotation quirethetreatmentofrelationsasbags. Weshowthatnon- stays at thetuplelevel. idempotent monoids are compatible only with“bag”semir- Instead, we will present a provenance representation for ings, i.e. semirings from which there exists an homomor- aggregation results that leads only to a poly-size increase, phism to N. one that we believeis tractably implementable using meth- In general, aggregation results may be used by the query ods similar to the ones used in Orchestra [23]. We achieve as the input to further operators, such as value-based joins this via a more radical approach: we annotate with prove- orselections. Heretheformalrepresentationofvaluesleads nanceinformationnotjustthetuplesoftheanswerbutalso to a difficulty: the truth values of comparison operators on themannerinwhichthevaluesinthosetuplesarecomputed. such formal expressions is undetermined! Consequently, we Wecangainintuitiontowardsourrepresentationfromthe particular case of bags, which are in fact N-relations, i.e., extendourframeworkandconstructsemiringsinwhichfor- mal comparison expressions between elements of the corre- relations whose tuples are annotated with elements of the semiring(N,+,·,0,1). AssumethatRinFigure1(a)issuch sponding semimodule are elements of the semiring. This arelation,i.e.,p ,...,r ∈Naretuplemultiplicities. Then, means that an expression like [p1⊗20=p2⊗10+p3⊗15] 1 2 maybeusedaspartoftheprovenanceofatuplesinthejoin after sum-aggregation the valueof theattributeSalMass in the tuple with Dept d is computed by p ×20+p ×10+ result. This expression is simply treated as a new prove- 1 1 2 nance token (with constraints), until p ,p ,p are assigned p ×15. Now, if the multiplicities are, for example p = 1 2 3 3 1 e.g. values from B or N, in which case we can interpret 2,p = 3,p = 1 then the aggregate value is 85. But what 2 3 if R is a relation annotated with provenance polynomials 3As shown in [32], for list collections it also makes sense to 2Both of these also give natural semantics to relational dif- consider non-commutativeaggregations. ference. Z[X] is used in [20] following the use of Z in [22], 4K-annotated relations with union (see Section 2.1) also while BoolExp(X) is used in theseminal paper[28]. form such a structure. bothsidesoftheequalityaselementsofthemonoidandde- are annotated with elements from commutative semirings. termine the truth value of the equality (see Section 4). We These are structures (K,+ ,· ,0 ,1 ) where (K,+ ,0 ) K K K K K K showinSection4thatthisconstructionallowsustomanage and (K,· ,1 ) are commutative monoids, · is distribu- K K K provenance information for arbitrary queries with aggrega- tive over + , and a · 0 = 0 · a = 0 . A semiring K K K K K tion,whilekeeping the representation size polynomial in the homomorphism is a mapping h : K → K′ where K,K′ sizeoftheinputdatabase. Ourconstructionisrobust: iffur- are semirings, and h(0 ) = 0 ,h(1 ) = 1 ,h(a +b) = K K′ K K′ therqueriesareapplied,thetoken[p ⊗20=p ⊗10+p ⊗15] h(a)+h(b), h(a·b) = h(a)·h(b). Examples of commuta- 1 2 3 can be used as part of a more complex expression, just as tivesemiringsareanycommutativering(ofcourse)butalso any other provenancetoken. any distributive lattice, hence any boolean algebra. Exam- The main result of this paper is providing, for the first plesofparticularinteresttousincludethebooleansemiring time, a semantics for aggregation (including group by) on (B,∨,∧,⊥,⊤) (for usual set semantics), the natural num- semiring-annotated relations that: bers semiring (N,+,·,0,1) (its elements are multiplicities, i.e., annotations that give bag semantics), and the secu- • Coincides with the usual semantics on set/bag rela- rity semiring (S,min,max,0,1) where Sis theordered set, tions for min/max/sum/prod. S S 1 < C < S < T < 0 whose elements have the following S S • Commutes with homomorphisms of semirings (hence meaning when used as annotations: 1 : public (“always S all theensuingapplications) available”), C : confidential, S : secret, T : top secret, and • Is representablewith only poly-size overhead. 0 means“neveravailable”. S Certainsemiringsplayanessentialroleincapturingprove- Asecondresultofthispaperisanewsemanticsfordifference nance information. Given a set X of provenance tokens onrelationsannotatedwithelementsfromanycommutative which correspond to“atomic”provenance information, e.g., semiring. This is done via an encoding of relational differ- tupleidentifiers,thesemiringofpolynomials(N[X],+,·,0,1) ence using nested aggregation. The fact that such an en- wasshownin[24]toadequately,andmostgenerally,capture codingcanbedoneisknown(seee.g.[29,9]),butcombined provenance for positive relational queries. The provenance withourprovenanceframework,theencodinggivesaseman- interpretationofthesemiringstructureisthefollowing. The tics for“provenance-aware”difference. Our new semantics + operation on annotations corresponds to alternative use forR−S isahybridofbag-styleandset-stylesemantics,in ofdata,the·operationtojointuseofdata,1annotatesdata thesensethattuplesofRthatappearinS donotappearin thatisalways andunrestrictedlyavailable,and0annotates R−S(i.e. abooleannegativeconditionisused),whilethose absent data. Thedefinitionof theK-relational algebra (see that donot appearin S appearinR−S with thesamean- notation(multiplicity,ifK =Nisused)thattheyhadinR. bellow for union, projection and join) fits indeed this inter- pretation. Algebraically, N[X] is the commutative semir- This makes the semantics different from the bag-monus se- ing freely generated by X, i.e., for any other commutative mantics and itsgeneralization to“monus-semirings”in [19] semiringK,anyvaluationoftheprovenancetokensX →K aswellasfromthe“negativemultiplicities”semanticsin[22] extends uniquely to a semiring homomorphism N[X] → K (more discussion in Section 6). We examine the equational (an evaluation in K of the polynomials). We say that any laws entailed by this new semantics, in contrast to those of semiring annotation semantics factors through the prove- previouslyproposedsemanticsfordifference. Inouropinion, nancepolynomialssemantics,whichmeansthatforpractical thissemanticsisprobablynotthelastwordondifferenceof purposes storing provenance information suffices for many annotated relations, but we hope that it will help inform other applications too. Other semirings can also be used and calibrate futurework on thetopic. tocapturecertain formsofprovenance,albeit lessgenerally than N[X] [24, 21]. For example, boolean expressions cap- PaperOrganization. Therestofthepaperisorganizedas tureenoughprovenancetoserveintheintensionalsemantics follows. Section2describesandexemplifiesthemainmath- ofqueriesonincomplete[28]andprobabilisticdata[18,40]. ematical ingredients used throughout the paper. Section Todefineannotatedrelationsweuseherethenamedper- 3 describes our proposed framework for “simple” aggrega- spectiveoftherelationalmodel[1]. Fixacountablyinfinite tionqueries,andthisframework isextendedinSection4to domain D of values (constants). For any finite set U of at- nested aggregation queries. We consider difference queries tributes a tuple is a function t : U → D and we denote the inSection5. RelatedWorkisdiscussedinSection6,andwe set of all such possible tuples by DU. Given a commuta- conclude in Section 7. tivesemiringK,aK-relation(withschemaU)isafunction R : DU → K whose support, supp(R) = {t | R(t) 6= 0 } is 2. PRELIMINARIES K finite. For a fixed set of attributes U we denote by K-Rel We provide in this section thealgebraic foundations that (when U is clear from the context) the set of K-relations willbeusedthroughoutthepaper. Westartbyrecallingthe with schema U. We also define a K-set to be a function notionofsemiringanditsusein [24]tocaptureprovenance S :D→K again of finitesupport. Wethen define: for theSPJUalgebraqueries. Wethenconsideraggregates, andshowthenewalgebraic constructionthatisrequiredto Union If Ri :DU →K, i=1,2 then R1∪K R2 :DU →K accurately support it. is defined by (R1 ∪K R2)(t) = R1(t)+K R2(t). The definition of union of K-sets follows similarly. 2.1 Semirings andSPJU We also define the empty K-relation (K-set) by ∅ (t) = K Webrieflyreviewthebasicframework introducedin[24]. 0 . It is easy to see that (K-Rel,∪ ,∅ ) is a commutative Acommutativemonoidisanalgebraicstructures(M,+M,0M) mKonoid 5.Similarly, we get the comKmuKtative monoid of K- where +M is an associative and commutative binary op- sets (K-Set,∪K,∅K). eration and 0M is an identity for +M. A monoid homo- Givenanamedrelationalschema,K-databasesaredefined morphism is a mapping h : M → M′ where M,M′ are from K-relations just as relational databases are defined monoids, and h(0 ) = 0 ,h(a + b) = h(a)+ h(b). We M M′ will consider database operations on relations whose tuples 5In fact, it also has a semiring structure. from usual relations, and in fact the usual (set semantics) sum, prod, min, and max aggregations on bags. SinceMIN databases correspond to the particular case K = B. The and MAX are B-semimodules this gives the usual min and (positive)K-relationalalgebradefinedin[24]correspondsto max aggregation on sets 9. a semantics on K-databases for the usual operations of the NotethatSetAggisanoperationonsets,notanoperation relational algebra. We have already defined the semantics onrelations. Inthesequelweshowhowtoextendittoone. ofunionaboveandwegiveherejusttwoothercasesleaving 2.3 A tensor product construction therest for AppendixA (for atuplet and an attributesset U′, t|U′ is therestriction of t to U′): More generally, we want to investigateM-aggregation on K-relations where M is a commutative monoid and K is Projection If R : DU → K and U′ ⊆ U then ΠU′R : somecommutativesemiring. SinceM maynothaveenough DU′ → K is defined by (ΠU′R)(t) = KR(t′) where elementstorepresentK-annotatedaggregationsweconstruct the+K sumisoverallt′ ∈supp(R)sucPhthatt′|U′ =t. aK-semimoduleinwhichM canbeembedded,bytransfer- Natural Join If Ri : DUi → K, i = 1,2 then R1 ⊲⊳ R2 : ring to semimodules the basic idea behind a standard alge- DU1∪U2 → K is defined by (R ⊲⊳ R )(t) = R (t )· braic construction, as follows. 1 2 1 1 K R2(t2) where ti =t|Ui, i=1,2. Let K be any commutativesemiring and M be any com- mutativemonoid. WestartwithK×M,denoteitselements 2.2 Semimodules andaggregates k⊗minsteadofhk,miandcallthem“simpletensors”. Next weconsider(finite)bagsofsuchsimpletensors,which,with Wewillconsideraggregatesdefinedbycommutativemonoids. SomeexamplesareSUM=(R,+,0)forsummation6,MIN= bagunionandtheemptybag,formacommutativemonoid. (R±∞,min,+∞)formin,MAX=(R±∞,max,−∞)formax, It will be convenient to denote bag union by +K⊗M, the and PROD=(R,×,1) for product. empty bag by 0K⊗M and to abuse notation denoting single- ton bags by the unique element they contain. Then, every In dealing with aggregates we have to extend the oper- non-empty bag of simple tensors can be written (repeating ation of a commutative monoid to operations on relations annotated with elements of semirings. This interaction will summands by multiplicity) k1⊗m1+K⊗M ···+K⊗M kn⊗mn. Now we define be capturedby semimodules. Definition 2.1. GivenacommutativesemiringK,astruc- k∗K⊗M Pki⊗mi=P(k·K ki)⊗mi ture(W,+ ,0 ,∗ )isaK-semimoduleif(W,+ ,0 )isa W W W W W Let ∼ be the smallest congruence w.r.t. + and ∗ commutativemonoidand∗W isabinaryoperationK×W → that satisfies (for all k,k′,m,m′): K⊗M K⊗M W such that (for all k,k ,k ∈K and w,w ,w ∈W): 1 2 1 2 (k+ k′)⊗m ∼ k⊗m+ k′⊗m k∗ (w + w ) = k∗ w + k∗ w (1) K K⊗M W 1 W 2 W 1 W W 2 0 ⊗m ∼ 0 k∗ 0 = 0 (2) K K⊗M W W W k⊗(m+ m′) ∼ k⊗m+ k⊗m′ (k + k )∗ w = k ∗ w+ k ∗ w (3) M K⊗M 1 K 2 W 1 W W 2 W k⊗0 ∼ 0 0 ∗ w = 0 (4) M K⊗M K W W (k · k )∗ w = k ∗ (k ∗ w) (5) We denote by K ⊗M the set of tensors i.e., equivalence 1 K 2 W 1 W 2 W classes of bags of simple tensors modulo ∼. We show in 1 ∗ w = w (6) K W AppendixBthat K⊗M forms a K-semimodule. In any (commutative) monoid (M,+ ,0 ) define for any M M n∈N and x∈M Liftinghomomorphisms. Givenahomomorphismofsemir- ingsh:K →K′,andsomecommutativemonoidM,wecan nx=x+ ···+ x (n times) M M “lift”htoahomomorphismofmonoidsinanaturalway. The in particular 0x = 0 . Thus M has a canonical struc- lifted homomorphism is denoted hM : K ⊗M → K′ ⊗M M ture of N-semimodule. Moreover, it is easy to check that and definedby: a commutative monoid M is a B-semimodule if and only if its operation is idempotent: x+ x = x. The K-relations hM( ki⊗mi)= h(ki)⊗mi M P P themselvesformaK-semimodule(K-Rel,∪ ,∅ ,∗ )where K K K (k∗KR)(t)=k·K R(t)7. 3. SIMPLE AGGREGATIONQUERIES We now show, for any K-semimodule W, how to define In this section we begin our study of the “provenance- W-aggregation of a K-set of elements from W. We assume aware” evaluation of aggregate queries, focusing on “sim- that W ⊆ D and that we have just one attribute, whose ple”such queries, that is, queries in which aggregations are values are all from W. Consider the K-set S such that donelast;forexample,un-nestedSELECTFROMWHERE supp(S) = {w1,...,wn} and S(wi) = ki ∈ K,i = 1,...,n GROUPBYqueries. Thisavoidstheneedtocomparevalues (i.e., each wi is annotated with ki). Then, the result of which are the result of annotated aggregations and simpli- W-aggregating S is definedas fies the treatment. The restriction is relaxed in the more SetAggW(S) = k1∗W w1+W ···+W kn∗W wn ∈W general framework presented in Section 4. The section is organized as follows. We list the desired FortheemptyK-setwedefineSetAggW(∅K)=0W. Clearly, features of a provenance-aware semantics for aggregation, SetAggW is a semimodule homomorphism 8. Since all com- andfirsttrytodesignasemanticswiththesefeatures,with- mutative monoids are N-semimodules this gives the usual out using the tensor product construction, i.e. by simply 6COUNT is particular case of summation and AVG is ob- 9The fact that the right algebraic structure to use for ag- tained from summation and COUNT. gregates isthat of semimodulescan bejustifiedin thesame 7Infact, it is theK semimodule freely generated byDU. way inwhichusingsemiringswas justifiedin[24]: byshow- 8In fact, it is the free homomorphism determined by the ing howthelaws of semimodules follow from desired equiv- identity function on W. alences between aggregation queries. workingwithK-relationsasdonein[24]. Weshowthatthis theannotationsoftheoutputΩ(D)aredefinedbythesame is impossible. Consequently, we turn to semantics that are (for allK) {+ ,· ,0 ,1 }-expressions, wheretheelements K K K K based on combining aggregation with values via the tensor in the expressions are the annotations of the input D. One product construction. We propose such semantics that do can see that the definition of the SPJU-algebra is indeed satisfy the desired features, first for relational algebra with algebraically uniform and was shown in [24, 17] to com- anadditionalAGGoperatoronrelations(thatallowsaggre- mute with homomorphisms. The connection between the gationofallvaluesinachosenattributes,butnogrouping); twopropertiesisgeneral(proofdeferredtotheAppendix): and then for GROUPBY queries. Proposition 3.1. Asemanticscommuteswithhomomor- 3.1 Semantic desiderata andfirst attempts phisms iff it is algebraically uniform. We next explain the desired features of a provenance- Afterstatingtwoofthedesiredproperties,namelyset/bag awaresemanticsforaggregation. Toillustratethedifficulties compatibility and commutation with homomorphisms we and the need for a more complex construction, we will first canalreadyshowthatitisnotpossibletogiveasatisfactory attempttodefineasemanticsonK-relations,withoutusing semanticstotheSPJU-Aalgebrawithintheframeworkused thetensor product construction of Section 2.3. in [24] for theSPJU-algebra. WeconsideracommutativesemiringK(e.g.,B,N,N[X],S, etc.) for tuple annotations and a commutative monoid M Proposition 3.2. There is no K-relation semantics for (e.g., SUM=(R,+,0),PROD=(R,×,1), MAX= MAX-(or MIN-)aggregation that is both set-compatible and (R−∞,max,−∞), MIN = (R∞,min,∞) etc.) for aggrega- commutes with homomorphisms. Similarly, there is no K- tion. WewillassumethattheelementsofM alreadybelong relation semantics for SUM-aggregation that is both bag- to thedatabase domain, M ⊆D. compatible and commutes with homomorphisms. WehaverecalledthesemanticsofSPJUqueriesinSection Proof. Assume by contradiction the existence of such 2.1. NowwewishtoaddanM-aggregation operation AGG semantics. ConsidertheN[X]-relation Rwithoneattribute onrelations. WethendenotebySPJU-Atherestrictedclass andtwotupleswithvalues10and20,withthecorresponding of queries consisting of any SPJU-expression followed pos- tuple annotations being x,y ∈ X. Let R′ be AGG (R) sibly by just one application of AGG. This corresponds to MAX according to the assumed semantics; R′ is also an N[X]- SELECT AGG(*) FROMWHEREqueries (nogrouping). relation. Because a tuple t with a value 10 is a possible For the moment, we do not give a concrete semantics to answer to the MAX-aggregation (when we set y = 0) it AGGM(R), allowing any possible semantics where the re- must occur in supp(R′). Let p ∈ N[X] be the annotation sult ofAGGM(R)isaK-relation. WenotethatAGGM(R) of the tuple t (having value 10) in R′. By algebraic unifor- should be defined iff R is a K-relation with one attribute mitytheonlyvariablesthatcanoccurinparexandy,and whose valuesare in M. we consequently denote it p(x,y). Consider two homomor- What properties do we expect of a reasonable semantics phisms h′,h′′ :N[X]→B defined byh′(x)=h′(y)=⊤ and forSPJU-A(including,ofcourse,asemanticsforAGGM(R))? by h′′(x) = ⊤,h′′(y) = ⊥. Applying AGG to h′ (R) A basic sanity checkis MAX Rel and h′′ (R) should, by set-compatibility, work as usual. Rel Set/Bag Compatibility Thesemanticscoincideswiththe Hence, by commutation with homomorphisms h′(p) = ⊥ usualonewhenK =B(sets) andM =MAXorMIN, and h′′(p) = ⊤. Functions on B defined by polynomials in and when K =N (bags) and M =SUMor PROD. N[X] are monotone in each variable. But ⊥=h′(p(x,y))= p(h′(x),h′(y))=p(⊤,⊤)and⊤=h′′(p(x,y))=p(h′′(x),h′′(y))= Notethatweassociateminandmaxwithsetsandsumand p(⊤,⊥), in contradiction to themonotonicity. product with bags. Min and max work fine with bags too, butwegetthesameresultifweconvertabagtoaset(elimi- nateduplicates)andthenapplythem. Sumandproduct(in the context of other operations such as projection) require Alternatively, one may consider going beyond semirings, us to use bags semantics in order to work properly. This toalgebraicstructureswith additionaloperations. Wehave is well-known, but our general approach sheds further light briefly explored the use of“negative”information in the in- ontheissuebydiscussingsuch“compatibility”forarbitrary troduction. Asweshowthere,onecould usetheringstruc- semirings and monoids in Section 3.4. ture on Z[X] (the additional subtraction operation) or the Asdiscussedintheintroduction,afundamentaldesidera- boolean algebra structure on BoolExp(X) (the additional tumwithmanyapplicationsiscommutationwithhomomor- complement operation) but the use of negative operation phisms. Note that a semiring homomorphism h : K → K′ does not avoid the need to enumerate in separate tuples of naturally extends to a mapping hRel :K-Rel→K′-Rel via theanswerallthepossibleaggregationresultsgivenbysub- hRel(R) = h◦R (i.e. the homomorphism is applied on the sets of the input. In the case of summation, at least, there annotation of every tuple), which then further extends to are exponentially many such tuples. We reject such an ap- K-databases. With this, thesecond desired property is proach and we state as an additional desideratum: Commutation with Homomorphisms Givenanytwocom- Poly-Size Overhead For any query Q and database D, mutative semirings K,K′ and any homomorphism h: thesizeofQ(D),includingannotations,shouldbeonly K →K′,foranyqueryQ,itssemanticsonK-databases polynomial in the size of D. andonK′-databasessatisfy h (Q(D))=Q(h (D)) Rel Rel for anyK-database D. We shall next show a semantics to the SPJU-A -algebra that satisfies all threeproperties we havelisted. Itturnsoutthatthispropertydeterminesquitepreciselythe wayinwhichtupleannotationsaredefined. Wesaythatthe 3.2 Annotations ⊗values andSPJU-A semanticsofan operation Ωon K-databasesisalgebraically LetusfixacommutativemonoidM (foraggregation)and uniformwithrespecttotheclassofcommutativesemiringsif a commutative semiring K (for annotation). The inputs of our queries are as before: K-databases whose domain D querieshomomorphismcommutationwasshownin[24],while includes the values M over which we aggregate. However, commutation for the new AGGM construct follows directly the outputs are more complicated. The basic idea for the from the definition. semantics of aggregation was already shown in Section 2.2 Example 3.4. Consider the following N[X]-relation R: where it is assumed that the domain of aggregation has a K-semimodule structure. As we have shown in Section 2.3, we can give a tensor product construction that embeds M Sal in the K-semimodule K⊗M (note that this embedding is 20 r 1 not always faithful,as discussed in Section 3.4). 10 r 2 For the output relations of our algebra queries, we thus 30 r 3 need results of aggregation (i.e., the elements of K ⊗M) to also be part of the domain out of which the tuples are LetM besomecommutativemonoid,thenAGGM(R)con- sist of a single tuple with value r ⊗20+ r ⊗10+ constructed. Thus for the output domain we will assume 1 K⊗M 2 K⊗M that K ⊗M ⊆ D, i.e. the result “combines annotations r3⊗30. The intuition is that this value captures multiple possible aggregation values, each of which may be obtained with values”. The elements of M (e.g., real numbers for sum or max aggregation) are still present, but only via the by mapping the ri annotations to N, standing for the multiplicity of the corresponding tuple. The commutation with embeddingι:M →K⊗M definedby ι(m)=1 ⊗m. K homomorphismallowsustofirstevaluatethequeryandonly HavingannotationsfromKappearinthevalueswillchange thewayinwhichweapplyhomomorphismstoqueryresults, then map the ri’s, changing directly the expression in the query result. For example, if M =SUM and we map r to sotoemphasizethechangewewillcall(M,K)-relationsthe 1 K-annotated relations over such that the data domain D 1,r2 to 0,r3 to 2, we obtain 1⊗20+K⊗M 2⊗30=1⊗20+K⊗M 1⊗30 + 1⊗30 = 1⊗ 80 (which corresponds to the M that includes K⊗M. To summarize, the semantics of the K⊗M element 80). As another example, the commutation with SPJU-A -algebra will map databases of K-relations (with M ⊆D) to (M,K)-relations (with K⊗M ⊆D). homomorphisms allows us to propagate the deletion of the first tuple in R, by simply setting in the aggregation result As we define the semantics of the SPJU-A -algebra, we r =0 (keeping the other annotations intact) and obtaining first note that for selection, projection, join and union the 1 2⊗30=(1+1)⊗30=1⊗30+1⊗30=1⊗(30+30)=1⊗60. definitionisthesameasfortheSPJU-algebraonK-databases. Thelaststepofthequeryisaggregation,denotedAGGM(R), Wefurtherdemonstrate an application for security. and is well-defined iff R is a K-relation with one attribute whose values are in the M subset of D. To apply the def- Example 3.5. Consider the following relation R, anno- inition that uses the semimodule structure (shown in Sec- tated by elements from the security semiring S. tion 2.2), we convert R to an (M,K)-relation ι(R) by re- Sal placingeachm∈M withι(m)=1 ⊗m∈K⊗M. Then,if K 20 S supp(R)={m1,...,mn} and R(mi)=ki ∈K,i=1,...,n 10 1 (i.e., each mi is annotated with ki) we defineAGGM(R) as 30 SS aone-attributerelationwithonetupleannotationis1 and K whose contentis SetAgg (ι(R)),which is equalto Recall (from Section 2.1) the order relation 1 <C<S< K⊗M S T < 0; a user with credentials cred can only view tuples k1∗K⊗M ι(m1)+K⊗M ···+K⊗M kn∗K⊗M ι(mn) annotaSted with security level equal or less than cred. Now =k1⊗m1+K⊗M ···+K⊗M kn⊗mn let M = MAX and we obtain: AGGMAX(R) = S⊗20+K⊗M We definetheannotation of theonly tuplein theoutputof 1S⊗10+K⊗M S⊗30 = S⊗(20+MAX 30)+1S⊗10 and we get AGGM to be 1K, since this tuple is always available. How- S⊗30+1S⊗10. ever,thecontentofthistupledoesdependonR. Forexam- Assume now that we wish to compute the query results as ple, even when R is emptythe outputis not empty: bythe viewedbyauserwithsecuritycredentialscred. Anaivecom- semimodule laws, its contentis 0 =ι(0 ). putation would delete from R all tuples that require higher K⊗M M credentials, andre-evaluate thequery(whichingeneral may becomplex). Butobserve thatthedeletionoftuplesisequiv- CommutationwithHomomorphisms. Wehaveexplained alent to applying to R a homomorphism that maps every inSection2.3howtolift ahomomorphismh:K →K′ toa annotation t > cred to 0, and t ≤ cred to 1. Using homo- homomorphismhM :K⊗M →K′⊗M. Viathiswecanlift morphism commutation we can do better by applying this h to a homomorphism h on (M,K)-relations: let R be Rel homomorphism only on the result representation (namely sucharelationandrecallthatsomevaluesinRareelements S⊗30+1⊗10). For example, for a user with credentials C, of K⊗M,andtheannotationsofthesetuplesareelements S wemapSto0and1 to1,andobtain0⊗30+1⊗10=1⊗10; ofK. Thenh (R)denotestherelationobtainedfromRby S Rel similarlyforauserwithcredentialsSweget1⊗30+1⊗10= replacingeveryk∈K withh(k),andadditionallyreplacing 1⊗(30+ 10)=1⊗30. every k⊗M ∈ K⊗M with hM(k⊗m). All other values MAX in R stay intact. Applying hRel on a (M,K)-database D From the above definition of the semantics for aggrega- amounts toapplyinghRel on each (M,K)-relation in D. tion, it is obvious that the poly-size overhead property is WecannowstatethemainresultforourSPJU-A-algebra: fulfilled. Indeed, consider the case of provenance for sum- Theorem 3.3. Let K,K′ be semirings, h : K → K′, Q mationasinExample3.4,andcompareittothenaiverepre- sentationprovidedintheIntroduction. Insteadofhavingto anSPJU-A query andletM beacommutative monoid. For listall(exponentiallymany)optionsforthesumofsalaries, every (M,K)-database D, Q(h (D))=h (Q(D)) ifand Rel Rel weusedanexpressioninK⊗SUMthatisoflinearsizewith only if h is a semiring homomorphism. respecttotheinputtotheaggregation. Asexemplified,the The proof is by induction on the query structure, and possibleaggregateanswersnowcorrespondtodifferentvalu- is straightforward given that for the constructs of SPJU ationsfortheprovenancetokens,appliedtothisexpression. 3.3 Group By andaqueryGB R,wherethemonoidusedisSUM. {Dept},Sal So far we have considered aggregation in a limited con- The result (denoted R’) is: text,wheretheinputrelationcontainsasingleattribute. In Dept Sal commoncases,however,aggregationisusedonarbitraryre- lationsandinconjunctionwithgrouping,sowenextextend d1 r1⊗20+K⊗SUM r2⊗10 δK(r1+K r2) the algebra to handle such an operation. The idea behind d2 r3⊗10 δK(r3) theconstructionisquitesimple: weseparatelygroupthetu- Each aggregated value (for each department) is computed ples according to the values of their“group-by”attributes, very similarlyto the computation in Example 3.4. Consider andtheaggregatedvaluesforeachsuchgrouparecomputed the provenance annotation of the first tuple: intuitively, we similarly to the computation for the AGG operator. When expect it to be 1 if at least one of the first two tuples of R K considering the annotation of the aggregated tuple, we en- exists, i.e. ifat least one out of r orr isnon-zero. Indeed 1 2 counter a technical difficulty: we want this annotation to the expression is δ(r + r ) and if we map r ,r to e.g. 2 1 K 2 1 2 be equal 1K if the input relation includes at least one tuple and 1 respectively, we obtain δN(3)=1. inthecorrespondinggroup,and0 otherwise(forintuition, K We use SPJU-AGB as the name for relational algebra consider the case of bag relations, in which the aggregated with the two new operators AGG and GB. We note that resultcanhaveatmostmultiplicity1);weconsequentlyen- the poly-size overhead property is still fulfilled for queries rich our structure to include an additional construct δ that in SPJU-AGB ; commutation with homomorphism also ex- will capturethat, as follows: tendsto SPJU-AGB(see proof in theAppendix). Recallthatanadditionaldesideratumfromthesemantics Definition 3.6. A (commutative) δ-semiring is an alge- was bag / set compatibility. Recall that sets and bags are braicstructure(K,+ ,· ,0 ,1 ,δ )where(K,+ ,· ,0 ,1 ) K K K K K K K K K modeled byK =N and K =B respectively. Wenext study isacommutativesemiringandδ :K →K isaunaryoper- K compatibilityinamoregeneralway,forarbitraryK andM. ationsatisfyingthe“δ-laws”δK(0K)=0K andδK(n1K)=1K foralln≥1. IfK andK′ areδ-semiringsthenahomomor- 3.4 Annotation-aggregation compatibility phism between them is a semiring homomorphism h:K → The first desideratum we listed was an obvious sanity K′, for which we have in addition h(δ (k))=δ (h(k)). K K′ check: whateversemanticswedefine,whenspecializedtothe familiar aggregates of max, min and summation, it should The δ-laws completely determine δB and δN. But they producefamiliar results. Sincewe had to takean excursion leavealotoffreedomforthedefinitionofδK inothersemir- through the tensor product K⊗M, this familiarity is not ings; in particular for the security semiring, a reasonable immediate. However, the following proposition holds (its choice for δS is theidentity function. correctness will follow from theorems 3.12 and 3.13). As with any equational axiomatization, we can construct Proposition 3.9. In the following constructions: B ⊗ thecommutativeδ-semiringfreely generated byasetX,denoted N[X,δ], bytaking thequotientof theset of MAX, B⊗MIN, and N⊗SUM, ι : M → K ⊗M where ι(m)=1 ⊗m is a monoid isomorphism. {+,·,0,1,δ}-algebraic expressionsbythecongruencegener- K ated by the equational laws of commutative semirings and andthismeansoursemanticssatisfiestheset/bagcompati- the δ-laws. For example, if e and e′ are elements of N[X,δ] bility property because in thesecases computingin K⊗M (i.e., congruence classes of expressions given by some rep- exactly mirrors computingin M. resentatives) then e+N[X,δ]e′ is thecongruence class of the Butofcourse,wearealsointerestedinworkingwithother expression e+e′. The elements of N[X,δ] are not standard semirings, in particular the provenance semiring, for which polynomials but certain subexpressions can be put in poly- N[X]⊗MandM areingeneralnotisomorphic(inparticular, nomial form, for exampleδ(2+3xy2) or 3+2δ(x2+2y)z2. ι is not surjective and thus not an isomorphism). In fact, We are now ready to define the group by (denoted GB) the whole point of working in N[X]⊗MAX, for example, operation; subsequently we exemplify its use, including in is to add annotated aggregate computations to the domain particular therole of δ: of values. Most of these do not correspond to actual real numbers as e.g. ι(MAX) is a strict subset of N[X]⊗MAX Definition 3.7. LetRbeaK-relationonsetofattributes (andsimilarlyι(SUM)isastrictsubsetofN[X]⊗SUMetc.). U, let U′ ⊆ U be a subset of attributes that will be grouped However,whenprovenancetokensarevaluatedtoobtainset and U′′ ∈U be the subset of attributes with values in M (to (orbag)instances,wecangobackintoι(MAX)(orι(SUM) be aggregated). We assume that U′∩U′′ =∅. For a tuple t, etc.), and then we should obtain familiar results by“strip- we define T ={t′ ∈supp(R)|∀u∈U′ t′(u)=t(u)}. ping off”the ι. It turns out that this works correctly with We then define the aggregation result R′ = GBU′,U′′(R) N[X]butnotnecessarilywitharbitrarycommutativesemir- as follows: ingsK. Thereason isthatnotonlythatιisnotanisomor- phism,butingeneralitmaybebeunfaithful(notinjective). Indeedι:SUM→B⊗SUMis not injective: R′(t)=δK(Σt′∈TR(t′)) ∀Tu6=∈φU,′a′ndt(u)=Σt′∈TR(t′)⊗t′(u) ι(4)=ι(2+2)=ι(2)+K⊗M ι(2)=⊤⊗2+K⊗M ⊤⊗2= 0 Otherwise. =(⊤∨⊤)⊗2=⊤⊗2=ι(2)  This is not surprising, as it is related to the well-known Example 3.8. Consider the relation R: difficulty of making summation work properly with set semantics. Ingeneral,wethusdefinecompatibilityasfollows: Dept Sal d1 20 r1 Definition 3.10. We say that a commutative semiring d1 10 r2 K and a commutative monoid M are compatible if ι is in- d2 10 r3 jective. The point of the definition is that when there is compat- abilitytousetheidentitiesthatholdinSandtothusreduce ibility, we can work in K ⊗M and whenever the results the size of annotations in query results. We can do better belongtoι(M),wecansafelyreadthemasfamiliaranswers by taking the quotient of N[S] by the smallest congruence from M. Wegivethreetheoremsthatcapturesomegeneral containing thefollowing identities: conditions for compatibility. First, we note that if we work with a semiring in which • ∀s1,s2 ∈S s1 ≥s2=⇒s1·N[S]s2 =s1. +K isidempotent,suchasBorS,acompatiblemonoidmust • ∀c∈N,s∈S 0·N[S]s=c·N[S]0S =0. also beidempotent (e.g. MIN or MAX but not SUM): Proposition 3.11. LetK besomecommutativesemiring • ∀c∈N c·N[S]1S =c. suchthat+ isidempotent,andletM besomecommutative We will denote the resulting quotient semiring by SN. It K monoid. IfM iscompatiblewithK,then+M isidempotent. is easy to check that the faithfulness of the embeddings of Proof. ι(m)=1 ⊗m=(1 + 1 )⊗m=1 ⊗m+ N and S in N[S] is preserved by taking the quotient. Most K K K K K K⊗M importantly,SN is still homomorphicto N. Thus, 1 ⊗m=1 ⊗(m+ m)=ι(m+ m) K K M M Corollary 3.15. SN is compatible with any commuta- Nicely enough, idempotent aggregations are compatible tive monoid M. with every annotation semiring K that is positive with respect to +K. K is said to be positive with respect to +K Example 3.16. Consider the SUM monoid. Let R,S be if k +K k′ = 0K ⇒ k = k′ = 0K. For instance, B, N, S thefollowingS-relationswhichbytheembeddingofSwetake and N[X] are such semirings (but not (Z,+,·,0,1)). The as SN-relations: following theorem holds: A A Theorem 3.12. IfM isacommutativemonoidsuchthat 30 T 30 S +M is idempotent, then M is compatible with any commu- 10 1S tative semiring K which is positive with respect to + . K R Proof sketch. Wedefineh:K⊗M →M as S h(Pi∈Iki⊗mi) = Pj∈Jmj where J = {j ∈ I|kj 6= 0}. Consider the query: AGG(R ∪ΠS.A(S ⊲⊳ R)). Ignoring We can show that h is well-defined (details deferred to the the annotations, the expected result (under bag semantics) Appendix); since ∀m∈M h◦ι(m)=m, ι is injective and is70. Workingwithinthe(compatible) semantics definedby thusK and M are compatible. SN⊗SUM, the query result contains an aggregated value of (T·SNS+SNS)⊗30+S⊗10. Wecan further simplifythisto For general (and in particular non-idempotent) monoids T⊗30+S⊗30+S⊗10=T⊗30+S⊗40. Thismeans that (ine.gp.arStUicMula)rwhoeldidsefnotrifNy[aXs])u,ffithciaetntalcloowndsiftoiorncoomnpKati(bwilhiticyh: ea.ngd. fwoeracaunseursewitthhecirnedveernsteiaolsfTι tthoemquaepryitrteosuNltaisn1dSNob⊗ta7i0n, Theorem 3.13. Let K be a commutative semiring. If 70. Similarly, for a user with credentials S, the query result there exists a semiring homomorphism from K to N then is mapped to 40. These are indeed the expected results. K is compatible with all commutative monoids. Note that if we would have used in the above example S Proof sketch. Let h′ be a homomorphism from K to instead of SN we would have(T+SS)=S so (T+SS)⊗30 N, and M be an arbitrary commutative monoid. We define would be thesame as S⊗30. For a user with credentials T a mapping h:K⊗M → M by h(Σki⊗mi) =Σh′(ki)mi. we could either use this, leading to the result of 1S⊗40, or We show in the Appendix that h is well-defined and that use the same computation done in the example, to obtain h◦ι is theidentityfunction henceι is injective. 1S⊗70. Indeed,inS⊗SUM,wehave1S⊗40=1S⊗70. This is the same phenomenon demonstrated in the beginning of Corollary 3.14. Thesemiringofprovenance polynomi- thissubsection forB,where ιisnot injective,preventingus als N[X] is compatible with all commutative monoids. from stripping it away. Now consider the security semiring S. It is idempotent, Note also that if we would have used N[S] instead of SN then we could not havedone theillustrated simplifications. andthereforenotcompatiblewithnon-idempotentmonoids such as SUM. Still, we want to be able to use S and other 4. NESTED AGGREGATIONQUERIES non-idempotent semirings, while allowing the evaluation of aggregation queries with non-idempotent aggregates. This Sofarwehavestudiedonlyquerieswheretheaggregation would work if we could construct annotations that would operatoristhelastoneperformed. Inthissectionweextend allow us to use Theorem 3.13, in other words, if we could thediscussiontoqueriesthatinvolvecomparisonsonaggre- combine annotations from S, with multiplicity annotations gate values. We first demonstrate the difficulties that arise (i.e. annotationsfromN). Weexplainnexttheconstruction in designing an algebra for such queries, then explain how of such a semiring SN (for security-bag), and its compati- to extendtheconstruction toovercome these difficulties. bilitywithanycommutativemonoidM willfollowfromthe existence of a homomorphism h:SN→N. Note. Forsimplicity,allresultsandexamplesarepresented forqueriesinwhichthecomparisonoperatorisequality(=). Constructinga compatiblesemiring. We start with the Howevertheresultscaneasilybeextendedtoarbitrarycom- semiringofpolynomialsN[S],i.e. polynomialswhereinstead parison predicates, that can bedecided for elementsof M. ofindeterminates(variables)wehavemembersofS,andthe coefficientsarenaturalnumbers. AlreadyN[S]iscompatible 4.1 Difficulties with any commutative monoid M, as there exists a homo- We start by exemplifying where the algebra proposed for morphismh:N[S]→N;butifweworkwithN[S]welosethe restricted aggregation queries, fails here: Example 4.1. Reconsidertherelation(denotedR′)which Theright-hand-sideisamonotone,infact continuousw.r.t. is the result of aggregation query, depicted in Example 3.8. the usual set inclusion operator, hence this equation has a Further consider a query Q that selects from R′ all tu- set-theoretic least solution (no need for order-theoretic do- select ples for which the aggregated salary equals 20. The crux maintheory). Thesolutionalsohasanobviouscommutative is that deciding the truth value of the selection condition semiring structureinduced bythat of polynomials. The so- involves interpreting the comparison operator on symbolic lutionsemiringisK =(X,+Kb,·Kb,0Kb,1Kb),andwecontinue representation ofvaluesinR′; sofar,wehavenowayofin- bytakingthequotibentonKdefinedbythefollowingaxioms. terpreting the obtained comparison expression, for instance r ⊗20+r ⊗10“equals”20, and thus we cannot decide the Forall k1,k2 ∈K,c1,c2,bc3,c4 ∈K⊗M: 1 2 b existence of tuples in the selection result. 0Kb ∼ 0K Note that in the above example, the truth value of the 1Kb ∼ 1K comparison(andconsequentlythesetoftuplesinthequery k1+Kbk2 ∼ k1+K k2 rteuspullets)indetpheen(dosriigninaanl)oinn-pmuotnroetlaotniiocnwRa:ynoonteththeaetxiifstweencmeaopf k1·Kbk2 ∼ k1+K k2 r to 1 and r to 0 then the tuple with dept. d appears in [c1 =c3] ∼ [c2 =c4](if c1 =Kb⊗Mc2, c3 =Kb⊗Mc4) 1 2 1 the query result, but if we map both to 1, it does not. The challenge that this non-monotonicity poses is fundamental, and if K and M are such that ι defined by ι(m) = 1 ⊗m andisencounteredbyanyalgebraon(M,K)-relations. The K isanisomorphism(andlethbeitsinverse),wefurthertake following proposition, which is the counterpart of proposi- thequotient definedby: for all a,b∈K⊗M, tion 3.2, holds(proof deferred to theAppendix): Proposition 4.2. There is no (M,K)-relation seman- (*)[a=b] ∼ 1K(if h(a)=Mh(b)) tics for nested aggregation queries with MAX-(or MIN-)- [a=b] ∼ 0K(if h(a)6=Mh(b)) aggregation that is both set-compatible and commutes with homomorphisms. Similarly for SUM-aggregation and bag- Weuse KM to denotethesemiring obtained byapplying compatibility. theaboveconstructiononasemiringK andacommutative Consequently,amoreintricateconstructionisrequiredfor monoid M. A key property is that, when we are able to nested aggregation queries. interprettheequalitiesinM,KM collapsestoK. Formally, 4.2 An Extended Structure Proposition 4.4. IfK andM aresuch that K⊗M and We start with an example of our treatment of nested ag- M are isomorphic via ι then KM =K. gregation queries, then givetheformal construction. The proof (deferred to the Appendix) is by induction on Example 4.3. Reconsider example 4.1, and recall that thestructureof elementsin KM,showing that at each step thechallengeinqueryevaluationliesincomparingelements wecan“solve”anequalitysub-expression,andreplaceitwith of K⊗M with elements of M (or K⊗M, e.g. in case of 0 or 1 . K K joins). Our solution is to introduce to the semiring K new elements,oftheform[x=y]wherex,y∈K⊗M (ifweneed Lifting homomorphisms. To conclude the description of to compare with m ∈ M, we use ι(m) instead). The result the construction we explain how to lift a semiring homo- of evaluating the query in example 4.1 (using M = SUM) morphism from h : K → K′ to hM : KM → K′M, for any will then be captured by: commutative monoid M and semirings K,K′. hM is defined recursively on the structure of a ∈ KM: if a ∈ K we define hM(a) = h(a), otherwise a = [b⊗m =c⊗m ] for Dept Sal 1 2 some b,c ∈ KM and m ,m ∈ M and we define hM(a) = d r ⊗20 δ(r + r )· 1 2 1 1 1 K 2 K hM(b)⊗m =hM(c)⊗m . Note that the application of +K⊗Mr2⊗10 (cid:2)r1⊗20+K⊗M r2⊗10=1K ⊗20(cid:3) (cid:2)a homomorp1hism hM maps2(cid:3)equalityexpressions toequality d2 r3⊗10 δ(r3)·K[r3⊗10=1K ⊗20] expressions (in which elements in K′ appear instead of el- Intuitively, since we do not know which tuples will satisfy ements of K appeared before). If K′ and M are such that the selection criterion, we keep both tuples and multiply the their corresponding ι : M → K ⊗M defined by ι(m) = provenanceannotationofeachofthembyasymbolicequality 1K ⊗M is injective, then we may“resolve the equalities”, expression. These equality expressions are kept as symbols otherwise the(new) equality expression remains. until we can embed the values in M = SUM and decide 4.3 The Extended Semantics the equality (e.g. if K = N), in which case we “replace” it by 1 if it holds or 0 otherwise. For example, given a Theextendedsemiring constructionallows ustodesign a K K homomorphism h : N[X] → N, h(r ) = h(r ) = 1, then semantics for general aggregation queries. Intuitively,when 1 2 hM(r1⊗20+K⊗Mr2⊗10) = h(r1)⊗20+K⊗Mh(r2)⊗10 = the existence of a tuple in the result relies on the result of 1⊗306=1⊗20, thus the equality expression isreplaced with a comparison involving aggregate values(as in theresult of (i.e. mapped by the homomorphism to) 0 . applying selection or joins), we multiply the tuple annota- K tion bythecorresponding equation annotation. We next define the construction formally; the idea un- In the sequel we assume, to simplify the definition, that derlying the construction is to define a semiring whose ele- thequeryaggregates andcomparesonlyvaluesofKM ⊗M ments are polynomials, in which equation elements are ad- (avaluem∈M isfirstreplacedbyι(m)=1 ⊗m). Inwhat K ditional indeterminates. To achieve that, we introduce for follows,letR(R ,R )be(M,KM)-relationsonanattributes 1 2 any semiring K and any commutative monoid M, the“do- set U. Recall that for a tuple t, t(u) (where u ∈ U) is the main”equation K = N[K ∪{[c = c ] | c ,c ∈ K⊗M}]. valueof theattributeuin t;also for U′ ⊆U, recall that we 1 2 1 2 b b uset|U′ todenotetherestrictionofttotheattributesinU′. Given a homomorphism h : N[X] → N we can “solve” Last, we use (KM ⊗M)U to denote the set of all tuples on the equations, e.g. if h(r ) = 1, h(r ) = 0 and h(r ) = 1 2 3 attributessetU,withvaluesfromKM⊗M. Thesemantics 2, we obtain an aggregated value of 1⊗40. Note that the follows: aggregation value is not monotone in r ,r ,r : map r to 1 1 2 3 2 (and keep r ,r as before), to obtain 1⊗20. 1. empty relation: ∀t φ(t)=0. 1 3 2. union: (R ∪R )(t)= 5. DIFFERENCE 1 2 R (t′)· [t′(u)=t(u)] if t∈supp(R ) We next show that via our semantics for aggregation, we Pt′∈supp(R1) 1 Qu∈U 1 canobtainforthefirsttimeasemanticsforarbitraryqueries +Pt′∈supp(R2)R2(t′)·Qu∈U[t′(u)=t(u)] ∪supp(R2) with difference on K-relations. We describe the obtained semantics and study some of its properties. 0 Otherwise. 5.1 Semantics forDifference 3. projection: Let U′ ⊆ U, and let T = {t|U′ | t ∈ We first note that difference queries may be encoded as supp(R)}. Then ΠU′(t)= querieswithaggregation,usingthemonoidB=({⊥,⊤},∨,⊥) R(t′)· [t(u)=t′(u)] if t∈T (thefollowing encodingwas inspired by [29b, 9]): P0 t′∈Supp(R) Qu∈U′ Otherwise. R−S =Π⊲⊳aa11..,..a..na{n(cid:0)(GRB×{a⊥1,.b.).a}n.},b(R×⊥b∪S×⊤b)(cid:1)  ⊥ and⊤ arerelationsonasingleattributeb,containing 4. selection: If P is an equality predicate involving the b b asingletuple(⊥)and(⊤)respectively,withprovenance1 . equation of some attribute u∈U and a value m∈M K Using thesemantics of Section 4, we obtain a semantics for then (σP(R))(t)=R(t)·[t(u)=ι(m)]. thedifference operation. 5. valuebasedjoin: WeassumeforsimplicitythatR and Interestingly, we next show that the obtained semantics 1 R havedisjointsetsofattributes,U andU resp.,and canbecapturedbyasimpleandintuitiveexpression. First, 2 1 2 that the join is based on comparing a single attribute we note that since B is idempotent, every semiring K posi- of each relation. Let u′1 ∈ U1 and u′2 ∈ U2 be the tive with respect tob+K is compatible with B (see Theorem attributesto join on. Forevery t∈(KM ⊗M)U1∪U2: 3.12). The following proposition then holdsbfor every K,K′ (R1 ⊲⊳R1.u1=R2.u2 R2)(t)= and every two (B,K)-relations R,S (proof deferred to the R1(t|U1)·R2(t|U2)·K[t(u1)=t(u2)]. Appendix): b Proposition 5.1. Foreverytuplet,semiringsK,K′such Simple Variants. Natural join (when U1 and U2 are that K′bB ⊗ B is isomorphic to B via ι(m) = 1K ⊗ m, if not necessarily disjoint) is captured by a similar ex- h:K →K′bis a semiring homomborphism then: pression, with the equality sub-expression on the at- hbB([(R−S)(t)])=hbB([S(t)⊗⊤=0]· R(t)). tributes common to U and U ; join on multiple val- K 1 2 uesiscapturedbymultiplicationbythecorresponding The obtained provenance expression is thus“equivalent” multiple equality expressions; in the representation of (in the precise sense of Proposition 5.1) to [S(t) ⊗ ⊤ = cartesian product (denoted by ×) no equality expres- 0]·R(t). The following lemma helps us to understand the sions appear (only R1(t|U1)·R2(t|U2)). meaning of theobtained equalityexpression: 6. Aggregation: AGG (R)(t)= Lemma 5.2. ForeverysemiringK whichispositivew.r.t. M + and h :K → B, hM([S(t)⊗⊤=0]) = ⊤ iff h(S(t)) =  1 t(u)=Pt′∈supp(R)R(t′)∗K⊗Mt′(u) ⊥K.  Proof. Itisclearthatifh(S(t))=⊥,hM([S(t)⊗⊤=0])= 0 otherwise [h(S(t))⊗⊤=0]= [⊥⊗⊤=0]= ⊤. For the other direc-  7. Group By: Let U′ ⊆U be a subset of attributes that tion, assume that h(S(t)) = ⊤. Thus [h(S(t))⊗⊤=0] = will be grouped and u ∈ U\U′ be the aggregated at- [⊤⊗⊤=0]. SinceBandBarecompatible,ι:B→B⊗Bis tribute. Then for every t∈(KM ⊗M)U′∪{u}: injective;thusι(⊥)6=ι(⊤)b;consequentlyhM([S(t)⊗⊤=b0])= [⊤⊗⊤=0]=⊥. GBU′,uR(t)= Consequently,thesemanticscanbeinterpretedasfollows: δ((ΠU′R)(t|U′)) t(u)=Pt′∈supp(R)(R(t′)·K a tuple t appears in the result of R−S if it appears in R,  Qu∈U′[t′(u)=t(u)])∗K⊗Mt′(u) rbeustuldtooefsRn−otSa,piptecaarrriinesSit.sWorihgeinnatlhaenntuoptaletioanppfreoamrsRin. tIh.ee. 0 otherwise theexistence of t in S is used as a boolean condition. Example 5.3. Let R,S be the following relations, where It is straightforward to show that the algebra satisfies R contains employees andtheir departments andS contain- set/bag compatibility and poly-sizeoverhead;commutation ing departments that are designated to be closed: with homomorphism is provedin theAppendix. Example 4.5. Reconsider the relation in Example 4.3, ID Dep and let us perform another sum aggregation on Sal. The 1 d t Dep 1 1 value in the result now contains equation expressions: 2 d t d t 1 2 1 4 δ(r + r )· r ⊗20+ r ⊗10=1 ⊗20 2 d t 1 K 2 K(cid:2) 1 K⊗M 2 K (cid:3) 2 3 ∗ r ⊗20+ r ⊗10 S K⊗M(cid:0) 1 K⊗M 2 (cid:1) + δ(r )· [r ⊗10=1 ⊗20]∗ r ⊗10 R K⊗M 3 K 3 K K⊗M 3