Bug istnieje, oto dowód

https://xpil.eu/p8y 📋

Dziś pchełka SQL o przewrotnym tytule, dzięki któremu mam cichą nadzieję ściągnąć tu jakiegoś wojującego... no nie wiem właściwie kogo. W każdym razie rzecz będzie o ciekawym i bardzo mało znanym błędzie SQL Servera, na który miałem niedawno okazję się nadziać (na szczęście bezboleśnie).

Jak powszechnie wiadomo^{[citation needed]}, jedną z bardziej wydajnych metod na sprawdzenie spójności danych jest ich haszowanie za pomocą funkcji skrótu.

O tym, czym jest funkcja skrótu oraz haszowanie, pisałem już kilka razy, na przykład tutaj: http://xpil.eu/1oRtY

Dziś przyjrzymy się pewnemu konkretnemu przypadkowi.

Często jest tak, że chcemy zaktualizować jakieś dane w tabeli, ale tylko pod warunkiem, że się zmieniły. Nie ma sensu nadpisywać miliona rekordów tymi samymi wartościami, jeżeli zmianie uległo zaledwie piętnaście czy pięćdziesiąt.

A skąd wiadomo które rekordy się zmieniły?

Oczywiście można stosować zaawansowane metody typu analiza logów transakcyjnych serwera albo triggery na UPDATE, ale załóżmy na chwilę, że potrzebujemy czegoś prostego.

Najprościej byłoby oczywiście porównać kolumny jedna po drugiej, ale to jest rozwiązanie nieeleganckie, upierdliwe i błędogenne.

Eleganciej będzie wyliczyć skrót kolumn w każdym rekordzie i zapisać go w osobnej kolumnie. A następnie - podczas aktualizacji danych - wyliczyć skrót "nowych" danych dla każdego rekordu i sprawdzić, czy jego wartość jest taka sama jak oryginału, czy nie.

Czy musimy taki skrót wyliczać każdorazowo "ręcznie"?

Nie. Tabela dopuszcza tworzenie tzw. kolumn wyliczanych, a więc takich, których wartości w każdym rekordzie będą automatycznie aktualizowane przy wstawianiu a także każdej modyfikacji rekordu.

Dodatkowo taka kolumna wyliczana ma opcję "persistence", której włączenie sprawi, że wyliczona wartość zostanie fizycznie zapisana w tabeli (a więc nie będzie trzeba jej wyliczać przy każdym zapytaniu SELECT, tylko raz, przy INSERT lub UPDATE).

Wyliczenie odbywa się na podstawie zadanej przez użytkownika formuły. Na przykład: mamy kolumny IMIE oraz NAZWISKO i na ich podstawie możemy stworzyć kolumnę wyliczaną IMIE_NAZWISKO jako [IMIE] + ' ' + [NAZWISKO]. Dzięki temu rekord z imieniem i nazwiskiem "Adam", "Kowalski" będzie miał dodatkowo wartość "Adam Kowalski" w tej wyliczanej kolumnie.

Jedynym wymaganym warunkiem jest, aby funkcja zwracająca wartość w kolumnie wyliczanej była funkcją deterministyczną. A więc nie dopuszczamy żadnych elementów, które mogą dawać inne wyniki przy kolejnych wywołaniach. Odpada GETDATE(), RANDOM() czy NEWID().

No dobra. A co z funkcją haszującą: jest ona deterministyczna, czy nie jest?

Na chłopski rozum jest. W końcu wyliczenie sumy kontrolnej dla tych samych danych wejściowych zawsze zwróci tę samą wartość na wyjściu.

Spróbujmy więc utworzyć sobie taką tabelę i sprawdźmy, czy uda się dodać automatycznie wyliczaną kolumnę z sumą kontrolną:

CREATE TABLE dbo.jakas_tabela
  (ID INT IDENTITY(1, 1) NOT NULL
 , JAKIS_TEKST NVARCHAR(255) NULL
 , JAKIS_INNY_TEKST NVARCHAR(127) NULL
 , JAKAS_LICZBA INT NULL
 , JAKAS_DATA DATETIME NULL
 , CONSTRAINT PK_jakas_tabela PRIMARY KEY CLUSTERED(ID)
  )

Na razie mamy "zwykłą" tabelę, bez żadnych kolumn wyliczanych. Wstawmy do niej teraz parę rekordów:

TRUNCATE TABLE dbo.jakas_tabela

INSERT INTO dbo.jakas_tabela
VALUES
('blabla', 'blablabla12', 314, '12-Jan-2017 12:34:56')
, ('blabla2', 'blablabla13', 3141, '13-Jan-2017 12:35:56')
, ('blabla3', 'blablabla14', 31415, '14-Jan-2017 12:36:56')
, ('blabla4', 'blablabla15', 314159, '15-Jan-2017 12:37:56')
, ('blabla5', 'blablabla16', 2, '16-Jan-2017 12:38:56')
, ('blabla6', 'blablabla17', 27, '17-Jan-2017 12:39:56')
, ('blabla7', 'blablabla18', 278, '18-Jan-2017 12:34:51')

Wreszcie utwórzmy kolumnę wyliczaną, która wyliczy hasz kryptograficzny każdego rekordu, używając algorytmu SHA256:

ALTER TABLE dbo.jakas_tabela
ADD HASZ AS
      (HASHBYTES(
       'SHA2_256'
     , ((JAKIS_TEKST + JAKIS_INNY_TEKST) + CONVERT(VARCHAR(MAX), JAKAS_LICZBA))
       + CONVERT(VARCHAR(MAX), JAKAS_DATA, (112))))

Czy wszystko gra?

Nie do końca. Stała 112 (trzeci parametr funkcji CONVERT) obcina część ułamkową z daty/godziny, pozostawiając samą datę. Tracimy informację. Zamiast tego wpiszmy tam 113, czyli format zachowujący godzinę:

ALTER TABLE dbo.jakas_tabela DROP COLUMN hasz

ALTER TABLE dbo.jakas_tabela
ADD HASZ AS
      (HASHBYTES(
       'SHA2_256'
     , ((JAKIS_TEKST + JAKIS_INNY_TEKST) + CONVERT(VARCHAR(MAX), JAKAS_LICZBA))
       + CONVERT(VARCHAR(MAX), JAKAS_DATA, 113))) PERSISTED

I co się okazuje?

Otóż okazuje się, że zamiast komunikatu o sukcesie dostajemy błąd:

Msg 4936, Level 16, State 1, Line 3
Computed column 'HASZ' in table 'jakas_tabela' cannot be persisted because the column is non-deterministic.

Co?

Przecież funkcja HASHBYTES jest deterministyczna. Zmieniliśmy tylko sposób formatowania daty, nic ponadto! A jednak w jakiś tajemniczy sposób funkcja HASHBYTES z deterministycznej przeobraziła się nagle w niedeterministyczną (uff, dużo sylab).

I tu ujawnia się Bug (ale nie Wszechmogący ani nawet nie taki wpływający do Zalewu Zegrzyńskiego) czyli błąd oprogramowania: SQL Server "nie lubi", jeżeli próbujemy utworzyć kolumnę wyliczaną z opcją PERSISTED, której formuła zawiera konwersję daty na tekst zawierający spacje

Sprawdźmy więc taki format daty, który nie obetnie nam części ułamkowej (czyli zachowa godzinę), a zarazem nie zawiera spacji:

ALTER TABLE dbo.jakas_tabela
ADD HASZ AS
      (HASHBYTES(
       'SHA2_256'
     , ((JAKIS_TEKST + JAKIS_INNY_TEKST) + CONVERT(VARCHAR(MAX), JAKAS_LICZBA))
       + CONVERT(VARCHAR(MAX), JAKAS_DATA, 126))) PERSISTED

(format nr 126 jest postaci: 2017-01-02T12:34:56 - jak widać żadnych spacji tam nie ma)

Powyższe zapytanie wykona się bezbłędnie, w wyniku dostaniemy ładnie wyliczone hasze wszystkich rekordów:

A więc właśnie udowodniliśmy, że istnieje Bug.

Hallelujah!

https://xpil.eu/p8y 📋