Почему ключевое слово MYSQL IN не учитывает значения NULL

Я использую следующий запрос:

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

Удивительно, но этот оператор не включает в себя строки, имеющие значение Error в качестве NULL. Мое намерение состоит в том, чтобы фильтровать только те строки, в которых значение Error равно «a timeout». (или) «Ошибка соединения». Мне нужно дать дополнительное условие (ИЛИ ошибка равна NULL), чтобы получить правильный результат.

Почему MYSQL отфильтровывает результаты со значениями NULL? Я думал, что ключевое слово IN вернет логический результат (1/0), и теперь я понимаю, что некоторые ключевые слова MYSQL не возвращают логические значения, он также может возвращать NULL .... но почему он обрабатывает NULL как особый?

Ответы на вопрос(6)

IN возвращаетсяNULL если выражение на левой сторонеNULL, Для того, чтобы получитьNULL значения, вы должны сделать:

'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);
 Never Back Down30 мая 2012 г., 07:40
Да, я упоминал, что это сам вопрос. Я просто хотел знать, почему это странное поведение?

но я хочу проиллюстрировать другой пример:

Я согласен с @Wagner Bianchi в [2] на этом форуме, когда он говорит: & Л; & л; Это хитрость при работе с данными и подзапросами & gt; & gt;

Более того, это НЕ должно быть таким поведением, я думаю, что дизайнеры Mysql ошибаются, когда они принимают это решение, документированное в [1]. Дизайн должен быть другим. Позвольте мне объяснить: вы знаете, что при сравнении

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

НО, если в списке есть хотя бы один NULL, тогда:

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.

Мы не первые, кого это смущает. Смотри [2]

Рекомендации:

[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

то ноль не равен нулю. Вы всегда должны явно обрабатывать ноль.

IN возвращает трехвалентныйBOOLEAN (который принимаетNULL как значение).NOT IN возвращает трехвалентное отрицаниеINи отрицаниеNULL этоNULL.

Представьте, что у нас есть таблица со всеми числами из1 в1,000,000 вid и этот запрос:

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

или его эквивалент:

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

Предикат возвращаетсяTRUE за1 а также2 а такжеNULL для всех других значений, так1 а также2 возвращаются.

В противоположность:

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

, или же

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

предикат возвращаетсяFALSE за1 а также2 а такжеNULL для всех других значений, поэтому ничего не возвращается.

Обратите внимание, что логическое отрицание не только меняет оператора (= в<>), но квантификатор тоже (ANY вALL).

Решение Вопроса

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

семантически эквивалентно:

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

Правила о нулевом сравнении также применимы к IN. Поэтому, если значение Error равно NULL, база данных не может сделать выражение истинным.

Чтобы исправить, вы можете сделать это:

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

Или еще лучше:

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

Или еще лучше:

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

OR не закорачивает, CASE может как-то закорачивать ваш запрос

Возможно, конкретный пример мог бы показать, почемуNULL NOT IN expression ничего не возвращает:

Учитывая эти данные: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');

И вы делаете это выражение:

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

Это выведет "привет" только.

NOT IN переводится как:

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

NULL <> 'bruce' не может быть определено, даже не верно, даже не ложно

NULL <> 'banner' не может быть определено, даже не верно, даже не ложно

Таким образом, выражение с нулевым значением эффективно разрешается в

can't be determined AND can't bedetermined

Фактически, если ваша СУБД поддерживает логическое значение в SELECT (например, MySQL, Postgresql), вы можете понять, почему:http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

Это возвращает ноль.

Это также возвращает ноль:

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

Учитывая, что вы используетеNOT IN, который в основном является выражением AND.

NULL AND NULL

Результаты к NULL. Так что вы делаете:http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

Ничего не будет возвращено

 Never Back Down30 мая 2012 г., 07:42
Да, я знаю, что есть обходной путь. Мне просто интересно узнать, почему это странное поведение?
 30 мая 2012 г., 07:47
А такжеIN / NOT IN, семантически эквивалентноV = 'Alpha' OR V = 'Beta', V <> 'Alpha' AND V <> 'Beta' соответственно, следовательно, правила о NULL все еще применяются
 30 мая 2012 г., 09:36
Возможно, неправильный перевод лежит на стороне питона. Я проверю язык, который поддерживает переменную Nullable
 Never Back Down30 мая 2012 г., 09:23
Спасибо, Майкл Буэн ... Я использую python с mysql.python возвращает 0/1 для операторов AND, OR, IN, но Mysql возвращает NULL
 30 мая 2012 г., 07:45
Это не странно, это даже в ANSI SQL. Думайте о NULL как о подстановочном знаке, вы не можете утверждать, что NULL & lt; & gt; & APOS; Привет & APOS ;; иногда NULL должен быть заменен на реальное значение. Вот как я на это смотрю. NULL имеет особое значение в базе данных

Ответ был правильным для моего случая, но позвольте мне упростить причину.

@ Майкл говорит в своем посте:

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

is semantically equivalent to:

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

Rules about null comparison applies to IN too. So if the value of Error is NULL, the database can't make the expression true.

И в [1] я нашел это предложение, которое подтверждает его самое важное утверждение для понимания, почему IN терпит неудачу с NULL. В спецификациях («спецификации») в [1] вы будете: «Если один или оба аргумента равны NULL, результатом сравнения будет NULL, за исключением NULL-safe & lt; = & gt; оператор сравнения равенства. & quot;

Так что да, дело в том, что Mysql теряется в таком случае. Я думаю, что разработчики Mysql не должны были этого делать, потому что когда я сравниваю 2 с NULL, Mysql ДОЛЖЕН видеть, что они РАЗНЫЕ, а не просто выдают ошибочные результаты. Например, я сделал:

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

тогда он бросает пустые результаты. НО. Если я сделаю

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

это показывает правильный результат. Поэтому при использовании оператора IN вы должны отфильтровывать NULL. Это нежелательное поведение для меня как пользователя, но это задокументировано в спецификациях в [1]. Я думаю, что языки и технологии должны быть более простыми, в том смысле, что вы должны иметь возможность УСТАВИТЬ без необходимости читать спецификации. И действительно, 2 отличается от NULL, я должен отвечать за контроль и устранение ошибок более высокого уровня абстракции, но MySQL ДОЛЖЕН выдавать ЛОЖНЫЙ результат при сравнении NULL с конкретным значением.

Ссылки на спецификации: [1]http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

Ваш ответ на вопрос