The Query Compiler

1
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

2
(No Transcript)
3
Grammar for simple SQL

• ltQuerygt ltSFWgt
• ltQuerygt (ltQuerygt)
• ltSFWgt SELECT ltSelListgt FROM ltFromListgt WHERE
  ltCondgt
• ltSelListgt ltAttrgt,ltSelListgt
• ltSelListgt ltAttrgt
• ltFromListgt ltRelationgt, ltFromListgt
• ltFromListgt ltRelationgt
• ltCondgt ltCondgt AND ltCondgt
• ltCondgt ltTuplegt IN ltQuerygt
• ltCondgt ltAttrgt ltAttrgt
• ltCondgt ltAttrgt LIKE ltPatterngt
• ltTuplegt ltAttrgt
• Atoms(constants), ltsyntactic categoriesgt(variable)
  ,

4
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'
• )

5
Another query equivalent

• SELECT title
• FROM StarsIn, MovieStar
• WHERE
• starName name AND birthdate LIKE '1960'

6
Parse Tree
ltQuerygt
ltSFWgt
SELECT ltSelListgt FROM ltFromListgt WHERE
ltConditiongt
ltAttributegt ltRelNamegt , ltFromListgt
AND
title StarsIn ltRelNamegt
MovieStar
ltConditiongt ltConditiongt
ltAttributegt ltAttributegt
ltAttributegt LIKE ltPatterngt
starName name
birthdate 1960
7
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

8
Algebraic laws for transforming logical query
plans

• Commutative and associative laws

Above laws are applicable for both sets and bags
9
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 cant join S and T using the
condition altd since a is an attribute of neither
S nor T.
10
Laws Involving Selection (?)
Splitting laws
Only if R is a set. The union is set union
Order is flexible
11
Laws Involving Selection (?)
What about intersection?
For intersection, the selection is required to be
pushed to one argument.
12
If all attributes in the condition C are in R
(for binary operators)
13
Example

• Consider relation schemas R(A,B) and S(B,C) and
  the expression below
• ?(A1 OR A3) AND BltC(R ?? S)
• Splitting AND ?A1 OR A3 (?B lt C(R ?? S))
• Push ? to S ?A1 OR A3 (R ?? ?B lt C(S))
• Push ? to R ?A1 OR A3 (R) ?? ?B lt C(S)

14
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!!

15
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 year1996
• Query Which stars worked for which studios in
  1996?
• SELECT starName,studioName
• FROM MoviesOf1996 NATURAL JOIN StarsIN

16
pull selection up then push down
17
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)

18
Example

• Schema StarsIn(title,year,starName)
• Query SELECT starName FROM StarsIn
• WHERE year 1996

?starName
?starName
?year1996
?year1996
Should we can transform to ?
?starName,year
StarsIn
StarsIn