Formlna smantika SQL dotazov Martin Koalko Michal Krl

Formálna sémantika SQL dotazov Martin Košalko & Michal Král

Obsah • formalizácia SQL: trojhodnotový kalkul E 3 VPC (Extended three-valued predicate calculus) • gramatika SQL – Martin Košalko • prevod SQL na E 3 VPC • ekvivalencia dotazov SQL – Michal Král

Extended 3 -valued tuple predicate calculus (E 3 VPC) • • rozšírenie 2 -hodnotového n-ticového kalkulu (2 VPC) jednoduchá štruktúra jednoduchšia manipulácia s dotazmi (napr. optimalizácia) schopnosť vyjadriť všetko, čo je možné vyjadriť v SQL

SQL: Príklad SELECT odd. manazer FROM oddelenia odd WHERE odd. mesto = ALL SELECT zam. mesto FROM zamestnenci zam WHERE zam. oddelenie_id = odd. id GROUP BY odd. manazer HAVING AVG(odd. pocet_zam)>500 schéma: oddelenia(id, pocet_zam, mesto, manazer) zamestnanci(id, oddelenie_id, mesto) E 3 VPC: {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 ||F}>500 ||F}

Jazyk E 3 VPC

operátory =, , <, , . . . majú zvyčajný význam ak je jeden z operandov null, výsledok je unknown porovnáva s hodnotou null: chová se rovnako =, okrem prípadu, keď majú oba operandy hodnotu null. Potom je výsledok true. · je unárny operátor vonkajšej väzby || · ||α je unárny operátor interpretácie, α {T, F} 5 1 N 5 T F U 1 F T U N U U T

výrazy • E 3 VPC výraz je {t(v 1, . . . , vn): ||P(v 1, . . . , vn)||α} v 1, . . . , vn t(v 1, . . . , vn) P(v 1, . . . , vn) || · ||α n-ticové premenné cieľový zoznam výrazov v tvare v 1 R 1, . . . , vn Rn, R 1, . . . , Rn sú relácie (tabuľky) formula interpretačný operátor α {T, F}

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} xxx. D odpovedá v SQL agregačnej funkcii xxx(DISTINCT)

formule • t 1, t 2 termy, op. porovnania => t 1 t 2 je atomická formula • T, F, U (true, false, unknown) sú atomické formule • P atomická formula => ||P|| je atomická formula • atomická formula je formula • P, Q formule => P, P Q sú formule • S výraz, Q formula => S Q, S Q sú formule

Interpretácia formulí • Výsledkom vyhodnotenia formule P je jedna z 3 hodnôt true, false, unknown • || · ||T interpretuje unknown ako true (pozitívna interpretácia) • || · ||F interpretuje unknown ako false (negatívna interpretácia) • Výsledkom vyhodnotenia formule ||P|| je jedna z hodnôt true, false

Interpretácia formálne • P(x) E 3 VPC formula, Q(x) 2 VPC 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

Vonkajšia väzba • Operátor vonkajšej väzby mení rozsah platnosti premennej • v sa odkazuje na premennú na nejbližšej vyššej úrovni • Napríklad vo výraze {v S: {v T: || v. a = v. b ||T}. . . } je premenná v dvakrát – raz ide o premennú v T, druhýkrát v S, pretože je použitý operátor .

Význam E 3 VPC výrazu {v 1 R 1, . . . , vk Rk: Q(v 1, . . . , vk)} • výsledkom vyhodnotenia výrazu je množina n-tíc vybraných z relácií R 1, . . . , Rk, ktoré po dosadení za príslušné premenné v 1, . . . , vk spĺňajú formulu Q • Q musí byť formula 2 VPC • predpokladá sa, že n-tice v reláciách sú navzájom rôzne • Vracajú sa celé n-tice, nie je možné vrátiť ich časti

Kvantifikácia 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) ak označíme M = {x S: P(x)}, potom platí: U true U false U unknown ( x M) Q(x) true ( x M) Q(x) false inak E true E false E unknown ( x M) Q(x) true ( x M) Q(x) false inak

Gramatika SQL

Cieľ • Syntaxou riadený preklad SQL dotazu na výraz E 3 VPC. • Potrebujeme teda popísať syntax SQL pomocou gramatiky

Zjednodušenie 1. Výrazy „boolean expression of“ alebo „list of“ sú skratkou pre množinu zrejmých pravidiel. 2. Aritmetické výrazy s atribútmi nie sú uvažované. 3. Používanie aliasov relácií je povinné. 4. Do niektorých pravidiel sú zavedené ďalšie neterminály, prípadne sú niektoré pravidlá rozdelené na viac menších.

Gramatika SQL (pod)dotazu vracia n-ticu: <QUERY> : : = SELECT [ALL | DISTINCT] <SELECT LIST><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] vracia hodnotu alebo stĺpec: <SUBQ> : : = SELECT [ALL | DISTINCT] <COL OR VAL><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] vracia výsledok agregačnej funkcie: <AF SUBQ> : : = SELECT [ALL | DISTINCT] <FUNCTION><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>]

pravidlo <SELECT LIST> : : = “list of <SELECT ELEMENT>” <SELECT ELEMENT> : : = <COL OR VAL> | <FUNCTION> <COL OR VAL> : : = <alias>. <column> | <literal> <FUNCTION> : : = <COUNT> | <AGGR> <COUNT> : : = <COUNT D> | COUNT(*) <COUNT D>: : = COUNT (DISTINCT <alias>. <column>) <AGGR> : : = <AGGR D> | <AGGR A> <AGGR D> : : = <AGGR NAME>(DISTINCT <alias>. <column>) <AGGR A> : : = <AGGR NAME>([ALL ]<alias>. <column>) <AGGR NAME> : : = AVG | MAX | MIN | SUM - zoznam hodnôt, stĺoupcov alebo výsledkov agregačných funkcií - Nie je možné vnoriť SELECT

pravidlá <* 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 [NOT] IN, EXISTS, operátor [SOME | ALL]

pravidlo <WHERE CONDITION> - podmienka je zoznamom jednoduchých a/alebo zložených predikátov: <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>: : = <SOME QUANTIFIED PRED> | <SOME QUANTIFIED AF PRED> | <ALL QUANTIFIED AF PRED> | <COMPLEX IN AF PRED> | <COMPLEX NOT IN AF PRED> | <COMPLEX COMP AF PRED> | <EXISTS PRED>

pravidlá <* [AF] PRED> <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> analogicky s agragačnou funkciou v poddotaze: <SOME Q. AF PRED>: : =<COL OR VAL><comp op>SOME<AF SUBQ> <ALL Q. AF PRED>: : =<COL OR VAL><comp op>ALL<AF SUBQ> <COMPLEX IN AF PRED>: : =<COL OR VAL>IN<AF SUBQ> <COMPLEX NOT IN AF PRED>: : =<COL OR VAL>NOT IN<AF SUBQ> <COMPLEX COMP AF PRED>: : =<COL OR VAL><comp op><AF SUBQ> <EXISTS PRED>: : =EXISTS <SUBQ>

pravidlo <HAVING CONDITION> - podmienka je zoznamom jednoduchých resp. zložených predikátov, porovnaní výsledkov agregačnej funkcie s hodnotou alebo inou agregačnou funkciou: <HAVING CONDITION>: : =“boolean expression of <HAVING PRED>” <HAVING PRED>: : =<H SIMPLE PRED> | <H COMPLEX PRED> | <H AF COLUMN PRED> | <H AF FUNCTION PRED> | <H AF COMPLEX PRED> <H SIMPLE PRED>: : =<SIMPLE PRED> <H COMPLEX PRED>: : =<COMPLEX PRED> <H AF COLUMN PRED>: : =<FUNCTION><comp op><COL OR VAL> <H AF FUNCTION PRED>: : =<FUNCTION><comp op><FUNCTION>

pravidlo <H AF COMPLEX PRED>: : = <AF <AF <AF SOME Q. PRED> | <AF SOME Q. AF PRED> | ALL Q. PRED> | <AF ALL Q. AF PRED> | COMPLEX IN PRED> | <AF COMPLEX IN AF PRED> | COMPLEX NOT IN PRED> | <AF COMPLEX IN AF PRED> | COMPLEX COMP PRED> | <AF COMPLEX COMP AF PRED>

pravidlá <AF * [AF] PRED> <AF <AF <AF SOME Q. PRED>: : =<FUNCTION><comp op>SOME<SUBQ> ALL Q. PRED>: : =<FUNCTION><comp op>ALL<SUBQ> COMPLEX IN PRED>: : =<FUNCTION>IN<SUBQ> COMPLEX NOT IN PRED>: : =<FUNCTION>NOT IN<SUBQ> COMPLEX COMP PRED>: : =<FUNCTION><comp op><SUBQ> analogicky s agragačnou funkciou v poddotaze: <AF SOME Q. AF PRED>: : =<FUNCTION><comp op>SOME<AF SUBQ> <AF ALL Q. AF PRED>: : =<FUNCTION><comp op>ALL<AF SUBQ> <AF COMPLEX IN AF PRED>: : =<FUNCTION>IN<AF SUBQ> <AF COMPLEX NOT IN AF PRED>: : =<FUNCTION>NOT IN<AF SUBQ> <AF COMPLEX COMP AF PRED>: : =<FUNCTION><comp op><AF SUBQ> <EXISTS PRED>: : =EXISTS <SUBQ>

Dílčí závěr • Definovali sme jazyk E 3 VPC. • Ukázali sme si gramatiku SQL (zjednodušene).

Překlad SQL => E 3 VPC

Pravidla překladu • • • Výsledek překladu symbolu w je ř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 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 E 3 VPC: <alias> <column> <table> <literal> <comp op> proměnná atribut relace konstanta operátor porovnání

Implicitní překlad • • • Buď LHS: : =RHS syntaktické pravidlo, w 1, . . . , wk neterminály na jeho pravé straně v pořadí zleva doprava. Nechť neexistuje pravidlo překladu pro LHS: : =RHS. Pak LHS : : = w 1. . . 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 E 3 VPC, 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

<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} • 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

<* 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>]. . . 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> : : = 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 poddotazuje musí 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: SELECT d. manager FROM dept d WHERE d. location = ALL SELECT e. residence FROM emp e WHERE e. dept. Id = d. id GROUP BY d. manager HAVING AVG(d. nof. Emp)>500

<QUERY> = { FRCLAUSE || WHCLAUSE ||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> = <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: SELECT d. manager FROM dept d WHERE d. location = ALL SELECT e. residence FROM emp e WHERE e. dept. Id = d. id GROUP BY d. manager HAVING AVG(d. nof. Emp)>500 schéma: dept(id, nof. Emp, location, manager) emp(id, dept. Id, residence) E 3 VPC: {d dept: ||( {e emp: || e. dept. Id = d. id ||F} d. location = e. residence) AVG(d. nof. Emp){d dept: ||( {e emp: || e. dept. Id = d. id ||F} d. location = e. residence) d. manager||F}>500 ||F}

Ekvivalence SQL dotazů

Cíl • • Zjistit, zda dva dané SQL výrazy jsou ekvivalentní. Použijeme k tomu E 3 VPC Oba dotazy přeložíme a zjistíme ekvivalenci E 3 VPC výrazů. Jak?

Kanonická forma E 3 VPC výrazu E 3 VPC 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)

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

Množina kritické ekvivalence <Q 1> = {x in R: ||P(x)||F } <Q 2> = {x in R: ||P(x)||T }

Jednoduché dotazy • Jednoduchý dotaz – bez negace a kvantifikace Pravidlo 3 <Q 1> = {x in R: ||P(x)||F } <Q 1> = {x in R: ¬||P(x)||F } {x in R: ||¬P(x)||T } Pravidlo 4 <Q 2> = {x in R: ||P(x)||F } <Q 2> = {x in R: || ||P(x)||T ||F } {x in R: ||P(x)||T }

Univerzální kvantifikátor 0)||¬ {y in S: ||Q(x, y) P(y)|| }|| 1)||¬ {y in S: ||Q(x, y)|| }P(y)|| 2)||¬ {y in S: ||P(y)|| }Q(x, y)|| 3)|| {y in S: ||Q(x, y)|| }¬P(y)|| 4)|| {y in S: ||P(y)|| }¬Q(x, y)||

Univerzální kvantifikátor II Canonické formy: 0 y in S: ||¬ Q(x, y) || ||¬ P(y) || 1 y in S: ||¬ Q(x, y) || ||¬ P(y) || 2 y in S: ||¬ Q(x, y) || ||¬ P(y) || 3) 4) =2 =3

Příklad SELECT * FROM R x WHERE NOT EXISTS SELECT * FROM S y WHERE x. Ai = y. Bi y. Bj 'P' SELECT * FROM R x WHERE 'P' = ALL SELECT y. Bj FROM S y WHERE x. Ai = y. Bi R: 1 -(1) S: 1 -(1, NULL)

Existenční kvantifikátor 0)|| {y in S: ||Q(x, y) P(y)|| }|| 1)|| {y in S: ||Q(x, y)|| }P(y)|| 2)|| {y in S: ||P(y)|| }Q(x, y)|| 3)||¬ {y in S: ||Q(x, y)|| }¬P(y)|| 4)||¬ {y in S: ||P(y)|| }¬Q(x, y)||

Existenční kvantifikátor II Canonické formy: 0 y in S: || Q(x, y) || P(y) || 1 y in S: || Q(x, y) || P(y) || 2 y in S: || Q(x, y) || P(y) || 3) = 2 4) = 3 =F

Ekvivalence SQL dotazů SQL dotazy, které neobsahují univerzální kvantifikátor nikdy nevedou na množinu kritické ekvivalence • SQL dotazy obsahující univerzální kvantifikátor vždy venou na množinu kritické ekvivalence • Počet různých interpretací dotazů se stejnou canonickou strukturou s alespoň jedným univerzálním kvantifikátorem se zvetšuje s každým dalším kvantifikátorem (univerzálním i existenčním) •

Závěr • Definovali jsme jazyk E 3 VPC. • Ukázali jsme si gramatiku SQL (zjednodušeně). • Víme, že lze sestrojit syntaxí řízený překladač SQL => E 3 VPC. • Viděli jsme překlad SQL dotazu. • Umíme rozhodnout, zda jsou dva SQL dotazy ekvivalentní.

Literatura M. Negri, G. Pelagatti, L. Sbattella: Formal Semantics of SQL Queries