The Query Compiler

The Query Compiler • Parses SQL query into parse tree • Transforms parse tree into expression tree (logical query plan) • Transforms logical query plan into physical query plan

SQL query parse parse tree convert answer logical query plan execute apply laws Pi “improved” l.q.p pick best estimate result sizes statistics {P1,C1>...} l.q.p. +sizes estimate costs consider physical plans {P1,P2,…..}

Grammar for simple SQL <Query> ::= <SFW> <Query> ::= (<Query>) <SFW> ::= SELECT<SelList>FROM<FromList>WHERE<Cond> <SelList> ::= <Attr>,<SelList> <SelList> ::= <Attr> <FromList> ::= <Relation>, <FromList> <FromList> ::= <Relation> <Cond> ::= <Cond>AND<Cond> <Cond> ::= <Tuple>IN<Query> <Cond> ::= <Attr>=<Attr> <Cond> ::= <Attr>LIKE<Pattern> <Tuple> ::= <Attr> Atoms(constants), <syntactic categories>(variable), ::= (can be expressed/defined as)

Query and parse tree StarsIn( title,year,starName ) MovieStar( name,address,gender,bdate ) Query: Give titles of movies that have at least one star born in 1960 SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960%' );

Another query equivalent SELECT title FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE '%1960%' ;

Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> , <FromList> AND title StarsIn <RelName> MovieStar <Condition><Condition> <Attribute> = <Attribute> <Attribute> LIKE <Pattern> starName name birthdate ‘%1960’

The Preprocessor(expand query & semantic checking) • Checks against schema definition: • Relation uses • Attribute uses, resolve names ( A to R.A) • Use of types (strings, integers, dates, etc) and operators’ arguments type/arity • These preprocessing functions are called semantic checking • If all tests are passed, then the parse tree is said to be valid

Algebraic laws for transforming logical query plans • Commutative and associative laws: Above laws are applicable for both sets and bags

Theta-join • Commutative: • Not always associative: • On schema R(a,b), S(b,c), T(c,d) the first query can not be transformed into the second: (Why?) Because, we can’t join S and T using the condition a<d since a is an attribute of neither S nor T.

Laws Involving Selection () Splitting laws Only if R is a set. The union is “set union” Order is flexible

Laws Involving Selection () What about intersection? For intersection, the selection is required to be pushed to one argument.

If all attributes in the condition C are in R (for binary operators)

Example: • Consider relation schemas R(A,B) and S(B,C)and the expression below: (A=1 OR A=3) AND B<C(RS) • Splitting ANDA=1 OR A=3(B<C(RS)) • Push to S A=1 OR A=3(RB<C(S)) • Push to RA=1 OR A=3(R) B < C(S)

Some Trivial Laws • Watch for some extreme cases: • an empty relation: • e.g., R S = S, if R =  • a selection or theta-join whose condition is always satisfied • e.g., C(R) = R, if C = true • a projection on all attributes is “better” not to be done at all!!

Pushing selections • Usually selections are pushed down the expression tree. • The following example shows that it is sometimes useful to pull selection up in the tree. StarsIn(title,year,starName) Movie(title,year,length,studioName) CREATE VIEW MoviesOf1996 AS SELECT * FROM MOVIE WHERE year=1996; Query:Which stars worked for which studios in 1996? SELECT starName,studioName FROM MoviesOf1996 NATURAL JOIN StarsIN;

pull selection up then push down

Laws for (bag) Projection • A simple law: Project out attributes that are not needed later. I.e. keep only the input attr. and join attr. • Projections cannot be pushed below S, or either set/bag versions of and – • Example: Consider R(A,B) and S(A,C). Supp. R = {(1,2)} and S = {(1,3)}. •  A(R S) = A() butA(R) A(S) = {(1)}

Example • Schema: StarsIn(title,year,starName) • Query:SELECT starName FROM StarsIn WHERE year = 1996; starName starName year=1996 year=1996 Should we can transform to  starName,year StarsIn StarsIn

The Query Compiler