04 października 2011

Przygód kilka Pana NULL'a

Znam się z NULL'em od lat


Oglądając materiały z Introduction to Databases, przypomniały mi się koszmary z dzieciństwa.  Zdarzyło mi się wspierać jako programista bądź inżynier support'u kilka systemów używających MS SQL Server'a (każdy z nich był rozwijany przynajmniej 5 lat). Miały one kilka cech wspólnych , jedna z nich sprawiła mi sporo problemów przy rozwijaniu i utrzymywaniu tych systemów. Posiadały one dużo tabel w których definicje kolumn umożliwiały wstawianie wartości NULL. Poniższa definicja tabeli FooTable prezetuje ten sposób definiowania kolumn.

CREATE TABLE FooTable(
....
FooName VARCHAR(20) NULL
....
)

Ogólnie zachęcam do definiowania explicite czy dana kolumna w tabeli jest NULL czy NOT NULL, ale wracając do tematu. W tym poście chciałbym podsumować w kilku słowach konsekwencje jakie niesie ze sobą tworzenie dużej ilości kolumn z możliwością wstawienia NULL'a. Moim celem jest przekonanie szanownego czytelnika do prostej zasady, która brzmi następująco.
Jeżeli ktoś nie przystawia wam towarzyszu "pistoletu do głowy" to proszę nie definiujcie kolumn, które umożliwiają wstawianie wartości NULL. Używajcie wartości domyślnych, stałych wartości lub pustych ciągów znakowych. 



NULL i operator NOT IN 


Pewnego dnia po wdrożeniu nowej wersji systemu w raportach zaczęło "brakować" danych. Oskarżenia padły na programistów, którzy na pewno coś pozmieniali w kodzie. Po dogłębnym przeanalizowaniu problemu, okazało się, że przyczyną problemu było pojawienie się wartości NULL w klauzuli operatora NOT IN. Ta sytuacja nie miała nic wspólnego z nową wersją, po prostu dane z zewnętrzengo systemu zawierały te wartości NULL.

Zatem na czym polegał kwas? Załóżmy, że mamy dwie tabele FooTable i BooTable.

CREATE TABLE dbo.FooTable
(
 FooId Int NOT NULL,
 FooName Varchar(10) NULL
)

CREATE TABLE dbo.BooTable
(
 BooId Int NOT NULL,
 FooName Varchar(10) NOT NULL
)



Do tabeli FooTable wstawiamy wartość NULL do kolumny FooName.

INSERT INTO dbo.FooTable(FooId, FooName)
VALUES (1, 'Foo 1'), (2, NULL), (3, 'Foo 3')

INSERT INTO dbo.BooTable(BooId, FooName)
VALUES (10, 'Foo 1'), (11, 'Foo 2' ), (12, 'Foo 3')

Spróbujmy wyciągnąć te rekordy z BooTable, które wartość FooName nie ma odpowiednika w tabeli FooTable. Zakładam, że powinien pojawić się jeden wiersz(BooId=11, FooName='Foo 2'), niestety otrzymujemy pusty zestaw wyników.

SELECT b.*
  FROM BooTable b
  WHERE b.FooName NOT IN (SELECT FooName FROM FooTable)


Powodem takiego zachowania silnika baz danych jest NULL w zbiorze wynikowym zapytania 'SELECT FooName FROM FooTable' i wynik porównywania wartości do NULL'a. Operator NOT IN można przetłumaczyć na b.FooName <> FooName1 AND b.FooName <> FooName2 etc. Ponieważ warunek b.FooName <> NULL zawsze zwróci NULL, niezależnie od wartości w kolumnie FooName. Zatem klauzula WHERE w naszych przypadku dla każdego wiersza zwróci NULL. To wyjaśnia kwestię pustego zestawu danych.


NULL w klauzuli WHERE


Inny problemem który powstaje w wyniku definiowania 'NULLowalnych' kolumn, jest konieczność używania funkcji ISNULL oraz operatora IS NULL. Jeżeli chcielibyśmy naprawić powyższe zapytanie, bez zmiany schematu danych możemy to zrobić na dwa sposoby.

Przykład z użyciem operatora IS NOT NULL

SELECT b.*
  FROM BooTable b
  WHERE b.FooName NOT IN (SELECT FooName FROM FooTable WHERE FooName IS NOT NULL)

Przykład z użyciem funkcji ISNULL, która zwraca wartość drugiego argumetu (w tym przypadku '') jeżeli FooName będzie równa NULL.

SELECT b.*
  FROM BooTable b
  WHERE b.FooName NOT IN (SELECT ISNULL(FooName,'') FooName FROM FooTable)



Rozwiązania nie są równoważne, ponieważ w przykładzie z ISNULL do zbioru wynikowego podzapytania dołożymy wiersz z wartością ''. Aczkolwiek, daje nam obraz jak kod może się komplikować z powodu występowania wartości NULL.

NULL i JOIN dwa bratanki


Moim największym zarzutem wobec NULL'a jest to, że często wymusza stosowanie pokrętnych konstrukcji z użyciem ISNULL, IS NULL lub COALESCE.  Powoduje to znaczące problemy z utrzymanie kodu. Przykładem może być ewolucja następującego zapytania.

Etap 1)

SELECT b.*
  FROM BooTable b
 INNER JOIN FooTable f
    ON b.FooName = f.FooName

Zapytanie zwraca następujący wynik:

Etap 2) Załużmy, że użytkownik zgłosi programiście, że barkuje mu rekordu (11, Foo 2) i wtedy po zmianie kodu mamy już taki twór.

SELECT b.*
  FROM BooTable b
 INNER JOIN FooTable f
    ON b.FooName = ISNULL(f.FooName,'Foo 2')

Powyższe zapytanie zwraca następujący wyniki:



I tam możemy bez końca, w powyższym zapytaniu mamy tylko dwie tabele. A jak to będzie wyglądało przy np. 7 tabelach i wielu kolumnach w warunku JOIN'a.


Mam nadzieję, że te kilka przykładów zachęci czytelnika do refleksji czy zawsze warto pójść na kompromis i zadeklarować Column1 VARCHAR(20) NULL, czy jednak lepiej być twardym, nie miętkim i wybrać NOT NULL.

Hope this helps.

6 komentarzy:

  1. 1) dodanie "WHERE FooName IS NOT NULL" to chyba nie jest raczej jakies specjalne "przeklenstwo"
    2) Przyklad scenariusza z "NULL i JOIN dwa bratanki" wydaje mi sie troche bezsensu... Po pierwsze łączeniu podlegaja 2 tabele ktorych wspolpraca ze soba nie byla przewidziana a wiec przypadek specjalny i w takich sprawach zawsze moga pojawic sie jakies schody. Poza tym zakladam, ze NULL przy varcharach uzywany jest w sytuacji "Nic nie wprowadzono" wiec żądanie uzytkownika zeby polaczyc te 2 dane majace inne znaczenie jest glupie. Poza tym ;)) wyobraz sobie zebys mial zamiast NULLa cos z "wartości domyślnych, stałych wartości lub pustych ciągów znakowych" to JOIN moglby "pożenić" dane o odmiennym znaczeniu. "Domyslna wartosc" zastepujaca NULL w I tabeli moglaby w 2 tabeli byc dana wprowadzona przez uzytkownika i wtedy wyrzucenie tych danych jako sobie rownych byloby bledem logicznym imho. W przypadku gdy zostal uzyty NULL to mozesz zrealizowac nieleogiczny kaprys uzytkownika ale _świadomie_ co za dane mu prezentujesz:) Mysle, ze w przykladzie z "zycia wzietym", z wdrozenia na poczatku wpisu, NULLe w danych i od razu widoczny blad z tego powodu byl blogoslawienstwem. Gdyby ktos uzyl jakichs "zamiennikow" to moze i raporty nie bylby puste ale "prawidlowosc" wynikow, ktore by te raporty prezentowaly to juz inna broszka. Odkrycie takiego bledu a nie puste zestawy danych to dopiero byloby trudne. A tak zbawienny :) NULL was ostrzegl i mogliscie zadzialac :)

    Moim zdaniem NULLe są spoko tylko trzeba poznac kilka zwiazanych z nimi trikensow ale tak jest ze wszystkim w programowaniu, warto wiedziec co jest pod maską.

    bo co z tego że ktoś powie, że po wykonaniu:
    SELECT COUNT(FooName) FROM FooTable

    wynikiem bedzie 3 podczas gdy jest 2. Czy to wina NULLa czy czyjejś niewiedzy?

    Imho "problem" z NULLami jest taki, ze ich uzycie nie jest intuicyjne. Poczatkujacy "nieogarniają kuwety" ;) w zalapaniu co to wlasciwie jest i ze to "nie taka sobie inna niewidoczna spacja" ;), trzeba wkuć do głowy pewne scenariusze a to jak wiadomo przy zaczynaniu zabawy moze byc trudne/pominiete/przeoczone.

    Mysle, ze gdyby kazdy kto zaczyna z T-SQL mial przy sobie kogos kto mu powie "Uwazaj na NULLe brachu, poczytaj troche tips&tricks o nich" to byloby spoko ;)) A nie tak jak u nas kiedys (do dzisiaj jest to kultowa opowiesc ;)) przyszedl kolejny student na praktyke i zrobil pewna baze. Ktos mu powiedzial zeby pewne pole dal jako NULL. Student sie zawinął a tu po jakimś czasie coś sie nie zgadza... No i dalej analizowac dane, matko co sie dzieje :) Okazało się, że student czesc danych wykomibnowla zebyw prowadzic recznie ale zamiast wciskać w management Studio Ctrl+0 (zeby zrobic NULLa) to wpisywal z reki tekst NULL i myslal ze jest to samo :) Hehe, dobre pol godziny kumpel sie dziwil ze w bazie są NULLe (lookal w edytorze) ale zapytanie nie pokazuje rekordow nim odkryl "geniusza" :DDD Przyklad niewiedzy programisty a nie argument na antyNULL ;)

    Dlatego imho Twoj artykul jest spoko, zwraca uwage na to że NULLe to rzecz specjalna i trzeba je troche zglebic i poznac w odroznieniu od "SELECT * FROM" ;)) ale nie zgadzam sie ze trzeba ich unikac poprzez "sztuczne" zastepniki.

    OdpowiedzUsuń
  2. @bincoder Dzięki za cenne uwagi.

    Zgodzę się, że podstawą do uniknięcia problemów jest zrozumienie tematu i chociaż części zawiłości trój-stanowej logiki NUUL'a.

    Trudno mi się zgodzić, że NULL w przypadku operatora NOT IN pomógł nam w czymś, raczej spowodował nieprzewidziane zachowanie systemu.

    Weź też pod uwagę, że używanie funkcji ISNULL do obsługi jakiś szególnych przypadków złączeń, może mieć też konsekwencje wydajnościowe dla wykonywanego zapytania (np. scan tabeli zamiast seek indeksu).

    Uśmiałem się z historii którą przytoczyłeś (respect dla studenta).

    OdpowiedzUsuń
  3. Oj, tak, przerabialiśmy ten temat. Nie lubię NULLi, bo trzeba je traktować wyjątkowo a wyjątki to coś czego jeszcze bardziej nie lubię :)

    pozdrawiam

    OdpowiedzUsuń
  4. Temat logiki trójwartościowej pojawił się również na egzaminie wstępnym (Introduction to SQL) z Oracle'a. Rozpisanie semantyki NOT IN ... wyjaśnia wszystko i niby jest trywialne, jednak moim zdaniem natrafienie przypadku NOT IN z NULLem pierwszy raz powoduje zaskoczenie większości użytkowników (w tym również mnie to zaskoczyło i potrzebowałem trochę czasu, zanim zrozumiałem dlaczego). NULLe są fajne, ale powodują czasem więcej kłopotów niż pożytku. Czy nie jest tak, że WHERE na kolumnie z NULLem zawsze daje full table scan niezależnie od tego czy są indeksy, na tej kolumnie czy nie?

    OdpowiedzUsuń
  5. @adamm Optymalizator w MS SQL Serverz'e daje radę z kolumnami z NULL'ami, koniec końców są w tych kolumnach inne wartości i istnieją odpowiednie statystyki. Dopóki nie zastosujesz np. jakiejś funkcji w klauzuli WHERE na tej kolumnie to optymalizator powinień użyć indeksu (jeżeli oczywiście inne warunki będą spełnione np. odpowiednia liczność, czy gęstość zbioru).

    OdpowiedzUsuń
  6. SELECT b.*
    FROM BooTable b
    WHERE not exists( SELECT FooName FROM FooTable ft where b.FooName=ft.FooName)

    OdpowiedzUsuń

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.