Разпознаване на функционални зависимости при групиране от MySQL

Ако сте използвали MySQL в миналото трябва да помните, че беше възможно по подразбиране да се сумира в заявки без GROUP BY клауза. Така можеше да напишете заявка като тази

SELECT D.dname, COUNT(E.empno) cnt
  FROM dept D,
       emp  E
 WHERE D.deptno = E.deptno;

най-вероятно с идеята да преброите служителите във всеки отдел. Обаче, резултата, който ще получите (без грешка) ще бъде:

+------------+-----+
| dname      | cnt |
+------------+-----+
| ACCOUNTING |  14 |
+------------+-----+

което просто е грешно и най-вероятно не това което искате в действителност. Такава заявка ще предизвика грешка в други бази данни (напр. Oracle ще даде ORA-00937: not a single-group group function, PostgreSQL ще каже SQL state: 42803 ERROR: column "d.dname" must appear in the GROUP BY clause or be used in an aggregate function, и т.н.).

Причината за това поведение беше SQL режима по подразбиране на сървъра и по-точно отсъствието на ONLY_FULL_GROUP_BY в списъка с режими. Има някои приложения (като Bugzilla и Cacti) които не ми позволяваха да разреша този режим на сървъра ми въпреки, че той беше отдавна препоръчван от някои (напр. спомням си представянията на Ronald Bradford от Есенната конференция на БГПО). Помня, че имах проблеми с тези приложения, така че сложих коментар в конфигурационния файл да не разрешавам ONLY_FULL_GROUP_BY. Трябва да призная, че не съм проверявал скоро, така че ситуацията може да се е подобрила, но с текущото състояние на Bugzilla е съмнително.

SQL режимите по подразбиране се промениха доста с последните версии на MySQL:

• MySQL 5.5: празно;
• MySQL 5.6: NO_ENGINE_SUBSTITUTION;
• MySQL 5.7: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER и NO_ENGINE_SUBSTITUTION;
• MySQL 8.0: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO и NO_ENGINE_SUBSTITUTION.

Единствената разлика в 8.0 в всъщност отсъствието на NO_AUTO_CREATE_USER, защото този режим беше премахнат след като бе обявен за остарял в предишните версии. Силно препоръчително е да придържате към тези режими по подразбиране особено при започване на ново приложение, за да не се налага да поправяте заявки и данни след това.

Когато ONLY_FULL_GROUP_BY беше разрешено с MySQL 5.7.5 (пуснат на 2014-09-25) Oracle се погрижи да изработи усъвършенствани функционални зависимости както Guilhem Bichot писа през Декември 2014. Усилието беше оценено от Markus Winand в неговата страхотна statiq One Giant Leap For SQL: MySQL 8.0 Released от Април 2018, който написа, че MySQL вече има "най-пълната проверка за функционални зависимости сред основните SQL бази данни". Да изследваме това, но преди това можете да се обърнете към Wikipedia за бърз преглед на определението за функционална зависимост. Казано просто Y е функционално зависим от X ако стойностите на X уникално определят стойностите на Y. Например стойностите на колоните в таблица са функционално зависими от първичния ключ, защото стойността му определя уникално всички други стойности в реда.

Вижте следния пример с първичен ключ. Това е заявката от горе, но с GROUP BY клауза:

SELECT D.dname, COUNT(E.empno) cnt
  FROM dept D,
       emp  E
 WHERE D.deptno = E.deptno
 GROUP BY D.deptno;

Правилна ли е тази заявка? Групирането е по D.deptno, но списъка с колони в SELECT съдържа само D.dname и сумиране по E.empno, така че е грешна, нали? Не и в MySQL 5.7 и по-нов с разрешен ONLY_FULL_GROUP_BY режим, така че старото правило да се слага всичко от GROUP BY клаузата в SELECT или да се ползва сумиране (т.е. без повече SQL-92 ограничения). Резултата е:

+------------+-----+
| dname      | cnt |
+------------+-----+
| ACCOUNTING |   3 |
| RESEARCH   |   5 |
| SALES      |   6 |
+------------+-----+

Защо така? MySQL правилно определя, че D.dname е функционално зависим от D.deptno, защото това е първичния ключ за dept таблицата. Същата заявка работи правилно и в PostgreSQL, но не и в Oracle и други основни SQL бази данни. MySQL може правилно да определя функционални зависимости също по уникални ключове, равенства в WHERE клауза или [LEFT] JOIN ON клаузи и от избрани изрази в тялото на изгледи и производни (derrived) таблици. Доста вълнуващо, нали? Гледайте да го използвате следващия път когато решите да пишете заявка с GROUP BY клауза.

За повече подробности, моля обърнете се към глава Detection of Functional Dependence в наръчника.