Pytanie w sprawie mysql-python, mysql – Dlaczego słowo kluczowe MYSQL IN nie uwzględnia wartości NULL

9

Używam następującego zapytania:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');

Zaskakujące jest, że instrukcja ta nie obejmuje wierszy o wartości błędu NULL. Zamierzam filtrować tylko wiersze z wartością błędu „Limit czasu” (lub) „Błąd połączenia”. Muszę podać dodatkowy warunek (błąd OR ma wartość NULL), aby uzyskać poprawny wynik.

Dlaczego MYSQL odfiltrowuje wyniki z wartościami NULL? Myślałem, że słowo kluczowe IN zwróci wynik boolowski (1/0), a teraz rozumiem, że niektóre słowa kluczowe MYSQL nie zwracają wartości boolowskich, może też zwrócić NULL .... ale dlaczego traktuje NULL jako wyjątkowe?

Twoja odpowiedź

6   odpowiedzi
1

IN zwraca trójwartościowąBOOLEAN (który akceptujeNULL jako wartość).NOT IN zwraca trójwartościową negacjęINi negacjaNULL jestNULL.

Wyobraź sobie, że mamy tabelę z wszystkimi liczbami1 do1,000,000 wid i to zapytanie:

SELECT  *
FROM    mytable
WHERE   id IN (1, 2, NULL)

lub jego odpowiednik:

SELECT  *
FROM    mytable
WHERE   id = ANY
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

Predykat powracaTRUE dla1 i2 iNULL dla wszystkich innych wartości, tak1 i2 są zwracane.

W jego przeciwieństwie:

SELECT  *
FROM    mytable
WHERE   id NOT IN (1, 2, NULL)

lub

SELECT  *
FROM    mytable
WHERE   id <> ALL
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

, predykat powracaFALSE dla1 i2 iNULL dla wszystkich innych wartości, więc nic nie jest zwracane.

Zauważ, że negacja boolowska nie tylko zmienia operatora (= do<>), ale także kwantyfikator (ANY doALL).

23

To:

Error not in ('Timeout','Connection Error');

jest semantycznie równoważny:

Error <> 'TimeOut' AND Error <> 'Connection Error'

Reguły dotyczące porównania zerowego dotyczą również IN. Jeśli więc wartość błędu wynosi NULL, baza danych nie może uczynić tego wyrażenia prawdziwym.

Aby to naprawić, możesz to zrobić:

COALESCE(Error,'') not in ('Timeout','Connection Error');

Lub jeszcze lepiej:

Error IS NULL OR Error not in ('Timeout','Connection Error');

Albo jeszcze lepiej:

 CASE WHEN Error IS NULL THEN 1
 ELSE Error not in ('Timeout','Connection Error') THEN 1
 END = 1

OR nie powoduje zwarcia, CASE może w jakiś sposób zwierać zapytanie

Być może konkretny przykład może zilustrować dlaczegoNULL NOT IN expression nic nie zwraca:

Biorąc pod uwagę te dane:http://www.sqlfiddle.com/#!2/0d5da/11

create table tbl
(
  msg varchar(100) null,
  description varchar(100) not null
  );


insert into tbl values
('hi', 'greet'),
(null, 'nothing');

I robisz to wyrażenie:

select 'hulk' as x, msg, description 
from tbl where msg not in ('bruce','banner');

To wyświetli tylko „cześć”.

NOT IN jest tłumaczone jako:

select 'hulk' as x, msg, description 
from tbl where msg <> 'bruce' and msg <> 'banner';

NULL <> 'bruce' nie można określić, nawet nie prawda, nawet fałsz

NULL <> 'banner' nie można ustalić, nawet nie prawda nawet fałszywa

Tak więc wyrażenie wartości zerowej, skutecznie rozwiązane do:

can't be determined AND can't bedetermined

W rzeczywistości, jeśli twój RDBMS obsługuje boolean na SELECT (np. MySQL, Postgresql), możesz zobaczyć, dlaczego:http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

To zwraca wartość null.

Zwraca również wartość null:

select null <> 'Bruce' and null <> 'Banner'

Biorąc pod uwagę, że używaszNOT IN, które jest w zasadzie wyrażeniem AND.

NULL AND NULL

Wyniki do NULL. Więc to tak, jakbyś robił:http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

Nic nie zostanie zwrócone

To świetnie! ... na pewno Never Back Down
Próbowałem na ADO.NET, theNULL <> 'Bruce' rozstrzyga system.DBNull, nie ma ani 1, ani 0. A może korzystam z Postgres. Myślę, że zależy to również od mechanizmu warstwy dostępu do danych. Wolałbym mieć DAL, który pokazuje to samo, co to, co widzę w samym RDBMS Michael Buen
Może błędne tłumaczenie leży po stronie pytona. Sprawdzę język, który obsługuje zmienną pustą Michael Buen
case when skrót jest geniuszem mtrbean
1

Ponieważ wartość null jest niezdefiniowana, wartość null nie jest równa null. Zawsze musisz jawnie obsługiwać wartość null.

1

IN zwracaNULL jeśli wyrażenie po lewej stronie jestNULL. Aby zdobyćNULL wartości, musisz zrobić:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);
Tak, wspomniałem, że jest to samo pytanie. Chciałem tylko wiedzieć, dlaczego to dziwne zachowanie? Never Back Down
0

Przepraszamy za dwa razy publikowanie na tym samym forum, ale chcę zilustrować inny przykład:

Zgadzam się z @Wagner Bianchi w [2] na tym forum, kiedy mówi: << To bardzo podstęp, gdy mamy do czynienia z danymi i podzapytaniami >>

Co więcej, NIE powinno to być zachowanie, myślę, że projektanci Mysql są w błędzie, gdy podjęli tę decyzję udokumentowaną w [1]. Projekt powinien być inny. Pozwól mi wyjaśnić: wiesz o tym, porównując

select (2) not in (1, 4, 3);
    you will get:
        +----------------------+
        | (2) not in (1, 4, 3) |
        +----------------------+
        |                    1 |
        +----------------------+
        1 row in set (0.00 sec)

ALE jeśli na liście masz co najmniej jeden NULL to:

select (2) not in (1, NULL, 3);
    throws:
        +-------------------------+
        | (2) not in (1, NULL, 3) |
        +-------------------------+
        |                    NULL |
        +-------------------------+
        1 row in set (0.00 sec)
    This is pretty absurd.

Nie jesteśmy pierwszymi, którzy są tym zdezorientowani. Zobacz [2]

Referencje:

[1]http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

[2]http://blog.9minutesnooze.com/sql-not-in-subquery-null/comment-page-1/#comment-86954

0

Odpowiedź @Michaela Buena była właściwą odpowiedzią na mój przypadek, ale pozwólcie mi uprościć dlaczego.

@Michael mówi w swoim poście:

Błąd nie występuje („Limit czasu”, „Błąd połączenia”);

jest semantycznie równoważny:

Błąd <> „TimeOut” I błąd <> „Błąd połączenia”

Reguły dotyczące porównania zerowego dotyczą również IN. Jeśli więc wartość błędu wynosi NULL, baza danych nie może uczynić tego wyrażenia prawdziwym.

I w [1] znalazłem to zdanie, które potwierdza jego najważniejszy punkt wyjścia do zrozumienia, dlaczego IN zawodzi z NULL. W specyfikacjach („specyfikacjach”) w [1] będziesz: „Jeśli jeden lub oba argumenty mają wartość NULL, wynikiem porównania jest NULL, z wyjątkiem NULL-bezpiecznego <=> operatora porównania równości.”

Więc tak, niestety Mysql gubi się w takim przypadku. Myślę, że projektanci Mysql nie powinni byli tego robić, ponieważ gdy porównuję 2 do NULL, Mysql POWINIEN być w stanie zobaczyć, że są RÓŻNE, i nie po prostu rzucają błędne wyniki. Na przykład:

select id from TABLE where id not in (COLUMN WITH NULLS);

następnie rzuca wyniki PUSTE. ALE. Jeśli zrobię

select id from TABLE where id not in (COLUMN WITH OUT NULLS);

pokazuje właściwy wynik. Więc gdy używasz operatora IN, musisz odfiltrować NULLS. To nie jest pożądane zachowanie dla mnie jako użytkownika, ale jest udokumentowane w specyfikacji w [1]. Uważam, że języki i technologia powinny być prostsze, w tym sensie, że powinieneś być w stanie ZMNIEJSZYĆ bez konieczności czytania specyfikacji. I rzeczywiście, 2 jest RÓŻNE od NULL, powinienem być odpowiedzialny za kontrolowanie i dbanie o błędy na wyższym poziomie abstrakcji, ale MySQL POWINIEN rzucać FAŁSZ, porównując NULL z określoną wartością.

Odniesienia do specyfikacji: [1]http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

Powiązane pytania