Ciekawa własność NULLa

W bólach implementacji swoich builderów natknąłem się na ciekawą własność NULLa w MySQL. Nie wiem czy pojawia sie ona również innych SQLach.

Najprościej bedzie na przykladzie. Załózmy, że mamy dwie tabele "process" i "task". Tabela "process" zawiera listę procesów wykonywanych przez pewien abstrakcyjny procesor, na każdy proces składa się pewna ilość zadań, przy czym każde zadanie może należeć tylko do jednego procesu; czyli prosta relacja jeden do wielu. Dodatkowo każde zadanie posiada właściwość "status" określająca stan tego zadania. Dla ustalenia uwagi niech pole "status" może przyjmować trzy wartości: "none", "running" lub "completed". Definicja tych tabel może wyglądac tak:

CREATE TABLE process (
   id INT UNSIGNED PRIMARY KEY NOT NULL auto_increment,
   name VARCHAR);
CREATE TABLE task (
   id INT UNSIGNED PRIMARY KEY NOT NULL auto_increment,
   id_process INT UNSIGNED NOT NULL,
   status ENUM('none', 'running', 'completed') NOT NULL);

Zapytanie które zwróci nam identyfikator procesu wraz z ilością zadań przyporzadkowanych do tego procesu jest oczywiście trywialne i wyglada tak:

SELECT id_process, COUNT(*) FROM task GROUP BY id_process;

Problemem natomiast jest zapytanie zwracajace identyfikator procesu wraz z ilością zadań przyporzadkowanych do tego procesu, ale takich które nie zostały jeszcze zakończone, czyli mają status różny od 'completed'. Proste:

SELECT id_process, COUNT(status != 'completed') FROM task GROUP BY id_process;

nie wystarcza ponieważ COUNT zlicza zgrupowane wartości bez wzgledu na ich wartość. W tym przypadku zarówno TRUE jak i FALSE jest traktowane jako element to zliczenia. Zatem powyższe zapytanie zwraca to samo co poprzednie, czyli identyfikator procesu wraz z ilością zadań przyporzadkowanych do tego procesu.

Możnaby oczywiście uzyć funkcji SUM i IF w następujący sposób:

SELECT id_process, SUM(IF(status != 'completed), 1, 0) FROM task GROUP BY id_process;

Ale to nie dość, że niezgodne z zasadami sztuki, to jeszcze mało czytelne i pewnie mało wydajne.

Aby w prosty sposób uzyskać oczekiwany wynik wystarczy wykorzystać to że wartości NULL są niepoliczalne dla funkcji COUNT. Jeżeli zgrupujemy rekordy które w zadanym polu maja cztery wartości z czego dwie są równe NULL to funkcja COUNT na tym polu zwróci nam 2, a nie 4.

Wystarczy zatem doprowadzić ostatnie zapytanie wykorzystujące funkcję COUNT do takiej postaci, w której wartość argumentu jest policzalna gdy pole "status" jest inne niż 'completed', a niepoliczalna (czyli równa NULL) w przeciwnym wypadku. Wyrażenie

status != 'completed' OR NULL

spełnia te założenia ponieważ przyjmuje jedną z dwóch wartości: TRUE dla pola "status" różnego od 'completed', albo NULL w przeciwnym wypadku.

Ostatecznie zapytanie zwracajace identyfikator procesu wraz z ilością zadań przyporzadkowanych do tego procesu, takich które nie zostały jeszcze zakończone wygląda tak:

SELECT id_process, COUNT(status != 'completed' OR NULL) FROM task GROUP BY id_process;

Śliczne, prawda?