14/10/2010

Dziw nad dziwy

Home

Dzisiaj ponownie napiszę o błędzie z serii UFO. Przyjrzyjmy sie przez chwilę dwóm poniższym zapytaniom:

Zapytanie 1
select * 
from tabela1 t1
where exists ( select 1 from tabela2 t2, tabela3 t3
               where t2.kolumna = t3.kolumna and
                     t3.kolumna = t1.kolumna )


Zapytanie 2
select * 
from tabela1 t1
where exists ( select 1 from tabela2 t2, tabela3 t3
               where t2.kolumna = t3.kolumna and
                     t3.kolumna = t1.kolumna ) and 
t1.data = '14-10-2010'


Każdy zapewne zauważył, że drugie zapytanie różni się od pierwszego tylko dodatkowym warunkiem w zapytaniu zewnętrznym. Innymi słowy zapytanie 2 zawęża wynik jaki otrzymany w zapytaniu 1. A teraz pytanie:

Czy zapytanie 2 może zwrócić wiersze, które nie zostaną zwrócone przez zapytanie 1?

Część z was zapewne stuka się teraz w głowę i zastanawia się po co pytam o rzeczy oczywiste. Odpowiedź jest oczywiście oczywista i brzmi NIE ale, o zgrozo, nie dla każdej bazy danych. Niestety, w przypadku Informix'a w wersji 10 w ''sprzyjających'' warunkach odpowiedź będzie brzmiała TAK. Tak wiem, ja też przecierałem oczy ze zdziwienia kiedy koleżanka z pracy pokazała mi wynik dwóch powyższych zapytań.

Niestety dokładnych przyczyn problemu mogę tylko podejrzewać ale znam jego rozwiązanie. Otóż okazało się, że problem ustąpił po przeliczeniu statystyk przy pomocy poniższego polecenia:
UPDATE STATISTICS LOW 
FOR TABLE table_name (column1, column2...) DROP DISTRIBUTIONS
Polecenie to oblicza statystyki na tzw. niskim poziomie czyli przy założeniu, że rozkład wartości w danych kolumnach jest równomierny. Dodatkowo usuwa rozkłady wartości wyznaczane przy obliczaniu statystyk na poziomie średnim lub wysokim. Tak czy inaczej to ewidentny błąd w optymalizatorze zapytań, mechanizmie indeksów czy też statystyk, który obniża zaufanie do bazy danych.

Na koniec podziękowania dla Agnieszki, która pokazała mi ten błąd oraz dla Marcina, który znalazł rozwiązanie.

4 comments:

Komodo said...

Czy na jednej z kolumn (t1.kolumna, t2.kolumna, t3.kolumna) nie było przypadkiem założonego indeksu mimo, że ta kolumna dopuszcza wartości NULL? W Oraclu jest podobnie, przy czym odpowiednio projektując struktury danych da się przed tym zabezpieczyć.

Michał Komorowski said...

Tak, jedna z kolumn dopuszcza wartości NULL i ma założony indeks, przy czym w praktyce w momencie zadawania pytania w kolumnie nie ma wartości NULL.

To ciekawe co piszesz o Oracle'u. Przepraszam, że się dopytam ale chcę być pewny, że dobrze Cię zrozumiałem. Czy jesteś pewny, że na tej bazie danych również można uzyskać taką anomalię?

Dodam jeszcze, że problem ustępuje również po przepisaniu tego zapytaniu tak aby nie używało operatora EXISTS.

Komodo said...

Jeżeli na kolumnie X, która dopuszcza NULL jest założony indeks i zadamy zapytanie Where X = :param (gdzie :param to np. 5), to takie zapytanie zostanie zapisane w cache z planem zapytania, z użyciem indeksu na kolumnie X. Kolejne takie same zapytanie, gdzie pod :param zostanie podstawiona wartość NULL zwróci wynik 0 wierszy, niezależnie od zawartości tabeli. Wynika to z tego, że baza danych w ogóle nie zajrzy do "danych", tzn do tabeli. Z planu zapytania wynika, że wystarczy odczytać wartości z indeksu - tylko, że w indeksach nie są przechowywane Nulle!

Dzisiaj mieliśmy jeszcze większy problem z Oraclem. Zapytanie select x from y where x = 5 zwraca wartosci x = 0. Jeszcze nie wiemy z czego wynika problem, ale baza wykonuje "index join rowid = rowid" i zwraca wyniki spod złego adresu rowid.

Michał Komorowski said...

Nie podoba mi się podstawianie wartości NULL pod :param Do sprawdzania czy coś jest lub nie NULLem służą operatory IS NULL oraz IS NOT NULL. Porównanie '= NULL' zwróci wartość nieznaną nawet jeżeli porównujemy dwa NULLe i dlatego zapytanie o jakim wspomniałeś zwraca zero wierszy. Nie jest więc to nic dziwnego chyba, że miałeś włączone zachowanie, w którym porównanie NULL = NULL daje wynik TRUE (ANSI NULLS OFF dla SqlServer'a)?

Post a Comment