SQL, Recursive Deductive Databases, Query Evaluation
Slides based on book chapter, By Ramankrishnan and Gehrke DBMS Systems, 3rd ed.
cs561
1
Motivation
Can SQL-92 express queries: Are we running low on any parts needed to build a ZX600 sports car? What is total component and assembly cost to build ZX600 at today's part prices? Can we extend the query language to cover such queries? Yes, by adding recursion.
2
Towards Semantics : Datalog
SQL queries can be read as follows: "If some tuples exist in FROM tables that satisfy WHERE conditions, then SELECT tuple is in answer."
Datalog is query language with if-then flavor: Important extra: Answer table can appear in From clause, i.e., be defined recursively. It's a logic: Prolog style syntax commonly used. 3
trike 3 1 wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
trike trike
part
Example
wheel 3 frame 1 1
frame seat
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
4
Assembly instance
number
subpart
wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
trike trike
part
3
1
wheel 3 frame 1 1
frame seat
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
Query : "Find all components of trike !" Can you write relational algebra query to compute answer on the given instance of Assembly?
Assembly instance 5
number
subpart
Example
trike
3
1
wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
trike trike
part
trike
wheel 3 frame 1 1
frame seat
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
Query : Find components of `trike'! There is no relational algebra (or SQL-92) query that computes answer on all Assembly instances.
Assembly instance 6
number
subpart
Example
wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
trike trike
part
Example
3
1
wheel 3 frame 1 1
frame seat
Find the components of a trike? We can write a relational algebra query to compute the answer on the given instance of Assembly. But there is no R.A. (or SQL-92) query that computes the answer on all Assembly instances.
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
Assembly instance 7
number
subpart
trike
Problem with R.A. / SQL-92
Intuitively,
we must join Assembly with itself to deduce that trike contains spoke and tire. Takes us one level down Assembly hierarchy. To find components that are yet one level deeper (e.g., rim), we need another join. To find all components, need as many joins as there are levels in the given instance!
8
Problem with R.A. and SQL-92
Conclude
:
we need as many joins as there are levels in the given instance!
Problem
:
For any RA expression, we can create an Assembly instance for which some answers are not computed by including more levels than number of joins in expression!
9
Datalog Query that Does the Job
Given: Assembly(Part, DirectSubParts, Qty). Compute: Comp(Part, AllSubparts). Comp(Part, Subpt) : Assembly(Part, Subpt, Qty). Comp(Part, Subpt) : Assembly(Part, Part2, Qty), Comp(Part2, Subpt). head of rule implication body of rule
Can read second rule as follows: "For all values of Part, Subpt and Qty, if there is a tuple (Part, Part2, Qty) in Assembly and a tuple (Part2, Subpt) in Comp, then there must be a tuple (Part, Subpt) in Comp."
10
Datalog
11
Datalog
Datalog : Relational QL inspired by Prolog. Program : A collection of rules Rule : If RHS exists, must be in LHS result.
12
Using Rule to Deduce New Tuples
Each
rule is a template for making inferences:
by assigning constants to variables so that each body "literal" is a tuple in the corresponding relation, we identify tuple(s) that must be in head relation.
13
Using Rule to Deduce New Tuples
Example:
Comp(Part, Subpt) : Assembly(Part, Subpt, Qty).
By setting (Part=trike, Subpt=wheel, Qty=3) in rule, we deduce that tuple < trike, wheel > is in relation Comp. This is called an inference using the rule.
Rule Application :
Given a set of tuples, we apply rule by making all possible inferences with tuples in body.
14
Example of Datalog
Comp(Part, Subpt) : Assembly(Part, Subpt, Qty), Qty>2
Conjunctive queries : PROJECT (SELECT ( JOIN )). Conjunctive queries with UNION: Several rules. Conjunctive queries are "monotonic" : Applying to superset of instances will return a larger equal result.
15
Example: Computation with Recursion
Comp(Part, Subpt) : Assembly(Part, Subpt, Qty). Comp(Part, Subpt) : Assembly(Part, Part2, Qty), Comp(Part2, Subpt).
For any instance of Assembly, we compute all Comp tuples by repeatedly applying two rules. Actually:
apply Rule 1 just once (projection) then apply Rule 2 repeatedly ( cross-product with equality join )
16
trike trike
wheel 3 frame 1 1
trike trike trike trike
spoke tire seat pedal
trike trike trike trike wheel wheel trike trike
spoke tire seat pedal rim tube rim tube
frame seat
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
wheel rim wheel tube
Comp tuples by applying Rule 2 once
Assembly instance Comp(Part, Subpt) :
Assembly(Part, Part2, Qty), Comp(Part2, Subpt).
Comp tuples by applying Rule 2 twice
17
Example
trike trike trike trike
spoke tire seat pedal
trike trike trike trike
spoke tire seat pedal
For any instance of Assembly, we can compute all Comp tuples by repeatedly applying the two rules. (Actually, we can apply Rule 1 just once, then apply Rule 2 repeatedly.)
wheel rim wheel tube
Comp tuples got by applying Rule 2 once
wheel rim wheel tube trike trike rim tube
Comp tuples got by applying Rule 2 twice
18
Datalog vs. SQL Notation
A
collection of Datalog rules can be rewritten in SQL syntax with recursion
Comp(Part, Subpt) :Assembly(Part, Subpt, Qty). Comp(Part, Subpt) :Assembly(Part, Part2, Qty), Comp(Part2, Subpt).
19
Datalog vs. SQL Notation
Datalog
rules rewritten into SQL syntax:
WITH RECURSIVE Comp(Part, Subpt) AS (SELECT A1.Part, A1.Subpt FROM Assembly A1) UNION (SELECT A2.Part, C1.Subpt FROM Assembly A2, Comp C1 WHERE A2.Subpt=C1.Part); SELECT * FROM Comp C2;
20
Datalog vs. SQL Notation
Or,
modify query to have selection:
WITH RECURSIVE Comp(Part, Subpt) AS (SELECT A1.Part, A1.Subpt FROM Assembly A1) UNION (SELECT A2.Part, C1.Subpt FROM Assembly A2, Comp C1 WHERE A2.Subpt=C1.Part); SELECT * FROM Comp C2
Where C2.part = trike ;
21
Theoretical Foundations
(least fixpoint semantics conceptual evaluation strategy a la relational algebra )
22
Fixpoint
Let f be a function that takes values from domain D and returns values from D. A value v in D is a fixpoint of f if f(v)=v.
23
Fixpoints
Consider function double+ on integers. Example:
double+({1,2,5}) = {2,4,10} Union {1,2,5}
What are example fixpoints for double+?
24
Fixpoints
Function double+ : double+ ({1,2,5})={2,4,10} Union {1,2,5} Example Fixpoints (input sets):
The set of all integers is a fixpoint of double+. The set of all even integers is another fixpoint The set of integer zero is another fixpoint.
25
Least Fixpoint Semantics
Least fixpoint of a function f is a fixpoint v of f such that every other fixpoint of f is larger than or equal to v. Observations :
Least fixpoint may not be unique, i.e., multiple exist. If two minimal fixpoints, neither is smaller than the other.
Least fixpoint of double+ ?
26
Least Fixpoint Semantics for Datalog
Datalog ~~ function defined by relational algebra (without set-difference). Datalog program is a function that applied to set of tuples returns another set of tuples Result : Datalog (fortunately) always has least fixpoint ! What does "least fixpoint" mean for us ?
27
Least Fixpoint Semantics for Datalog
Comp = PROJECT [1,5] (PROJECT[1,2] (Assembly) UNION (Assembly JOIN[2=1] Comp) )
with Comp = function (Comp, Assembly) defined by RA expression. Least Fixpoint ~ Is instance of Comp that satisfies this query (our query answer). Yeah !
28
Least Fixpoint Semantics for Datalog
The least fixpoint of a function f is a fixpoint v of f such that every other fixpoint of f is smaller than or equal to v. Eg., Big depends on Small table. In general, there may be no least fixpoint (we could have two minimal fixpoints, neither of which is smaller than the other). If we think of a Datalog program as a function that is applied to a set of tuples and returns another set of tuples, this function (fortunately!) always has a least fixpoint.
29
Unsafe/Safe Datalog Program
If an unbound variable on RHS, then program is unsafe:
PriceParts (Part,Price) : Assembly(Part, Subpart, Qty), Qty>2.
Note: Infinite number of different values for
Price would all make the rule correct.
If
least model of program is not finite, then program is unsafe. Conclusion : all variables in head of rule must also appear in body (range-restricted).
30
Negation (Set-Difference)
Big(Part) : Assembly(Part, Subpt, Qty), not Small(Part).
What is it in relational algebra?
Big(Part) = PROJECT_part (Assembly) DIFFERENCE PROJECT_part (Assembly JOIN_on_part Small ).
31
Negation (Set-Difference)
What problem does it cause ? Big(Part) : Assembly(Part, Subpt, Qty), Qty >2, not Small(Part). Small(Part) : Assembly(Part, Subpt, Qty), not Big(Part).
If rules contain negation, then there may not be a least fixpoint.
32
Negation
Big(Part) : Assembly(Part, Subpt, Qty), Qty >2, not Small(Part). Small(Part) : Assembly(Part, Subpt, Qty), not Big(Part).
No one least fixpoint? Consider our example Assembly instance
What is intuitive answer? trike is the only part that has 3 or more copies of some subpart. Intuitively, it should be in Big() If we apply Rule 1 first, we have Big (trike). If we apply Rule 2 first, we have Small (trike) and Big () is empty. All other parts are in Small () in both fixpoints. 33 Which one is right answer to our query ? ? ?
Negation
Big(Part) : Assembly(Part, Subpt, Qty), Qty >2, not Small(Part). Small(Part) : Assembly(Part, Subpt, Qty), not Big(Part).
If rules contain not, then there may be two or more least fixpoints. Order of applying rules determines answer Bad ! Unpredictable result ! Need method to choose intended fixpoint. Analysis : Order of applying rules determines answer because :
Addition of tuples into one output relation may disallow inference of other tuples
34
"NOT" in Body?
Still Safe : Not always, must be careful ! Range-restricted program :
every variable X in head of rule appears in some relation occurrence in body. every variable appears in some positive (nonnegated) predicate p in body , and p is either a base relation or defined by a safe rule.
Big(Part) :- Assembly(Part, Subpt, Qty), Qty >2
35
Stratification :
Technique to determine if recursive datalog with negation is safe
36
Stratification ( Solution )
T
depends on S if some rule with T in the head contains S or (recursively) some predicate that depends on S, in the body. Big () depends on Small ().
Example: Stratified
program: If T depends on not S, then S cannot depend on T (or not T).
37
Stratification
If
program is stratified, tables in program can be partitioned into strata (fully order dependencies using topological sort ):
Stratum 0: All tables. database Stratum I: Tables defined in terms of tables in Stratum I and lower strata.
(1) If T depends on not S, S is in lower stratum than T. (2) Or, table in stratum I depends negatively only on tables in stratum I-
38
Stratified Program
Datalog query is safe if stratification exits. Graph Method:
Each relation and predicate is a node Each dependency is an edge Each dependency on negated predicate (on RHS) is marked as negative edge.
Check:
Find all strongly connected componts If there is a negative edge in a strongly connected compoentn, query is not safe. Otherwise, query is safe.
Find Strata:
Use topological sort starting from base relations
39
Stratified Program
Question
: Is below Big/Small program stratified?
Big(Part) :- Assembly(Part, Subpt, Qty), Qty >2, not Small(Part). Small(Part) :- Assembly(Part, Subpt, Qty),
40
Stratified Program
Question
: Is Big/Small program stratified?
Big(Part) :- Assembly(Part, Subpt, Qty), Qty >2, not Small(Part). Small(Part) :- Assembly(Part, Subpt, Qty), not Big(Part).
Big/Small:
Mutually recursive tables
41
Fixpoint Semantics for Stratified Programs
Semantics of stratified program given by one of its minimal fixpoints. This fixpoint identified by operational definition: Stratum 0 tables are fixed First compute least fixpoint of all tables in Stratum 1. Then, compute least fixpoint of tables in Stratum 2. Then, compute least fixpoint of tables in Stratum 3, and so on, stratum-by-stratum.
42
Fixpoint Semantics for Stratified Programs
This evaluation strategy is sometimes called "bottom-up" semantics. It is guaranteed to find one minimal fixpoint (even if several may exist). RA : Corresponds to range-restricted stratified Datalog. SQL3 requires stratified programs.
43
Aggregate Operators
44
Aggregate Operators
SELECT A.Part, SUM(A.Qty) FROM Assembly A GROUP BY A.Part
NumParts(Part, SUM(<Qty>)) : Assembly(Part, Subpt, Qty).
The < ... > notation in head indicates grouping; remaining arguments (Part) are GROUP BY fields. To apply such rule, must have all of Assembly relation available. (not on partial computed relation). Stratification with respect to use of < ... > is restriction to deal with this problem; 45 similar to negation.
So far Semantics, Now Query Optimization
46
Evaluation of Datalog Programs
Avoid Avoid
Repeated inferences Unnecessary inferences
47
Query Optimization #1.
48
Evaluation of Datalog Programs
Avoid
Repeated inferences: When recursive rules are repeatedly applied in nave way, we make same inferences in several iterations.
49
trike trike
wheel 3 frame 1 1
trike trike trike trike
spoke tire seat pedal
trike trike trike trike
spoke tire seat pedal
frame seat
frame pedal 1 wheel spoke 2 wheel tire tire tire rim tube 1 1 1
wheel rim wheel tube
Comp tuples by applying Rule 2 once
wheel rim wheel tube trike trike rim tube
Assembly instance Comp(Part, Subpt) :
Assembly(Part, Part2, Qty), Comp(Part2, Subpt).
Comp tuples by applying Rule 2 twice
50
Avoiding Repeated Inferences
Semi-naive
Fixpoint Evaluation: Ensure that when rule is applied, at least one of body facts used was generated in most recent iteration. Such new inference could not have been carried out in earlier iterations.
51
Avoiding Repeated Inferences
Idea:
For each recursive table P, use table delta_P to store P tuples generated in previous iteration.
1. Rewrite program to use delta tables 2. Update delta tables between iterations.
Comp(Part, Subpt) : Assembly(Part, Part2, Qty), Comp(Part2, Subpt). Comp(Part, Subpt) : Assembly(Part, Part2, Qty), delta_Comp(Part2, Subpt).
52
Query Optimization #2.
53
Avoiding Unnecessary Inferences
WITH RECURSIVE Comp(Part, Subpt) AS (SELECT A1.Part, A1.Subpt FROM Assembly A1) UNION (SELECT A2.Part, C1.Subpt FROM Assembly A2, Comp C1 WHERE A2.Subpt=C1.Part) SELECT * FROM Comp C2
Where C2.part = trike.
54
Evaluation of Datalog Programs
Unnecessary
inferences: If we just want to find components of a particular part, say wheel, then first computing general fixpoint of Comp program and then at end selecting tuples with wheel in the first column is wasteful. This computes many irrelevant facts.
55
Evaluation of Datalog Programs
Avoid Idea:
unnecessary inference ! How ?
How to push selection into datalog program?
56
Avoiding Unnecessary Inferences
SameLev(S1,S2) : Assembly(P1,S1,Q1), Assembly(P1,S2,Q2). SameLev(S1,S2) : Assembly(P1,S1,Q1), SameLev(P1,P2), Assembly(P2,S2,Q2).
Semantics?
3
trike
1
wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
57
Avoiding Unnecessary Inferences
SameLev(S1,S2) : Assembly(P1,S1,Q1), Assembly(P1,S2,Q2). SameLev(S1,S2) : Assembly(P1,S1,Q1), SameLev(P1,P2), Assembly(P2,S2,Q2).
Tuple (S1,S2) is in SameLev : if there is path up from S1 to some node and down to S2 with same number of up and down edges.
3
trike
1
wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
58
Avoiding Unnecessary Inferences
Query:
Want all SameLev tuples with spoke in first column. Intuition: Push this selection into fixpoint computation.
How
SameLev(S1,S2) : SameLev(spoke ,S2) : Assembly(P1,S1,Q1), Assembly(P1,spoke,Q1), SameLev(P1,P2), SameLev(P1?spoke?,P2), Assembly(P2,S2,Q2). Assembly(P2,S2,Q2).
59
do that?
Avoiding Unnecessary Inferences
Intuition: "Push" this selection with spoke
SameLev(spoke ,S2) : SameLev(S1,S2) : Assembly(P1,spoke,Q1), Assembly(P1,S1,Q1), SameLev(P1,P2), SameLev(P1,P2), Assembly(P2,S2,Q2). Assembly(P2,S2,Q2). SameLev(spoke,seat) : Assembly(wheel,spoke,2), SameLev(wheel,frame), Assembly(frame,seat,1).
into fixpoint computation.
Other SameLev tuples are needed to compute all such tuples with spoke, e.g., wheel60
"Magic Sets" Idea
1. Define "filter" table that computes all relevant values 2. Restrict computation of SameLev to infer only tuples with relevant value in first column.
61
Intuition : Relevant Values
General: Relevant values contains all tuples t for which we have to compute all same-level tuples with t in first column to answer query. Example : relevant values are all Same-Level tuples whose first field contains value on path trike from spoke up to root.
3 1 wheel frame 2 1 1 1 spoke tire seat pedal 1 1 rim tube
62
We call it Magic-SameLevel
"Magic Sets" in Example
Idea: Define "filter" table that computes all relevant values Here : C...