Pregunta sobre mysql, mysql-python – ¿Por qué la palabra clave MYSQL IN no considera valores NULL?

9

Estoy usando la siguiente consulta:

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

Sorprendentemente, esta declaración no incluye las filas que tienen el valor de Error como NULL. Mi intención es filtrar solo las filas con el valor de Error como 'Tiempo de espera' (o) 'Error de conexión'. Necesito dar una condición adicional (O error es NULL) para recuperar el resultado correcto.

¿Por qué MYSQL filtra los resultados con valores NULL? Pensé que la palabra clave IN devolvería un resultado booleano (1/0) y ahora comprendo que algunas palabras clave MYSQL no devuelven valores booleanos, también podrían devolver NULL ... pero ¿por qué trata NULL como especial?

Tu respuesta

6   la respuesta
1

Debido a que null no está definido, null no es igual a null. Siempre hay que manejar explícitamente el nulo.

23

Esta :

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

es semánticamente equivalente a:

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

Las reglas sobre la comparación nula se aplican también a IN. Entonces, si el valor de Error es NULL, la base de datos no puede hacer que la expresión sea verdadera.

Para arreglarlo, podrías hacer esto:

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

O mejor aún:

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

O más mejor aún:

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

OR no cortocircuita, CASE puede de alguna manera provocar un cortocircuito en su consulta

Quizás un ejemplo concreto podría ilustrar por quéNULL NOT IN expression no devuelve nada:

Teniendo en cuenta estos datos: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');

Y haces esta expresión:

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

Eso dará como resultado 'hola' solamente.

El NOT IN se traduce como:

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

NULL <> 'bruce' No puede ser determinado, ni siquiera verdadero, ni siquiera falso.

NULL <> 'banner' No se puede determinar, ni siquiera es verdad, ni siquiera es falsa.

Así que la expresión de valor nulo, efectivamente resuelto a:

can't be determined AND can't bedetermined

De hecho, si su RDBMS admite booleano en SELECT (por ejemplo, MySQL, Postgresql), puede ver por qué:http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

Eso devuelve nulo.

Esto devuelve nulo también:

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

Dado que estás usandoNOT IN, que es básicamente una expresión AND.

NULL AND NULL

Resultados a NULL. Así que es como si estuvieras haciendo un:http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

Nada sera devuelto

¡Eso es genial! ... seguro Never Back Down
lacase when atajo es genio mtrbean
Gracias, Michael Buen ... Estoy usando python con mysql.python devuelve 0/1 para las instrucciones AND, OR, IN pero Mysql devuelve NULL Never Back Down
Tal vez la mala traducción se encuentra en el lado de python. Revisaré el lenguaje que soporta la variable anulable Michael Buen
0

Lo siento por publicar dos veces en el mismo foro, pero quiero ilustrar otro ejemplo:

Estoy de acuerdo con @Wagner Bianchi en [2] en este foro cuando dice: << Es un gran truco cuando se trata de datos y subconsultas >>

Además, este NO debe ser el comportamiento, creo que los diseñadores de Mysql están equivocados cuando tomaron esta decisión documentada en [1]. El diseño debe ser diferente. Déjame explicarte: sabes que al 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)

PERO si en la lista tienes al menos un NULL entonces:

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.

No somos los primeros en confundirnos por esto. Ver [2]

Referencias:

[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

1

IN devolucionesNULL Si la expresión del lado izquierdo esNULL. Para obtener elNULL Valores, tienes que hacer:

'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);
Sí, he mencionado que esta es la pregunta en sí misma. Solo quería saber por qué este extraño comportamiento. Never Back Down
0

pero déjeme simplificar por qué.

@Michael dice en su post:

Error no en ('Tiempo de espera', 'Error de conexión');

es semánticamente equivalente a:

Error <> 'TimeOut' Y error <> 'Error de conexión'

Las reglas sobre la comparación nula se aplican también a IN. Entonces, si el valor de Error es NULL, la base de datos no puede hacer que la expresión sea verdadera.

Y en [1], encontré esta frase que confirma su más importante énfasis para entender por qué IN falla con NULL. En las especificaciones ("especificaciones") en [1], usted: "Si uno o ambos argumentos son NULL, el resultado de la comparación es NULL, excepto para el operador de comparación de igualdad <=> seguro para NULL".

Entonces, sí, la cosa es que, lamentablemente, Mysql se pierde en tal caso. Creo que los diseñadores de Mysql no deberían haber hecho esto, porque cuando comparo 2 con NULL, Mysql DEBE poder ver que son DIFERENTES, y no simplemente arrojando resultados erróneos. Por ejemplo, yo hice:

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

Luego arroja resultados VACÍOS. PERO. Si lo hago

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

Muestra el resultado correcto. Así que cuando se usa el operador IN, debe filtrar las NULLS. Este no es un comportamiento deseado para mí como usuario, pero está documentado en las especificaciones en [1]. Creo que los idiomas y la tecnología deberían ser más simples, en el sentido de que deberían poder DEDUCIR sin necesidad de leer las especificaciones. Y realmente, 2 es DIFERENTE de NULL, yo debería ser el encargado de controlar y encargarse de los errores de un nivel más alto de abstracción, pero MySQL DEBE arrojar un resultado FALSO al comparar NULL con un valor específico.

Referencias para las especificaciones: [1]http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

1

IN devuelve un trivalenteBOOLEAN (que aceptaNULL como un valor).NOT IN devuelve la negación trivalente deIN, y la negacion deNULL es unNULL.

Imagina que tenemos una tabla con todos los números de1 a1,000,000 enid y esta consulta:

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

o su equivalente:

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

El predicado vuelve.TRUE para1 y2 yNULL para todos los demás valores, por lo que1 y2 son devueltos.

En su opositor:

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

o

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

, el predicado vuelveFALSE para1 y2 yNULL para todos los demás valores, por lo que no se devuelve nada.

Tenga en cuenta que la negación booleana no solo cambia el operador (= a<>), pero el cuantificador también (ANY aALL).

Preguntas relacionadas