Formálna sémantika SQL

Formálna sémantika SQL DBI 001 - Dotazovací jazyky Alexander Kuzmin, Tomáš Kovařík

Obsah • Úvod, výhody formalizácie jayzka SQL • Trojhodnotový n-ticový predikátový kalkul • Popis gramatiky jazyka SQL • Prevod SQL na E3VPC • Ekvivalencia SQL dotazov

Prečo formalizovať SQL? • Optimalizácia dotazov • Určenie výpočetnej sily jazyka • Ekvivalencia SQL dotazov • Vývoj a vylepšovanie samotného jazyka SQL

Ako budeme postupovať • Zavedenie E3VPC (Extended 3-valued tuple predicate calculus) • Výrazy, termy, operátory, formule, kvantifikácia • Rozšírenia • Formálna definícia gramatiky jazyka SQL • Prevod SQL dotazov na E3VPC • Ekvivalencia SQL dotazov

E3VPC • Založený na klasickom 2 hodnotovom n-ticovom predikátovom • kalkule • Základným rozdielom je využitie 3 hodnotovej logiky • Obohatený o niektoré rozšírenia potrebné k definícii sémantiky • jazyka SQL

E3VPC - Výrazy • E3VPC výraz má štruktúru: • {t(v1, ..., vn): ||P(v1, ..., vn)||α} • kde • v1, ..., vnn-ticové premenné • t(v1, ..., vn) cieľový zoznam výrazov v tvare • v1R1, ..., vnRn, • R1, ..., Rn sú relácie (tabuľky) • P(v1, ..., vn) predikátová formula • || · ||α, α {T, F}interpretačný operátor

E3VPC - Operátory • =, , <,  klasické operátory pre porovnávanie • ak je jeden z operandov null, výsledok je unknown •  porovnanie s hodnotou null: • Správa sa rovnako ako operátor = okrem prípadu, keď majú oba operandy hodnotu null. V tom prípade je výsledok true. • · unárny operátor vonkajšej väzby • Rozoberieme podrobne neskôr • || · ||αunárny operátor interpretácie, α{T,F} • Opäť podrobne neskôr

E3VPC - Termy • konštanta je term • v premenná, a atribút => v.a, v.a sú termy • S výraz, v premenná,a atribút, F agregačná funkcia => F(v.a)S je term • F  {COUNT, AVG, SUM, MIN, MAX, COUNTD, AVGD, SUMD} • Funkcie COUNTD, AVG a SUMD odpovedajú SQL funkciám COUNT/AVG/SUM DISTINCT

EVPC – Formule • Atomické formule • t1, t2 termy,  operátor porovnania => t1 t2 je atomická formula • True, False, Unknown (T, F, U) sú atomické formule • P atomická formula => ||P|| je atomická formula • Vytváranie zložitejších formulí • atomická formula je formula • P, Q formule => P, P Q, P Q sú formule • Svýraz, Q formula =>SQ, SQ sú formule • Žiadne ďalšie výrazy nie sú E3VPC formule

Interpretácia E3VPC formulí • Výsledkom interpretácie formule je jedna z troch hodnôt: • true, false, unknown • Vzniká problém s významom výrazov {x  R : P(x)} pre x také, že P(x) = U • Je potreba transformovať 3-hodnotovú formulu na2-hodnotovú

Operátor interpretácie • P(x) E3VPC formula,Q(x) 2VPC formula. • Q(x) je pozitívne interpretovaný 2-hodnotový ekvivalent P(x), značíme ||P||T, ak pre každé x platí: • a) P(x)  T => Q(x)  T • b) P(x)  F => Q(x)  F • c) P(x)  U => Q(x) T • Q(x) je negatívne interpretovaný 2-hodnotový ekvivalent P(x), značíme ||P||F, ak pro každé x platí: • a) P(x)  T => Q(x)  T • b) P(x)  F => Q(x)  F • c) P(x)  U => Q(x) F

Operátor vonkajšej väzby • Operátor vonkajšej väzby  mení rozsah platnosti premennej • v sa odkazuje na premennú na najbližšej vyššej úrovni • Napríkladvo výraze • {vS: {v T: || v.a = v.b ||T} ... } • Vo výraze v.a = v.b ide raz o premennú v T a druhýkrát je to premenná vS vďaka použitiu operátora . • Operátor  výrazne uľahčuje prevod SQL dotazov

Vyhodnotenie E3VPC výrazu • E3VPC výraz: • {v1  R1, ..., vk Rk: Q(v1, ..., vk)} • výsledkom vyhodnotenia výrazu je množina n-tíc vybraných z relácií R1, ..., Rk, ktoré po dosadení za príslušné premenné v1, ..., vk spĺňajú formulu Q • Q musí byť formula 2VPC (použitie || . ||a) • predpokladáme, že n-tice v reláciách sú navzájom rôzne

Kvantifikované formule U = {x S: P(x)}Q(x)  (x)x S  P(x)Q(x) E = {x S: P(x)}Q(x)  (x)x S  P(x)Q(x) Nech M = {x S: P(x)}, potom platí: U  true  (x M) Q(x)true U  false  (x M) Q(x)false U unknown  inak E  true  (x M) Q(x)  true E  false  (x M) Q(x)  false E unknown  inak

Príklad Schéma: oddelenia(id, pocet_zam, mesto, manazer)zamestnanci(id, oddelenie_id, mesto) SQL: SELECTodd.manazer FROModdeleniaodd WHEREodd.mesto = ALL SELECTzam.mestoFROMzamestnencizam WHERE zam.oddelenie_id = odd.id GROUP BYodd.manazer HAVING AVG(odd.pocet_zam)>500 E3VPC: {oddoddelenia:||({zam  zamestnenci:|| zam.oddelenie_id = odd.id||F} odd.mesto = zam.mesto) AVG(odd.pocet_zam){odd  oddelenia: ||({zam zamestnenci:|| zam.oddelenie_id = odd.id||F} odd.mesto = zam.mesto) odd.manager  odd.manager||F}>500||F}

Pokračovanie… • Zavedenie E3VPC (Extended 3-valued tuple predicate calculus) • Formálna definícia gramatiky jazyka SQL • Syntaxou riadený preklad • Popis syntaxe SQL pomocou gramatiky • Prevod SQL dotazov na E3VPC • Ekvivalencia SQL dotazov

Úpravy SQL • Vychádzame z normy ANSI SQL 85 • Pridávame nasledujúce úpravy: • Výrazy „boolean expression of“ a „list of“ sú skratkou pre množinu zrejmých pravidiel. • Aritmetické výrazy s atribútmi nie sú uvažované. • Používanie aliasov relácií je povinné. • Do niektorých pravidiel sú zavedené ďalšie neterminály, prípadne sú niektoré pravidlá rozdelené na viac menších.

Gramatika SQL dotazov • Dotazy, ktoré vracajú n-ticu: • <QUERY> ::= • SELECT [ALL | DISTINCT] <SELECT LIST><FROM CLAUSE> • [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] • Dotazy, ktoré vracajú hodnotu alebo stĺpec: • <SUBQ> ::= • SELECT [ALL | DISTINCT] <COL OR VAL><FROM CLAUSE> • [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] • Dotazy, ktoré vracajú výsledok agregačnej funkcie: • <AF SUBQ> ::= • SELECT [ALL | DISTINCT] <FUNCTION><FROM CLAUSE> • [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>]

<SELECT LIST> <SELECT LIST> ::= “list of <SELECT ELEMENT>” <SELECT ELEMENT> ::= <COL OR VAL> | <FUNCTION> <COL OR VAL> ::= <alias>.<column> | <literal> <FUNCTION> ::= <COUNT> | <AGGR> <COUNT> ::= <COUNT DISTINCT> | COUNT(*) <COUNT DISTINCT>::= COUNT (DISTINCT <alias>.<column>) <AGGR> ::= <AGGR DISTINCT> | <AGGR ALL> <AGGR DISTINCT> ::= <AGGR NAME>(DISTINCT <alias>.<column>) <AGGR ALL> ::= <AGGR NAME>([ALL ]<alias>.<column>) <AGGR NAME> ::= AVG | MAX | MIN | SUM

<* CLAUSE> • <FROM CLAUSE> ::= FROM “list of <TABLE REFERENCE>” • <TABLE REFERENCE> ::= <table> <alias> • <WHERE CLAUSE> ::= WHERE <WHERE CONDITION> • <HAVING CLAUSE> ::= HAVING <HAVING CONDITION> • <GROUP BY CLAUSE> ::= GROUP BY “list of <alias>.<column>” • do klauzule FROM nie je možné vnoriť SELECT • SELECT je možné vnoriť len do klauzulí WHERE, HAVING, za IN,NOT IN, EXISTS a operátory SOME a ALL

<* [AF] PRED> • Bez agregačnej funkcie v poddotaze: • <SOME Q. PRED>::=<COL OR VAL><comp op>SOME<SUBQ> • <ALL Q. PRED>::=<COL OR VAL><comp op>ALL<SUBQ> • <COMPLEX IN PRED>::=<COL OR VAL>IN<SUBQ> • <COMPLEX NOT IN PRED>::=<COL OR VAL>NOT IN<SUBQ> • <COMPLEX COMP PRED>::=<COL OR VAL><comp op><SUBQ> • S agragačnou funkciou v poddotaze: • <SOME Q. AFPRED>::=<COL OR VAL><comp op>SOME<AFSUBQ> • <ALL Q. AFPRED>::=<COL OR VAL><comp op>ALL<AFSUBQ> • <COMPLEX IN AFPRED>::=<COL OR VAL>IN<AFSUBQ> • <COMPLEX NOT IN AFPRED>::=<COL OR VAL>NOT IN<AFSUBQ> • <COMPLEX COMP AFPRED>::=<COL OR VAL><comp op><AFSUBQ> • <EXISTS PRED>::=EXISTS <SUBQ>

<HAVING CONDITION> <HAVING CONDITION>::=“boolean expressionof<HAVING PRED>” <HAVING PRED>::=<H SIMPLE PRED> | <H COMPLEX PRED>| <H AF COLUMN PRED> | <H AF FUNCTION PRED> | <H AF COMPLEX PRED> <HAVING SIMPLE PRED>::=<SIMPLE PRED> <HAVING COMPLEX PRED>::=<COMPLEX PRED> <HAVING AF COLUMN PRED>::=<FUNCTION><comp op><COL OR VAL> <HAVING AF FUNCTION PRED>::=<FUNCTION><comp op><FUNCTION>

<AF * [AF] PRED> • Bez agregačnej funkcie v poddotaze: • <AFSOME Q. PRED>::=<FUNCTION><comp op>SOME<SUBQ> • <AF ALL Q. PRED>::=<FUNCTION><comp op>ALL<SUBQ> • <AF COMPLEX IN PRED>::=<FUNCTION>IN<SUBQ> • <AF COMPLEX NOT IN PRED>::=<FUNCTION>NOT IN<SUBQ> • <AF COMPLEX COMP PRED>::=<FUNCTION><comp op><SUBQ> • S agragačnou funkciou v poddotaze: • <AFSOME Q. AFPRED>::=<FUNCTION><comp op>SOME<AFSUBQ> • <AF ALL Q. AFPRED>::=<FUNCTION><comp op>ALL<AFSUBQ> • <AF COMPLEX IN AFPRED>::=<FUNCTION>IN<AFSUBQ> • <AF COMPLEX NOT IN AFPRED>::=<FUNCTION>NOT IN<AFSUBQ> • <AF COMPLEX COMP AFPRED>::=<FUNCTION><comp op><AFSUBQ> • <EXISTS PRED>::=EXISTS <SUBQ>

Pokračovanie… • Zavedenie E3VPC (Extended 3-valued tuple predicate calculus) • Formálna definícia gramatiky jazyka SQL • Prevod SQL dotazov na E3VPC • Ekvivalencia SQL dotazov

Překlad SQL => E3VPC

Pravidla překladu • Výsledek překladu symbolu wje řetězec symbolů w. • S některými pravidly gramatiky SQL jsou spojena překladová pravidla. • Překladové pravidlo pro symbol w definuje w. • Některá syntaktická pravidla mohou mít i více překladových pravidel. Rozlišíme je indexem: i • Použití závisí na kontextu.

Překlad terminálů • Je-li w terminál, pak w ::= w. • Význam přeložených SQL terminálů v E3VPC: • <alias> proměnná • <column> atribut • <table> relace • <literal> konstanta • <comp op> operátor porovnání

Implicitní překlad • BuďLHS::=RHS syntaktické pravidlo, w1, ..., wk neterminály na jeho pravé straně v pořadí zleva doprava. • Nechť neexistuje pravidlo překladu pro LHS::=RHS. • Pak LHS ::= w1...wk • syntaktické pravidlo: • <WHERE CLAUSE>::=WHERE <WHERE CONDITION> • překlad: • <WHERE CLAUSE> ::= <WHERE CONDITION>

Pravidla popsaná metajazykem 1<FUNCTION> ::= odpovídající jméno agregační funkce a v závorkách její parametr 2<FUNCTION> ::= hodnota odpovídající funkce pro prázdnou množinu “boolean expression of <X>” ::= odpovídající výraz v E3VPC, v němž jsou predikáty nahrazeny svými překlady “list of <X>” ::= seznam přeložených neterminálů oddělených čárkami <COL OR VAL> ::= konstanta nebo proměnná.atribut

negativní interpretace • <GROUP BY CLAUSE> se projeví až při překladu <HAVING CLAUSE> a při překladu agregačních funkcí • <SELECT LIST> není použit – jedná se o vnější SELECT <QUERY> syntaktické pravidlo: <QUERY> ::= SELECT [ALL | DISTINCT] <SELECT LIST><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] pravidlo překladu: <QUERY> ::= {<FROM CLAUSE>: ||<WHERE CLAUSE><HAVING CLAUSE>||F}

<* CLAUSE> <FROM CLAUSE> ::= FROM “list of <TABLE REFERENCE>” <TABLE REFERENCE> ::= <table> <alias> <WHERE CLAUSE> ::= WHERE <WHERE CONDITION> <HAVING CLAUSE> ::= HAVING <HAVING CONDITION> <GROUP BY CLAUSE> ::= GROUP BY “list of <alias>.<column>” pravidla překladu spojená s těmito syntaktickými pravidly: <TABLE REFERENCE> ::= <alias>  <table> <GROUP BY CLAUSE> ::= <alias>.<column><alias>.<column> [<alias>.<column><alias>.<column>] ... zbývá definovat: <WHERE CONDITION>, <HAVING CONDITION>

<* CONDITION> implicitní překlad a překlad popsaný metajazykem: <WHERE CONDITION>::=“boolean expression of <WHERE PRED>” <WHERE PRED>::=<SIMPLE PRED> | <COMPLEX PRED> <SIMPLE PRED>::=<COL OR VAL><comp op><COL OR VAL> <COMPLEX PRED>::= ... | <ALL QUANTIFIED PRED> | ... <HAVING CONDITION>::=“boolean expression of <HAVING PRED>” <HAVING PRED>::= ... | <H AF COLUMN PRED> | ... zbývá tedy přeložit: <ALL QUANTIFIED PRED>, <H AF COLUMN PRED>, ...

<ALL QUANTIFIED PRED>, ... <ALL QUANTIFIED PRED>::=<COL OR VAL><comp op>ALL<SUBQ> 1<SUBQ><COL OR VAL> <comp op> 2<SUBQ> <SOME QUANTIFIED PRED>::=<COL OR VAL><comp op>SOME<SUBQ> 1<SUBQ><COL OR VAL> <comp op> 2<SUBQ> <COMPLEX IN PRED>::=<COL OR VAL>IN<SUBQ> 1<SUBQ><COL OR VAL> = 2<SUBQ> <COMPLEX NOT IN PRED>::=<COL OR VAL>NOT IN<SUBQ> 1<SUBQ><COL OR VAL> 2<SUBQ> <EXISTS PRED>::=EXISTS <SUBQ> 1<SUBQ> ...

<SUBQ> <SUBQ> ::= SELECT [ALL | DISTINCT] <COL OR VAL><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] výsledek poddotazu může být množina hodnot: 1<SUBQ> = {<FROM CLAUSE>: ||<WHERE CLAUSE><HAVING CLAUSE>||F} výsledek poddotazumusí být jenom jedna hodnota: 2<SUBQ> = <COL OR VAL>

<H AF COLUMN PRED>, ... <H AF COLUMN PRED>::=<FUNCTION><comp op><COL OR VAL> 1<FUNCTION> {<FROM CLAUSE>:||<WHERE CLAUSE> <GROUP BY CLAUSE>||F} <comp op> <COL OR VAL> <H AF FUNCTION PRED>::=<FUNCTION><comp op><FUNCTION> 1<FUNCTION> {<FROM CLAUSE>:||<WHERE CLAUSE> <GROUP BY CLAUSE>||F} <comp op> 1<FUNCTION> {<FROM CLAUSE>:||<WHERE CLAUSE> <GROUP BY CLAUSE>||F} <H SIMPLE PRED>::=<SIMPLE PRED> {<FROM CLAUSE>:||<WHERE CLAUSE> <GROUP BY CLAUSE>||F} <SIMPLE PRED> ...

Příklad SQL:SELECTd.manager FROM dept dWHERE d.location = ALL SELECT e.residence FROM emp e WHERE e.deptId = d.idGROUP BY d.managerHAVING AVG(d.nofEmp)>500

<QUERY> • <QUERY> = {FRCLAUSE||WHCLAUSEHCLAUSE||F} • FRCLAUSE<correlation name>intable name • WHCLAUSEWHERE SEARCH COND • HCLAUSE = HAVING SEARCH COND{d in DEPT:||WHERE SEARCH CONDHAVING SEARCH COND||F}

WHERE SEARCH COND • ALL QUANTIFIED PRED1<SUBQ><COL or VAL><comp op>2<SUBQ>1<SUBQ> d.location = 2<SUBQ> • <SUBQ> = {FRCLAUSE:||WHCLAUSE>HCLAUSE>||F}1<SUBQ> => {e in EMP: ||e.d# = d.d#||F } • <SUBQ> = <COL or VAL>2<SUBQ> => e.residence

WHERE SEARCH COND {e in EMP: ||e.d# = d.d#||F } d.location = e.residence

HAVING SEARCH COND • <HAVING SEARCH COND> = „Boolean expression of“ <HPRED> • <HPRED> = <HACOL PRED>|... • <HACOL PRED> = <FUNC SPEC>{<FRCLAUSE>:||<WHCLAUSE><GBCLAUSE>||F} <comp op><COL or VAL>AVG(d.nofemp){<FRCLAUSE>:||<WHCLAUSE><GBCLAUSE>||F} > 500

HAVING SEARCH COND AVG(d.nofemp){<FRCLAUSE>:||<WHCLAUSE><GBCLAUSE>||F} > 500 AVG(d.nofemp){d in DEPT::||{e in EMP: ||e.d# = d.d#||F } d.location = e.residence <GBCLAUSE>||F} > 500

<GBCLAUSE> • <GBCLAUSE> = <correlation name><column name> <correlation name><column name>d.manager dmanager

HAVING SEARCH COND AVG(d.nofemp){d in DEPT::||{e in EMP: ||e.d# = d.d#||F } d.location = e.residence<GBCLAUSE>||F} > 500 AVG(d.nofemp){d in DEPT::||{e in EMP: ||e.d# = d.d#||F } d.location = e.residenced.manager dmanager||F} > 500

Dokončení příkladu SQL: schéma: SELECTd.manager FROM dept d WHERE d.location = ALL SELECT e.residence FROM emp e WHERE e.deptId = d.id GROUP BY d.manager HAVING AVG(d.nofEmp)>500 dept(id, nofEmp, location, manager) emp(id, deptId, residence) E3VPC: {d  dept: ||({e emp: || e.deptId = d.id ||F} d.location = e.residence)  AVG(d.nofEmp){d dept: ||({e emp: || e.deptId = d.id ||F}d.location = e.residence) d.managerd.manager||F}>500 ||F}

Ekvivalence SQL dotazů

Úkol • Zjišťujeme, zda dané SQL výrazy jsou ekvivalentní. • Uděláme to přes E3VPC • SQL výrazy přeložíme di E3VPC a zjistíme ekvivalenciE3VPC výrazů. • Jak to provedeme?

E3VPC výraz je kanonický, pokud • interpretační operátor je aplikován na každou atomickou formuli • interpretační operátor není aplikován na jinou než atomickou formuli • neobsahuje zkrácené kvantifikované výrazy: {xS: P(x)} Q(x), {xS: P(x)} Q(x) Kanonická forma E3VPC výrazu

S kanonickým výrazem lze pracovat ve 2VPC. • Ve 2VPC umíme zjistit ekvivalenci dvou výrazů. • Má každý E3VPC výraz svůj kanonický tvar? • Ano – následuje lemma, jejichž násobnou aplikací dostaneme kanonickou formu výrazu Pozorování

Formálna sémantika SQL