Pergunta sobre mysql-python, mysql – Por que a palavra-chave MYSQL IN não considera valores NULL

9

Eu estou usando a seguinte consulta:

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

Surpreendentemente, essa instrução não inclui as linhas que possuem o valor Error como NULL. Minha intenção é filtrar apenas as linhas com o valor Error como 'Timeout' (ou) 'Connection Error'. Eu preciso dar uma condição adicional (OU erro é NULL) para recuperar o resultado correto.

Por que o MYSQL está filtrando os resultados com valores NULL? Eu pensei que IN palavra-chave iria retornar um resultado booleano (1/0) e agora eu entendo que algumas palavras-chave MYSQL não retornam valores booleanos, ele pode retornar NULL também .... mas Por que ele está tratando NULL como especial?

Sua resposta

6   a resposta
0

A resposta do @Michael Buen foi a resposta certa para o meu caso, mas deixe-me simplificar o porquê.

@Michael diz em seu post:

Erro não em ('Timeout', 'Erro de conexão');

é semanticamente equivalente a:

Erro <> 'TimeOut' E erro <> 'Erro de conexão'

Regras sobre comparação nula também se aplicam ao IN. Portanto, se o valor de Error for NULL, o banco de dados não poderá tornar a expressão verdadeira.

E em [1] eu encontrei esta frase que confirma seu st mais importante, por entender porque IN falha com NULL. Nas especificações ("especificações") em [1] você irá: "Se um ou ambos os argumentos forem NULL, o resultado da comparação será NULL, exceto para o operador de comparação de igualdade NULL-safe <=>."

Então, sim, a coisa é que, infelizmente, o Mysql se perde em tal caso. Acho que os designers do Mysql não deveriam ter feito isso, porque quando eu comparo 2 a NULL, o Mysql deveria ser capaz de ver que eles são DIFERENTES, e não simplesmente lançar resultados equivocados. Por exemplo, eu fiz:

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

em seguida, lança resultados VAZIOS. MAS. Se eu fizer

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

mostra o resultado certo. Portanto, ao usar o operador IN, você deve filtrar os NULLS. Este não é um comportamento desejado para mim como usuário, mas está documentado nas especificações em [1]. Eu acho que linguagens e tecnologia deveriam ser mais simples, no sentido de que você deveria ser capaz de DEDUZIR sem a necessidade de ler as especificações. E verdadeiramente, 2 é DIFERENTE de NULL, eu deveria ser o encarregado de controlar e cuidar dos erros de um nível mais alto de abstração, mas o MySQL DEVE lançar um resultado FALSE quando se compara NULL com um valor específico.

Referências para as especificações: [1]http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

1

Porque null é indefinido, portanto, null não é igual a null. Você sempre tem que manipular explicitamente null.

1

IN retorna um trivalenteBOOLEAN (que aceitaNULL como um valor).NOT IN retorna a negação trivalente deINe negação deNULL é umNULL.

Imagine que temos uma mesa com todos os números de1 para1,000,000 emid e esta consulta:

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

ou seu equivalente:

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

O predicado retornaTRUE para1 e2 eNULL para todos os outros valores, então1 e2 são retornados.

Em seu oposto:

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

ou

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

, o predicado retornaFALSE para1 e2 eNULL para todos os outros valores, então nada é retornado.

Note que a negação booleana não apenas muda o operador (= para<>), mas o quantificador também (ANY paraALL).

23

Este :

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

é semanticamente equivalente a:

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

Regras sobre comparação nula também se aplicam ao IN. Portanto, se o valor de Error for NULL, o banco de dados não poderá tornar a expressão verdadeira.

Para corrigir, você poderia fazer isso:

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

Ou melhor ainda:

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

Ou melhor ainda:

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

OR não causa curto-circuito, CASE pode de algum modo causar um curto-circuito na sua consulta

Talvez um exemplo concreto possa ilustrar por queNULL NOT IN expression não retorna nada:

Dado este dado: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');

E você faz esta expressão:

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

Isso só produzirá 'oi'.

O NOT IN é traduzido como:

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

NULL <> 'bruce' não pode ser determinado, nem mesmo verdadeiro, nem mesmo falso

NULL <> 'banner' não pode ser determinado, nem mesmo verdadeiro nem mesmo falso

Então, a expressão de valor nulo, efetivamente resolvida para:

can't be determined AND can't bedetermined

De fato, se o seu RDBMS suporta booleano em SELECT (por exemplo, MySQL, Postgresql), você pode ver porque:http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

Isso retorna null.

Isso também retorna null:

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

Dado que você está usandoNOT IN, que é basicamente uma expressão AND.

NULL AND NULL

Resultados para NULL. Então é como se você estivesse fazendo um:http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

Nada será devolvido

acase when atalho é genial mtrbean
Isso é ótimo! ... claro Never Back Down
Eu tentei no ADO.NET, oNULL <> 'Bruce' resolve para System.DBNull, não é 1 nem 0. Ou talvez eu esteja usando o Postgres. Eu acho que isso depende do mecanismo de camada de acesso a dados também. Eu preferiria ter um DAL que mostrasse a mesma coisa que eu posso ver no próprio RDBMS Michael Buen
Você fez um excelente ponto. Você diz no seu post: ---------------- Erro não em ('Timeout', 'Erro de conexão'); é semanticamente equivalente a: Erro <> 'TimeOut' AND Error <> 'Erro de conexão' As regras sobre a comparação nula também se aplicam ao IN. Portanto, se o valor de Error for NULL, o banco de dados não poderá tornar a expressão verdadeira. ----------------- E emdev.mysql.com/doc/refman/5.6/en/type-conversion.html  Eu encontrei esta frase que confirma sua declaração: "Se um ou ambos os argumentos são NULL, o resultado da comparação é NULL, exceto para o operador de comparação de igualdade NULL-safe <=>." David L
0

Desculpe por postar duas vezes no mesmo fórum, mas quero ilustrar outro exemplo:

Concordo com @Wagner Bianchi em [2] neste fórum quando ele diz: << É muito complicado quando se lida com dados e subconsultas >>

Além disso, este não deve ser o comportamento, acho que os designers do Mysql estão enganados quando tomaram esta decisão documentada em [1]. O design deve ser diferente. Deixe-me explicar: você sabe que ao comparar

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

MAS se na lista você tiver pelo menos um NULL então:

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.

Nós não somos os primeiros a ficar confusos com isso. Veja [2]

Referências:

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

[2]http://blogs.minminno.com/google

1

IN devolveNULL se a expressão do lado esquerdo éNULL. A fim de obter oNULL valores, você tem que fazer:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);
Sim, eu mencionei que esta é a questão em si. Eu só queria saber por que esse comportamento estranho? Never Back Down

Perguntas relacionadas